IAM authentication token does not work in Postgres connection string

[ejmolinelli]

First Check

• I added a very descriptive title to this issue.
• I used the GitHub search to find a similar issue and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

import os
import boto3
from sqlmodel import create_engine, Session

# postgres url variables from environment
PG_HOST = os.environ.get('PG_HOST', False)
PG_USER = os.environ.get('PG_USER', False)
PG_PORT = os.environ.get('PG_PORT', '5432')
PG_REGION = os.environ.get('PG_REGION', False)
PG_DBNAME = os.environ.get('PG_DBNAME', False)
assert PG_HOST  is not False
assert PG_USER is not False
assert PG_PORT == '5432'
assert PG_REGION is not False
assert PG_DBNAME is not False

# IAM authentication with user profile
# NOTE: requires that the runtime has access to an aws credential profile with the appropriate permissions to access an RDS instance or Proxy (e.g. 'postgres_admin_role')

# create token via IAM role
session = boto3.session.Session(profile_name='postgres_admin_role', region_name='us-east-1')
token = session.client('rds').generate_db_auth_token(
            DBHostname=PG_HOST, 
            Port=PG_PORT, 
            DBUsername=PG_USER, 
            Region=PG_REGION)

# insert token into password section of postgres url
url = f'postgresql://{PG_USER}:{token}@{PG_HOST}{PG_PORT}/{PG_DBNAME}'
DATABASE = create_engine(url)

# get connection and exec command
with Session(DATABASE) as session:
   print(session.exec('SELECT NOW()'))

# RESULT: Error "The IAM authentication failed for the role postgres. Check the IAM token for this role and try again."

Description

The above code results in a runtime error:

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

However, the URL is correct. I can login in from the same environment using the psql command.

Wanted Solution

The above code should run as is.

I did however come up with a solution which I posted here on stackoverflow.

The solution is to use the 'creator' argument to the create_engine function which returns a raw psycopg2 connection. (see alternatives below)

Wanted Code

# SAME CODE AS ABOVE

url = f'postgresql://{PG_USER}:{token}@{PG_HOST}{PG_PORT}/{PG_DBNAME}'
DATABASE = create_engine(url)

# get connection and exec command
with Session(DATABASE) as session:
   print(session.exec('SELECT NOW()'))

Alternatives

The following code works as a workaround

# the url
def get_pg_url(token) -> str:
    ssl = '?sslmode=require'
    return f'postgresql://{PG_USER}{token}@{PG_HOST}:{PG_PORT}/{PG_DB}{ssl}'

# the connection 
def get_connection(token):
   return psycopg2.connect(host=PG_HOST, user=PG_USER, port=PG_PORT, database=PG_DBNAME, password=token)

# Create the database engine
DATABASE = create_engine(get_pg_url(token), creator=get_connection)

with Session(DATABASE) as session:
   session.exec('SELECT NOW()')

Operating System

Linux

Operating System Details

ubuntu EC2 image

SQLModel Version

0.0.6

Python Version

3.9

Additional Context

It can be a lot of setup to recreate this problem as it has to run in an AWS VPC (and I'd be happy to help ). Roughly the steps are

1 - Create a VPC
2 -Security Group 1 - Accept TCP traffic on port 5432 from Security Group 2
3 - Security Group 2 - Accept all incoming traffic and allow outgoing TCP traffic on port 5432
4 - RDS postgres instance and attach to security group 1 (same vpc and all subnets)
4a - add a new role with login and password (in addition to the master username and password)
5 - save master and new user username/password in secrets manager
6 - Create RDS proxy and attach Security Group 2 (same vpc and all subnets)
6a - proxy needs a role with policy to get secrets and decrypt secret
7 - Create an ec2 instance (same vpc)
7a - ec2 needs a role with policy to connect to rds-proxy and for both users (i.e. master user and new user)
7b - pip install psycopg2-binary
7c - pip install sqlmodel boto3
7d - run example code from python terminal on this instance

[Guydada]

I can confirm this issue still exists

[theprivileges]

You need to escape the token. The best way to do this is to to use URL.create from sqlalchemy.engine.

https://stackoverflow.com/a/79152471/403434