[IAM/RDS] rds-db:connect authentication to Postgresql with ${aws:username} in the IAM policy

The approach you're trying to use with ${aws:username} in the IAM policy for RDS IAM authentication is not supported as you've described. The RDS IAM authentication system does not interpret or extrapolate IAM policy variables like ${aws:username} when generating the authentication token.

Instead, you need to create individual IAM policies for each user or use a more specific approach. Here's how you can set up individual users to connect to your RDS PostgreSQL database using IAM authentication:

1. Create individual database users in your PostgreSQL database for each IAM user. You can do this by connecting to the database as a master user and executing:

CREATE USER iam_user_name; GRANT rds_iam TO iam_user_name;

Replace iam_user_name with the actual IAM username.

2. Create an IAM policy for each user with their specific database username:

{ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": "rds-db:connect", "Resource": "arn:aws:rds-db:us-east-1:1234567890:dbuser:cluster-ABCVDEFGHIJKLMNOPQRSTUVWXYZ/iam_user_name" } ] }

Again, replace iam_user_name with the actual IAM username.

3. Attach this policy to the corresponding IAM user or role.

This approach ensures that each IAM user can only connect as their specific database user. While it requires more setup, it provides the individual access you're looking for.

If you need a more dynamic solution, you might need to consider using a custom authentication system or a proxy that can interpret the IAM username and map it to the appropriate database user.
Sources
Creating and using an IAM policy for IAM database access - Amazon Relational Database Service
Creating a database account using IAM authentication - Amazon Relational Database Service

If you're using IAM Identity Center or another SAML IdP, try ${saml:sub}. See https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_providers_saml.html#CreatingSAML-userid for more info.