How to securely (SSL) connect to RDS database with async SQLAlchemy engine?

user_1391466 0
Viewed 197

Using a synchronous engine we do the following and it works:

engine = create_engine(
    "postgresql+psycopg2://USER:PASS@XXX.us-east-1.rds.amazonaws.com/dbname",
    pool_pre_ping=True,
    connect_args={"sslmode": "verify-full", "sslrootcert": 'path/to/cert'},
    future=True,
    echo=False,
)

But when using the same for async it doesn't work for me:

engine = create_async_engine(
    "postgresql+asyncpg://USER:PASS@XXX.us-east-1.rds.amazonaws.com/dbname",
    pool_pre_ping=True,
    connect_args={"sslmode": "verify-full", "sslrootcert": 'path/to/cert'},
    future=True,
    echo=False,
)

The error is rather lengthy but ends simply with:

File "/Users/chhenning/.pyenv/versions/cooper/lib/python3.10/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 777, in connect
    await_only(self.asyncpg.connect(*arg, **kw)),
TypeError: connect() got an unexpected keyword argument 'sslmode'

As far as I can tell asyncpg can deal with ssl. Link here:

https://magicstack.github.io/asyncpg/current/api/index.html#connection

Is there a way to pass arguments to the underlying db driver when calling create_async_engine?

asynchronous sqlalchemy python-3.x postgresql
1 Answers

This appears to be working but I'm not 100% that ssl is being used. Is there a way to tell with asyncpg connections?

import asyncio
from ssl import create_default_context

from sqlalchemy import text
from sqlalchemy.ext.asyncio import create_async_engine

async def main():

    ctx = create_default_context(
        cafile="/path/to/rds-combined-ca-bundle.pem"
    )

    engine = create_async_engine(
        "postgresql+asyncpg://postgres:XXX@XXX.us-east-1.rds.amazonaws.com/dbname",
        pool_pre_ping=True,
        connect_args={"ssl": ctx},
        future=True,
        echo=True,
    )

    async with engine.connect() as conn:
        result = await conn.execute(text("select * from t1 limit 10;"))
        print(result.fetchall())

    await engine.dispose()


asyncio.run(main())
user_13914660
Related