62

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.

killthrush
  • 4,859
  • 3
  • 35
  • 38
Alex
  • 978
  • 1
  • 9
  • 22
  • 1
    psycopg2. Search that up in google. – Games Brainiac Jan 30 '15 at 02:34
  • @GamesBrainiac Thanks. I have. The problem is with the added ssl requirements. I could not find the syntax for that. – Alex Jan 30 '15 at 02:44
  • 1
    @Pep, SSL options are described here: https://www.postgresql.org/docs/current/static/libpq-connect.html#LIBPQ-PARAMKEYWORDS you can pass any of these to the `connect` method. – bluesmoon Dec 21 '16 at 17:04

4 Answers4

87

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.

mhawke
  • 84,695
  • 9
  • 117
  • 138
  • 10
    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. – Jens Timmerman Aug 31 '16 at 10:44
  • 2
    Note that `database` is a deprecated alias for `dbname` according to the [psycopg2 documentation](http://initd.org/psycopg/docs/module.html). – ljhennessy Nov 27 '18 at 00:04
  • @ljhennessy: thanks for pointing that out. I've updated the answer accordingly. – mhawke Nov 27 '18 at 00:32
  • Note to those using MacOS and sqlalchemy, run `pip install psycopg2-binary` before running `create_engine()`. – user1717828 May 20 '19 at 19:27
  • @mhawke What happens if sslkey file is password protected? Can psycopg2 handle password encrypted key files? – javaDeveloper Jul 23 '19 at 11:02
  • 1
    "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 – francistheturd Jul 29 '21 at 05:59
  • @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. – Vineet Menon Oct 29 '21 at 07:41
  • @mhawke Can we try using Environment Variables, How would the connection string look? – P Kernel Mar 18 '22 at 16:25
12

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)
Dan
  • 1,159
  • 13
  • 8
2

If you need to connect to your PostgresSQL database with an SSL certificate using psycopg2, you'll need to put your certificate SSL certificate in a subdirectory of your python program, and then you can reference the certificate in your connection string. I believe you could also set an environment variable as well, but in my example my SSL certificate will be in a subdirectory.

My python script is in a directory which looks like:

/Users/myusername/Desktop/MyCoolPythonProgram/test_database_connection.py

And my SSL certificate is in a directory which looks like:

/Users/myusername/Desktop/MyCoolPythonProgram/database/ssl_certificate/database/ssl_certificate/ca-certificate.crt

My HOSTNAME is a URL from DigitalOcean, but yours might be an IP Address instead.

This is what my test_database_connection.py script looks like:

import psycopg2
import os

POSTGRES_DATABASE_HOST_ADDRESS = "your-database-name-do-user-12345678-0.b.db.ondigitalocean.com"
POSTGRES_DATABASE_NAME = "defaultdb"
POSTGRES_USERNAME = "doadmin"
POSTGRES_PASSWORD = "$uperD00P3Rp@$$W0RDg0E$here"
# HOW TO (Relative Path Python): https://stackoverflow.com/questions/918154/relative-paths-in-python
path_to_current_directory = os.path.dirname(__file__)
relative_path_to_ssl_cert = 'database/ssl_certificate/ca-certificate.crt'
SSL_ROOT_CERT = os.path.join(path_to_current_directory , relative_path_to_ssl_cert )
POSTGRES_CONNECTION_PORT = "1234" # Set this to the correct port! Mine is provided by DigitalOcean and it's NOT 1234

db_info = "host='%s' dbname='%s' user='%s' password='%s' sslmode='require' sslrootcert='%s' port='%s'" % (POSTGRES_DATABASE_HOST_ADDRESS, POSTGRES_DATABASE_NAME, POSTGRES_USERNAME, POSTGRES_PASSWORD, SSL_ROOT_CERT, POSTGRES_CONNECTION_PORT)
postgres_connection = psycopg2.connect(db_info)
with postgres_connection:
    with postgres_connection.cursor() as postgres_cursor:
        sql = "SELECT * FROM your_table;"
        postgres_cursor.execute(sql)
        results = postgres_cursor.fetchall()
        for row in results:
            print("row in result")

        print("Connection Success!")

        # Close Database Cursor/Connection
        postgres_cursor.close()
topherPedersen
  • 541
  • 7
  • 15
1

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.

dayofthepenguin
  • 139
  • 1
  • 6