PostgreSQL 10 : PostgreSQL over SSL/TLS2020/01/24 |
|
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-postgresql10/lib/pgsql/data/ [root@www ~]# chown postgres. /var/opt/rh/rh-postgresql10/lib/pgsql/data/*.pem [root@www ~]# chmod 600 /var/opt/rh/rh-postgresql10/lib/pgsql/data/*.pem
[root@www ~]#
vi /var/opt/rh/rh-postgresql10/lib/pgsql/data/postgresql.conf # line 79: uncomment and change ssl = on
# line 84: uncomment and change to your own certs ssl_cert_file = ' /var/opt/rh/rh-postgresql10/lib/pgsql/data/cert.pem 'ssl_key_file = ' /var/opt/rh/rh-postgresql10/lib/pgsql/data/privkey.pem 'ssl_ca_file = ' /var/opt/rh/rh-postgresql10/lib/pgsql/data/chain.pem '
[root@www ~]#
vi /var/opt/rh/rh-postgresql10/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-postgresql10-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 (10.6)
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 (10.6)
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 (10.6) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help. testdb=> |
| Sponsored Link |