Encryption has become security norm and communicating with databases on insecure network should not be an exception to this norm. In this tutorial we will learn how to connect to postgres database using ODBC connection which is configured to use 2 way SSL connection.
Before starting you should get certificate files in
.crt format from the Postgres Database Admins. Certificates will be of 2 types :
Trustore Certificate to validate server certificates using trusted certificate authority (CA) and
Keystore certificates which will have private certificates to validate client. If above certificates are encrypted using key then you should also have key file in
.key format. We will perform below configuration on Linux machine which has
openssl installed in it. ODBC recognizes certificates in
.pfx format and hence we will generate and export certificates in pfx file.
Export truststore certificates by creating new truststore or inside existing truststore with following command. You will need to provide password to create trustore.
$ openssl pkcs12 -export -in ca.crt -out pgtruststore.pfx -nokeys Enter Export Password: Verifying - Enter Export Password:
ca.crt file would be provided by DBA and it will contain chain of certificates for the certification authority. Certificates will get exported inside file
pgtruststore.pfx. Note that if you already have existing truststore file then you can export the certificates into the same by replacing filename
You can validate all the certificates are correctly exported inside your truststore with following command. Enter the same password which was created while generating truststore in previous step.
$ openssl pkcs12 -info -in pgtruststore.pfx Enter Import Password:
Similar to truststore you will also need to create unique keystore with the keystore certificates and keys shared for the Postgres Client. You will be asked to set the password for the keystore file.
$ openssl pkcs12 -export -out pgkeystore.pfx -inkey pg.key -in pg.crt Enter Export Password: Verifying - Enter Export Password:
pg.key file would be provided by DBA & will contain encrypted postgres client certificates which will get exported inside file
You can validate all the postgres certificates are correctly exported inside in your keystore with following command. Enter the same password which was created while generating keystore in previous step.
$ openssl pkcs12 -info -in pgkeystore.pfx Enter Import Password:
Once you have correctly created keystore and trustore you can use the PFX file in ODBC configuration. ODBC entry will have same details as that of non-ssl database however it will have additional flags like :
- ValidateServerCertificate: To validate Postgres Server Certificates
- EncryptionMethod: To have encrypted communication between client and server
- KeyStore : Absolute File Path to Keystore File
- KeyStorePassword : Import Password for the keystore
- TrustStore : Absolute File Path to Truststore File
- TrustStore Password : Import Password for the truststore
Below is the Example ODBC Entry with SSL
[POSTGRES_WITH_SSL] Driver=/path/to/postgres/odbc/driver/library/DWpsql27.so Description=PostgreSQL Wire Protocol Database=POSTGRES_DB_NAME HostName=postgres.example.com PortNumber=5432 PacketSize=32 UserName=DB_USER Password=********************* EncryptionMethod=1 ValidateServerCertificate=1 KeyStore=/absolute/path/to/keystore/pgkeystore.pfx KeyStorePassword=************** TrustStore=/absolute/path/to/keystore/pgtruststore.pfx TrustStorePassword=***************
Once you have created ODBC entry inside file
odbc.ini then you can test the entry. If you are using tool like Informatica then it comes prebuilt with tool called
ssgodbc to test ODBC connection. Using following command you should be able to test the connection. You shall get connected message if connection was successful.
ssgodbc.linux64 -d POSTGRES_WITH_SSL-u DB_USER -p *************** -v Connected ODBC version = -03.10.0000- DBMS name = -PostgreSQL- DBMS version = -11.04.0001 PostgreSQL 11.4 (Centos 11.4 pgdg) Driver name = -DWpsql27.so- Driver version = -07.11 Driver ODBC version = -03.36- Enter SQL string:
We have connected to SSL Configured Postgres Database using ODBC connection. You can follow the same steps to connect to some other database using ODBC which uses mutual TLS authentication.