A simple example of setting cert authentication in Postgresql

Products

• VMware Tanzu Greenplum

Issue/Introduction

This article introduces a simple example to configure cert authentication in Postgresql for testing. For PROD, it is still recommended to follow all the steps as described in the doc:

https://www.postgresql.org/docs/12/auth-cert.html 

https://www.postgresql.org/docs/12/ssl-tcp.html

Environment

Product Version: 10.15

Resolution

Environment

server: 192.168.26.55

client: 192.168.26.53

​postgresql.conf

ssl = on

ssl_ca_file = 'root.crt'

​pg_hba.conf on the server side

hostssl  all       all       192.168.26.53/32   cert

On the server:

##create self-signed server certificate
openssl req -new -x509 -days 365 -nodes -text -out server.crt \
  -keyout server.key -subj "/CN=192.168.26.55"

cp server.crt root.crt

chmod og-rwx server.key
 
##create client certificate, here postgres is the database user name
openssl req -new -nodes -keyout client.key -out client.csr -subj '/CN=postgres' 
 
openssl x509 -req -CAcreateserial -in client.csr -CA root.crt -CAkey server.key -out client.crt
 
chmod og-rwx client.key

scp client.key postgres@192.168.26.53:/home/gpadmin
scp root.crt postgres@192.168.26.53:/home/gpadmin
scp client.crt postgres@192.168.26.53:/home/gpadmin

On the client:
Run below command to connect database:

psql 'host=pgfailover_monitor port=5432 dbname=postgres user=postgres sslcert=client.crt sslkey=client.key sslrootcert=root.crt'