Here are a set of commands to create self-signed certificates.
# Create a Certificate Signing Request
umask u=rw,go= && openssl req -new -text -nodes -subj '/C=US/ST=Massachusetts/L=Bedford/O=Personal/OU=Personal/emailAddress=example@example.com/CN=example-postgres-host.com' -keyout server.key -out server.csr
# Generate self-signed certificate
umask u=rw,go= && openssl req -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
umask u=rw,go= && openssl req -new -nodes -subj '/C=US/ST=Massachusetts/L=Bedford/O=Personal/OU=Personal/emailAddress=example@example.com/CN=example' -keyout client.key -out client.csr
# Create a signed certificate for the client using our root certificate.
umask u=rw,go= && openssl x509 -req -CAcreateserial -in client.csr -CA root.crt -CAkey server.key -out client.crt
# Remove the now-redundant CSR
rm client.csr
I use them to create self-signed certificates for my Postgres
installations.
For the purposes of Postgres connections, you need to replace
CN=example with
CN=actual-database-user-name in the command titled
'Create a signed certificate for the client'. Then place the
server.* and root.* files in the Postgres' data directory. Place the
client.* and root.crt files on the client machine and use the following
format to connect, say
psql utility, to the database:
PGSSLMODE=verify-ca PGSSLCERT=client.crt PGSSLKEY=client.key PGSSLROOTCERT=root.crt psql -h postgres-server.com -p 5432 -U postgres -d postgres
Of course, you also need
ssl = on in your postgresql.conf file.
