The IAM authentication failed for the role postgres. Check the IAM token for this role and try again

Question

I'm facing a hard time connecting Python Lambdas to RDS proxy.

I have rest api that has a few Javascript and python lambdas and I manage and deploy everything using CDK. I made sure that the lambdas can connect to the RDS proxy and handled all the roles and permissions.

In both Javascript and Python I generate an auth token to be used as a password for IAM authentication with the RDS proxy.

The problem is that Python Lambdas always throw this error:

The IAM authentication failed for the role postgres. Check the IAM token for this role and try again.

while Javascript doesn't and connect to the proxy.

I'm using psycopg2 with sqlalchemy in Python and the following is how I create the db engine.

session = boto3.Session( 
aws_access_key_id=os.environ.get('AWS_ACCESS_KEY_ID'), aws_secret_access_key=os.environ.get('AWS_SECRET_ACCESS_KEY'),
            region_name=region,
        )

client = session.client('rds')

token = client.generate_db_auth_token(host, port, user_name, region)

_engine = create_engine('postgresql://{user}:{password}@{host}:{port}/{db}'.format(
            user=user_name,
            host=host,
            port=port,
            db=db_name,
            password=token
        ), connect_args={'sslmode': 'require'},)

The role attached to the lambda:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": "rds-db:connect",
            "Resource": "arn:aws:rds-db:us-east-1:xxxxxxxx:dbuser:prx-xxxxxxxxxx/postgres"
        }
    ]
}

Can anybody tell me what I'm missing here?

Answer 1

I had a very similar issue. Assuming the lambda function can assume the role and all the other IAM policies are in place, here are a few things to check (in no particular order).

1 - SQLModel vs SQLAlchemy

I was able to use sqlalchemy to create_engine(url) where the token was used a the password in the url string. However, the same url did not work when using SQLModel create_engine(url).I had to to:

def get_connection():
   token = boto3.generate_db_token(DBHostname=host, DBUsername=dbuser, Region='us-east-1', Port=5432)
   return psycopg2.connect(host=host, port=5432, database=dbname, user=dbuser, password=token)

and then create the database engine with:

url = f'postgresql://{dbuser}@{host}:5432/{dbname}?sslmode=require'
database = sqlmodel.create_engine(url, creator=get_connection)

This resolved my IAM credentials issue.

2 - Secrets and KMS keys

Ensure whatever role you've assigned to the proxy has access to the db users (e.g. "postgres" or "my_user_1") username and passwords stored as separate secrets in aws secrets manager. The secrets should be encrypted, so the same role has to be able to use the key to decrypt.

3 - Dont use admin ("postgres") user

From your rds-connect policy it looks like you are trying to log in as user "postgres", which is the default administrator username for new postgres instance. Even if that's what you want, I don't think that will work.

1. Create a new read-write role and another role with login on your actual RDS instance (not the proxy) aws blog on postgres roles

CREATE ROLE my_user_1 WITH LOGIN PASSWORD 'my_secret_for_secretsmanager'

2. Modify your `rds-db:connect' policy to include that user

{
    "Version": "2012-10-17",
    "Statement":
    [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": "rds-db:connect",
            "Resource":
            [
                "arn:aws:rds-db:us-east-1:xxxxxxxx:dbuser:prx-xxxxxxxxxx/postgres",
                "arn:aws:rds-db:us-east-1:xxxxxxxx:dbuser:prx-xxxxxxxxxx/my_user_1"
            ]
        }
    ]
}

3. Use 'my_user_1' in your application code to generate the token and create the database engine

4 - VPC issues

The RDS instance and the RDS proxy have to be in the same VPC and use the same subnets. And the instance must use a security group (sg-instance) that accepts TCP traffic on port 5432 with a source security group used by proxy (sg-proxy). Then sg-proxy should allow all incoming traffic and have restricted outgoing traffic to TCP on port 5432.

Furthermore, your lambda has to have the AWSLambdaVPCAccessExecutionRole (arn:aws:iam::aws:policy/service-role/AWSLambdaVPCAccessExecutionRole)

Finally, your lambda has to be assigned to the same VPC as your instance and proxy.

NOTE on debugging You cannot connect to your proxy from the public internet. This is not obvious when you start using proxy, but it is the case aws faq. Just be sure you're code is running from inside the vpc. I think you can use the SAM CLI "https://docs.aws.amazon.com/serverless-application-model/latest/developerguide/serverless-sam-cli-install.html" or log onto a free-tier ec2 with all the requisite dependencies.