CentOS 7
Sponsored Link

PostgreSQL 12 : PostgreSQL over SSL/TLS2020/01/31

 
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/www.srv.world] and set the [Common Name] as [www.srv.world].
[2] Copy certificates and configure PostgreSQL.
[root@www ~]#
cp /etc/letsencrypt/live/www.srv.world/* /var/opt/rh/rh-postgresql12/lib/pgsql/data/

[root@www ~]#
chown postgres. /var/opt/rh/rh-postgresql12/lib/pgsql/data/*.pem

[root@www ~]#
chmod 600 /var/opt/rh/rh-postgresql12/lib/pgsql/data/*.pem

[root@www ~]#
vi /var/opt/rh/rh-postgresql12/lib/pgsql/data/postgresql.conf
# line 100: uncomment and change

ssl =
on
# line 101: uncomment and change to your own certs

ssl_ca_file = '
/var/opt/rh/rh-postgresql12/lib/pgsql/data/chain.pem
'
ssl_cert_file = '
/var/opt/rh/rh-postgresql12/lib/pgsql/data/cert.pem
'
#ssl_crl_file = ''
ssl_key_file = '
/var/opt/rh/rh-postgresql12/lib/pgsql/data/privkey.pem
'
[root@www ~]#
vi /var/opt/rh/rh-postgresql12/lib/pgsql/data/pg_hba.conf
# line 77 and later: settings for authentication methods

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident
# add to the end
# [hostssl] ⇒ use TCP/IP connection only when enabling SSL/TLS
# [10.0.0.0/24] ⇒ allowed network to connect
# [md5] ⇒ use MD5 passdword method
hostssl all             all             10.0.0.0/24             md5

[root@www ~]#
systemctl restart rh-postgresql12-postgresql
[3] Verify settings to connect to PostgreSQL Database from hosts in network you allowed to connect.
# no SSL/TLS on Unix socket connection

[cent@www ~]$
psql testdb

psql (12.1)
Type "help" for help.

testdb=> \q

# on TCP/IP connection, SSL/TLS is enabled

# on SSL/TLS connection, messages [SSL connection ***] is shown

[cent@www ~]$
psql -h www.srv.world testdb

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

testdb=> \q


# SSL/TLS is enabled from other hosts, too

[root@node01 ~]#
psql -h www.srv.world -d testdb -U cent

Password for user cent:
psql (12.1)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

testdb=>
Matched Content