Want to know how to establish a secure connection to your PostgreSQL container in Jelastic Cloud? Look no further! Read on for all the details.

  1. Postgresql Ssl Without Client Certificate
  2. Postgresql Ssl Client Certificate Is Required
Join the DZone community and get the full member experience.
Join For Free

One of the most demanded challenges in the modern world of Internet of Things is to gain the highest level of security. That’s why today we are going to consider the process of establishing secure SSL connection to your PostgreSQL container, hosted at Jelastic Cloud.

When striving to keep information in your PostgreSQL database safe, the first thing you need to do is to encrypt all connections to it for protecting authentication credentials (usernames / passwords) and stored data from interception.

Below, we’ll explore the appropriate database server adjustment, required for SSL-enabling, and certificates generation for it. Then, we’ll create and add certs for client machine and, lastly, will establish 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 Jelastic 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 yourselves.

Tips:
  • We won’t explain commands parameters in details here, but if you’d like to know more, just refer to the Self-Signed Custom SSL page in our documentation or check the official OpenSSL site for the full list of available actions
  • You can also use custom SSL certificate similarly to the described below (follow the Generate a Custom SSL Certificate section of the linked guide to get such). In this latter case, you can skip the generation instruction and jump directly to the 6thstep of this instruction

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

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

  • Execute the next commands:

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

Re-enter pass phrase one more time for confirmation.

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

Note: It’s required to set your personal data for subj parameter if the certificate is intended to be used in production:

You can also just skip the -subj parameter within the command and pass all these arguments in the interactive mode within the automatically opened inquiry.

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 its copy with the appropriate name:

Now, as you have all three certificate files, you can proceed to 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 preferable terminal editor (vim for example) or directly via the dashboard.

Replace its default content with the following lines:

Tip: In case you are going to work with the database not as default webadmin user, change the appropriate value within the last line of the file to the required name. Note, that in this case, you’ll need to use the same user name for all the further commands (we’ll denote where this is required)

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 at the 80th line) and activate SSL usage itself, through uncommenting the same-named setting and changing its value to “on”. Also, add the new ssl_ca_file parameter below:

Don’t forget to save these changes.

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

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 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 client (also without a pass phrase, just as it was done in the previous section), for example within the tmp directory:

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

Note:
Client

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).

Tip: If such directory does not exist yet, create it with mkdir ~/.postgresql or similar command according to your OS distribution.

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.

Tip: Don’t forget to remove keys from the tmp directory on your DB server afterwards.

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 to 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:

The rest of the fields can be left unchanged or adjusted according to 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 bind your application to database (use the Connect to Database guide as an example) and enable SSL configurations for your project to encrypt your data while fetching/transferring. And in case you face any issues while configuring, feel free to appeal for our technical experts` assistance at StackOverflow.

So, go ahead and secure your project – just choose a cloud hosting provider within Jelastic Cloud Union and register for free!

Like This Article? Read More From DZone

server ,database ,postgresql ,certificate ,connection ,secure ,ssl ,jelastic
Postgresql
Published at DZone with permission of Tetiana Markova , DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
I had to setup it relatively recently, and hit some roadblocks, so figured I'll write about my experiences – for myself in the future, or for anyone else that might want to set it up.
First, let's state goals:
First bad news – we should setup CA (certificate authority). Of course it's possible to use some other CA (like any commercial one), but setting our own makes experimenting easier and cheaper.
Luckily, it's not that big of a deal, as openssl comes with program named CA.pl, which is located in /usr/lib/ssl/misc/ directory, which greatly helps. So let's use it.
On my pgdba account (system user I run PostgreSQL as, usually it's postgres, but I use pgdba), I made ~/ca directory – this will contain everything related to certificate authority.
To this directory I copied /etc/ssl/openssl.cnf file, and modified it:
As you can see I changed (or added) defaults to certificate identification values, so that I will not need to retype everything for each certificate.
Then, I copied /usr/lib/ssl/misc/CA.pl to ~/ca/ directory, and also made small change:
That is, I made CA.pl in ~/ca to automatically use openssl.cnf from my directory.
Now, with this in place, in ~/ca directory, I run:
Of course ca key should be protected by password.
For Common Name I entered pg/ca – I want to keep cnames uniques and formatted in a way that does not look like email or hostname, so I chose pg prefix, and will add /ca, /server, or /user/… suffixes.
Now, I have CA certificate and key generated. Now, let's make key for server:
key has been generated, now I need to sign it with CA:
of course, to sign I had to give password for CA key.
After these two commands, I have 3 new files in ~/ca: newkey.pem, newreq.pem and newcert.pem.
newreq is no longer necessary, but newkey and newcert as important. So, let's rename them properly:
With this in place, I can modify my postgresql.conf to make it work with ssl. To do it I need to change:
there is also ssl_crl_file, but that's not interesting for now.
After these have been setup, let's chance pg_hba.conf to:
of course, this is just an example, but the point is simple – I reject all host (remote, not encrypted) connections, and trust all encrypted connection, or local via unix socket.
With this in place, let's reload postgresql:
I had to use -w option, because otherwise pg_ctl ends before PostgreSQL will get password for ssl key.
The dots after “Enter PEM pass phrase:' are printed by pg_ctl, while postgres is waiting for me to type in the passphrase, but it doesn't break anything.
Of course – you might want to opt to use passwordless ssl key, so that you can start/restart server without being prompted for passwords. To do it, it's enough to:
To do it you will be asked passphrase to the key file, but that is the last time you will need it. Afterwards, I can restart Pg without any prompts:
Now, let's see if encryption works:
Nice. Works. And what if I explicitly disable SSL?
Nice. But, at least hypothetically, one could do MITM attack. How to make it impossible? And also – why does it allow connection without even warning about certificate that is signed by some unknown party?
The second part is simple. Let's force verification of ca:
In ~/.postgresql, I need to put root.ca. Convert cdr x8 to x7 online. What will happen, if the root.ca is not the one that signed server certificate (by mistake, or someone is trying MITM attack)?
Interestingly, the same happens without forcing verify-ca:
This is because libpq (what psql uses to connect to database) checks for certificate, and if it exists, it will verify server certificate even without verify-ca. If it will fail (as it did now), it will fail over to plaintext connection – thus making mitm attack impossible.
OK, so let's get proper cerfiticate to /home/depesz/.postgresql/root.crt, and retry:
Works again.

Postgresql Ssl Without Client Certificate

So, we have connection that is secure, MITM will not work, but (at the moment) anyone can connect. We could use “md5' in pg_hba.conf, but since we have CA, why don't we use it for user authentication?
I will create two certificates for users depesz and pgdba:
and the same for user depesz.
Afterwards, I got 4 new files:
Now, I need to change pg_hba.conf again, to change “trust' to something more secure. That is “cert':

Postgresql Ssl Client Certificate Is Required

With this in place, I reload Pg, and immediately try to connect:
of course, I didn't provide the user cert in any way, so I can't connect. This is OK. So, how do I provide the certs so that psql will use them?
That's simple – I copy them to ~/.postgresql/postgresql.crt and ~/.postgresql/postgresql.key.
With these files in place, I can try to connect again:
Now, why is that?
My shell account is named “depesz', so psql tries to connect to db user “depesz'. But my certificate cname is “pg/user/depesz', and not “depesz'. What can I do? That's actually pretty simple, we need to modify pg_hba.conf again, and change it to:
This new addition (map=cert), will make pg use pg_ident.conf file, so I need to modify it too:
This means that map named “cert' (you can have many maps, for different purposes) will allow any user connecting with cname pg/user/* to connect to user that is the same as whatever is after pg/user/, so in case of pg/user/depesz – depesz.
Another reload of Pg config, and … :
Nice. What's also helpful, is that I can add to pg_ident another line:
And now, using my pg/user/depesz certificate I will be able to log in to both “depesz' account (due to previously added line, with regexp), and to “pgdba' account – thanks to this new line.
This leads to one final question. What if someone rogue would generate another certificate with the same cname, and other fields? Of course it would be signed by the same CA (as we should keep the CA key very secure).
Let's try. This is relatively simple to do:
In my ~pgdba/ca directory, I will make self-signed certificate, that will have the same data as correct certificate, but will be self-signed, and not signed by my trusted CA:
now, I have rogue.crt and rogue.key. Moved them to ~/.postgresql/postgresql.{crt,key} in home directory, and now, when I try to connect:
So, I'm set. Looks like I'm safe against MITM attacks (as long as I remember to put root.crt in ~/.postgresql/ directory, and users can authenticate with certificates, logging to whatever user they need.
Hope anyone will find it useful ?