How to connect to a remote PostgreSQL database through SSL with Python
I want to connect to a remote PostgreSQL database through Python to do some basic data analysis. This database requires SSL (verify-ca), along with three files (which I have):
- Server root certificate file
- Client certificate file
- Client key file
I have not been able to find a tutorial which describes how to make this connection with Python. Any help is appreciated.
Answer
Use the psycopg2 module.
You will need to use the ssl options in your connection string, or add them as key word arguments:
import psycopg2
conn = psycopg2.connect(dbname='yourdb', user='dbuser', password='abcd1234', host='server', port='5432', sslmode='require')
In this case sslmode specifies that SSL is required.
To perform server certificate verification you can set sslmode to verify-full or verify-ca. You need to supply the path to the server certificate in sslrootcert. Also set the sslcert and sslkey values to your client certificate and key respectively.
It is explained in detail in the PostgreSQL Connection Strings documentation (see also Parameter Key Words) and in SSL Support.