Hello everyone,
I'm trying to pass the "sslrootcert" connection keyword parameter
through sqlalchemy and psycopg2. Unfortunately, that's currently not
possible in a straightforward way, meaning, this sqlalchemy URI doesn't
work:
postgresql://<username>@<hostname>:<port>/<dbname>?sslmode=verify-full&sslrootcert=<cert>
The sqlalchemy dialect for psycopg2 passes all parameters as kwargs
(instead of as dsn string) to psycopg2.connect, but psycopg2.connect
only supports a subset [1] of all valid parameter keywords [2], so
"sslrootcert" isn't accepted.
There's a work-around [3], but it's ugly. So now I'd like to fix it and
I'm trying to find out whether to change psycopg2 or sqlalchemy.
Properly fixing it in psycopg2 would probably mean accepting all missing
keywords [4] in psycopg2.connect.
At this point I'm interested in what kind of patch would be acceptable.
The options I came up with:
a) A patch to explicitly support all keywords that are currently
supported by the newest libpq. (psycopg2 would need to be changed
for future keywords.)
b) A patch to accept and pass-on any keyword parameters.
c) "None of the above, go fix sqlalchemy to use a dsn string instead
of keyword parameters."
Cheers
Fabian
1: Supported parameters: dbname, database, user, password, host, port,
sslmode
2:
http://www.postgresql.org/docs/current/static/libpq-connect.html#LIBPQ-PQCONNECTDBPARAMS
3: Work-around: Using psycopg2.connect's undocumented keyword parameter
'dsn' through an sqlalchemy URI such as
"postgresql:///?dsn=user=<username>%20host=<hostname>%20port=<port>%20dbname=<dbname>%20sslmode=verify-full%20sslrootcert=<cert>"
4: Currently missing keywords: hostaddr, connect_timeout,
client_encoding, options, application_name, fallback_application_name,
keepalives, keepalives_idle, keepalives_interval, keepalives_count,
sslcert, sslkey, sslrootcert, sslcrl, requirepeer, krbsrvname, gsslib,
service. (I've omitted requiressl and tty, because they are
deprecated/ignored.)