loading...
Cover image for PostgreSQL: TLS Connection

PostgreSQL: TLS Connection

nabbisen profile image Heddi Nabbisen Updated on ・3 min read

* 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.
I use PostgreSQL 12.2 on OpenBSD 6.7.

References: official documentation about "ssl-tcp" for 12, current

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 # option= verify-ca=0 # if your client doesn't have valid cert file
  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

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 documentation:

$ # 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 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

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 setting is treated as an option of the hostssl line.
If you have valid client certificates, "verify-ca" or "verify-full" instead of "0" is available.

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}=> 

TLS connection is acquired :)

Posted on by:

nabbisen profile

Heddi Nabbisen

@nabbisen

An ICT designer/developer and a security monk. "With a cool brain and a warm heart", I am challenging unsolved problems in our society. I use OpenBSD/Rust/etc.

Discussion

pic
Editor guide