asyncpg: asyncpg does not work with "sslmode" query param when called from SQLAlchemy

asyncpg version: 0.22
PostgreSQL version: 12.3
Do you use a PostgreSQL SaaS? If so, which? Can you reproduce the issue with a local PostgreSQL install?: I use DigitalOcean and was able to also reproduce it locally
Python version: 3.8.9
Platform: Linux
Do you use pgbouncer?: No
Did you install asyncpg with pip?: Yes
If you built asyncpg locally, which version of Cython did you use?: -
Can the issue be reproduced under both asyncio and uvloop?: N/D

First, congratulation to the people of MagicStack for this great library.

When using DigitalOcean default environment DATABASE URL to set the database string on my app I got the following error:

  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 599, in __connect
    connection = pool._invoke_creator(self)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/create.py", line 578, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 558, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 747, in connect
    await_only(self.asyncpg.connect(*arg, **kw)),
TypeError: connect() got an unexpected keyword argument 'sslmode'

In my app, since I use psycopg2 to do some “management tasks”, I also need to do something like that:

if ssl_enabled and driver == "asyncpg":
    base_url += "?ssl=require"
elif ssl_enabled and driver == "psycopg2":
    base_url += "?sslmode=require"

Shouldn’t asyncpg use the more standard sslmode= query param instead of the ssl=?

About this issue

Original URL
State: open
Created 3 years ago
Reactions: 6
Comments: 16 (3 by maintainers)

Commits related to this issue

Updated circleci database url for asyncpg See https://github.com/MagicStack/asyncpg/issues/737 for background — committed to FanvidDB/fanviddb-server by sandalwoodbox 2 years ago
work around https://github.com/MagicStack/asyncpg/issues/737 — committed to ewdurbin/fides by ewdurbin a year ago

Most upvoted comments

Here’s how I finally got it to work (https://github.com/sqlalchemy/sqlalchemy/discussions/5975):

from ssl import create_default_context, Purpose as SSLPurpose

ssl_context = create_default_context(
    SSLPurpose.CLIENT_AUTH,
    cafile="docker-compose.d/cockroach-certs/ca.crt"
)
ssl_context.load_cert_chain(
    "docker-compose.d/cockroach-certs/client.root.crt",
    keyfile="docker-compose.d/cockroach-certs/client.root.key"
)
ssl_context.check_hostname = True

_engine = create_async_engine(
    "cockroachdb+asyncpg://root@localhost:26257/mydb",
    echo=True,
    connect_args={"ssl": ssl_context}
)

Equivalent to sslmode=verify-full&sslcert=..&sslkey=..&sslrootcert=.. per https://magicstack.github.io/asyncpg/current/api/index.html

evindunn on Feb 26, 2022

On asyncpg the query string is ssl instead of sslmode

igortg on Jun 14, 2021

On asyncpg the query string is ssl instead of sslmode

this worked for me, thanks!

Abhi011999 on Apr 25, 2023