Introduction
To use remote PostgreSQL server as database server, it’s better to use TLS/SSL connection. This post shows a simple and easy way with self-signed certificate.
Environment
- OpenBSD 6.7
- PostgreSQL 12.2
References
Summary
There are just 3 steps.
- Prepare for server certificates
- generate self-signed certificate
- Configure server to edit files
- postgresql.conf : edit options, listen_address / ssl
- pg_hba.conf : add hostssl definition, and include “verify-ca=0” as one of auth-options in it if your clients don’t have valid certificates
- Verify in client machine
$ psql "sslmode=require host=$DB_HOST user=$DB_USER dbname=$DB_NAME"
Tutorial
1. Prepare for server certificates
Create server certificates in the PostgreSQL data directory as _postgresql user.
$ doas su _postgresql
$ cd /var/postgresql/data
Use openssl command line tool.
$ # create a self-signed certificate
$ openssl req -new -x509 -days 36500 -nodes -text -out server.crt -keyout server.key -subj "/CN=$DB_HOST_DOMAIN"
$ chmod 400 server.key
Of course, “36500” which means 36500 days = almost 100 years as valid term can be modified.
Optionally, if you don’t hesitate to edit /etc/ssl/openssl.cnf to use v3_ca extensions, it is able to create a server certificate whose identity can be validated by clients. It creates root and intermediate certificates. The detail about editing is in official docs:
$ # create a certificate signing request (CSR) and a public/private key file
$ openssl req -new -nodes -text -out root.csr -keyout root.key -subj "/CN=$ROOT_CA_DOMAIN"
$ chmod 400 root.key
$ # create a root certificate authority
$ openssl x509 -req -in root.csr -text -days 36500 -extfile /etc/ssl/openssl.cnf -extensions v3_ca -signkey root.key -out root.crt
$ # create a server certificate signed by the new root certificate authority
$ openssl req -new -nodes -text -out server.csr -keyout server.key -subj "/CN=$DB_HOST_DOMAIN"
$ chmod 400 server.key
$ openssl x509 -req -in server.csr -text -days 36500 -CA root.crt -CAkey root.key -CAcreateserial -out server.crt
Here, you might meet the error about v3_ca extension. In this case, try to modify /etc/ssl/openssl.cnf following this post.
After running one of the commands sets above, some files are created. server.crt and server.key are important.
$ ls {root,server}*
root.crt root.csr root.key root.srl server.crt server.csr server.key
Stay in /var/postgresql/data as _postgresql.
2. Configure server to edit files
First, edit postgresql.conf so as to enable requests from remote clients and also ssl connection:
#listen_addresses = 'localhost'
...
+ listen_addresses = '*'
...
#ssl = off
+ ssl = on
#ssl_ca_file = ''
#ssl_cert_file = 'server.crt'
#ssl_crl_file = ''
#ssl_key_file = 'server.key'
ssl_ca_file may be filled when it was created as root.crt or something else.
Next, edit pg_hba.conf and add a hostssl line at the bottom so as to allow ssl connection from clients:
+ hostssl all all 0.0.0.0/0 md5 clientcert=0
clientcert is one of auth-options. Set it to “0” if you clients don’t have valid certificates. When they have valid ones, use “verify-ca” or “verify-full” instead. (Update: Be careful “0” is no longer supported as of 14.)
Well, when something fails on the way, /var/postgresql/logfile is surely useful to get the detail.
After all done, say thank you and goodbye to _postgresql.
$ exit
Finally, restart the database server.
$ doas rcctl restart postgresql
3. Verify in client machine
In the client machine, use psql with “sslmode=require” statement like this:
$ psql "sslmode=require host=$DB_HOST user=$DB_USER dbname=$DB_NAME"
Password for user ...:
Enter db user’s password.
psql (12.2)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
{$DB_NAME}=>
Here, TLS connection is acquired :)