DEV Community

Cover image for PostgreSQL 12: TLS Connection
nabbisen
nabbisen

Posted on • Updated on • Originally published at obsd.solutions

PostgreSQL 12: TLS Connection

* The cover image is originally by TheDigitalArtist and edited with great appreciation.


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

References


Summary

There are just 3 steps.

  1. Prepare for server certificates
    • generate self-signed certificate
  2. 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
  3. 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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Here, you might meet the error about v3_ca extension. In this case, try to modify /etc/ssl/openssl.cnf following the below 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
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Finally, restart the database server.

$ doas rcctl restart postgresql
Enter fullscreen mode Exit fullscreen mode

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 fullscreen mode Exit fullscreen mode

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}=> 
Enter fullscreen mode Exit fullscreen mode

Here, TLS connection is acquired :)

Top comments (0)