This is not the first time I’ve struggled getting SSL certificate validation to work,
so I thought this time I better write down how I did to avoid future time-waste.
For security and convenience reasons, I want to do the signing of client certificates
on a separate dedicated machine, also known as certificate authority (CA).
This allows us to grant new clients access without having to login to the PostgreSQL
server signing certs or modifying pg_hba.conf.
We will create a special database group called sslcertusers.
All users in this group will be able to connect provided they have a
client certificate signed by the CA.
In the example below, replace “trustly” with the name of your company/organisation.
The instructions are for Ubuntu Linux 12.04 LTS.
Setup CA
The CA should be an offline computer locked in a safe.
Generate CA private key
sudo openssl genrsa -des3 -out /etc/ssl/private/trustly-ca.key 2048 sudo chown root:ssl-cert /etc/ssl/private/trustly-ca.key sudo chmod 640 /etc/ssl/private/trustly-ca.key
Generate CA public certificate
sudo openssl req -new -x509 -days 3650 \ -subj '/C=SE/ST=Stockholm/L=Stockholm/O=Trustly/CN=trustly' \ -key /etc/ssl/private/trustly-ca.key \ -out /usr/local/share/ca-certificates/trustly-ca.crt sudo update-ca-certificates
Configure PostgreSQL-server
Generate PostgreSQL-server private key
# Remove default snakeoil certs sudo rm /var/lib/postgresql/9.1/main/server.key sudo rm /var/lib/postgresql/9.1/main/server.crt # Enter a passphrase sudo -u postgres openssl genrsa -des3 -out /var/lib/postgresql/9.1/main/server.key 2048 # Remove the passphrase sudo -u postgres openssl rsa -in /var/lib/postgresql/9.1/main/server.key -out /var/lib/postgresql/9.1/main/server.key sudo -u postgres chmod 400 /var/lib/postgresql/9.1/main/server.key
Request CA to sign PostgreSQL-server key
sudo -u postgres openssl req -new -nodes -key /var/lib/postgresql/9.1/main/server.key -days 3650 -out /tmp/server.csr -subj '/C=SE/ST=Stockholm/L=Stockholm/O=Trustly/CN=postgres'
Sign PostgreSQL-server key with CA private key
sudo openssl x509 -days 3650 \ -req -in /tmp/server.csr \ -CA /usr/local/share/ca-certificates/trustly-ca.crt \ -CAkey /etc/ssl/private/trustly-ca.key -CAcreateserial \ -out /var/lib/postgresql/9.1/main/server.crt sudo chown postgres:postgres /var/lib/postgresql/9.1/main/server.crt
Create root cert = PostgreSQL-server cert + CA cert
sudo -u postgres sh -c 'cat /var/lib/postgresql/9.1/main/server.crt /etc/ssl/certs/trustly-ca.pem > /var/lib/postgresql/9.1/main/root.crt' sudo cp /var/lib/postgresql/9.1/main/root.crt /usr/local/share/ca-certificates/trustly-postgresql.crt sudo update-ca-certificates
Grant access
CREATE GROUP sslcertusers; ALTER GROUP sslcertusers ADD USER joel;
# /etc/postgresql/9.1/main/pg_hba.conf: hostssl nameofdatabase +sslcertusers 192.168.1.0/24 cert clientcert=1
Restart PostgreSQL
sudo service postgresql restart
PostgreSQL-client(s)
Copy root cert from PostgreSQL-server
mkdir ~/.postgresql cp /etc/ssl/certs/trustly-postgresql.pem ~/.postgresql/root.crt
Generate PostgreSQL-client private key
openssl genrsa -des3 -out ~/.postgresql/postgresql.key 1024 # If this is a server, remove the passphrase: openssl rsa -in ~/.postgresql/postgresql.key -out ~/.postgresql/postgresql.key
Request CA to sign PostgreSQL-client key
# Replace "joel" with username: openssl req -new -key ~/.postgresql/postgresql.key -out ~/.postgresql/postgresql.csr -subj '/C=SE/ST=Stockholm/L=Stockholm/O=Trustly/CN=joel' sudo openssl x509 -days 3650 -req -in ~/.postgresql/postgresql.csr -CA /etc/ssl/certs/trustly-ca.pem -CAkey /etc/ssl/private/trustly-ca.key -out ~/.postgresql/postgresql.crt -CAcreateserial sudo chown joel:joel -R ~/.postgresql sudo chmod 400 -R ~/.postgresql/postgresql.key
Files
The following files are created/modififed on each machine:
CA
/etc/ssl/private/trustly-ca.key /usr/local/share/ca-certificates/trustly-ca.crt /etc/ssl/certs/trustly-ca.pem -> /usr/local/share/ca-certificates/trustly-ca.crt
PostgreSQL-server
/var/lib/postgresql/9.1/main/server.key /var/lib/postgresql/9.1/main/server.crt /var/lib/postgresql/9.1/main/root.crt /usr/local/share/ca-certificates/trustly-ca.crt /usr/local/share/ca-certificates/trustly-postgresql.crt /etc/ssl/certs/trustly-ca.pem -> /usr/local/share/ca-certificates/trustly-ca.crt /etc/ssl/certs/trustly-postgresql.pem -> /usr/local/share/ca-certificates/trustly-postgresql.crt /etc/postgresql/9.1/main/pg_hba.conf
PostgreSQL-client
~/.postgresql/root.crt ~/.postgresql/postgresql.key ~/.postgresql/postgresql.crt
Joel on SQL 
I’m getting this error:
FATAL: connection requires a valid client certificate
However my client cert seems to be valid:
openssl verify -CAfile root.crt postgresql.crt
postgresql.crt: OK
What could I’ve done wrong?
Wondering if the certs can be replaced without bouncing postgresql
This is a great guide. I had trouble getting it working until I found Albe Laurenz’s May 16, 2008; 3:46am post here: http://postgresql.1045698.n5.nabble.com/SSL-auth-problem-td1898656.html
It seems that:
sudo openssl req -x509 \
-key /etc/ssl/private/trustly-ca.key \
-in /tmp/server.csr \
-out /var/lib/postgresql/9.1/main/server.crt
Should perhaps be instead:
sudo openssl x509 -days 3650 \
-req -in /tmp/server.csr \
-CA /usr/local/share/ca-certificates/trustly-ca.crt \
-CAkey /etc/ssl/private/trustly-ca.key -CAcreateserial \
-out /var/lib/postgresql/9.1/main/server.crt
Thanks Peter! Article updated.
For most recent news you have to pay a visit world-wide-web
and on the web I found this web page as a most excellent web site for most up-to-date updates.
Awesome content. You should use social websites to increase traffic.
There are tools which automate this time consuming process.Visitors can flood your page in no time,
just type in google for:
Rixisosa’s Social Automation
Let me begin by expressing my gratitude for this tutorial. I’ve been struggling for the past 3 days with installing PostgreSQL on my Ubuntu VM and configuring SSL and nothing on the internet comes even close to this.
I have only the smallest observation: when trying to connect through SSL (verify-full) using the pgAdmin client, I received an error stating that the Common Name on the server certificate must be the host on which the server instance is running. Therefore, I only had to change the info on the server certificate as follows:
sudo -u postgres openssl req -new -nodes -key /var/lib/postgresql/9.1/main/server.key -days 3650 -out /tmp/server.csr -subj ‘/C=SE/ST=Stockholm/L=Stockholm/O=Trustly/CN=localhost’ (kept original info from the article in this excerpt)
I’m using the 9.3 version of the DB server, so maybe there was something changed in the meantime.
All in all, thank you once more for sharing this with us! It really was of immense help. 🙂
yyou are truly a gokd webmaster. The web site loading velocity is amazing.
It kind of feels that you’re doingg any unique trick.
In addition, The contents are masterpiece.
youu have performed a excellent activity on this subject!
can you explain how to create trustly-postgresql.pem
Hi i’m getting error “SSL error: tlsv1 alert unknown ca” can anyone explain why it comes?
Maybe this articles could be complete if modifications to pg_hba and postgresql.conf were added ? Those are slight be are to be done (if I’m not mistaking saying that).
So far that’s the *very only* step by step tutorial explaining correctly this important security part in postgres
Thank you for this — really useful. I’m not sure I would’ve got this set up without you.
For the server and client(s) parts, after generating each CSR, I inferred that this needed copying it to the CA server for running the x509 command there, then copying the generated certificate back to the database server/client. The way the commands are written above could make it look like they all run on the same computer.
your trustly-ca.pem appears out of nowhere in the middle of this (when creating the root certificate for postgres). I assume that was trustly-ca.crt? or does the update-ca-certificate create this?
For anyone reading this in 2022 with recent versions of Postgres on OSes with recent versions of OpenSSL (3.0+) you’ll need to remove the -des3 from the command lines to avoid getting “ee key too small” from OpenSSL or “sslv3 alert bad certificate” from psql when trying to connect with “sslmode=verify-full” (as you should be doing now), and the entry in pg_hba.conf ending with “clientcert=verify-full”.