Generate self-signed SSL certificates for PostgreSQL server and client

generate-pg-ssl.md

CNs are important!!! -days 3650

Create a Certificate Signing Request (CN=localhost)

umask u=rw,go= && openssl req -days 3650 -new -text -nodes -subj '/C=US/ST=Massachusetts/L=Bedford/O=Personal/OU=Personal/[email protected]/CN=localhost' -keyout server.key -out server.csr

Generate self-signed certificate

umask u=rw,go= && openssl req -days 3650 -x509 -text -in server.csr -key server.key -out server.crt

Also make the server certificate to be the root-CA certificate

umask u=rw,go= && cp server.crt root.crt

Remove the now-redundant CSR

rm server.csr

Generate client certificates to be used by clients/connections

Create a Certificate Signing Request (CN=db-user)

umask u=rw,go= && openssl req -days 3650 -new -nodes -subj '/C=US/ST=Massachusetts/L=Bedford/O=Personal/OU=Personal/[email protected]/CN=chesco' -keyout client.key -out client.csr

Create a signed certificate for the client using our root certificate

umask u=rw,go= && openssl x509 -days 3650 -req  -CAcreateserial -in client.csr -CA root.crt -CAkey server.key -out client.crt

Remove the now-redundant CSR

rm client.csr

Edit postgresql.conf

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

Edit pg_hba.conf

hostssl all	all	::1/128		cert clientcert=1                
hostssl	all	all	127.0.0.1/32	cert clientcert=1
#host  all  all  127.0.0.1/32  md5
#host	all 	all 	127.0.0.1/32	 	trust
#host  	all  	all  	::1/32  	trust

Connect:

psql "host=localhost dbname=dbName user=chesco sslmode=verify-ca \
 sslcert=client.crt \
 sslkey=client.key \
 sslrootcert=root.crt"

You can also put certificate files into default directory, rename them and run psql command with less option:

mkdir ~/.postgresql
cp client.crt client.key root.crt .postgresql/
cd ~/.postgresql	
mv client.crt postgresql.crt
mv client.key postgresql.key

psql "host=localhost dbname=dbName user=chesco sslmode=verify-full"

In my case I was using PostgreSQL 15 in Debian 12. Had to put following method in pg_hba.conf file:

cert clientcert=verify-full