Establish Secure SSL Connection to PostgreSQL Database Server

| May 1, 2018 | Databases, DevOps PaaS, Installer | , , , ,

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.   secure ssl connection to postgresqlWhen 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 processed data from interception.

Below, we’ll explore the appropriate database server adjustment and certificates generation, required for SSL enabling. Then, we’ll discover how to add certs to a client machine and, lastly, will establish a secure connection to our server via pgAdmin. So, let’s go on!

PostgreSQL Server Configuration

Within the instruction below,  we’ll consider two use cases for two separate environments: with PostgreSQL database single node and clustered two nodes with master-slave replication.

In case of a single node installation, you can simply create it via environment topology wizard.    postgresql environment

  • For quick setup of a clustered solution, you can use the pre-configured PostgreSQL Database Replication package, available via the Marketplace > Clusters section.

pre-configured PostgreSQL Database Replication package available via MarketplaceTo easily find the required solution, use the search bar at the frame top. Click on Install for the appropriate solution plank.   postgresql database environmentDepending on the preferred topology, you’ll see a new environment of a single or two interconnected database nodes (like in the image above) appeared at your Jelastic dashboard.

1.To start with SSL connection setup, you have to access your database server through Jelastic SSH Gate - this can be accomplished in two ways.

  • The easier one is to utilize an embedded Web SSH client and establish the connection directly through your Jelastic Dashboard (note that this option is available starting from Jelastic 5.4 platform version).   utilize embedded Web SSH client and establish connection via dashboard
  • Another way to enter the required container is to use your local SSH client. In case you haven’t performed the similar operation before, you’ll need to:

2. Once you’ve entered your PostgreSQL database server via SSH, you’ll need to add the following three files to its /var/lib/pgsql/data directory to make it work via SSL:

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

If you already have a set of such certification files being pre-generated, you can just upload them to the above-mentioned folder (the appropriate option is available within the embedded Configuration Manager at Jelastic dashboard) and jump to the 6th step of the current instruction.

Otherwise, pay attention to the 3rd-5th steps below, where we briefly describe how to generate them on your own.

3. To create and configure the first above mentioned file (server.key), perform the following steps:

  • execute the commands:

cd /var/lib/pgsql/data
openssl genrsa -des3 -out server.key 1024     configuring server key fileDuring the generation, you’ll be asked for a pass phrase – specify any and confirm it to finish the creation.

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

         openssl rsa -in server.key -out server.key     remove pass phrase postgresql opensslRe-enter pass phrase one more time for confirmation.

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

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

Note: In case you are going to use a pre-generated certificate, please, after uploading it to the server, make sure that its access permissions and ownership rights coincide with the set above ones for the appropriate private key file.

set access permission and ownership rights for private key file4. Next, you need to create a server certificate based on your server.key file, e.g.:

openssl req -new -key server.key -days 3650 -out server.crt -x509 -subj '/C=US/ST=California/L=PaloAlto/O=Jelastic/CN=mysite.com/emailAddress=mail@jelastic.com'    server certificate based on server-key file, postgre sql database

Note: The -subj parameter is optional, being required only in the case the generated certificate is going to be used in production. If so, you’ll need to provide the following personal data as its value according to the shown above format (i.e. ‘unit1=meaning_value1/unit2=meaning_value2/unit3=…’ etc).
format to provide pesonal data value, postgresql ssl connectionAlternatively, the appropriate information can be set further in interactive mode by means of the  automatically opened inquiry.

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

cp server.crt root.crt   trusted root certificate, access to postgresql database serverNow, as you have all three certificate files, you can proceed to PostgreSQL database configurations, required for actual SSL activation and usage.

6. In the same folder, open the pg_hba.conf file for editing (either with any preferable terminal editor - vim, for example - or directly through the dashboard).

Replace its default content with the following lines:

# 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=1
# IPv6 remote connections for authenticated users
hostssl all         webadmin ::/0         md5 clientcert=1

postgresql database configurations, required for ssl connection

Tips:

 

  • If you have installed clustered solution, perform the same step described above, except for the line related to replication - it must be left unchanged. Otherwise, the data replication feature will be disabled.   clustered solution with data replication feature
  • If you are going to work with the database not as the default webadmin user, you’ll need to change the appropriate value within the last lines of the file, substituting it with the required database username. Note, that in this case you’ll need to use the same username for all the further commands (we’ll point where it 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 the SSL usage itself through uncommenting the same-named setting and changing its value to “on”. Also, append the new ssl_ca_file parameter below:

ssl = on
ssl_ca_file = 'root.crt'

activate ssl connection to postgresql dbDon’t forget to save these changes.

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

sudo service postgresql restart   restart postgresql server

Client Certificates

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

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

openssl genrsa -des3 -out /tmp/postgresql.key 1024
openssl rsa -in /tmp/postgresql.key -out /tmp/postgresql.key     established SSH connection to PostgreSQL server2. Next, create SSL certificate for your PostgreSQL database user (webadmin, by default) and sign it with our trusted root.crt file on a server.

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

Note:

 

  • Whist commonly data, provided within the -subj parameter, can be changed to your personal info, its Common Name (/CN=) must be equal to the database user name you’ve set in the pg_hba.conf server configuration file during the first certificate generation (webadmin, in our case)
  • The 2nd command from above should be executed from the location (server directory), where the root.crt and server.key files are stored; otherwise, the full path to them should be specified

SSL certificate for your PostgreSQL database user3. After these files (i.e. postgresql.key, postgresql.crt and root.crt) are ready, you need to move them to the .postgresql folder on your client machine. You can use the Jelastic dashboard UI to get them (just download them or copy/paste the files' content).   transfering files to postgresql folder

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

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

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

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 another preferred one) installed beforehand.

1.In order to connect to the database 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.    add new endpoint postgresql database server2. Now, when you have an access point, run your pgAdmin 3 client and select the New Server Registration option.   pgAdmin 3 client, new server registration, propertiesIn 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 5432 port number 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 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.   postgresql new server registrationThat’s all! The required certificates will be loaded automatically during the first connection establishment (if not you can choose them manually), so just click OK to start managing your database via secure connection.

Now you can bind your application to the required database (use the Connect to Database guide for that) and enable SSL configurations for your project to encrypt your data while its fetching or transferring.

Feel free to ask for a help from our technical experts at Stackoverflow in case you have any questions whilst establishing SSL connection to your PostgreSQL server.

Go ahead and secure your project – just register for free at one of the globally-presented Jelastic Cloud Hosting Providers.

Related Articles

PostgreSQL Auto-Clustering with Asynchronous Master-Slave Replication
How to Connect PostgreSQL with Java Application
How to Install Hasura GraphQL Engine for PostgreSQL-Based Applications