如何安全地(SSL)连接到RDS数据库与异步SQLAlchemy引擎?

Question

使用同步引擎，我们做了以下操作，它就工作了:

engine = create_engine(
"postgresql+psycopg2://USER:[e-mail]/dbname",
pool_pre_ping=True,
connect_args={"sslmode": "verify-full", "sslrootcert": 'path/to/cert'},
future=True,
echo=False,
)

但是当使用相同的async时，它不适合我:

engine = create_async_engine(
"postgresql+asyncpg://USER:[e-mail]/dbname",
pool_pre_ping=True,
connect_args={"sslmode": "verify-full", "sslrootcert": 'path/to/cert'},
future=True,
echo=False,
)

错误相当长，但简单地以:

结尾

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'

据我所知asyncpg可以处理ssl。链接:

https://magicstack.github.io/asyncpg/current/api/index.html连接在调用create_async_engine时是否有一种方法将参数传递给底层db驱动程序?

Answer 1

这似乎是工作，但我不是100% ssl正在使用。是否有一种方法来告诉异步连接?

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:[e-mail]/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())