How to figure SSL for OSS PostgreSQL

Products

• VMware Tanzu Greenplum

Issue/Introduction

This article details how to set up SSL for the OSS PostgreSQL server.

Resolution

1) Go to PostgreSQL data directory.

2) Create a private key:

openssl genrsa -des3 -out server.key 2048

3) Remove the password:

openssl rsa -in server.key -out server.key

4) Set the proper privileges and owner for the private key:

chmod 400 server.key
chown <user>:<user-group> server.key

5) Create the server certificate based on the private key just generated in previous steps and provide related information:

openssl req -new -key server.key -days 3650 -out server.crt -x509

6) Copy the server certificate just generated:

cp server.crt root.crt

7) Configure postgresql.conf:

ssl = on
ssl_ca_file = 'root.crt'
ssl_key_file = 'server.key'

8) Edit pg_hba.conf, change related entries from 'host' to 'hostssl', for example:

hostssl    all             jjiang          172.16.76.0/24       trust

9) Restart PostgreSQL

10) Test from a remote server:

[gpadmin@localhost ~]$ psql -h 172.16.76.1 -U jjiang -p 5432 postgres
Timing is on.
psql (8.3.23, server 11.2)
WARNING: psql version 8.3, server version 11.0.
         Some psql features might not work.
SSL connection (cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256)
Type "help" for help.

postgres=#