0

We're setting up Redash and need to connect to multiple Data Sources that are Postgres databases that require SSL connections. I have a postgres.key and postgres.crt file from each database. Redash is running as user redash on Ubuntu 16.04.

What should I do with these keys so that Redash (and the Python psycopg2 library, behind the scenes) can find the proper credentials to connect to the Data Sources?

The best I've been able to do is put one set of them in /home/redash/.postgresql, which is where psycopg2 looks for credentials by default. However, that doesn't allow me to connect to the second database.

  • It sounds like you are doing client certs, not (or in addition to) server certs, correct? postgres.key and postgres.crt belong to the client, not the server, so to get them "from each database" doesn't make any sense. What error do you get when you try to connect to the second database? – jjanes Apr 17 '18 at 22:37
  • @jjanes Well -- I'm using Google Cloud SQL, which provides the client certificates (documentation: cloud.google.com/sql/docs/postgres/configure-ssl-instance), so I meant that I generated them in the Google Cloud SQL console and put them on the VM that is running Redash. However, I have two separate Cloud SQL instances running in different regions, so I have two sets of client credentials that I need Redash to be able to find. – Clay Apr 18 '18 at 12:41
  • what controls does redash give you over the connection parameters? – Jasen Apr 19 '18 at 1:16
  • Describe the interface you need to use to control this in more detail. – Jasen Apr 19 '18 at 1:42
  • @Jasen It allows me to specify to require as the SSL mode, along with standard host, port, user, password, database name, but it doesn't allow me to specify additional connection string parameters. I think it relies on how psycopg2 uses the underlying Ubuntu system to look for SSL client certificates in the /home/redash/.postgresql directory. Perhaps I need to just put this in as a feature request with the Redash team. – Clay Apr 19 '18 at 13:32
1

use the sslcert and sslkey paramterer in the connection string to indicate the files you want.

https://www.postgresql.org/docs/10/static/libpq-connect.html#LIBPQ-PARAMKEYWORDS

eg:

 con=psycopg2_connect(dsn='host=host1 user=yourname sslcert=host1.crt sskkey=host1.key')

probably something using string::format would be more practical.

.

If that seems too messy

Using a service definition to store the parameters is another option define the connection paramaters (like above) for each server in pg_services.conf (create that file) and use connection strings like

con=psycopg2_connect(dsn='service=host1')

https://www.postgresql.org/docs/10/static/libpq-pgservice.html

other clients (like psql) can also use the services thus defined, so this way will save you typing in the future.

but it seems redash is configured using environment variables with the database as as a URL perhaps like this:

like this with a service definition

export REDASH_DATABASE_URL=postgres:///?service=database1

or

like this explicitly:

export REDASH_DATABASE_URL="postgres://db_user@host.name.or.ip/db_name?sslcert=host1.crt&sskkey=host1.key"

from a bries sfan of the docs redash seems to use postgres for db_user and db_name

so far as I know libpq does not use a different interface for database names, connection strings, and database urls, where one can be used any other form can be used instead, however the redash docs say to use a URL.

https://www.postgresql.org/docs/10/static/libpq-connect.html#LIBPQ-CONNSTRING

  • Unfortunately, with Redash, I'm not able to add a connection string per Data Source. I'm only able to log into the VM and modify things there to put certificates in a place that the redash user will find them when it executes code with psycopg2. Hence, I'm trying to figure out if it's possible to create a system-level setting that psycopg2 will find and use when trying to connect via SSL. – Clay Apr 18 '18 at 12:57
  • I wsould say that it's possible, but I don't fully understand what you want – Jasen Apr 19 '18 at 1:43
  • FWIW, I ended up hacking Redash to support specification of the sslkey and sslcert paths in the UI. Details are here: github.com/getredash/redash/issues/2000#issuecomment-382774655 – Clay Apr 19 '18 at 15:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.