- 29 Oct 2024
- 1 Minute to read
- Print
- DarkLight
- PDF
Postgres SSL Setup
- Updated on 29 Oct 2024
- 1 Minute to read
- Print
- DarkLight
- PDF
Changes to the postgres.conf file performed in /var/lib/pgsql/9.6/data/ on the server where postgres is installed (Either application server or its own server) Modify the postgres.conf:
#authentication_timeout = 1min # 1s-600s
ssl = on # (change requires restart)
ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH' # allowed SSL ciphers
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'root.crt'
Have to add the hostssl entries to pg_hba.conf. In the example below, you set specific hosts using the /32 or network hosts using the 192.168.1.0/24 as an example for the second one
hostssl all all 192.168.1.15/32 md5
hostssl all all 192.168.1.0/24 md5
Restart postgresql-9.6
To verify the ssl configuration is set up and confirmed correctly (test from the server where postgres is installed):
[root@tstdsc01-bld data]# psql -U brainspace -h localhost dbname=brainspace
Password for user brainspace:
psql (9.6.3)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
[2018-05-16T16:44:27,620] [localhost-startStop-1] INFO CoreModule Search pool being setup with parallelism: 16
[2018-05-16T16:44:28,307] [localhost-startStop-1] INFO PostgresConnector Database connection pool created
[host=tstdsc01-bld.dcl.bs] [port=5432] [database=brainspace] [user=brainspace] [maxConnections=50] [ssl=true]
[sslFactory=null]
SSL is now working with 9.6 postgres and Brainspace 6:
Modify the brainspace.DBConfig from all servers connecting to the database.
The DBhost will need to match the hostname or IP of the server where Postgres is installed:
[root@tstdsc01-run .brainspace]# cat brainspace.DBConfig
#written by encrypt-db-password at 2018-05-15 14:24:15
#Tue May 15 14:24:15 CDT 2018
dbPasswordEncrypted=FtkghV9Q2FuhIlFlkLKfmA\=\=
dbUser=brainspace
dbPort=5432
dbName=brainspace
dbHost=tstdsc01-bld.dcl.bs
ssl=true
(sslFactory=org.postgresql.ssl.NonValidatingFactory to be used when using self signed or internally signed certificates for the connection)
Restart services on all 3 servers for the application to use the new SSL connection.