PostgreSQL with SSL auth (Java client)

PostgreSQL Server

1. Generate certificates

Download easyrsa2 from github and extract it
 

# ./easyrsa build-ca
# ./easyrsa build-server-full postgresql-server
# ./easyrsa build-client-full postgresql-client

 

This will generate ca.crt in pki folder, postgres-server.crt, postgres-client.crt in pki/issued folder and postgres-server.key and postgres-client.key in pki/private folder.

 

PostgreSQL JDBC library cannot read .key file, which is why we have to convert the key to DER format (.pk8) file.

 

openssl pkcs8 -topk8 -outform DER -in postgres-client.key -out postgres-client.key.pk8 -nocrypt 

 

Give proper unix permissions to the certificates and keys, for eg.

 

# chown postgres:postgres postgres-server.key 

# chown postgres:postgres postgres-server.crt

# chmod go-r postgres-server.key

  

2. Edit postgresql.conf

ssl = on         

ssl_cert_file = '/opt/postgres-sec/postgres-server.crt'   
ssl_key_file = '/opt/postgres-sec/postgres-server.nopass.key'
ssl_ca_file = '/opt/postgres-sec/ca.crt'

2. Edit pg_hba.conf

On PostgreSQL > 12.0

 

hostssl    all     all      0.0.0.0/0     cert

 

On PostgreSQL < 12.0

 

hostssl    all     all      0.0.0.0/0     cert     clientcert=verify-ca

 

This will allow only remote machines with ssl turned on to connect to the server with a password and a valid certificate without verifying 'cn' attribute in the certificate with the hostname. More details on pg_hba.conf file and options supported are available here.

 

Note that, in older PostgreSQL servers, the only option is full certificate verification including cn. So, make sure that your client certificate has cn equal to your username.

Java Client

Using ConnectionPooling (Apache DBCP2)

Using Plain JDBC