RDS IAM Auth over SSM SessionManager port forwarding

I don't see why the authentication part wouldn't work. If you generate the authentication token with the real name of the RDS cluster/instance and connect to it with a database client with localhost as the host name for the connect operation, I think it should work.

However, if you're using a TLS-encrypted connection to the database and have told the database client to verify that the names on the TLS certificate match the hostname you're connecting to, that would cause your connection attempt to fail, regardless of authentication method, because "localhost" wouldn't match the "rds{id}.123456789012.{region}.rds.amazonaws.com" style name on the certificate presented by RDS.

The examples in AWS's documentation with IAM authentication include enabling TLS encryption with full certificate validation, but your attempts to connect with a static username and password might not include those parameters, possibly not enabling TLS at all, and that's why you'd only be encountering the issue with IAM authentication.

Since you have an end-to-end-encrypted and authenticated connection to your EC2 instance running the SSM Agent, located in a VPC that you control, and you're connecting to an RDS database under your control in that same or connected VPC that you own, you don't need to rely on the cryptographic signature on the certificate to confirm whether you're connecting to the legitimate database or a bad actor intercepting your traffic. You can and should continue to use TLS encryption for the connection, but I suggest you disable certificate validation in your SQL client.

For example, in the mysql CLI tool, --sslmode=VERIFY_CA should instruct the client to check that the certificate is valid but ignore whether the name on the certificate matches the hostname being connected to. --sslmode=VERIFY_IDENTITY would check both the validity of the certificate and that the name matches, which would lead to the error. https://dev.mysql.com/doc/refman/8.4/en/using-encrypted-connections.html

For PostgreSQL, the similar parameters are sslmode=verify-ca and sslmode=verify-full, respectively. https://www.postgresql.org/docs/current/libpq-ssl.html#LIBQ-SSL-CERTIFICATES