Unable to connect to Cockroachdb using JWT token, enabled in option param

[kcsaff]

Hi, I use cockroachdb, with a connection authenticated by JWT token. My SQLAlchemy/psycopg2 connection string ends in ?options=--crdb:jwt_auth_enabled=true, which works brilliantly, but I'm having trouble translating that into asyncpg-speak. I think in general the FAQ could answer a few questions about how to translate connection string options over from psycopg2.

The main thing I've tried so far is to set different variants of --crdb:jwt_auth_enabled and jwt_auth_enabled to true in connect_args[“server_settings”], but I'm still failing to connect via asyncpg. Presumably this flag needs to be passed a different way.

I have more background over at the CockroachDB forum: https://forum.cockroachlabs.com/t/sqlalchemy-asyncpg-and-jwt/7009

Please let me know if there's something else I should try.

[byigorduvanov]

server_settings is the right place, but the key needs to be options, with the whole value as you have it in the psycopg2 string:

conn = await asyncpg.connect(
    host=host, port=port, user=user, password=jwt_token, database=db,
    server_settings={'options': '--crdb:jwt_auth_enabled=true'},
)

With SQLAlchemy:

engine = create_async_engine(
    url,
    connect_args={'server_settings': {'options': '--crdb:jwt_auth_enabled=true'}},
)

Keeping it in a plain asyncpg DSN (?options=--crdb:jwt_auth_enabled=true) also works, since asyncpg treats unknown query parameters as server settings.

I checked this with a small fake server that captures the startup message on asyncpg 0.32.0. Both forms sent options: --crdb:jwt_auth_enabled=true, the same startup parameter you'd expect from libpq. I didn't test against CockroachDB itself, so this only confirms what's sent. If it still fails, the error is probably in the JWT authentication step rather than this flag. asyncpg takes the token through password, as in the example.