SSL Certificates For PostgreSQL

This describes how to set up ssl certificates to enable encrypted connections from PgAdmin on some client machine to postgresql on a server machine. The assumption is that postgresql (compiled with ssl support) and openssl are already installed and functional on the server (Linux). PgAdmin is already installed on the client (either Windows or Linux).

On the server, three certificates are required in the data directory. CentOS default is /var/lib/pgsql/data/:
root.crt (trusted root certificate)
server.crt (server certificate)
server.key (private key)

Issue commands as root.

sudo -

cd /var/lib/pgsql/data

Generate a private key (you must provide a passphrase).

openssl genrsa -des3 -out server.key 1024

Remove the passphrase.

openssl rsa -in server.key -out server.key

Set appropriate permission and owner on the private key file.

chmod 400 server.key
chown postgres.postgres server.key

Create the server certificate.
-subj is a shortcut to avoid prompting for the info.
-x509 produces a self signed certificate rather than a certificate request.

openssl req -new -key server.key -days 3650 -out server.crt -x509 -subj '/C=CA/ST=British Columbia/L=Comox/O=TheBrain.ca/CN=thebrain.ca/[email protected]'

Since we are self-signing, we use the server certificate as the trusted root certificate.

cp server.crt root.crt

You'll need to edit pg_hba.conf. For example:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
# "local" is for Unix domain socket connections only
local   all         all                               trust
# IPv4 local connections:
host    all         all         127.0.0.1/32          trust

# IPv4 remote connections for authenticated users
hostssl all         www-data    0.0.0.0/0             md5 clientcert=1
hostssl all         postgres    0.0.0.0/0             md5 clientcert=1

You need to edit postgresql.conf to actually activate ssl:

ssl = on

Postgresql server must be restarted.

/etc/init.d/postgresql restart

If the server fails to (re)start, look in the postgresql startup log, /var/lib/pgsql/pgstartup.log default for CentOS, for the reason.

On the client, we need three files. For Windows, these files must be in %appdata%\postgresql\ directory. For Linux ~/.postgresql/ directory.
root.crt (trusted root certificate)
postgresql.crt (client certificate)
postgresql.key (private key)

Generate the the needed files on the server machine, and then copy them to the client. We'll generate the needed files in the /tmp/ directory.

First create the private key postgresql.key for the client machine, and remove the passphrase.

openssl genrsa -des3 -out /tmp/postgresql.key 1024

openssl rsa -in /tmp/postgresql.key -out /tmp/postgresql.key

Then create the certificate postgresql.crt. It must be signed by our trusted root (which is using the private key file on the server machine). Also, the certificate common name (CN) must be set to the database user name we'll connect as.

openssl req -new -key /tmp/postgresql.key -out /tmp/postgresql.csr -subj '/C=CA/ST=British Columbia/L=Comox/O=TheBrain.ca/CN=www-data'

openssl x509 -req -in /tmp/postgresql.csr -CA root.crt -CAkey server.key -out /tmp/postgresql.crt -CAcreateserial

Copy the three files we created from the server /tmp/ directory to the client machine.

Copy the trusted root certificate root.crt from the server machine to the client machine (for Windows pgadmin %appdata%\postgresql\ or for Linux pgadmin ~/.postgresql/). Change the file permission of postgresql.key to restrict access to just you (probably not needed on Windows as the restricted access is already inherited). Remove the files from the server /tmp/ directory.

view as pdf | print

Share this page:

14 Comment(s)

Add comment

Comments

By: Gurjeet Singh

Using /tmp directory to generate certificates is simply asking for trouble. I am not sure what permissions OpenSSL uses when creating files, but anything in /tmp is world-readable by default, hence insecure since anybody can copy it from there before you get a chance to delete the files.

Regards,

By: Jason Smith

I think the same process can be used if you are using other certificates. I use a Quick SSL and used the above process and it was successful. What do you think, will i face any problems later?

By: Vince Herried

in the last steps u say copy the the three files generated on

/tmp on the server to the client.

Where on the client?

u say copy root.crt into pgadmin ~/.posgresql/

I have no directory by that name. Huh... user pgadmin?

or user 'vince' ( me )

I'm running Fedora F14 postgresql-8.4.7-1

By:

As user "vince". That is, as the user (on the client machine) you normally work as.

You need to create the directory on the client machine:mkdir ~/.postgresql

After copying the three files from the server (/tmp/{postgresql.key,postgresql.crt,root.crt}) to the client machine (into directory ~/.postgresql/), you'll need to set the permission of the key to not world readable: chmod 0400 ~/.postgresql/postgresql.key

On windows, permissions in the are handled automatically for you.

By: Jean-Yves F. Barbier

Hi,

I followed this HOWTO but found the last command is wrong; ORG code is:

openssl x509 -req -in /tmp/postgresql.csr -CA root.crt -CAkey server.key -out /tmp/postgresql.crt -CAcreateserial

but should be:

openssl x509 -req -in server.req -out /tmp/postgresql.csr -CA root.crt -CAkey server.key -out /tmp/postgresql.crt -CAcreateserial

Anyway, thanks for this excellent HOWTO as it is only using the user name and thus permits a real easy use when you don't have an official IP address nor domain :)

JYFB

By: gwyn

You must add "clientcert=1" to hostssl options for checking the client certificates, otherwise everyone will be granted access in your setup:
hostssl all postgres 0.0.0.0/0 trust clientcert=1
See: PostgreSQL documentation, 17.9.1. Using Client Certificates

By: Jeff E Mandel

If you want to use this with certificates from CACert.org, you are limited in what you can place in the CN of the certificate. The workaround is to create server certificates for users in your domain and use a map. Thus, I create a csr on my client machine:

cd ~/.postgresql

openssl req -out postgresql.csr -new -newkey rsa:2048 -nodes -keyout postgresql.key

Specifying CN=myusername.mydomain.org. Paste the CSR into cacert.org, and save the resulting certificate as ~/.postgresql/postgresql.crt (you might also need place the cacert.org root cert in that directory).

On the server, in pg_hba:

hostnossl all all 0.0.0.0/0 reject

hostssl all all 0.0.0.0/0 cert map=ssl clientcert=1

In pg_ident:

ssl /^(.*).mydomain\.org$ \1

Now you can connect from the client:

psql -h server.mydomain.org

psql (9.4.6, server 9.4.1)

SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)

Next project - figure out how to make this work with JDBC

By: Slobodan Vesovic

Tnx! Very helpful

By: francis

I just come back from a client demo. Yesterday evening everything was ok, but this morning my servers were down.

What happend is that my client certificate expired during the night. I'm so pissed off.

Please, update the last line of yout tuto :

openssl x509 -req -in /tmp/postgresql.csr -CA root.crt -CAkey server.key -out /tmp/postgresql.crt -CAcreateserial

should be

openssl x509 -days 3650 -req -in /tmp/postgresql.csr -CA root.crt -CAkey server.key -out /tmp/postgresql.crt -CAcreateserial

if you don't want your client cert to be short-lived.

Thanks

By: david

Recent versions of postgres require this in postgresql.conf:

ssl_ca_file = 'root.crt'

By: Chetan

Can we enable ssl connection for IP based postgresql, means without domain name?

By: midipix

With modern pgAdmin4, you'd be better off installing pgAdmin4 on the same server where postgresql is running in server mode (`SERVER_MODE = True` in `config_local.py`), then require valid client certificates as part of your http server configuration. With nginx, that'd be, for instance:

`ssl_client_certificate /etc/easy-rsa/pki/ca.crt;`

`ssl_verify_client on;`

By: MiPham

If I use PostgreSQL 10.16 on Windows 10.

What should "Set appropriate permission and the owner of the private key file" is?

icacls server.key /reset icals server.key /inheritance:r /grant:r "CREATOR OWNER:F"

By: Craig Anderson

Thansk for an excellent article which helped us immensly. We were using Postgresql 11 on a linux box and as of December 2022, we found a couple of things we changed.

1. We ignored all the parameters of 1024 because we found that generated an error of the key being too small. So we left that parameter off and allowed it to default to 2048.

2. We changed the user name when creating the postgresql.csr file from www-data to the name of the database cluster owner. (In our case '_system') and we left the pg_hba.conf set to all instead of specifiying www-data there. Maybe what we did makes it a little less secure, I'm not sure, but it was what we had to do in our case to get things to work.

3. We added a -days 365 parameter to the last command which creates the postgresql.crt file.

Other than that, we followed the article exactly and everything came together, so big thanks to the author for their help.

Best regards