DEV Community

nabbisen
nabbisen

Posted on • Originally published at obsd.solutions

PostgreSQL 14: TLS Connection

#postgres #database #tls #security

Summary

To use PostgreSQL as external database servers, it's better to use TLS/SSL connection. This post shows how to generate certificates, configure servers and verify them.

There are just 3 steps.

  1. Prepare for server certificates
    • Generate self-signed certificates
    • (Optional) Generate CA-signed certificates for clients to verify
  2. Edit server config files
    • postgresql.conf : Edit options, listen_address / ssl
    • pg_hba.conf : Add hostssl definition
  3. Verify in a client machine
    • Use psql with sslmode

Environment

Reference

Tutorial

1. Prepare for server certificates

Generate self-signed certificates

Create server certificates in the PostgreSQL data directory as _postgresql user.

$ doas su - _postgresql $ whoami _postgresql $ cd /var/postgresql/data
Enter fullscreen mode Exit fullscreen mode

Create a self-signed certificate with openssl command line tool.
Of course, -days 36500 below can be modified, which means it will be valid within 36500 days = almost 100 years.

$ # ksh $ DB_HOST_DOMAIN="(...)" $ openssl req -new -x509 -days 36500 -nodes -text -out server.crt -keyout server.key -subj "/CN=$DB_HOST_DOMAIN"
Enter fullscreen mode Exit fullscreen mode

The output was:

Generating a 2048 bit RSA private key ..............................................................................+++++ ................+++++ writing new private key to 'server.key' -----
Enter fullscreen mode Exit fullscreen mode

You will see:

$ ls -l server\.* -rw-r--r-- 1 _postgresql _postgresql 3660 Apr 24 13:17 server.crt -rw-r--r-- 1 _postgresql _postgresql 1704 Apr 24 13:17 server.key
Enter fullscreen mode Exit fullscreen mode

Modify permission of the key:

$ chmod 400 server.key
Enter fullscreen mode Exit fullscreen mode

Stay in /var/postgresql/data as _postgresql.

(Optional) Generate CA-signed certificates for clients to verify

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 the 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 $ # ... might be end with "Error Loading extension section v3_ca"  $ # 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
Enter fullscreen mode Exit fullscreen mode

Here, you might meet "Error Loading extension section v3_ca". In this case, try to modify /etc/ssl/openssl.cnf following this post.

$ ls {root,server}* root.crt root.csr root.key root.srl server.crt server.csr server.key
Enter fullscreen mode Exit fullscreen mode

2. Edit server config files

You are in /var/postgresql/data as _postgres. Right?

$ whoami _postgresql $ pwd /var/postgresql/data
Enter fullscreen mode Exit fullscreen mode

Edit postgresql.conf:

$ nvim postgresql.conf
Enter fullscreen mode Exit fullscreen mode

so as to enable requests from remote clients and also ssl connection:

 #listen_addresses = 'localhost' ... + listen_addresses = '*'  ... #port = 5432 + port = {$DB_PORT} # (optional) for security  ... #ssl = off + ssl = on  #ssl_cert_file = 'server.crt' #ssl_ca_file = '' #ssl_crl_file = '' #ssl_key_file = 'server.key'
Enter fullscreen mode Exit fullscreen mode

Besides, when you use not-self-signed certificates and have root.crt, ssl_ca_file must be filled.

Next, edit pg_hba.conf:

$ nvim pg_hba.conf
Enter fullscreen mode Exit fullscreen mode

to add a line on hostssl to the bottom so as to allow ssl connection from clients:

+ hostssl all all 0.0.0.0/0 md5
Enter fullscreen mode Exit fullscreen mode

As of 14, you don't have to set clientcert to 0 when you use self-signed certificates. It is one of "auth-options", which can be set to verify-ca or verify-full when you have valid client certificates. }}">In 13 and smaller, it can be set to 1 (defalut, then)/0/no-verify.

Besides, when something fails on the way, /var/postgresql/logfile is surely useful to get the detail.

Let's come back to your user:

$ # end of behavior as _postgresql $ exit
Enter fullscreen mode Exit fullscreen mode

Restart the database server:

$ doas rcctl restart postgresql
Enter fullscreen mode Exit fullscreen mode

Done.

3. Verify in a client machine

In a client machine, use psql with "sslmode=require":

$ psql "sslmode=require host=$DB_HOST port=$DB_PORT user=$DB_USER dbname=$DB_NAME" Password for user ...:
Enter fullscreen mode Exit fullscreen mode

Enter db user's password.

psql (14.2) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) Type "help" for help. postgres=#
Enter fullscreen mode Exit fullscreen mode

Here, TLS connection based on "protocol: TLSv1.3" etc. is acquired :)

Top comments (0)