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 also used CDK to deploy, so all permissions were set correctly. It was just the token itself.

What helped for me was enabling enhanced logging on the proxy. There it gave me a better explanation of what was wrong with the IAM token. (In my case I did not correctly provide the region to my signing function. It was undefined and I thought it would pick the region of my function by default.).

Answer 2

I had the same issue. Looking at the sqlalchemy documentation about database urls, it says that special characters such as @ signs need to be escaped in passwords. I suspect that this is the root cause of this issue as the rds token might contain such characters and they are not escaped when the engine is created using the url assembled as a string. I would recommend creating a URL object instead of a string and feed that to the engine as it will handle the escaping of the characters in the token for you. Example from the sqlalchemy docs:

from sqlalchemy import URL, create_engine

url_object = URL.create(
    "postgresql+pg8000",
    username="dbuser",
    password="kx@jj5/g",  # plain (unescaped) text
    host="pghost10",
    database="appdb",
)
engine = create_engine(url_object)