Postgres SSL mode doesn't pass credentials correctly

[razodactyl]

Hi,

My application is running CockroachDB v22.2.

Below files are fine. (I did a small test using psycopg3 raw inside the same project)

• ca.crt
• client.root.crt
• client.root.key

I'm trying to use this connection string:

postgres://root@node1:26257/defaultdb%3Fsslmode%3Dverify-ca%26sslrootcert%3D%2Fusr%2Fsrc%2Fcerts%2Fca.crt%26sslcert%3D%2Fusr%2Fsrc%2Fcerts%2Fclient.root.crt%26sslkey%3D%2Fusr%2Fsrc%2Fcerts%2Fclient.root.key

I can see the SanicMayimExtension opening the following:

Opening <PostgresPool postgres://root:...@node1:26257/defaultdb%3Fsslmode%3Dverify-ca%26sslrootcert%3D%2Fusr%2Fsrc%2Fcerts%2Fca.crt%26sslcert%3D%2Fusr%2Fsrc%2Fcerts%2Fclient.root.crt%26sslkey%3D%2Fusr%2Fsrc%2Fcerts%2Fclient.root.key>

Then during application runtime the SQL server (CockroachDB) is returning this.

error connecting in 'pool-1': connection failed: ERROR: password authentication failed for user root

I've had to escape the '/' characters as the base/interface implementation assumes the default DSN but doesn't account for connections like above.

Furthermore, there's no mapping between the urlparse query parameters back to the main connection.

So, psycopg3 is compatible with the format above however Sanic + Mayim extension breaks the connection parameters in transit.

Any known workarounds so far? Is there a way to pass the raw connection in for the moment?

If you can give me some pointers I could possibly give it a go myself - not that I have much experience in this space.

[ahopkins]

Hmm... This is good to know. If you wouldn't mind, I would be happy if you could submit a PR for this.

In the meantime, you could try something like this:

from psycopg_pool import AsyncConnectionPool
from mayim.sql.postgres.interface import PostgresPool
from mayim import Mayim

# Manually create this either with connection params or DSN
# We will override the connection in a minute though...
pool = PostgresPool(...)

# Override the default pool with your own
pool._pool = = AsyncConnectionPool(...)

# Pass to Mayim
Mayim(pool=pool, ...)