Description
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