General principle. PostgreSQL allows authentication via X.509 certificates. The certificate common name (CN) has to equal the PostgreSQL user (i.e. role) name. The certificate has to be signed by a CA that the PostgreSQL server trusts.
How-to. You probably want to run your own CA to sign certificates for PostgreSQL clients. There are many step by step articles around for doing this with OpenSSL. Hence I will focus on the PostgreSQL part.
- Configure your PostgreSQL server to trust your CA only. This is done by setting
ssl_ca_file=to the path of your CA cert in your postgresql.conf config. Note that it must be readable by the postgresql user. - Adjust your pg_hba.conf to “TYPE”
hostsslinstead ofhostand set the “METHODE” tocert. - Generate a key + CSR (certificate signing request) on the client and sign the request with our CA. Place the key and the certificate in ~/.postgresql/ as postgresql.key and postgresql.crt on the client.
- You should be able to connect for example with
psql. Depending on the client, you may adjust the client settings via parameter key words. Relevant are the keyssslmode,sslcertandsslkey.
What you get. The use of SSL/X.509 authentification has two advantages:
- Private key authentication: You don’t use a password that a man in the middle attacker could steel.
- Your secret for authentication is nicely separated from the rest of your configuration etc.
- Your communication is encrypted, but …
A man in the middle might steel informations that you send or retrieve from the server.
Verifying the server. You can use the same private CA for this purpose. Generate key + CSR for the server and sign it. Use the ssl_cert_file and ssl_key_file options in the servers postgresql.conf to point so key/crt. Place the root certificate of your CA in ~/.postgresql/root.crt on the client or use the sslrootcert parameter key on your client. I would recommend to specify sslmode=verify-ca to force the verification. Syntax for testing via psql:
psql postgres://<user>@<host>/<dbname>?sslmode=verify-ca
