asyncpg
asyncpg copied to clipboard
Unable to connect to RDS Proxy
asyncpg=0.26.0 postgres=13.4
We're trying to connect to RDS through and RDS proxy with IAM auth and it doesn't work. We've verified that the provided code works fine when connecting straight to the database with IAM auth. We've also tested that we can connect through the proxy to the database. The only piece that does not work is asyncpg. This also works when using aiopg.
EDIT: I suspect that it's something with SSL but we've gotten nowhere pretty fast on it. The logs on RDS Proxy don't say anything other than Internal Error which is very.... unhelpful.
import asyncio
import aiopg
import asyncpg
import boto3
import os
import sys
import ssl
import certifi
from urllib.parse import quote_plus
ENDPOINT="<our proxy url>"
PORT="5432"
USER="<our user>"
REGION="us-east-1"
DBNAME="<our db>"
session = boto3.Session(profile_name='profile')
client = session.client('rds')
token = client.generate_db_auth_token(DBHostname=ENDPOINT, Port=PORT, DBUsername=USER, Region=REGION) # type: ignore
async def main():
try:
print("trying connection")
conn = await asyncpg.connect(dsn=f"postgres://{USER}:{quote_plus(token)}@{ENDPOINT}:5432/{DBNAME}?sslmode=require&sslrootcert=./AmazonRootCA1.pem")
#conn = await asyncpg.connect(user=USER, password=quote_plus(token), database=DBNAME, host=ENDPOINT, ssl='require')
print("connected, trying query")
print(await conn.fetch("SELECT 'connected'"))
except Exception as e:
print("CAUSE", e.__cause__)
raise
# async with aiopg.connect(f'dbname={DBNAME} user={USER} password={token} host={ENDPOINT} sslmode=require') as conn:
# async with conn.cursor() as cur:
# await cur.execute("SELECT 'connected'")
# async for row in cur:
# print(row)
asyncio.run(main())
The error we get back is
Traceback (most recent call last):
File "/tmp/test/__init__.py", line 50, in <module>
asyncio.run(main())
File "/usr/lib/python3.10/asyncio/runners.py", line 44, in run
return loop.run_until_complete(main)
File "/usr/lib/python3.10/asyncio/base_events.py", line 646, in run_until_complete
return future.result()
File "/tmp/test/__init__.py", line 35, in main
conn = await asyncpg.connect(dsn=f"postgres://{USER}:{quote_plus(token)}@{ENDPOINT}:5432/{DBNAME}?sslmode=require&sslrootcert=./AmazonRootCA1.pem")
File "/home/charles/.local/lib/python3.10/site-packages/asyncpg/connection.py", line 2093, in connect
return await connect_utils._connect(
File "/home/charles/.local/lib/python3.10/site-packages/asyncpg/connect_utils.py", line 889, in _connect
return await _connect_addr(
File "/home/charles/.local/lib/python3.10/site-packages/asyncpg/connect_utils.py", line 776, in _connect_addr
return await __connect_addr(params, timeout, False, *args)
File "/home/charles/.local/lib/python3.10/site-packages/asyncpg/connect_utils.py", line 839, in __connect_addr
await compat.wait_for(connected, timeout=timeout)
File "/home/charles/.local/lib/python3.10/site-packages/asyncpg/compat.py", line 66, in wait_for
return await asyncio.wait_for(fut, timeout)
File "/usr/lib/python3.10/asyncio/tasks.py", line 445, in wait_for
return fut.result()
asyncpg.exceptions.ConnectionDoesNotExistError: connection was closed in the middle of operation```
Try connecting with direct_tls=True
No dice, that times out. Tried it with both the DSN and with using the params. Just to double check that it wasn't something else I also reverted that and was back to the previous error.
I see same issue -- also get timeout connecting occasionally
I also ran into this issue. With direct_tls=True
the connection would hang and eventually timeout.
Exact same issue. with direct_tls=True the connection hangs. Is there some debug switch I can use to give you more information?
Confirmed here as well. I can connect directly to the RDS instance using asyncpg with normal username/password, however when using IAM auth via RDS Proxy, same error as above. I am able to get this to work with pyscopg.
Python: psycopg==3.1.8, asyncpg==0.27.0
Aurora PostgreSQL: 14.6
Here's the code I was testing with on an ec2 instance that had connectivity to both RDS and an RDS proxy.
import ssl
import asyncpg
import boto3
import psycopg
# Downloaded from https://www.amazontrust.com/repository/AmazonRootCA1.pem
cafile = 'AmazonRootCA1.pem'
region = 'us-east-2'
session = boto3.Session(region_name=region)
rds = session.client('rds')
host = f'proxy-#####.{region}.rds.amazonaws.com'
user = 'some_db_user'
database = 'some_db'
password = rds.generate_db_auth_token(DBHostname=host, Port=5432, DBUsername=user, Region=region)
# psycopg works
async with await psycopg.AsyncConnection.connect(host=host, user=user, password=password, sslmode='require',
sslrootcert=cafile, dbname=database) as conn:
async with await conn.execute('select 1') as cur:
print(await cur.fetchone())
# asyncpg errors with: "ConnectionDoesNotExistError: connection was closed in the middle of operation"
# See stack trace here https://pastebin.com/rMaHvJpM
ssl_ctx = ssl.create_default_context(cafile=cafile)
ssl_ctx.verify_mode = ssl.CERT_REQUIRED
conn = await asyncpg.connect(host=host, user=user, password=password, database=database, ssl=ssl_ctx)
Did anybody manage to solve this?
Same issue with [email protected]
@elprans This issue is open for a year now. Any progress on it would be highly appreciated. Given this being a blocker for us, we're left with no other choice than migrating to psycopg3 which is benchmarked with 5x lower performance in the README.
almost a year. let's celebrate the anniversary by starting working on it)
@pritamrungta did you manage to connect to RDS-proxy using IAM-auth using psycopg3?
@pritamrungta did you manage to connect to RDS-proxy using IAM-auth using psycopg3?
Yes. I did this https://github.com/MagicStack/asyncpg/issues/952#issuecomment-1526168429
It's blocking us as well. We also are forced to use psycopg3
unfortunately
Same here, commenting for visibility.