Posted on Wed, 03 Aug 2016

How to make a secure connection to postgresql server from your web2py app

For any reasonably larger web app you will want to have a separate web app server and database server. The advantage is that you can customize each server to use full amount of server's memory. The disadvantage is that now your web application has to talk to the database server over the network. And that may be visible to anyone. The solution is to encrypt their communication and here is a short howto.


Generating server keys

The first thing you will do is to generate a server keys. While you can put your key and certificate anywhere on the server my advice is to create them in the postgresql database directory.

Go to postgresql database directory and generate a private key (here you must provide a passphrase).

openssl genrsa -des3 -out server.key 1024

Now remove the passphrase.

openssl rsa -in server.key -out server.key

Set appropriate permission and owner on the private key file.

chmod 400 server.key
chown postgres.postgres server.key

Create the server certificate.

openssl req -new -key server.key -days 3650 -out server.crt -x509
cp server.crt root.crt

Now you have to modify pg_hba.conf file. Note the 'hostssl' instead of 'host'

hostssl       db_name     user_name       md5 clientcert=1

After that you also need to modify postgresql.conf

ssl = on
ssl_ca_file = 'root.crt'           

these changes require that you restart your postgresql server. When the server is restarted we can create a client keys.

Generating client keys

You can create them in /tmp directory

cd /tmp
openssl genrsa -des3 -out /tmp/postgresql.key 1024
openssl rsa -in /tmp/postgresql.key -out /tmp/postgresql.key

Then create the certificate postgresql.crt. It must be signed by our trusted root (which is using the private key file on the server machine).

openssl req -new -key /tmp/postgresql.key -out /tmp/postgresql.csr
openssl x509 -req -in /tmp/postgresql.csr -CA /var/lib/pgsql/9.5/data/root.crt -CAkey /var/lib/pgsql/9.5/data/server.key -out /tmp/postgresql.crt -CAcreateserial -days 3650


You now need to copy the following 3 files to your web app machine:



root.crt from your postgresql data dir

Testing connection

In your python prompt type the following (note that the sslrootcert, sslcert and sslkey parameters must contain the full path to the files if they are not in the current directory)

import psycopg2

db=psycopg2.connect(host="host_address", database="db_name", user="user_name", password="password", sslmode="require", sslrootcert='root.crt', sslcert='postgresql.crt', sslkey='postgresql.key')

and if that worked you can also check the connection with web2py DAL

import DAL

db=DAL('postgres://user_name:password@host_address/db_name', driver_args={"sslmode":"require", "sslrootcert":"root.crt", "sslcert":"postgresql.crt", "sslkey":"postgresql.key"})


If there are no errors it means that the connection is established and you can use these certificates in your web2py app

Log in to leave a comment