Passing SSL certificates to mysql and postgresql

[Cattes]

To pass an ssl certiciat to pymysql one can use

Database(creds=DATABASE_CREDS[creds], sql_dir=SQL_DIR, connect_args={"ssl": DATABASE_CREDS['ssl']})

where DATABASE_CREDS['ssl'] contains a dictionary with the entry "ca": "<path to .pem file>

For Postgresql(psycopg2) I have to pass different arguments to the Databse class:

Database(creds=DATABASE_CREDS[creds], connect_args={"sslmode": "require", "sslrootcert": DATABASE_CREDS['ssl']})

I suggest using an "ssl" key in the database creds dictionary and depending on the driver entry it is passed on as a correctly formatted connect_args dict.

[phainom]

This is a good callout, I will work on it as soon as possible. Note that you can always pass the connection URL as well, including SSL details.

There is a general problem with supporting different SQL dialects with the additional capabilities dbrequests offers compared to eg records, as it became apparent in #11. Please feel free to join the discussion there.

[phainom]

The problem here is that the handling of SSL arguments is driver specific, ie for postgres, where a lot of very opinionated DBAPIs are out there, there is a difference for every driver used (eg between psycopg2 and pg8000). SQL alchemy chose to not offer an unified syntax for this and I tend to agree, since this would introduce driver specific code into the Database class, while the gain from it seems negligible?

With #14 there will be a way to easily extend dbrequest with driver specific code, so I would vote for creating child classes implementing this for the different drivers.

[wahani]

We should resolve this together with #27. What I would suggest is, that we allow the credentials object to comprise the connect_args, which are passed on to the sqlalchemy driver. E.G.

creds = {
    host='1.2.3.4',
    ...,
    ssl='/path/to/ssl/cert',
    other_arg=some_value
}

from which we can build the url + the connect_args={ssl='/path/to/ssl/cert', other_arg=some_value}. This would cover the use case in this issue.

SQL alchemy chose to not offer an unified syntax for this and I tend to agree,

I agree. If we construct the connect_args from the creds dict, we can leave it to the user to specify the appropriate settings, e.g. ssl for MySQL and (sslmode, sslrootcert) for PostgreSQL.

Any objections?

[Cattes]

I think allowing the creds object to contain additional arguments that are passed on to the respective drivers is a good idea. It is what I first tried when I wanted to add the ssl certificate so it is an intuitive approach.