Skip to content

Instantly share code, notes, and snippets.

@dincosman

dincosman/configure_ssl_for_postgresql.txt

Last active April 29, 2024 20:29
Show Gist options
  • Save dincosman/ac2288982199264ccf9e42b58d718d4c to your computer and use it in GitHub Desktop.
Save dincosman/ac2288982199264ccf9e42b58d718d4c to your computer and use it in GitHub Desktop.
Download ZIP
Enable TLS/SSL for your PostgreSQL database
Raw
configure_ssl_for_postgresql.txt
[postgres@posvt01 ~]# vi csr_details.txt
[ req ]
default_bits = 2048
default_md = sha256
req_extensions = v3_req
distinguished_name = dn
prompt = no
[ dn ]
C = TR
ST = Ankara
L = Cankaya
OU = BJKIT
O = BJK
CN = pgcluster.localdomain
[ v3_req ]
subjectAltName = @alt_names
[ alt_names ]
DNS.1 = pgcluster.localdomain
DNS.2 = posvt01.localdomain
DNS.3 = posvt02.localdomain
IP.1 = 192.168.60.101
IP.2 = 192.168.60.102
[postgres@posvt01 ~]$ openssl req -newkey rsa:2048 -sha256 -nodes -keyout keys/server.key -config csr_details.txt -out certs/server.csr
Generating a 2048 bit RSA private key
................................................................................................................++
................................................................................................................++
writing new private key to 'keys/server.key'
-----
#-- We delivered our request server.csr to Certifcation Authority.
#-- After certification authority signed it, put it in under /etc/ssl/postgres directory with root certificate.
#-- If you have also intermediate certificates between your server and root certificates. Make a bundle certificate for root containing intermediate.
[root@posvt01 ~]# cat intermediate.cer > /etc/ssl/postgres/root.crt
[root@posvt01 ~]# cat root.cer >> /etc/ssl/postgres/root.crt
[postgres@posvt01 ~]$ cd certs
[postgres@posvt01 certs]$ mv server.crt /etc/ssl/postgres/server.crt
[postgres@posvt01 ~]$ cd ../keys
[postgres@posvt01 keys]$ mv server.key /etc/ssl/postgres/server.key
[root@posvt01 postgres]# cd /etc/ssl/postgres
[root@posvt01 postgres]# ls -ls
total 16
8 -rw-r--r-- 1 postgres postgres 4542 May 12 10:24 root.crt
4 -rw-r--r-- 1 postgres postgres 2384 May 12 10:24 server.crt
4 -rw------- 1 postgres postgres 1704 May 12 10:24 server.key
[postgres@posvt01 ~]$ chmod 0600 /etc/ssl/postgres/server.key
[postgres@posvt01 postgres]$ scp /etc/ssl/postgres/* postgres@posvt02:/etc/ssl/postgres/
#-- Configure ssl related parameters
[postgres@s001posvt01 keys]$ patronictl -c /etc/patroni.yml edit-config
...
ssl: on
ssl_cert_file: /etc/ssl/postgres/server.crt
ssl_key_file: /etc/ssl/postgres/server.key
...
#-- Configure your pg_hba.conf to force ssl_certificate for clients, edit all your current application access rules to use hostssl
[postgres@s001posvt01 ~]$ vi $PGDATA/pg_hba.conf
...
local all postgres peer
local replication replicator trust
hostnossl all all 0.0.0.0/0 reject
hostssl replication replicator 127.0.0.1/32 scram-sha-256
hostssl replication replicator 192.168.60.0/24 scram-sha-256
hostssl all all 127.0.0.1/32 scram-sha-256
hostssl all all 192.168.60.0/24 scram-sha-256
hostssl all all 192.168.64.0/24 scram-sha-256
...
#-- Reload config on only production server
postgres=# SELECT pg_reload_conf();
#-- Reload config on all cluster nodes
[postgres@posvt01 keys]$ patronictl -c /etc/patroni.yml reload pgcluster
#-- Check ssl used or not
SQL> SELECT datname,usename, ssl, client_addr
FROM pg_stat_ssl
JOIN pg_stat_activity
ON pg_stat_ssl.pid = pg_stat_activity.pid;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment