Ubuntu 18.04
Sponsored Link

PostgreSQL : SSL/TLS Setting
2018/06/25
 
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/10/main/

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

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

root@dlp:~#
vi /etc/postgresql/10/main/postgresql.conf
# line 79: uncomment and change

ssl =
on
# line 84: uncomment and change

ssl_cert_file = '
/etc/postgresql/10/main/cert.pem
'
ssl_key_file = '
/etc/postgresql/10/main/privkey.pem
'
ssl_ca_file = '
/etc/postgresql/10/main/chain.pem
'
root@dlp:~#
vi /etc/postgresql/10/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

# no SSL/TLS connection from localhost with peer

ubuntu@dlp:~$
psql testdb

psql (10.4 (Ubuntu 10.4-0ubuntu0.18.04))
Type "help" for help.

testdb=>

# for other connections, connection is on SSL/TLS

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

Password:
psql (10.4 (Ubuntu 10.4-0ubuntu0.18.04))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

testdb=>

# from other hosts, connection is on SSL/TLS

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

Password:
psql (10.4 (Ubuntu 10.4-0ubuntu0.18.04))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

testdb=>
Matched Content