Specify SSL certificate locations for Postgres databases?


#1

Issue Summary

Is it possible to specify, per PostgreSQL Data Source, the location of the SSL certificate and key to be used to connect to the server?

I’ve deployed using the provided image on Google Compute Engine. I SSH’d into the VM, created a /home/redash/.postgresql directory and put my postgresql.key and postgresql.crt there, chmod’ing them to 0600. That allows Redash to use those credentials to connect to one Postgres Data Source that I use. However, I have another Postgres database that also requires SSL and has a different set of credentials.

What’s the best way to go about telling Redash which credentials to use with which data source?

Technical details:

  • Redash Version: 4.0.0+b3948
  • Browser/OS: Ubuntu 16.04 on Chrome
  • How did you install Redash: Google Compute Environment via the Redash-provided VM image.

#2

I think this is borderline a feature request.

Without a way to specify the location of the client key and certificate for connecting to PostgreSQL databases that requires SSL, you can only get Redash to connect to a single one (I think) and that only happens when you put the client key and certificate in the /home/redash/.postgresql directory on the VM.

I would like to see a field when setting up a Data Source with PostgreSQL for “Additional connection parameters,” where I can pass in the Postgres-specific parameters that will allow Redash to locate the appropriate credentials.

Here is where you can find the PostgreSQL documentation on this:

https://www.postgresql.org/docs/9.6/static/libpq-ssl.html

The relevant text:

If the server attempts to verify the identity of the client by requesting the client’s leaf certificate, libpq will send the certificates stored in file ~/.postgresql/postgresql.crt in the user’s home directory. The certificates must chain to the root certificate trusted by the server. A matching private key file ~/.postgresql/postgresql.key must also be present. The private key file must not allow any access to world or group; achieve this by the command chmod 0600 ~/.postgresql/postgresql.key. On Microsoft Windows these files are named %APPDATA%\postgresql\postgresql.crt and %APPDATA%\postgresql\postgresql.key, and there is no special permissions check since the directory is presumed secure. The location of the certificate and key files can be overridden by the connection parameters sslcert and sslkey or the environment variables PGSSLCERT and PGSSLKEY.

psql uses the allowed environment variables or accepts optional parameters to allow specification of the location of the certificates. With psql, it looks like this:

$>psql "port=5431 host=localhost user=postgres sslcert=./test/client.crt sslkey=./test/client.key sslrootcert=./test/server.crt sslmode=require"

It’s the ability to specify sslcert and sslkey that Redash lacks for PostgreSQL.


#3

For reference, in case anybody has a similar problem, I hacked Redash to get this to work. The details are here: