Secure Connections With PostgreSQL
Cryptography technology is changing all the time, and serious vulnerabilities in current software will probably always continue to be found. Both master and standby servers need OpenSSL installed before beginning. Before installing OpenSSL, make sure your ports collection is up-to-date:
# portsnap fetch && portsnap update # cd /usr/ports/security/openssl # make all install clean
Before deciding on build options, make sure to check out which protocols and ciphers are no longer secure. You definitely don't want any support for the SSL protocols, despite everyone using the term SSL.
Creating Certificates With OpenSSL
Ideally, you might set up/use a Certificate Authority, but in this case, we will just use self-signed certificates. Depending on your resources, you may want to generate a 2048-bit key instead of 4096.
man openssl has more information about the commands below.
$ mkdir ssl_stuff $ cd ssl_stuff $ openssl genrsa -out server.key 4096
Now, create an X.509 certificate signed with the key we just generated. OpenSSL will ask you for information to fill out the information required by X.509. The only thing that is important is that you use your exact domain name on the server for the Common Name. If you don't have a domain name for your server, you can use an IP address.
$ openssl req -new -x509 -days 3650 -key server.key -out server.crt
PostgreSQL also requires another certificate called
root.crt to be provided. If you were using a CA, this certificate would be provided by them. Since we are using self-signed certificates, we can just copy the
server.crt we just created to
$ cp server.crt root.crt
In order to connect to your server using certificates, a client certificate and key signed by the root certificate need to be in the user's
~/.postgresql directory. These are generated much like the server certificates, except they are named differently, and you have to use your username on the server as your Common Name. The challenge password and company name can be left blank.
$ openssl genrsa -out postgresql.key 4096 $ openssl req -new -key postgresql.key -out postgresql.csr $ openssl x509 -req -in postgresql.csr -CA root.crt -CAkey server.key -out postgresql.crt -CAcreateserial
Putting Things In The Right Places
Now, change the ownership of the files to the correct user and group, and restrict their permissions so that group and other can't access them at all. We're setting this up so that our server on one machine can contact a server on another machine. Since both are running under the
pgsql user, that's what we'll use here.
$ su # chmod 600 * # chown pgsql:pgsql *
On the client:
- put a copy of
root.crt, as well as both
On the server:
- put a copy of root.crt, as well as both
/usr/local/pgsql/data/postgresql.confto enable ssl by setting
ssl = on.
pg_hba.confin the same directory to enable an SSL replication connection for the
pgsqluser. It should probably look something like this:
hostssl replication pgsql STANDBY_IP_ADDRESS/0 cert clientcert=1
Now restart your server.