I had to setup it relatively recently, and hit some roadblocks, so figured I'll write about my experiences – for myself in the future, or for anyone else that might want to set it up.

First, let's state goals:

• remote communication to PostgreSQL (as in: not within localhost) should go via ssl encrypted channels
• to authenticate, clients should use their own certificates.
• we shouldn't accept connections from clients with “forget" client certificates

First bad news – we should setup CA (certificate authority). Of course it's possible to use some other CA (like any commercial one), but setting our own makes experimenting easier and cheaper.

Luckily, it's not that big of a deal, as openssl comes with program named CA.pl, which is located in /usr/lib/ssl/misc/ directory, which greatly helps. So let's use it.

On my pgdba account (system user I run PostgreSQL as, usually it's postgres, but I use pgdba), I made ~/ca directory – this will contain everything related to certificate authority.

To this directory I copied /etc/ssl/openssl.cnf file, and modified it:

--- /etc/ssl/openssl.cnf        2015-03-19 16:14:06.000000000 +0100
+++ openssl.cnf 2015-05-11 21:15:30.089342711 +0200
@@ -126,17 +126,18 @@
 
 [ req_distinguished_name ]
 countryName                    = Country Name (2 letter code)
-countryName_default            = AU
+countryName_default            = PL
 countryName_min                        = 2
 countryName_max                        = 2
 
 stateOrProvinceName            = State or Province Name (full name)
-stateOrProvinceName_default    = Some-State
+stateOrProvinceName_default    = Mazowieckie
 
 localityName                   = Locality Name (eg, city)
+localityName_default = Warsaw
 
 0.organizationName             = Organization Name (eg, company)
-0.organizationName_default     = Internet Widgits Pty Ltd
+0.organizationName_default     = depesz
 
 # we can do this but it is not needed normally :-)
 #1.organizationName            = Second Organization Name (eg, company)

As you can see I changed (or added) defaults to certificate identification values, so that I will not need to retype everything for each certificate.

Then, I copied /usr/lib/ssl/misc/CA.pl to ~/ca/ directory, and also made small change:

--- /usr/lib/ssl/misc/CA.pl     2015-03-19 16:14:06.000000000 +0100
+++ CA.pl       2015-05-11 21:22:02.217241400 +0200
@@ -44,7 +44,7 @@
        $ENV{OPENSSL} = $openssl;
 }
 
-$SSLEAY_CONFIG=$ENV{"SSLEAY_CONFIG"};
+$SSLEAY_CONFIG=$ENV{"SSLEAY_CONFIG"} || '-config ./openssl.cnf ';
 $DAYS="-days 365";     # 1 year
 $CADAYS="-days 1095";  # 3 years
 $REQ="$openssl req $SSLEAY_CONFIG";

That is, I made CA.pl in ~/ca to automatically use openssl.cnf from my directory.

Now, with this in place, in ~/ca directory, I run:

=$ ./CA.pl -newca
CA certificate filename (or enter to create)
 
Making CA certificate ...
Generating a 2048 bit RSA private key
.......................................................+++
................+++
writing new private key to './demoCA/private/cakey.pem'
Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [PL]:
State or Province Name (full name) [Mazowieckie]:
Locality Name (eg, city) [Warsaw]:
Organization Name (eg, company) [depesz]:
Organizational Unit Name (eg, section) []:
Common Name (e.g. server FQDN or YOUR name) []:pg/ca 
Email Address []:depesz@depesz.com
 
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
Using configuration from ./openssl.cnf
Enter pass phrase for ./demoCA/private/cakey.pem:
Check that the request matches the signature
Signature ok
Certificate Details:
        Serial Number: 13060781863828843728 (0xb54137e7fa61ecd0)
        Validity
            Not Before: May 11 19:25:24 2015 GMT
            Not After : May 10 19:25:24 2018 GMT
        Subject:
            countryName               = PL
            stateOrProvinceName       = Mazowieckie
            organizationName          = depesz
            commonName                = pg/ca
            emailAddress              = depesz@depesz.com
        X509v3 extensions:
            X509v3 Subject Key Identifier: 
                27:A7:0B:EB:DA:D2:D0:8E:75:9D:02:40:34:15:1E:59:1D:61:46:BA
            X509v3 Authority Key Identifier: 
                keyid:27:A7:0B:EB:DA:D2:D0:8E:75:9D:02:40:34:15:1E:59:1D:61:46:BA
 
            X509v3 Basic Constraints: 
                CA:TRUE
Certificate is to be certified until May 10 19:25:24 2018 GMT (1095 days)
 
Write out database with 1 new entries
Data Base Updated

Of course ca key should be protected by password.

For Common Name I entered pg/ca – I want to keep cnames uniques and formatted in a way that does not look like email or hostname, so I chose pg prefix, and will add /ca, /server, or /user/… suffixes.

Now, I have CA certificate and key generated. Now, let's make key for server:

=$ ./CA.pl -newreq
...
Common Name (e.g. server FQDN or YOUR name) []:pg/server
...
Request is in newreq.pem, private key is in newkey.pem

key has been generated, now I need to sign it with CA:

=$ ./CA.pl -sign
Using configuration from ./openssl.cnf
Enter pass phrase for ./demoCA/private/cakey.pem:
Check that the request matches the signature
Signature ok
Certificate Details:
        Serial Number: 13060781863828843729 (0xb54137e7fa61ecd1)
        Validity
            Not Before: May 11 19:28:54 2015 GMT
            Not After : May 10 19:28:54 2016 GMT
        Subject:
            countryName               = PL
            stateOrProvinceName       = Mazowieckie
            localityName              = Warsaw
            organizationName          = depesz
            commonName                = pg/server
            emailAddress              = depesz@depesz.com
        X509v3 extensions:
            X509v3 Basic Constraints: 
                CA:FALSE
            Netscape Comment: 
                OpenSSL Generated Certificate
            X509v3 Subject Key Identifier: 
                ED:11:E6:48:DF:7B:C3:0B:84:09:16:BE:40:43:7B:65:7F:9D:25:44
            X509v3 Authority Key Identifier: 
                keyid:27:A7:0B:EB:DA:D2:D0:8E:75:9D:02:40:34:15:1E:59:1D:61:46:BA
 
Certificate is to be certified until May 10 19:28:54 2016 GMT (365 days)
Sign the certificate? [y/n]:y
 
 
1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated
Signed certificate is in newcert.pem

of course, to sign I had to give password for CA key.

After these two commands, I have 3 new files in ~/ca: newkey.pem, newreq.pem and newcert.pem.

newreq is no longer necessary, but newkey and newcert as important. So, let's rename them properly:

=$ mv newcert.pem pg-server.crt
=$ mv newkey.pem pg-server.key

With this in place, I can modify my postgresql.conf to make it work with ssl. To do it I need to change:

• ssl = on
• ssl_cert_file = ‘/home/pgdba/ca/pg-server.crt'
• ssl_key_file = ‘/home/pgdba/ca/pg-server.key'
• ssl_ca_file = ‘/home/pgdba/ca/demoCA/cacert.pem'

there is also ssl_crl_file, but that's not interesting for now.

After these have been setup, let's chance pg_hba.conf to:

# TYPE     DATABASE  USER  CIDR-ADDRESS  METHOD
local      all       all   trust
hostnossl  all       all   0.0.0.0/0     reject
hostssl    all       all   0.0.0.0/0     trust

of course, this is just an example, but the point is simple – I reject all host (remote, not encrypted) connections, and trust all encrypted connection, or local via unix socket.

With this in place, let's reload postgresql:

=$ pg_ctl -w -D $PGDATA reload
server signaled

I had to use -w option, because otherwise pg_ctl ends before PostgreSQL will get password for ssl key.

The dots after “Enter PEM pass phrase:" are printed by pg_ctl, while postgres is waiting for me to type in the passphrase, but it doesn't break anything.

Of course – you might want to opt to use passwordless ssl key, so that you can start/restart server without being prompted for passwords. To do it, it's enough to:

=$ openssl rsa -in pg-server.key -out pg-server.new.key
=$ mv pg-server.new.key pg-server.key

To do it you will be asked passphrase to the key file, but that is the last time you will need it. Afterwards, I can restart Pg without any prompts:

=$ pg_ctl -w -D $PGDATA restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2015-05-11 21:47:36.992 CEST @ 28232  LOG:  redirecting log output to logging collector process
2015-05-11 21:47:36.992 CEST @ 28232  HINT:  Future log output will appear in directory "pg_log".
 done
server started

Now, let's see if encryption works:

=$ psql -h localhost
...
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
...

Nice. Works. And what if I explicitly disable SSL?

=$ PGSSLMODE=disable psql -h localhost
psql: FATAL:  pg_hba.conf rejects connection FOR host "127.0.0.1", USER "depesz", DATABASE "depesz", SSL off

Nice. But, at least hypothetically, one could do MITM attack. How to make it impossible? And also – why does it allow connection without even warning about certificate that is signed by some unknown party?

The second part is simple. Let's force verification of ca:

=$ PGSSLMODE=verify-ca psql -h localhost
psql: root certificate file "/home/depesz/.postgresql/root.crt" does not exist
Either provide the file or change sslmode to disable server certificate verification.

In ~/.postgresql, I need to put root.ca. What will happen, if the root.ca is not the one that signed server certificate (by mistake, or someone is trying MITM attack)?

=$ wget -q -O ~/.postgresql/root.crt http://www.startssl.com/certs/sub.class1.server.ca.pem
 
=$ PGSSLMODE=verify-ca psql -h localhost
psql: SSL error: certificate verify failed

Interestingly, the same happens without forcing verify-ca:

=$ psql -h localhost
psql: SSL error: certificate verify failed
FATAL:  pg_hba.conf rejects connection for host "127.0.0.1", user "depesz", database "depesz", SSL off

This is because libpq (what psql uses to connect to database) checks for certificate, and if it exists, it will verify server certificate even without verify-ca. If it will fail (as it did now), it will fail over to plaintext connection – thus making mitm attack impossible.

OK, so let's get proper cerfiticate to /home/depesz/.postgresql/root.crt, and retry:

=$ psql -h localhost
psql (9.5devel)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

Works again.

So, we have connection that is secure, MITM will not work, but (at the moment) anyone can connect. We could use “md5" in pg_hba.conf, but since we have CA, why don't we use it for user authentication?

I will create two certificates for users depesz and pgdba:

=$ ./CA.pl -newreq
...
Common Name (e.g. server FQDN or YOUR name) []:pg/user/pgdba
...
=$ ./CA.pl -sign
...
=$ openssl rsa -in newkey.pem -out user-pgdba.key
...
=$ mv newcert.pem user-pgdba.crt
=$ rm newreq.pem

and the same for user depesz.

Afterwards, I got 4 new files:

• user-depesz.crt
• user-depesz.key
• user-pgdba.crt
• user-pgdba.key

Now, I need to change pg_hba.conf again, to change “trust" to something more secure. That is “cert":

# TYPE     DATABASE  USER  CIDR-ADDRESS  METHOD
local      all       all   trust
hostnossl  all       all   0.0.0.0/0     reject
hostssl    all       all   0.0.0.0/0     cert

With this in place, I reload Pg, and immediately try to connect:

=$ psql -h localhost
psql: FATAL:  connection requires a valid client certificate
FATAL:  pg_hba.conf rejects connection for host "127.0.0.1", user "pgdba", database "pgdba", SSL off

of course, I didn't provide the user cert in any way, so I can't connect. This is OK. So, how do I provide the certs so that psql will use them?

That's simple – I copy them to ~/.postgresql/postgresql.crt and ~/.postgresql/postgresql.key.

With these files in place, I can try to connect again:

=$ psql -h localhost
psql: FATAL:  certificate authentication failed FOR USER "depesz"
FATAL:  pg_hba.conf rejects connection FOR host "127.0.0.1", USER "depesz", DATABASE "depesz", SSL off

Now, why is that?

My shell account is named “depesz", so psql tries to connect to db user “depesz". But my certificate cname is “pg/user/depesz", and not “depesz". What can I do? That's actually pretty simple, we need to modify pg_hba.conf again, and change it to:

# TYPE     DATABASE  USER  CIDR-ADDRESS  METHOD
LOCAL      ALL       ALL   trust
hostnossl  ALL       ALL   0.0.0.0/0     reject
hostssl    ALL       ALL   0.0.0.0/0     cert map=cert

This new addition (map=cert), will make pg use pg_ident.conf file, so I need to modify it too:

# MAPNAME  SYSTEM-USERNAME    PG-USERNAME
cert       /^pg\/USER\/(.*)$  \1

This means that map named “cert" (you can have many maps, for different purposes) will allow any user connecting with cname pg/user/* to connect to user that is the same as whatever is after pg/user/, so in case of pg/user/depesz – depesz.

Another reload of Pg config, and … :

=$ psql -h localhost
psql (9.5devel)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

Nice. What's also helpful, is that I can add to pg_ident another line:

cert       pg/user/depesz     pgdba

And now, using my pg/user/depesz certificate I will be able to log in to both “depesz" account (due to previously added line, with regexp), and to “pgdba" account – thanks to this new line.

This leads to one final question. What if someone rogue would generate another certificate with the same cname, and other fields? Of course it would be signed by the same CA (as we should keep the CA key very secure).

Let's try. This is relatively simple to do:

In my ~pgdba/ca directory, I will make self-signed certificate, that will have the same data as correct certificate, but will be self-signed, and not signed by my trusted CA:

=$ ./CA.pl -newcert
...
Common Name (e.g. server FQDN or YOUR name) []:pg/user/depesz
...
=$ openssl rsa -in newkey.pem -out rogue.key
...
=$ mv newcert.pem rogue.crt

now, I have rogue.crt and rogue.key. Moved them to ~/.postgresql/postgresql.{crt,key} in home directory, and now, when I try to connect:

=$ psql -h localhost
psql: SSL error: tlsv1 alert unknown ca
FATAL:  pg_hba.conf rejects connection for host "127.0.0.1", user "depesz", database "depesz", SSL off

So, I'm set. Looks like I'm safe against MITM attacks (as long as I remember to put root.crt in ~/.postgresql/ directory, and users can authenticate with certificates, logging to whatever user they need.

Hope anyone will find it useful 🙂