Table of Contents

1. What is SSL in PostgreSQL?

By default, data travels between a PostgreSQL client and server in plain text. Anyone sniffing the network can read your queries, results, and sometimes even credentials.

SSL/TLS encrypts this traffic. With SSL enabled, PostgreSQL gives you:

• Encryption: Data in transit cannot be read by anyone on the network.
• Server authentication: The client can verify it is talking to the real server (not an impostor).
• Client authentication (optional): The server can require clients to present a certificate.

2. Prerequisites & Environment

Confirm PostgreSQL was built with OpenSSL support and OpenSSL is installed:

[postgres@pgdb01 ~]$ pg_config --configure | grep -o "with-openssl"
with-openssl
[postgres@pgdb01 ~]$

[postgres@pgdb01 ~]$ openssl version
OpenSSL 3.5.5 27 Jan 2026 (Library: OpenSSL 3.5.5 27 Jan 2026)
[postgres@pgdb01 ~]$

Tip: PGDG packages (yum/dnf) are always built with OpenSSL. If you compiled from source, make sure you used --with-openssl.

3. Check Current SSL Status

postgres=# SHOW ssl;
 ssl
-----
 off   <---------------
(1 row)

postgres=#

-- Check whether current sessions are encrypted
postgres=# SELECT pid, ssl, version, cipher FROM pg_stat_ssl;
 pid  | ssl | version | cipher
------+-----+---------+--------
 3933 | f   |         |
(1 row)

postgres=#

SSL is OFF. All traffic between clients and this server is currently unencrypted.

4. Generate a Self-Signed Certificate

For testing or internal use, a self-signed certificate is the quickest way to get started. We can create it directly inside the data directory, but keeping them in a separate directory is cleaner.

[postgres@pgdb01 ~]$ mkdir -p /pgData/pgsql17/certs
[postgres@pgdb01 ~]$ cd /pgData/pgsql17/certs
[postgres@pgdb01 certs]$

-- Generate private key + certificate valid for 365 days
[postgres@pgdb01 certs]$ openssl req -new -x509 -days 365 -nodes -text \
  -out server.crt \
  -keyout server.key \
  -subj "/CN=pgdb01"
.........+........+...+...+....+..+...............+.+..+...+.......+..+...................+............+.........+...+...........+++++++++++++++++++++++++++++++++++++++*......+.........+...........+...+......+....+..+...+++++++++++++++++++++++++++++++++++++++*...............+.+.........+...+..............+......+.......+........+...+............+.........+..........+...+..+......+.......+...+.........+.......................+.+..+....+...........+....+.......................+.+......+...+...............+.....+...+....+...+..+...+......+.+......+...+.....+.+.....+...............+.+...+.........+......+..+............+.+......+...+...+..............+....+...........+.+............+...+..+...+...+....+......+.....+.........+.+...........+......+..........+........+.......+..+.+.....+.+........+.+..+............+.......+.....+.........+...................+......+......+........+...+...+...............+.+.....+.+..+.+...........+.+........+....+......+.....+.......+............+..+....+..+...+..........+..+..........+..+.+...............+..+....+...+...+..+...+....+.....+......+.......+........+....+.........++++++
..........+...+.............+..+............+....+.....+.+.........+.....+.+++++++++++++++++++++++++++++++++++++++*........+.......+...+...+.....+++++++++++++++++++++++++++++++++++++++*.+......+........+......+.+.................+...+.........+......+...+.+...+........+...+....+..+..........+...+......+..+...+.+...+.....+.+..+.........+....+......+........+.+.....+......+.............+..+....+.....+............+...............+...+.+........+.+..+....+...+..+.+..+.......+......+..+...+....+......+.....+...+.+..............+...................+......+........................+......+......+.....+.+..+.............+.....++++++
-----
[postgres@pgdb01 certs]$


-- Secure the private key (MANDATORY - PostgreSQL refuses to start otherwise)
[postgres@pgdb01 certs]$ chmod 600 server.key
[postgres@pgdb01 certs]$ chown postgres:postgres server.key server.crt
[postgres@pgdb01 certs]$ 

[postgres@pgdb01 certs]$ ls -l
total 12
-rw-r--r--. 1 postgres postgres 4118 Sep 24 19:06 server.crt
-rw-------. 1 postgres postgres 1700 Sep 24 19:06 server.key
[postgres@pgdb01 certs]$

What each option means:

• -x509: Create a self-signed certificate (not a signing request).
• -nodes: Do not encrypt the private key with a passphrase (so PostgreSQL can start without prompting).
• -days 365: Certificate validity.
• -subj "/CN=pgdb01": Common Name — should match the server hostname.

5. Configure postgresql.conf

[postgres@pgdb01 certs]$ vi /pgData/pgsql17/data/postgresql.conf

# - SSL Connection Settings -
# - SSL -
ssl = on
ssl_cert_file = '/pgData/pgsql17/certs/server.crt'
ssl_key_file = '/pgData/pgsql17/certs/server.key'
ssl_min_protocol_version = 'TLSv1.2'     # reject old, insecure protocols
ssl_max_protocol_version = 'TLSv1.3'
# - SSL END-

[postgres@pgdb01 certs]$ cat /pgData/pgsql17/data/postgresql.conf | grep ssl
ssl = on
ssl_cert_file = '/pgData/pgsql17/certs/server.crt'
ssl_key_file = '/pgData/pgsql17/certs/server.key'
ssl_min_protocol_version = 'TLSv1.2'     # reject old, insecure protocols
ssl_max_protocol_version = 'TLSv1.3'
[postgres@pgdb01 certs]$

—————————————————————– OR   ——————–            do the same using ALTER SYSTEM (written to postgresql.auto.conf):

postgres=# ALTER SYSTEM SET ssl = 'on';
ALTER SYSTEM
postgres=# ALTER SYSTEM SET ssl_cert_file = '/pgData/pgsql17/certs/server.crt';
ALTER SYSTEM
postgres=# ALTER SYSTEM SET ssl_key_file = '/pgData/pgsql17/certs/server.key';
ALTER SYSTEM
postgres=# ALTER SYSTEM SET ssl_min_protocol_version = 'TLSv1.2';
ALTER SYSTEM
postgres=#
postgres=# ALTER SYSTEM SET ssl_max_protocol_version = 'TLSv1.3';
ALTER SYSTEM
postgres=#

⚠️ Note:SSL settings need a reload.

6. Configure pg_hba.conf to Enforce SSL

Setting ssl = on only allows SSL. To force clients to use it, use hostssl entries in pg_hba.conf.

[postgres@pgdb01 data]$ vi /pgData/pgsql17/data/pg_hba.conf

# TYPE       DATABASE   USER   ADDRESS            METHOD
# Allow ONLY SSL connections from the application subnet
hostssl      all        all    192.168.2.0/24     scram-sha-256

# Explicitly reject any NON-SSL TCP connection
hostnossl    all        all    0.0.0.0/0          reject


[postgres@pgdb01 certs]$ egrep "^hostssl|^hostnossl" /pgData/pgsql17/data/pg_hba.conf
hostssl      all        all    192.168.2.0/24     scram-sha-256
hostnossl    all        all    0.0.0.0/0          reject
[postgres@pgdb01 certs]$

7. Reload PostgreSQL & Verify SSL is ON

-- If only SSL/pg_hba settings changed, a reload is enough:
[postgres@pgdb01 ~]$ pg_ctl -D /pgData/pgsql17/data reload
server signaled
[postgres@pgdb01 ~]$

[postgres@pgdb01 ~]$ psql
psql (17.11)
Type "help" for help.

postgres=# SHOW ssl;
 ssl
-----
 on
(1 row)

postgres=# SELECT name, setting FROM pg_settings WHERE name LIKE 'ssl%' ORDER BY name;
                  name                  |             setting
----------------------------------------+----------------------------------
 ssl                                    | on
 ssl_ca_file                            |
 ssl_cert_file                          | /pgData/pgsql17/certs/server.crt
 ssl_ciphers                            | HIGH:MEDIUM:+3DES:!aNULL
 ssl_crl_dir                            |
 ssl_crl_file                           |
 ssl_dh_params_file                     |
 ssl_ecdh_curve                         | prime256v1
 ssl_key_file                           | /pgData/pgsql17/certs/server.key
 ssl_library                            | OpenSSL
 ssl_max_protocol_version               | TLSv1.3
 ssl_min_protocol_version               | TLSv1.2
 ssl_passphrase_command                 |
 ssl_passphrase_command_supports_reload | off
 ssl_prefer_server_ciphers              | on
(15 rows)

postgres=#

-- Test Connection from PostgreSQL DB server using -h 192.168.2.21

[postgres@pgdb01 ~]$ psql -h 192.168.2.21 -U postgres -d postgres
Password for user postgres:
psql (17.11)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "192.168.2.21" at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql)
postgres=#
postgres=# SELECT a.pid, a.usename, a.client_addr, s.ssl, s.version, s.cipher, s.bits
FROM pg_stat_ssl s
JOIN pg_stat_activity a ON a.pid = s.pid
WHERE a.client_addr IS NOT NULL;
 pid  | usename  | client_addr  | ssl | version |         cipher         | bits
------+----------+--------------+-----+---------+------------------------+------
 4188 | postgres | 192.168.2.21 | t   | TLSv1.3 | TLS_AES_256_GCM_SHA384 |  256
(1 row)

postgres=#

SSL is ON! The server is now ready to accept encrypted connections.

8. Connect From a Client Using SSL

[sugi@client01 ~]$ hostname -i
192.168.2.50
[sugi@client01 ~]$
[sugi@client01 ~]$ psql "host=192.168.2.21 port=5432 dbname=postgres user=postgres sslmode=require"
Password for user postgres:
psql (17.10, server 17.11)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql)
Type "help" for help.

postgres=#

Look for this line — it proves the session is encrypted:

SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384)   <-- ENCRYPTED!

-- Confirm from inside the session
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "192.168.2.21" at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql)
postgres=#


-- See SSL status of all sessions
postgres=# SELECT a.pid, a.usename, a.client_addr, s.ssl, s.version, s.cipher, s.bits
FROM pg_stat_ssl s
JOIN pg_stat_activity a ON a.pid = s.pid
WHERE a.client_addr IS NOT NULL;
 pid  | usename  | client_addr  | ssl | version |         cipher         | bits
------+----------+--------------+-----+---------+------------------------+------
 4188 | postgres | 192.168.2.21 | t   | TLSv1.3 | TLS_AES_256_GCM_SHA384 |  256
 4197 | postgres | 192.168.2.50 | t   | TLSv1.3 | TLS_AES_256_GCM_SHA384 |  256
(2 rows)

postgres=#

9. Test That Non-SSL Connections Are Rejected

[sugi@client01 ~]$ hostname -i
192.168.2.50
[sugi@client01 ~]$
[sugi@client01 ~]$ psql "host=192.168.2.21 dbname=postgres user=postgres sslmode=disable"
psql: error: connection to server at "192.168.2.21", port 5432 failed: FATAL:  pg_hba.conf rejects connection for host "192.168.2.50", user "postgres", database "postgres", no encryption
[sugi@client01 ~]$

 Working as intended. The hostnossl ... reject rule blocks every unencrypted connection.