Debian 10 Buster
Sponsored Link

PostgreSQL : SSL/TLS Setting
2019/08/07
 
Enable SSL/TLS connection to PostgreSQL.
[1]
Get SSL certificates, refer to here.
This example is based on the case that SSL certificates are gotten under the [/etc/letsencrypt/live/dlp.srv.world] and set the [Common Name] as [dlp.srv.world].
[2] Copy certificates created above and configure PostgreSQL.
root@dlp:~#
cp /etc/letsencrypt/live/dlp.srv.world/* /etc/postgresql/11/main/

root@dlp:~#
chown postgres. /etc/postgresql/11/main/*.pem

root@dlp:~#
chmod 600 /etc/postgresql/11/main/*.pem

root@dlp:~#
vi /etc/postgresql/11/main/postgresql.conf
# line 98: change

ssl =
on
# line 100: change to your own certs

ssl_ca_file = '
/etc/postgresql/11/main/chain.pem
'
ssl_cert_file = '
/etc/postgresql/11/main/cert.pem
'
ssl_key_file = '
/etc/postgresql/11/main/privkey.pem
'
root@dlp:~#
vi /etc/postgresql/11/main/pg_hba.conf
# line 92: change like follows

# all users except localhost with peer are required SSL/TLS

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
#host    all             all             127.0.0.1/32            md5
hostssl all             all             127.0.0.1/32            md5
hostssl all             all             10.0.0.0/24             md5
hostssl all             all             ::1/128                 md5

root@dlp:~#
systemctl restart postgresql
# verify accessing

# no SSL/TLS connection from localhost with peer

debian@dlp:~$
psql testdb

psql (11.4 (Debian 11.4-1))
Type "help" for help.

testdb=>

# for other connections, connection is on SSL/TLS

debian@dlp:~$
psql "user=debian host=localhost dbname=testdb"

Password:
psql (11.4 (Debian 11.4-1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

testdb=>

# from other hosts, connection is on SSL/TLS

debian@node01:~$
psql "host=dlp.srv.world dbname=testdb"

Password:
psql (11.4 (Debian 11.4-1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

testdb=>
Matched Content