How to connect to a remote PostgreSQL database through SSL with Python

Question

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.

Solution 1

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.

Solution 2

You may also use an ssh tunnel with paramiko and sshtunnel:

import psycopg2
import paramiko
from sshtunnel import SSHTunnelForwarder

mypkey = paramiko.RSAKey.from_private_key_file('/path/to/private/key')

tunnel =  SSHTunnelForwarder(
        (host_ip, 22),
        ssh_username=username,
        ssh_pkey=mypkey,
        remote_bind_address=('localhost', psql_port))

tunnel.start()
conn = psycopg2.connect(dbname='gisdata', user=psql_username, password=psql_password, host='127.0.0.1', port=tunnel.local_bind_port)

Solution 3

Adding this for completeness and because I couldn't find it anywhere else on SO. Like @mhawke says, you can use psycopg2, but you can also use any other Python database modules (ORMs, etc) that allow you to manually specify a database postgresql URI (postgresql://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]) to connect to since the sslmode="require" parameter that psycopg2.connect uses to enforce ssl connections is just part of the postgresql:// URI that you use to connect to your database (see 33.1.2. Parameter Key Words). So, if you wanted to use sqlalchemy or another ORM instead of vanilla psycopg2, you can tack your desired sslmode onto the end of your database URI and connect that way.

import sqlalchemy

DATABASE_URI = "postgresql://postgres:postgres@localhost:5432/dbname"
# sqlalchemy 1.4+ uses postgresql:// instead of postgres://
ssl_mode = "?sslmode=require"
DATABASE_URI += ssl_mode

engine = sqlalchemy.create_engine(URI)
Session = sqlalchemy.orm.sessionmaker(bind=engine)

There's a nifty figure (Table 33.1) in the postgres documentation on SSL Support that breaks down the different options you can supply. If you want to use any of the fancier options that require you to specify a path to a specific certificate, you can drop it in with a format string.

Comments

[Games Brainiac]

psycopg2. Search that up in google.

[Alex]

@GamesBrainiac Thanks. I have. The problem is with the added ssl requirements. I could not find the syntax for that.

[bluesmoon]

@Pep, SSL options are described here: postgresql.org/docs/current/static/… you can pass any of these to the connect method.

[Jens Timmerman]

in django this works if you specify something like: ` 'OPTIONS': { 'sslmode': 'verify-full', 'sslrootcert': '/etc/pki/CA/certs/cabundle.pem'}` as an extra 'option' to your DATABASES['default'] dict.

[ljhennessy]

Note that database is a deprecated alias for dbname according to the psycopg2 documentation.

[mhawke]

@ljhennessy: thanks for pointing that out. I've updated the answer accordingly.

[user1717828]

Note to those using MacOS and sqlalchemy, run pip install psycopg2-binary before running create_engine().

[Rakesh Nair]

@mhawke What happens if sslkey file is password protected? Can psycopg2 handle password encrypted key files?

[francistheturd]

"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." Supply where? Can someone modify the example please

[Vineet Menon]

@francistheturd, (dbname='dbname', user='username', host='localhost', port='5432', sslmode='require', sslcert='ssl.crt', sslkey='ssl.nopass.key') there's a provision for sslpassword as well, but the version installed at my pc was old.

[P Kernel]

@mhawke Can we try using Environment Variables, How would the connection string look?