Postgres SSL Setup
  • 29 Oct 2024
  • 1 Minute to read
  • Dark
    Light
  • PDF

Postgres SSL Setup

  • Dark
    Light
  • PDF

Article summary

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.


ESC

Eddy AI, facilitating knowledge discovery through conversational intelligence