Establishing SSL Connection to PostgreSQL DB Server

When striving to keep information in your PostgreSQL database secure, the first thing you need to do is encrypt all connections to it to protect authentication credentials (usernames/passwords) and stored data from interception. This guide is intended to help you establish a secure SSL connection to your PostgreSQL container, hosted at the platform.

Below, we’ll explore the appropriate database server configuration required to enable SSL and generate certificates for it. Then, we’ll create and add certs for the client machine and, lastly, will establish a secure connection to our server via pgAdmin tool. So, let’s go on!

PostgreSQL Server Configuration

Obviously, for this tutorial we’ll use an environment with PostgreSQL database inside – you can easily create such if you haven’t done this yet.

1. To start with, connect to your database server via SSH Gate.

2. Now, in order to make it work with SSL, you need to add the following three files to the /var/lib/pgsql/data server directory:

• server.key – private key
• server.crt – server certificate
• root.crt – trusted root certificate

Within this tutorial, we’ll briefly consider how you can generate them by yourself.

So, navigate to the mentioned folder and proceed with the steps below.

3. First of all, let’s create the first file – private key:

• execute the next commands:

1
2

cd /var/lib/pgsql/data
openssl genrsa -des3 -out server.key 1024

During the server.key generation, you’ll be asked for a pass phrase – specify any and confirm it to finish creation.

• Now, in order to work with this key further, it’s required to remove the pass phrase you’ve added previously. Execute the following command for this:

1

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

Re-enter the pass phrase one more time for confirmation.

• Set the appropriate permission and ownership rights for your private key file with the next commands:

1
2

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

4. Now, you need to create server certificate based on your server.key file, e.g.:

1

openssl req -new -key server.key -days 3650 -out server.crt -x509 -subj '/C=US/ST=California/L=PaloAlto/O=Jelastic/CN=mysite.com/[email protected]'

5. Since we are going to sign certs by ourselves, the generated server certificate can be used as a trusted root certificate as well, so just make a copy with the appropriate name:

1

cp server.crt root.crt

Now that you have all three certificate files, you can proceed with the PostgreSQL database configurations required for SSL activation and usage.

6. Open the pg_hba.conf file, located in the same folder, for editing with any preferred terminal editor (vim, for example) or directly via the dashboard.

Replace its default content with the following lines:

1
2
3
4
5
6
7
8

# 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         webadmin    0.0.0.0/0             md5 clientcert=verify-full

1

hostssl all         webadmin    0.0.0.0/0             md5 clientcert=1

Save the updated file.

7. To finish configurations, you need to apply some more changes to the postgresql.conf file.

Navigate to its Security and Authentication section (approximately on the 80th line) and enable SSL by uncommenting the same-named setting and changing its value to “on”. Also, add the new ssl_ca_file parameter below:

1
2

ssl = on
ssl_ca_file = 'root.crt'

Don’t forget to save these changes.

8. Lastly, restart your PostgreSQL container in order to apply new settings:

1

sudo service postgresql restart

Client Certificates

Now, let’s create one more set of SSL certificate files for client instance, in order to support secure connection on both sides.

1. Return to the terminal window with an SSH connection to your PostgreSQL server you’ve operated through during server setup (or reconnect to it) – you’ll need your server certificates for further actions.

Once inside, generate a private key for the client (also without a pass phrase, just as it was done in the previous section), for example, within the tmp directory:

1
2

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

2. Next, create SSL certificate for your PostgreSQL database user (webadmin by default) and sign it with our trusted root.crt file on server.

1
2

openssl req -new -key /tmp/postgresql.key -out /tmp/postgresql.csr -subj '/C=US/ST=California/L=PaloAlto/O=Jelastic/CN=webadmin'
openssl x509 -req -in /tmp/postgresql.csr -CA root.crt -CAkey server.key -out /tmp/postgresql.crt -CAcreateserial

3. After the files – postgresql.key, postgresql.crt, root.crt – are ready, you need to move them to to the .postgresql folder at your client machine (for that, you can use FTP add-on or just copy and paste files content).

Also, if needed, you can set the key read permission for owner only with the chmod 0400 ~/.postgresql/postgresql.key command to achieve more security.

Establish Connection via PgAdmin

Eventually, after server and client configurations are done, you are ready to establish the connection. In our case, we’ll use the pgAdmin 3 tool as an example, so get this application (or any other preferred one) installed beforehand.

1. In order to connect to the DB server via SSL, you need either public IP or endpoint being attached for your PostgreSQL database container.

We’ll consider the latter case – access environment Settings, switch to the Endpoints section and Add new endpoint with the same-named button at the top pane.

2. Now, when you have an access point, run your pgAdmin 3 client and select the New Server Registration option.

In the Properties tab of the opened window, specify the following data:

• Name – any desired connection name (e.g. ssl-to-pgsql)
• Host – access point you’ve added in the first step (Public IP address or endpoint Access URL without port number)
• Port – use the default port 5432 for External IP or endpoint’s Public port (denoted in the same-named section of the appropriate column)
• Username – database user you’ve set the SSL certificate and configurations for (i.e. webadmin by default)
• Password – the corresponding user’s password (sent via email for webadmin or the one you’ve set otherwise)

The rest of the fields can be left unchanged or adjusted according your requirements.

3. Next, switch to the SSL tab and, for the same-named line, select the require option from the drop-down list.

That’s all! The required certificates will be loaded automatically during the first connection establishment, so just click OK to start managing your database via secure connection.

Now you can connect your application to a database (use the Connect to Database guide as an example) and enable SSL configurations for your project to encrypt your data while fetching/transferring.