SSL and Encrypted Connections

ssl_library — the compile-time proof

A pg_settings row that reports which TLS library, if any, PostgreSQL was linked against at build time (typically OpenSSL). An empty value means no library was linked in — ssl cannot be enabled, and any hostssl rule can never be satisfied by any client. This is the single fastest way to confirm SSL capability before touching pg_hba.conf.

hostssl vs host

Both are pg_hba.conf connection-type keywords for TCP/IP connections. A plain host rule matches a connecting client whether or not it negotiated SSL. hostssl matches only clients that did negotiate SSL — a plaintext attempt against an address covered only by hostssl rules finds no match and is rejected outright. On a build with no SSL support, that rejection applies to every client, not just plaintext ones.

sslmode (general knowledge)

A client-side connection parameter controlling how much SSL enforcement and verification the client itself demands, on a server that actually supports SSL. disable refuses SSL; require encrypts but does not check the server's identity; verify-ca encrypts and validates the certificate against a trusted root; verify-full additionally checks that the certificate's hostname matches the one being connected to. This cluster cannot demonstrate any mode past disable live — the exam-relevant distinctions are covered here conceptually.

pg_stat_ssl

A system view with one row per backend, showing whether that connection is using SSL and, if so, its protocol version, cipher suite, and key size. It is the authoritative way to confirm a connection is actually encrypted, as opposed to merely being allowed to be — and it is equally authoritative when the honest answer is that nothing on the cluster can be encrypted at all.

🔌 Connect as postgres

Connect as postgres. The application-only HBA rules will preserve administrator access.

psql -U postgres -d beer_db

SET psql (18.4) Type "help" for help. beer_db=#

🔍 Check the Current ssl Setting and Certificate Files

Inspect the current ssl setting and the provisioned localhost certificate and private key.

SHOW ssl;

\! as-postgres ls -al /var/lib/postgresql/18/data/server.crt /var/lib/postgresql/18/data/server.key

ssl: off; server.crt and server.key exist

🔐 Enable TLS

Enable ssl and reload the configuration. Allow the asynchronous reload to finish before checking the setting. This image was compiled with OpenSSL support.

ALTER SYSTEM SET ssl = 'on';

SELECT pg_reload_conf();

SELECT pg_sleep(0.2);

SHOW ssl;

ALTER SYSTEM pg_reload_conf: t ssl: on

🔬 Confirm It — the Compile-Time Proof

Confirm ssl_library reports OpenSSL.

SELECT name, setting FROM pg_settings WHERE name = 'ssl_library';

ssl_library | OpenSSL

🛡️ Require TLS for app_svc

Prepend hostssl authentication followed by a plaintext rejection for app_svc. Existing administrator connections remain available.

\! as-postgres sed -i '1i hostssl beer_db app_svc 127.0.0.1/32 scram-sha-256\nhost beer_db app_svc 127.0.0.1/32 reject' /var/lib/postgresql/18/data/pg_hba.conf

SELECT pg_reload_conf();

SELECT type, address, auth_method FROM pg_hba_file_rules WHERE 'app_svc' = ANY(user_name);

hostssl | 127.0.0.1 | scram-sha-256 host | 127.0.0.1 | reject

🚫 Reject a Plaintext Connection

Use sslmode=disable to prove app_svc cannot connect without encryption.

\! PGPASSWORD=app_svc_pw1 psql "host=127.0.0.1 dbname=beer_db user=app_svc sslmode=disable" -c "SELECT current_user;"

FATAL: pg_hba.conf rejects connection: no encryption

✅ Connect with Required Encryption

Connect with sslmode=require and verify that this backend uses TLS.

\! PGPASSWORD=app_svc_pw1 psql "host=127.0.0.1 dbname=beer_db user=app_svc sslmode=require" -c "SELECT current_user, ssl FROM pg_stat_ssl WHERE pid = pg_backend_pid();"

current_user | ssl app_svc | t

📐 Inspect pg_stat_ssl's Shape

Inspect the columns that report encryption, protocol, cipher, and certificate information.

\d pg_stat_ssl

beer_db=# \d pg_stat_ssl View "pg_catalog.pg_stat_ssl" Column | Type | Collation | Nullable | Default ---------------+---------+-----------+----------+--------- pid | integer | | | ssl | boolean | | | version | text | | | cipher | text | | | bits | integer | | | client_dn | text | | | client_serial | numeric | | | issuer_dn | text | | |

📊 Inspect Active Connection Encryption

Join pg_stat_ssl with pg_stat_activity to inspect TLS on active backends.

SELECT a.usename, a.client_addr, s.ssl FROM pg_stat_ssl s JOIN pg_stat_activity a ON a.pid = s.pid ORDER BY a.usename;

beer_db=# SELECT a.usename, a.client_addr, s.ssl beer_db-# FROM pg_stat_ssl s beer_db-# JOIN pg_stat_activity a ON a.pid = s.pid beer_db-# ORDER BY a.usename; usename | client_addr | ssl ----------+-------------+----- postgres | | f (1 row)