Tech Monger

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.

---

Prerequisites

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.

---

TrustStore Generation

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:

Here 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 pgtruststore.pfx with your-truststore.pfx

---

TrustStore Validation

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:

---

Keystore Generation

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:

Here pg.crt and pg.key file would be provided by DBA & will contain encrypted postgres client certificates which will get exported inside file pgkeystore.pfx.

---

Keystore Validation

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:

---

ODBC Configuration

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=***************

---

Testing Connection

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:

---

Conclusion

Tagged Under : Informatica Linux ODBC Open Source Web