I'm connecting to an RDS Instance running PostgreSQL through a proxy.
I'm able to connect the database without issue when I run it through psycopg2 using the code below
creds = read_config(config_file_location)
rds_client = boto3.client('rds')
auth_token = rds_client.generate_db_auth_token(
DBHostname=creds['host'],
Port=creds['port'],
DBUsername=creds['user'],
Region=creds['region']
)
return psycopg2.connect(user=creds['user'],
password=auth_token,
host=creds['host'],
port=creds['port'],
database=creds['db'],
sslmode='require')
But when I try with SQLAlchemy
config = read_config(config_file_location)
rds_client = boto3.client('rds')
auth_token = rds_client.generate_db_auth_token(
DBHostname=config['host'],
Port=config['port'],
DBUsername=config['user'],
Region=config['region']
)
config['password'] = auth_token
sqlalchemy_url = "postgresql://{user}:{password}@{host}:{port}/{db}".format(**config)
engine = create_engine(sqlalchemy_url(), connect_args={"sslmode": "require"})
This throws an error
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) connection to server
at "<<PROXY HOST>>" (<< IP >>), port 5432 failed:
FATAL: The IAM authentication failed for the role << USER >>.
Check the IAM token for this role and try again.
The SQLAlchemy connection works if I connect directly to the DB using the password
Not sure only Proxy + Alchemy fails. Any suggestions are welcome
auth_tokenwith the result offrom urrlib.parse import quote;auth_token = quote(auth_token)?