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 192.168.0.0/16 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:
/tmp/postgresql.crt /tmp/postgresql.key 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