Generating self-signed SSL certificates

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.