3

I connect to my PostgreSQL databases (which run on AWS EC2 instances) using certificate authentication (and not passwords). An example of a psql command I would use to connect to one of my databases is:

psql "host=<AWS EC2 instance> user=<db user> sslcert=<path to .crt> sslkey=<path to .key> sslrootcert=<path to .crt> sslmode=require dbname=<db name>"

I would like to be able to connect Tableau Desktop to one of my databases. The standard PostgreSQL connector does not allow me to specify which SSL certs to use (but it does have a Require SSL checkbox - which I think is for encryption not authentication).

So I have tried to use the ODBC connector. I make the following entries:

Connect Using
Driver: PostgreSQL Unicode

Connection Attributes
Server: <AWS EC2 instance>
Port: 5432
Database: <db name>
Username: <db user>
String Extras: sslcert=<path to .crt>; sslkey=<path to .key>; sslrootcert=<path to .crt>; sslmode=require

By much trial and error I think the String Extras require a semicolon as a delimiter, but I still cannot connect. I get the error message:

An error occurred while communicating with Other Databases (ODBC).

Unable to connect to the server. Check that the server is running and that you have access privileges to the requested database.
FATAL:  connection requires a valid client certificate

Generic ODBC requires additional configuration. The driver and DSN (data source name) must be installed and configured to match the connection.
Unable to connect to the server "<AWS EC2 instance>" using the driver "PostgreSQL Unicode". Check that the server is running and that you have access privileges to the requested database.

I would be really grateful to hear from anyone who has connected Tableau Desktop to PostgreSQL using certificate authentication and could tell me what I’m doing wrong. Cheers!

walmat
  • 166
  • 1
  • 7

1 Answers1

4

I managed to fix this myself, and just in case there's someone out there who might be vaguely interested I'll go through the salient features:

1. Created a DSN (Data Store Name)

  • This made testing much easier rather than constantly retyping details into dialog boxes.
  • Rather than create manually I used this: http://www.odbcmanager.net/
  • On Mac OS I had to run it as sudo from the terminal or it wouldn't create anything.
  • I created a User DSN, which on my Mac have details stored in /Users/<user name>/.odbc.ini
  • It picked up the PostgreSQL Unicode driver that I'd previously installed. On Mac OS you might need to do brew install brew install psqlodbc and/or brew install unixodbc
  • Most of the configuration was specified by adding key/value pairs e.g. DBNAME <db name>
  • Set SSLMODE verify-ca (changed from require to make behaviour specific - psql assumes verify-ca/verify-full if SSL certs are supplied)
  • The cert paths are set with key Pqopt, value sslcert=<path to .crt> sslkey=<path to .key> sslrootcert=<path to .crt>. Must be lower case and only spaces between entries!

Here's what it added to my user's odbc.ini file:

[<DSN name>]
Driver      = <path to driver, this was mine /usr/local/lib/psqlodbcw.so>
Description = <description>
SSLMODE     = verify-ca
HOST        = <host>
DBNAME      = <database>
PORT        = 5432
UID         = <db user>
Pqopt       = sslcert=<path to .crt> sslkey=<path to .key> sslrootcert=<path to .crt>

2. Tableau Desktop Connection

  • Select Other Database (ODBC) connector.
  • Select the DSN you created previously (it should be automatically picked up), it will automatically populate host, port, db and user fields, then click Sign In.
  • Once signed in behaviour was slightly different to the dedicated PostgreSQL connector. Tables did not appear on left-hand side initially. But they are accessible - they can all be displayed by clicking on the 'contains' radio button and searching with a blank name.

Hope this helps. Let me know if you've got any questions.

walmat
  • 166
  • 1
  • 7