Every online individual in this world wants to gain the highest level of security but it’s one of the most demanded challenges. Therefore, we are today going to learn the process of obtaining secure SSL connection to your PostgreSQL container hosted at MilesWeb cloud powered by Jelastic.
When you are struggling to safeguard the information in your PostgreSQL database, firstly all the connections need to be encrypted to it, in order to secure authentication credentials (usernames/passwords) as well as processed data from interception.
In this article, we will gain the knowledge of adjusting the database server and generating certificates needed for enabling SSL. Further we will learn to add certificates to a client machine and at last, we will discover how to establish a secure connection to our server via pgAdmin. So, let’s start!
PostgreSQL Server Configuration
Let’s consider two use cases for two different environments: with PostgreSQL database single node and clustered two nodes with master-slave replication.
- In order to setup a clustered solution quickly, you can opt for the pre-configured PostgreSQL Database Replication package via the Marketplace > Clusters
The search bar at the frame top can be used to easily find the required solution. Click on Install for the suitable solution window.
A new environment of a single or two interconnected database nodes appeared at your MilesWeb dashboard will be seen based on the preferred topology.
1. In order to setup the SSL connection, go to your database server through MilesWeb SSH Gate. It is possible via two ways:
- The easy option is to utilize an embedded Web SSH client and establish the connection directly through your MilesWeb Dashboard (note that this option is available starting from Jelastic 5.4 platform version).
- You can enter the required container by using your local SSH client. In case you haven’t done this before, follow the steps below:
- generate an SSH keypair
- add your public SSH key to the dashboard
- access your account via SSH protocol
2. Include the three files mentioned below, after accessing your PostgreSQL database server through SSH, to its /var/lib/pgsql/data directory so that it works via SSL:
- key – private key
- crt – server certificate
- crt – trusted root certificate
If such certification-files are already being generated in your account, simply upload then to the above mentioned folder (you will find the suitable option within the embedded Configuration Manager at MilesWeb dashboard) and go to the step 6 of the current instruction.
If not then follow the steps from 3rd-5th below which describe generating the above files on your own.
3. Below are steps to create and configure the server.key file:
- execute the commands:
openssl genrsa -des3 -out server.key 1024
While the process is on, you will be asked for a pass phrase where you can enter any and confirm it to complete the file creation.
- For working with this key further, remove the pass phrase added in the above step. Execute the below command for this:
openssl rsa -in server.key -out server.key
You will need to again enter the pass phrase second time to confirm the process.
- Lastly, you will need to set the appropriate permission and ownership rightsfor your private key file with the below commands:
chmod 400 server.key
chown postgres.postgres server.key
Note: Suppose you are planning to use a pre-generated certificate, after you upload it to the server, please make sure that it access permissions and ownership rights match with the set above ones for the appropriate private key file.
4. After this, you will need to create a server certificate depending 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/[email protected]‘
Note: The -subj parameter isn’t compulsory as it is needed only when the generated certificate is to be used in production. In that case, you will need to specify the below personal data as its value as per the below format displayed:
You can also set the appropriate information further in interactive mode with the inquiry opened automatically.
5. As we are going to sign certificates by ourselves, you can also use the generated certificate as a trusted root certificate. For this you simply need to make its copy with the appropriate name:
cp server.crt root.crt
You now have all three certificate files and so you can proceed to PostgreSQL database configurations needed for actually activating and using SSL.
6. Open the pg_hba.conf file in the same folder in order to edit (either with any preferable terminal editor such as vim or directly through the dashboard).
Its default content is required to be replaced with the below 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
- If there is a clustered solution installed, follow the same above step, without changing the line related to replication. If you change it, the data replication feature will get disabled.
- If you aren’t going to work with the database as the default webadmin user, you will require replacing the suitable value within the last lines of the file with the required database username. Note that here you will need to mention the same username for all the further commands (we will alert you when it is required).
Now, save the updated file.
7. For completing the configurations, it is required to make some more changes to the postgresql.conf file.
In its Security and Authentication section (just about at the 80th line), uncomment the setting with similar name and change its status to “on” to activate the use of SSL itself. Also, add the new ssl_ca_file parameter below:
ssl = on
ssl_ca_file = ‘root.crt’
8. At last, restart your PostgreSQL server for applying new settings.
sudo service postgresql restart
It’s time to create one more set of SSL certificate files for client instance for supporting secure connection at both sides.
1. Go back to the terminal window with the SSH connection to your PostgreSQL server you have operated during setting up the server (or reconnecting to it) – you will need your server certificates for further actions.
Next, generate a private key for a client (also with as pass phrase, similar to the one 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
2. Then create SSL certificate for your PostgreSQL database user (webadmin, by default) and sign in 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
- You can change the common data provided with -subj parameter to your personal info, its Common Name (/CN=) needs to be equal to the username of database that has been set in the pg_hba.conf server configuration file while generating the first certificate (webadmin, in our case).
- You can execute the second command from the above, from the location (server directory), where the root.crt and server.key files are stored or you can even specify full path to them.
3. After postgresql.key, postgresql.crt and root.crt files are ready, you require moving them to the .postgresql folder on your client machine. Use the MilesWeb dashboard UI to obtain them by simply downloading or copy-pasting the content of the files.
Additionally, if you require, the key read permission can be set for owner only with chmod 400 ~/.postgresql/postgresql.key command for obtaining more security.
Establish Connection via PgAdmin
Finally, when the server and client configurations are completed, you are ready to build the connection. In this case, we will use the pgAdmin 3 tool for example, so get this application (or another preferred one) installed prior.
In the latter case, go to the environment Settings -> Endpoints section and Add new endpoint with the similar name button at the top pane.
2. After getting an access point, run your pgAdmin 3client and select the option of New Server Registration.
In the opened window, go to the Properties tab and mention the below data:
- Name – give any preferred connection name (e.g. ssl-to-pgsql)
- Host – mention the access point you’ve added in the first step (Public IP address or endpoint Access URL without port number)
- Port – For the External IP or endpoint’s Public port use the default 5432 port number (mentioned in the appropriate column’s similar name section)
- Username – use the database user you’ve set the SSL certificate and configurations for (i.e. webadmin by default)
- Password – use the corresponding user’s password (sent via email for webadmin or the one you’ve set otherwise)
You can leave the rest of the fields unchanged or adjust them according to your requirements.
3. Then switch to the SSL tab and for the similar-named line, select the needed option from the drop-down list.
You have completed the process of establishing secure SSL connection to PostgreSQL database server. There will be automatic loading of the needed certificates during establishment of the first connection (if not you can choose them manually). You simply need to click OK to start managing your database via secure connection.
Your application can now be attached to the required database (for this process use the Connect to Database guide) and enable SSL configurations for your project for encrypting your data when it fetches or transfers.