Configure PostgreSQL server for SSL/TLS connection

1. Create / download trusted root certificate.

2. Create server certificate and private keys for your PostgreSQL server.

   server.crt (server certificate)

   server.key (private key)

   Please note that certificate's subject CN (Common Name) must be equal to PostgreSQL server's domain name.

   In case your key/certificate is in a different format than specified, you can convert it following the guide in Conversions between different keystores and certificate types.

3. Copy your root certificate, server certificate and private key to PostgreSQL's /data folder, named as root.crt, server.key and server.crt .

4. Verify if the file postgresql.conf in PostgreSQL's data folder supports SSL connection, meaning the configuration property "ssl" has to be set accordingly: ssl = on

5. Configure the hosts that are required to use SSL/TLS connection in pg_hba.conf in PostgreSQL's data folder, by using hostssl instead of host, e.g.

   hostssl <database name> <db user name> <IP of the client>/32 md5

6. Restart the postgresql service

Configure the client

1. Create client certificate and private key and sign the certificate by the server's root certificate.

   root.crt ( trusted certificate authorities )

   postgresql .crt (client certificate)

   postgresql .key ( client private key )

   Note that certificate's CN (Common Name) must be equal to the database user name you’ve set in the pg_hba.conf server configuration.

2. These files must be in the following directories:
   

• %appdata%\postgresql\ - This directory is used by the installer

• C:\Windows\system32\config\systemprofile\AppData\Roaming\postgresql\ - This directory is used by YSoft SafeQ