In this post, you will learn what it takes to enable the SSL encryption in PostgreSQL. We will discuss the steps to enable the SSL connection when working with PostgreSQL. Read on!
Step-by-Step Guide on Enabling the SSL Encryption in PostgreSQL
When you want to enable the SSL encryption in PostgreSQL, you should configure PostgreSQL to use the generated SSL certificates and adjust its configuration file to accommodate the adjusted SSL connection details.
When encrypting with SSL, you utilize the certificate which acts as the encryption and decryption keys. The certificate will only be approved to be legitimate if it is signed by the certificate authority that you selected as the trusted one to help establish the connection.
You can enable the SSL encryption in PostgreSQL using the following steps:
Start by installing PostgreSQL if it is not yet installed. After installing it, check its status to ensure that it is up and running.
With the PostgreSQL being active, you should create a new SSL directory in the /etc. We installed PostgreSQL 14, so run the following command to create it:
Next, change the ownership of the created directory.
To enable SSL for PostgreSQL, you must generate the SSL certificates. For this case, you will generate the self-assigned certificates using the “openssl” command. Run the following commands to work with OpenSSL to generate the certificates. For the first command, you will get prompted to enter a few details regarding the SSL certificates to be generated.
Start by entering the PEM phrase of your choice.
On the other prompts, you will be required to add details such as your country name, state, locality, email address, etc. Once you enter them, you will be one step closer to completing in generating the certificates.
You must then generate the “server.key” file that is required to secure the connection.
You will get an output that the RSA key has been successfully written.
Next, generate the self-assigned certificate with the following command:
Change the file permissions of the generated “server.key” file. Give it a file permission of 600 as follows:
Change the ownership of the “server.key” and the “server.crt” that you generated in the previous steps.
After generating the certificates, we must adjust the PostgreSQL configuration file to enable the SSL by changing a few lines.
We use the nano editor to open the configuration file.
Once it opens, scroll down to the SSL part as shown in the following image:
Adjust the two lines above to set the SSL key and cert file path to match where you generated and stored them earlier.
If we check the current directory and list its contents, we verify that our key and certificate are in the /etc/postgresql/14/main/ssl. Ensure that you get the correct path for your case.
After adjusting the configuration file, our new configuration looks as follows:
You also need to adjust the “pg_hba.conf” to allow the SSL connections. For that, open the configuration file using an editor of your choice.
Scroll down and find the lines that contain the host. For each line, add “ssl” in the “Method” section of the line.
Lastly, restart your PostgreSQL and check its status.
You successfully enabled the SSL encryption, and your connection is now secured. The steps that are covered in this post helped generate the self-assigned certificates which are ideal for personal use but not in a production environment.
Conclusion
We now learned the steps to follow when you want to enable the SSL encryption in PostgreSQL. For personal use, generating the self-assigned certificates will work. However, in a production environment, obtain the certificates from a trusted source. Hopefully, these steps helped to guide you on quickly enabling the SSL encryption in PostgreSQL.
