Making the best use of modern SSL

Why encrypt?

keep data confidential
maintain data integrity
regulations and policies
defense in depth

Standards and regulations

PCI DSS 💳
FIPS 🇺🇸
HIPAA ⚕️
GDPR 🇪🇺
…

For this presentation …

SSL = TLS

Trivia

When was SSL added to PostgreSQL?

What version?

Trivia

When was SSL added to PostgreSQL? — 2001

What version? — 7.1

Client vs Server


+--------+                              +--------+
|        |                              |        |
|        |   connect please?            |        |
| client |----------------------------->| server |
|        |                              |        |
|        |      ---->        <----      |        |
+--------+ I don't             I don't  +--------+
           know you            know you

Server configuration

postgresql.conf

#ssl = off
#ssl_ca_file = ''
#ssl_cert_file = 'server.crt'
#ssl_crl_file = ''
#ssl_crl_dir = ''
#ssl_key_file = 'server.key'
#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'   # allowed SSL ciphers
#ssl_prefer_server_ciphers = on
#ssl_ecdh_curve = 'prime256v1'
#ssl_min_protocol_version = 'TLSv1.2'
#ssl_max_protocol_version = ''
#ssl_dh_params_file = ''
#ssl_passphrase_command = ''
#ssl_passphrase_command_supports_reload = off

Client configuration

libpq connection parameters

sslmode
sslnegotiation
sslcompression
sslcert
sslkey
sslcertmode
sslpassword
sslrootcert
sslcrl
sslcrldir
sslsni

Client configuration

JDBC connection parameters (properties)

ssl
sslfactory
sslfactoryarg
sslmode
sslNegotiation
sslcert
sslkey
sslrootcert
sslhostnameverifier
sslpasswordcallback
sslpassword
sslResponseTimeout

OS configuration

$ openssl version -d
OPENSSLDIR: "/opt/homebrew/etc/openssl@3"

${OPENSSLDIR}/openssl.cnf:

openssl_conf = openssl_init

[openssl_init]
ssl_conf = ssl_configuration

[ssl_configuration]
server = server_tls_config
client = client_tls_config
system_default = tls_system_default

[server_tls_config]
... configuration for SSL/TLS servers ...

[client_tls_config]
... configuration for SSL/TLS clients ...

[tls_system_default]
MinProtocol = TLSv1.2
CipherString = ...

see man config(5ssl), SSL_CONF_CMD(3ssl)

(probably less confusing to do it all inside PostgreSQL?)

When to use SSL

for all TCP/IP connections
even localhost ones
(maybe even Unix-domain socket ones, but doesn't work — yet)

Force SSL server-side

pg_hba.conf

# TYPE    DATABASE     USER  ADDRESS   METHOD
hostssl   all          all   xxx       yyy
hostssl   replication  all   xxx       yyy

Force SSL client-side

libpq / JDBC

sslmode=require  (*)

SSL versions

version	spec	published	deprecated	OpenSSL
SSL 2.0	—	1995	2011 (RFC 6176)
SSL 3.0	(RFC 6101)	1996	2015 (RFC 7568)
TLS 1.0	RFC 2246	1999	2021 (RFC 8996)
TLS 1.1	RFC 4346	2006	2021 (RFC 8996)	1.0.1 (2012)
TLS 1.2	RFC 5246	2008	—	1.0.1 (2012)
TLS 1.3	RFC 8446	2018	—	1.1.1 (2018)

SSL versions

only TLS 1.2 and 1.3 are acceptable
TLS 1.2 is minimum version by default as of PostgreSQL 13
consider mandating TLS 1.3 for new deployments
- also faster connection

Set minimum SSL version server-side

postgresql.conf

ssl_min_protocol_version = 'TLSv1.2'

(requires PostgreSQL 12)

Set minimum SSL version client-side

libpq

ssl_min_protocol_version=TLSv1.2

(requires PostgreSQL 13)

Set minimum SSL version OS

openssl.cnf

MinProtocol=TLSv1.2

(requires OpenSSL 1.1.0)

Ciphers

ssl_ciphers = 'ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:DHE-RSA-AES128-GCM-SHA256:DHE-RSA-AES256-GCM-SHA384:DHE-RSA-CHACHA20-POLY1305'

Huh?!?

This only affects TLS ≤1.2!
New setting for TLS 1.3 maybe in PG18

Set ciphers in OS

openssl.cnf

# TLS ≤1.2
CipherString = ...

# TLS 1.3
Ciphersuites = ...

Certificates and keys

use a CA or make your own
- no self-signed certificates
server: certificate + key with host name
(client optional)
automation!
key management / certificate management

Certificate verification — client

~~sslmode=disable~~
~~sslmode=allow~~
~~sslmode=prefer~~
~~sslmode=require~~
~~sslmode=verify-ca~~
sslmode=verify-full

remember that sslrootcert=system is now an option

Certificate verification — server

for client authentication; useful for clients that you control, e.g., replication

pg_hba.conf

# TYPE    DATABASE     USER  ADDRESS   METHOD
hostssl   replication  all   xxx       cert

or

# TYPE    DATABASE     USER  ADDRESS   METHOD
hostssl   all          all   xxx       yyy clientcert=verify-full

need CRL

Certificate expiration

≤397 days

if accessible from Internet: matches public CAs
else: forces you to check it once in a while

Password-protected private keys

probably not useful

server: usually impractical, but sometimes required by internal policies
client: better combine an unprotected key with actual password authentication

https://ssl-config.mozilla.org/

Paranoid extra credit

libpq

gssencmode=disable

(GSS encryption preempts SSL)

Paranoid extra credit

libpq

require_auth=scram-sha-256
channel_binding=require

Paranoid extra credit

libpq

sslcertmode=require

Paranoid — no extra credit

ssl_dh_params_file = ...
ssl_ecdh_curve = ...

Obsolete settings

~~libpq sslcompression~~

PgBouncer(*) and SSL

(*) or similar


+--------+               +-----------+               +--------+
|        |               |           |               |        |
|   PG   |               |           |               |   PG   |
| client |-------------->| PgBouncer |-------------->| server |
|        |        SSL    |           | SSL           |        |
|        |        server |           | client        |        |
+--------+               +-----------+               +--------+

PgBouncer SSL configuration

pgbouncer.ini

;;; TLS settings for accepting clients
;client_tls_sslmode = disable
;client_tls_ca_file = system default
;client_tls_key_file =
;client_tls_cert_file =
;client_tls_ciphers = default
;client_tls_protocols = secure
;client_tls_dheparams = auto
;client_tls_ecdhcurve = auto

;;; TLS settings for connecting to backend databases
;server_tls_sslmode = prefer
;server_tls_ca_file = system default
;server_tls_key_file =
;server_tls_cert_file =
;server_tls_protocols = secure
;server_tls_ciphers = default

PgBouncer SSL config. suggestions

pgbouncer.ini

;;; TLS settings for accepting clients
;client_tls_sslmode = require
;client_tls_ca_file = your file
;client_tls_key_file = your file
;client_tls_cert_file = your file
;client_tls_ciphers = default
;client_tls_protocols = secure  ; = tlsv1.2,tlsv1.3
;client_tls_dheparams = auto
;client_tls_ecdhcurve = auto

;;; TLS settings for connecting to backend databases
;server_tls_sslmode = verify-full
;server_tls_ca_file = your file
;server_tls_key_file =
;server_tls_cert_file =
;server_tls_protocols = secure  ; = tlsv1.2,tlsv1.3
;server_tls_ciphers = default

New in PostgreSQL 17

sslnegotation=postgres (old)

C --> S: how about SSL?
C <-- S: ok
C --> S: start SSL, StartupMessage

sslnegotation=direct (new)

C --> S: start SSL, StartupMessage

WIP for PostgreSQL 18

remove support for OpenSSL 1.0.2
server-side SNI
TLS 1.3 ciphers configuration
better curve/group configuration

Summary

always use SSL
TLS 1.2 and 1.3 only
always use verify-full
check Mozilla config generator
cool new things in PG17+