Bug: Unable to connect to RDS Aurora Serverless V2 with IAM authentication using role with only rds-db:connect permission

Question

When I attempt to connect to an RDS Aurora Serverless V2 instance using IAM authentication, I receive an error during client authentication:

"pg_hba.conf rejects connection for host "***.***.***.***", user "***", database "***", no encryption" (host, user, and database names redacted)

I tried both on the shell by fetching the password using AWS CLI with the aws rds generate-db-auth-token command as well using Python by fetching the password using Python's boto3 library with boto3.client("rds").generate_db_auth_token(...).

If I add the AdministratorAccess AWS managed IAM policy to the role that I'm using, then I am able to successfully log in to the database. But if I remove AdministratorAccess then I am not able to log in. In both cases the role has the "rds-db:connect" permission with the appropriate db resources. If I update the role to "rds-db:*" with resource "*" I still receive the error.

To try to learn what it is about AdministratorAccess that makes it work, I tried instead granting all of kms:*, sts:*, "iam:*", "rds:*", "rds-data:*", and "acm:*, with resource: "*", thinking that perhaps there was some undocumented IAM permission that was needed, but that didn't fix the issue.

Accepted Answer

I found the answer in this post: https://repost.aws/knowledge-center/aurora-postgresql-connect-iam

It turns out that for Aurora PostgreSQL the resource id in the IAM policy needs to be the rds cluster id, not the rds instance id. In contrast, for RDS PostgreSQL the resource id needs to be the db instance resource id. And my policy was using the instance id.

I wish the generate-auth-token request would fail immediately with an IAM error – that would have helped pinpoint the problem much faster. Instead, the generate-auth-token request still succeeded, but the password that was generated wouldn't allow me to connect. And the "pg_hba.conf rejects the connection ... no encryption" error message is misleading as well.

Answer

In my environment, I was able to access it using the following IAM policy.  
The configuration is based on the following document.  
https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/UsingWithRDS.IAMDBAuth.IAMPolicy.html  
```
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "rds-db:connect"
            ],
            "Resource": [
                "arn:aws:rds-db:region:account-id:dbuser:*/*"
            ]
        }
    ]
}
```

Bug: Unable to connect to RDS Aurora Serverless V2 with IAM authentication using role with only rds-db:connect permission

Relevant content