notes/postgresql/postgres_TLS_01.md

Generate PostgreSQL server certificate

PostgreSQL server generate certificate private key and a certificate request for the CN = PostgreSQL server

openssl genrsa -out postgres_server.key 4096
openssl req -new -key postgres_server.key -out postgres_server.csr

The CA root put the certificate request in a temporary location (ex. generated directory), generate a signed certificate and optionally create a certificate full chain.

Configuration file used by root CA:

[ req ]
default_bits       = 4096
prompt             = no
default_md         = sha256
distinguished_name = dn
req_extensions     = req_ext

[ dn ]
CN = PostgreSQL server

[ req_ext ]
subjectAltName = @alt_names

[ alt_names ]
DNS.1 = raxus.swgalaxy
DNS.2 = mobus.swgalaxy
DNS.3 = pgsql.swgalaxy

NOTE: CN in CA configuration file can be diffrent than the CN of CSR. The CA does not replace it unless explicitly configured to do so.

openssl x509 -req \
  -in generated/postgres_server.csr \
  -CA rootCA.pem -CAkey rootCA.key -CAserial rootCA.srl \
  -out generated/postgres_server.crt \
  -days 3650 \
  -sha256 \
  -extensions req_ext -extfile generated/postgres_server.cnf

cat generated/postgres_server.crt rootCA.pem > generated/postgres_server.fullchain.crt

To inspect a certificate in Linux command line:

openssl x509 -in rootCA.pem -text -noout
openssl x509 -in generated/postgres_server.crt -text -noout

Generate PostgreSQL client certificate(s)

We will generate 2 certificates for:

• CN=PostgreSQL client1
• CN=PostgreSQL client2

openssl genrsa -out postgres_client1.key 4096
openssl req -new -key postgres_client1.key -out postgres_client1.csr

openssl genrsa -out postgres_client2.key 4096
openssl req -new -key postgres_client2.key -out postgres_client2.csr

Configuration file used by root CA will be the same for both certificates:

[ req ]
default_bits       = 4096
prompt             = no
default_md         = sha256
distinguished_name = dn
req_extensions     = req_ext

[ dn ]
CN = Generic Client PostgreSQL

[ req_ext ]
subjectAltName = @alt_names

[ alt_names ]
DNS.1 = anyhost.anydomain

openssl x509 -req \
  -in generated/postgres_client1.csr \
  -CA rootCA.pem -CAkey rootCA.key -CAserial rootCA.srl \
  -out generated/postgres_client1.crt \
  -days 3650 \
  -sha256 \
  -extensions req_ext -extfile generated/postgres_client.cnf

cat generated/postgres_client1.crt rootCA.pem > generated/postgres_client1.fullchain.crt

openssl x509 -req \
  -in generated/postgres_client2.csr \
  -CA rootCA.pem -CAkey rootCA.key -CAserial rootCA.srl \
  -out generated/postgres_client2.crt \
  -days 3650 \
  -sha256 \
  -extensions req_ext -extfile generated/postgres_client.cnf

cat generated/postgres_client2.crt rootCA.pem > generated/postgres_client2.fullchain.crt

On PostgreSQL server, add the root CA certificate as trusted certificate.

Put root CA certificate (.crt ou .pem)under /etc/pki/ca-trust/source/anchors, then update the system trust store.

cd /etc/pki/ca-trust/source/anchors
chmod 644 rootCA.pem
update-ca-trust extract

openssl verify -CAfile /etc/pki/tls/certs/ca-bundle.crt <path_to>/postgres_server.crt 
# Inspect the root CA certificate to get the exact CN
grep -R "<CN_name>" /etc/pki/ca-trust/extracted/

Set up SSL on PostgreSQL server

Place server certificate, server private key and root CA certificate (optional but recommended) in the PostgreSQL data directory. Default paths:

• $PGDATA/server.key
• $PGDATA/server.crt

Or override with:

ssl_cert_file = '/path/to/server.crt'
ssl_key_file  = '/path/to/server.key'
ssl_ca_file   = '/path/to/root.crt'

Set file pemisions:

chmod 640 $PGDATA/postgres_server.crt
chmod 600 $PGDATA/postgres_server.key
chmod 640 $PGDATA/rootCA.pem

root CA certificate is required only for mTLS mode when the server will validate the client certificate authenticity
Add (concatebate) all CA intermediate (if any) in ssl_cert_file

Enable TLS in $PGDATA/postgresql.conf.

ssl_cert_file = 'postgres_server.crt'
ssl_key_file  = 'postgres_server.key'
ssl_ca_file   = 'rootCA.pem'

ssl = on
ssl_ciphers = 'HIGH:!aNULL:!MD5'
ssl_prefer_server_ciphers = on
ssl_min_protocol_version = 'TLSv1.2'
ssl_max_protocol_version = 'TLSv1.3'

PostgreSQL will now listen for both encrypted and unencrypted connections on the same port.

Server side SSL modes

Request TLS mode

Config in $PGDATA/pg_hba.conf:

host    all             all             all                     md5

Require TLS mode

Config in $PGDATA/pg_hba.conf:

hostssl all             all             0.0.0.0/0               md5

Require TLS mode + client certificate

Config in $PGDATA/pg_hba.conf:

hostssl all             all             0.0.0.0/0               cert

Client side SSL modes

Mode	Encrypts	Validates CA	Validates Hostname	Typical Use
require	Yes	No	No	Basic encryption
verify-ca	Yes	Yes	No	Internal/IP-based
verify-full	Yes	Yes	Yes	Production

Examples:

# mode: require
psql "host=raxus.swgalaxy port=5501 user=vplesnila dbname=postgres sslmode=require"

# mode: verify-ca
psql "host=raxus.swgalaxy port=5501 user=vplesnila dbname=postgres sslmode=verify-ca sslrootcert=rootCA.pem"

For verify-full mode the client need client certificate, client private key and root CA certificate on client side.
In our example we will use previously generated certificats for:

• CN=PostgreSQL client1
• CN=PostgreSQL client2

Set file pemisions:

chmod 640 postgres_client1.crt
chmod 600 postgres_client1.key

chmod 640 postgres_client2.crt
chmod 600 postgres_client2.key

chmod 640 rootCA.pem

In verify-full mode we can get ride of client password by mapping the CN in the certificate to a local PostgreSQL role (aka local user).
Create local roles (with no password) in PostgreSQL instance:

CREATE ROLE app1 LOGIN;
CREATE ROLE app2 LOGIN;

Add in $PGDATA/pg_ident.conf:

# MAPNAME       SYSTEM-IDENTITY          PG-USERNAME
certmap_app1    "PostgreSQL client1"     app1
certmap_app2    "PostgreSQL client2"     app2

Add in $PGDATA/pg_hba.conf:

hostssl  all  app1  0.0.0.0/0  cert  map=certmap_app1
hostssl  all  app2  0.0.0.0/0  cert  map=certmap_app2

Restart PostgreSQL instance after modifying $PGDATA/pg_ident.conf and $PGDATA/pg_hba.conf.

Connect in verify-full mode using certificate for authentification:

# mode: verify-full
psql "host=raxus.swgalaxy port=5501 user=app1 dbname=postgres sslmode=verify-full sslrootcert=rootCA.pem sslcert=postgres_client1.crt sslkey=postgres_client1.key"

psql "host=raxus.swgalaxy port=5501 user=app2 dbname=postgres sslmode=verify-full sslrootcert=rootCA.pem sslcert=postgres_client2.crt sslkey=postgres_client2.key"

As SAN (Subject Alternative Name) of the server certificate match to:

• raxus.swgalaxy
• mobus.swgalaxy
• pgsql.swgalaxy

it is possible to connect to all theses DNS enteries using the same server certificate.
Example:

psql "host=pgsql.swgalaxy port=5501 user=app1 dbname=postgres sslmode=verify-full sslrootcert=rootCA.pem sslcert=postgres_client1.crt sslkey=postgres_client1.key"

⭐ Server certificates → client checks SAN/CN for hostname
⭐ Client certificates → server checks only CN for user identity

Important

: we can mix TLS authentification by certificate and password.
PostgreSQL processes pg_hba.conf top‑to‑bottom, and the first matching rule wins. Once a connection matches a line (based on type, database, user, address, etc.), PostgreSQL stops and applies that rule’s authentication method.

The folowing $PGDATA/pg_hba.conf allows authentification using certificates for PostgreSQL users app1 and app2 and authentification using password for all other users.

hostssl  all  app1  0.0.0.0/0  cert  map=certmap_app1
hostssl  all  app2  0.0.0.0/0  cert  map=certmap_app2
hostssl  all  all   0.0.0.0/0  md5

Check if connections are using TLS:

SELECT * FROM pg_stat_ssl;

Check for connection username:

select pid as process_id, 
       usename as username, 
       datname as database_name, 
       client_addr as client_address, 
       application_name,
       backend_start,
       state,
       state_change
from pg_stat_activity;