--- title: "How to Secure Your Database The Right Way via PostgreSQL SSL" description: "Learn the ins-and-outs of securing your database to protect information in transit via PostgreSQL SSL in this ATA Learning tutorial!" canonical: "https://adamtheautomator.com/postgresql-ssl/" --- # How to Secure Your Database The Right Way via PostgreSQL SSL > Learn the ins-and-outs of securing your database to protect information in transit via PostgreSQL SSL in this ATA Learning tutorial! Source: https://adamtheautomator.com/postgresql-ssl/ --- ATA Learning Tap to hide [ ATA Learning ](/) * [Home](/) * [Tutorials](/tutorials/) * [Instructors](/author/) * [Advertising](/advertising/) * [Recommended Resources](/resources/) * [About Adam](/about-adam/) Search for: * [](https://twitter.com/adbertram) * [](https://github.com/Adam-the-Automator) * [](https://www.linkedin.com/company/adam-the-automator-llc) * [](/feed/) ![How to Secure Your Database The Right Way via PostgreSQL SSL](https://adamtheautomator.com/wp-content/uploads/2022/09/How-to-Secure-Your-Database-The-Right-Way-via-PostgreSQL-SSL.jpg) # How to Secure Your Database The Right Way via PostgreSQL SSL [![](https://secure.gravatar.com/avatar/2788bb1a3f735603f81eca51d68daec56a9d97e805a10268fb2c20afcc76b81b?s=192&d=mm&r=g)Nicholas Xuan Nguyen](https://adamtheautomator.com/author/nicholas-xuan-nguyen/)21 September 20228 min. read Categories: [IT Ops](/category/it-ops/) Tags:[Databases](/tag/databases/)[PostgreSQL](/tag/postgresql/) Table of Contents * [Prerequisites](#prerequisites) * [Starting PostgreSQL without SSL](#starting-postgresql-without-ssl) * [Installing Certbot to Create PostgreSQL SSL Certificates](#installing-certbot-to-create-postgresql-ssl-certificates) * [Creating a Certbot Renewal Hook](#creating-a-certbot-renewal-hook) * [Configuring PostgreSQL to Use SSL](#configuring-postgresql-to-use-ssl) * [Testing Your SSL Connection](#testing-your-ssl-connection) * [Conclusion](#conclusion) SSL is a security measure that encrypts data sent between two devices (i.e., a server and a computer.) With databases like PostgreSQL, SSL is crucial to ensure your sensitive information, such as credit card numbers or social security numbers, cannot be intercepted by anyone other than you. By default, PostgreSQL does not come with SSL enabled. But don’t worry, though. this tutorial has got you covered in setting up PostgreSQL SSL. You’ll learn to create an SSL certificate, configure PostgreSQL, and verify that SSL is working correctly. Read on to take your PostgreSQL security to the next level! ## Prerequisites This tutorial will be a hands-on demonstration. If you’d like to follow along, be sure you have the following. * A Linux machine – This tutorial uses Ubuntu Server 18.04, but any recent distribution should work fine. Related:[How to Install Ubuntu 20.04 \[Step-by-Step\]](https://adamtheautomator.com/install-ubuntu/) * A non-root user with sudo privileges – This tutorial uses a root account, but you can achieve the same results using a sudo-enabled user. * PostgreSQL [installed](https://computingforgeeks.com/installing-postgresql-database-server-on-ubuntu/) on your Linux machine. Related:[How to Deploy Postgres to Kubernetes](https://adamtheautomator.com/postgres-to-kubernetes/) * A fully qualifying registered domain name (FQDN) [pointing](https://www.namecheap.com/support/knowledgebase/article.aspx/9837/46/how-to-connect-a-domain-to-a-server-or-hosting/) to your server (not strictly required but makes setting up SSL much simpler) – This tutorial uses the domain _[psql.example.com](http://psql.example.com)._ Still, you can replace the domain with your own throughout this tutorial. * [Snap](https://snapcraft.io/docs/installing-snap-on-ubuntu) package manager installed. ## Starting PostgreSQL without SSL Before you can set up PostgreSQL to use SSL, you’ll need to have a working installation. For this tutorial, you’ll need to have PostgreSQL running without SSL enabled to test that everything is working after you’ve set up SSL. Open your terminal, and run the below `systemctl` command to `start` the PostgreSQL service. By default, the PostgreSQL service starts automatically after installation, but running this command is a good measure to ensure the server is running. > _Append sudo before every command when running commands that require elevated privileges if you use a non-root account throughout this tutorial._ Related:[Correct Way of Using Ubuntu systemctl to Control Systemd](https://adamtheautomator.com/ubuntu-systemctl/) ```bash systemctl status postgresql ``` The output below indicates the PostgreSQL service is active (exited). ![Verifying the PostgreSQL service is running](https://adamtheautomator.com/wp-content/uploads/2022/09/image-298.png) Verifying the PostgreSQL service is running If PostgreSQL is not running, run the below command to `start`, and `enable` the service to start automatically on bootup. ```bash # Starts the PostgreSQL service systemctl start postgresql # Enables the PostgreSQL service to run on system bootup systemctl enable postgresql ``` ![Starting and enabling the service to run on bootup](https://adamtheautomator.com/wp-content/uploads/2022/09/image-299.png) Starting and enabling the service to run on bootup ## Installing Certbot to Create PostgreSQL SSL Certificates Now that you have PostgreSQL running, you’ll install [Certbot](https://certbot.eff.org/) to simplify acquiring and renewing SSL certificates. You’ll use these [certificates](https://adamtheautomator.com/self-signed-certificates/) to encrypt the communication between the server and any clients that connect to your database. Related:[Master Self-Signed Certificates on Windows & Linux](https://adamtheautomator.com/self-signed-certificates/) In this tutorial, you’ll use the Snap package manager to install Certbot to make keeping Certbot up-to-date a breeze. 1\. Run the below command to refresh the list of available Snap packages. ```bash snap refresh ``` ![Refreshing the list of available Snap packages](https://adamtheautomator.com/wp-content/uploads/2022/09/image-300.png) Refreshing the list of available Snap packages 2\. Next, run the [snap install](https://www.tecmint.com/install-snap-in-linux/) command below to install certbot. ```bash snap install --classic certbot ``` ![Installing Certbot](https://adamtheautomator.com/wp-content/uploads/2022/09/image-301.png) Installing Certbot 3\. Once installed, run the following command to link (ln) your Certbot executable to _/usr/bin_ so you can call Certbot quickly. ```bash ln -s /snap/bin/certbot /usr/bin/certbot ``` ![Linking your Certbot executable to /usr/bin](https://adamtheautomator.com/wp-content/uploads/2022/09/image-302.png) Linking your Certbot executable to _/usr/bin_ 4\. Now, run the below [certbot](https://manpages.ubuntu.com/manpages/bionic/en/man1/certbot.1.html) command to request your domain’s (psql.example.com) SSL certificate (certonly). The –standalone mode tells Certbot to use its built-in server to verify your domain ownership and requests a certificate from Let’s Encrypt. Related:[How to Provision a Website With aaPanel and LetsEncrypt](https://adamtheautomator.com/aapanel/) ```bash certbot certonly --standalone -d psql.example.com ``` Input your email address, press Enter to opt-in for important account notifications, and agree to the Let’s Encrypt terms of service. ![Entering your email address for account notifications](https://adamtheautomator.com/wp-content/uploads/2022/09/image-303.png) Entering your email address for account notifications 5\. When prompted, type Y and press Enter to agree to the terms of service. ![Accepting the terms of service](https://adamtheautomator.com/wp-content/uploads/2022/09/image-304.png) Accepting the terms of service 6\. Next, type Y and press Enter to share your email address with the Electronic Frontier Foundation (EFF), a non-profit that helps support Certbot’s development). ![Sharing your email address with the Electronic Frontier Foundation](https://adamtheautomator.com/wp-content/uploads/2022/09/image-305.png) Sharing your email address with the Electronic Frontier Foundation If your domain is successfully verified, you’ll see the below output with certificate information like the following: * Certificate validation – 90 days and will be automatically renewed. * Certification location and key files. Be sure to note down the path to your certificate and key files. You’ll need them later while configuring PostgreSQL to use SSL. If you receive any errors, make sure you have followed step one up to this point. In particular, check that you’ve linked the Certbot executable to _/usr/bin_ and that your domain name is correctly specified. ![ Creating SSL certificates](https://adamtheautomator.com/wp-content/uploads/2022/09/image-306.png) Creating SSL certificates ## Creating a Certbot Renewal Hook You now have a valid SSL certificate, but you haven’t configured PostgreSQL to use the certificate yet. You must create a Certbot renewal hook and configure PostgreSQL to use SSL. A renewal hook is required because the certificate file is only accessible by root. With a renewal hook, the certificate and key files are copied to the PostgreSQL data directory (owned by the _postgres_ user) whenever Certbot renews the certificate. To create a Certbot renewal hook: 1\. Find the data directory for your PostgreSQL installation first. The data directory is usually _/var/lib/postgresql/data_. But the location can be different depending on how you installed PostgreSQL. Run the following command to switch to the postgres user and query ([psql](https://www.geeksforgeeks.org/postgresql-psql-commands/)) for the data directory (‘SHOW data\_directory’). ```bash su - postgres psql -U postgres -c 'SHOW data_directory' ``` The data directory is located in the same directory as the _postgresql.conf_ file, in this case, _/var/lib/postgresql/12/main_. Note the location, as you will need it later to create a hook script (step three). ![Finding the data directory for your PostgreSQL installation](https://adamtheautomator.com/wp-content/uploads/2022/09/image-307.png) Finding the data directory for your PostgreSQL installation 2\. Next, create a renewal hook script called postgresql.deploy with your preferred text editor. ```bash nano /etc/letsencrypt/renewal-hooks/deploy/postgresql.deploy ``` 3\. Add the following code to the _postgresql.deploy_ file, and replace the following variable values: * `DATA_DIRECTORY` – The data directory you noted in step one. * `DOMAIN - Your actual domain.` The code below defines the permission (umask), DOMAIN, and PostgreSQL DATA\_DIRECTORY and copies the certificate (.crt) and key (.key) files to the expected locations. After modifying the variable values, save the changes, and close the file. Related:[How To Wrangle Ubuntu Environment Variables](https://adamtheautomator.com/ubuntu-environment-variables/) ```bash #!/bin/bash umask 0177 DOMAIN=psql.example.com DATA_DIR=/var/lib/postgresql/12/main cp /etc/letsencrypt/live/$DOMAIN/fullchain.pem $DATA_DIR/server.crt cp /etc/letsencrypt/live/$DOMAIN/privkey.pem $DATA_DIR/server.key chown postgres:postgres $DATA_DIR/server.crt $DATA_DIR/server.key ``` 4\. Finally, run the chmod command below to set the correct permissions for the renewal hook script (postgresql.deploy). This command gives the _postgresql_._deploy_ script executable permissions so that Certbort can run the script. Related:[Manage Directory and File Permissions with Chmod Recursive](https://adamtheautomator.com/chmod-recursive/) ```bash chmod +x /etc/letsencrypt/renewal-hooks/deploy/postgresql.deploy ``` ![Setting the correct permissions on the renewal hook script](https://adamtheautomator.com/wp-content/uploads/2022/09/image-308.png) Setting the correct permissions on the renewal hook script ## Configuring PostgreSQL to Use SSL With the renewal hook script created, you can now configure PostgreSQL to use SSL. You’ll need the _postgresql.conf_ file’s location for your PostgreSQL installation. This file contains all of the configuration settings for your PostgreSQL server. The _postgresql.conf_ file is usually located in the /_main_/ directory, which you found in step one of the “Creating a Certbot Renewal Hook” section. 1\. Run the psql command below to search for the _postgresql.conf_ file’s location if you can’t find it. ```bash psql -U postgres -c 'SHOW config_file' ``` Note down the path of your _postgresql.conf_ file, as shown below. ![Finding the postgresql.conf file](https://adamtheautomator.com/wp-content/uploads/2022/09/image-309.png) Finding the _postgresql.conf_ file 2\. Open the _/etc/postgresql/12/main/postgresql.conf_ file in your text editor, find and change the values of directives under the SSL section with the following: * **ssl\_cert\_file**, and **ssl\_key\_file** – Uncomment the directives by removing the **#** characters at the beginning of each line. Change the values with the paths to your certificate and key files you noted in the last step of the “Installing Certbot to Create SSL Certificates” section. These directives tell PostgreSQL where to find your SSL certificate and SSL key. * **ssl\_prefer\_server\_ciphers** – Uncomment the directive by removing the # characters at the beginning of the line. Change the value to on to tell PostgreSQL to use only strong ciphers. ![Defining the SSL certificate and SSK key](https://adamtheautomator.com/wp-content/uploads/2022/09/image-310.png) Defining the SSL certificate and SSK key 3\. Next, find, uncomment the listen\_address directive, and change the directive’s value to \* to tell PostgreSQL to listen for connections on all available IP addresses. Once you’ve changed the value, save, and close the file. ![Telling PostgreSQL to listen for connections on all available IP addresses](https://adamtheautomator.com/wp-content/uploads/2022/09/image-311.png) Telling PostgreSQL to listen for connections on all available IP addresses 4\. Next, open the _/etc/postgresql/12/main/pg\_hba_.conf file in your preferred text editor. This file contains all the client authentication settings for your PostgreSQL server and is controlled by the _postgresql_.conf file. 5\. Add the following lines to the _pg\_hba_.conf file right after any existing host lines, save and close the _pg\_hba_.conf file. Those two lines tell PostgreSQL to require SSL for all client connections and use password authentication. Adding both is crucial because some tools, such as pgAdmin 4, do not support SSL connections by default. Related:[Installing the PGAdmin Ubuntu (PostgreSQL Dashboard)](https://adamtheautomator.com/pgadmin-ubuntu/) ```bash hostssl all all 0.0.0.0/0 md5 host all all 0.0.0.0/0 md5 ``` ![Requiring SSL for all client connections and using password authentication](https://adamtheautomator.com/wp-content/uploads/2022/09/image-312.png) Requiring SSL for all client connections and using password authentication 6\. Now, run the below certbot renew command to force a renewal of your SSL certificate. Doing so causes Certbot to run your renewal hook script (_postgresql.deploy_). This action copies a new certificate and key, which PostgreSQL will use on the next start-up. ```bash certbot renew --force-renewal ``` ![Forcing a renewal of the SSL certificate](https://adamtheautomator.com/wp-content/uploads/2022/09/image-313.png) Forcing a renewal of the SSL certificate 7\. After the renewal, run the following command to list (ls) the contents of your data directory (_/var/lib/postgresql/12/main_). ```bash ls /var/lib/postgresql/12/main/server.* ``` Below, you can verify that the new certificate and key have been copied with the two new files named server.crt and server.key, as shown below. ![Verifying that the new certificate and key have been copied](https://adamtheautomator.com/wp-content/uploads/2022/09/image-314.png) Verifying that the new certificate and key have been copied 8\. Finally, run the [service](https://itsfoss.com/start-stop-restart-services-linux/#method-2-managing-services-in-linux-with-init) command below to restart your PostgreSQL server, apply the new configuration settings, and load the SSL certificate. ```bash service postgresql restart ``` ![Restarting the PostgreSQL server](https://adamtheautomator.com/wp-content/uploads/2022/09/image-315.png) Restarting the PostgreSQL server ## **Testing Your SSL Connection** You’ve completed configuring PostgreSQL to use SSL. But how do you know it’s working? You’ll have to test your SSL by connecting to the server with the [psql](https://www.postgresql.org/docs/current/reference-client.html) command-line tool. Run the below command to connect to your server from another machine that has [psql](https://www.timescale.com/blog/how-to-install-psql-on-mac-ubuntu-debian-windows/) installed. Ensure you replace username (postgres), database name (postgres), and psql.example.com with your own values. The sslmode=require query parameter lets you connect to your PostgreSQL server only if establishing an SSL connection with the server is possible. ```bash psql -d "dbname=postgres sslmode=require" -h psql.example.com -U postgres ``` When prompted, enter the password for the `postgres` user, and you’ll see an output similar to the following. This output indicates that the SSL connection is encrypted using **AES256-GCM**, so you can be confident that your data is safe from eavesdroppers. ![Connecting to the PostgreSQL server](https://adamtheautomator.com/wp-content/uploads/2022/09/image-316.png) Connecting to the PostgreSQL server ## **Conclusion** Securing your database is always a top priority. With a PostgreSQL SSL certificate, you’re one step away from keeping out entities prying into your data. And in this tutorial, you’ve learned to encrypt a PostgreSQL database connection. At this point, you can already confidently encrypt all client/server communications and keep your data safe With this newfound knowledge, why not [configure TLS connectivity in Azure Database](https://docs.microsoft.com/en-us/azure/postgresql/single-server/concepts-ssl-connection-security) for PostgreSQL? Protect your database communications while securing your data at rest and in transit! Related:[How to Create Azure SQL Database with PowerShell](https://adamtheautomator.com/create-azure-sql-database/) Share this article [Share on X](https://twitter.com/intent/tweet?url=https%3A%2F%2Fadamtheautomator.com%2Fpostgresql-ssl%2F&text=How%20to%20Secure%20Your%20Database%20The%20Right%20Way%20via%20PostgreSQL%20SSL)[Share on Facebook](https://www.facebook.com/sharer/sharer.php?u=https%3A%2F%2Fadamtheautomator.com%2Fpostgresql-ssl%2F)[Share on LinkedIn](https://www.linkedin.com/sharing/share-offsite/?url=https%3A%2F%2Fadamtheautomator.com%2Fpostgresql-ssl%2F) ## Related Posts ![](https://adamtheautomator.com/wp-content/uploads/2023/01/install-postgresql-on-mac.jpg) ### [How to Install PostgreSQL on Mac](/install-postgresql-on-mac/) Get started with a powerful database solution and learn how to install PostgreSQL on Mac in this ATA Learning tutorial! ![](https://adamtheautomator.com/wp-content/uploads/2022/03/Learn-How-to-Install-PostgreSQL-on-a-Ubuntu-Linux-Server.jpg) ### [Install PostgreSQL on a Ubuntu Server for Security Configurations](/install-postgresql-on-a-ubuntu/) Learn the different ways to install PostgreSQL on Ubuntu and perform initial configuration to setup a working RDBMS. ![](https://adamtheautomator.com/wp-content/uploads/2024/02/postgresql-on-windows.jpg) ### [A MySQL Alternative: Learning PostgreSQL on Windows](/postgresql-on-windows/) Dive into PostgreSQL on Windows—a MySQL alternative—for seamless integration, scalability, and robust data management solutions. ## Categories * [IT Ops](/category/it-ops/) * [Cloud](/category/cloud/) * [DevOps](/category/devops/) * [Home Ops](/category/home-ops/) * [Information Security](/category/infosec/) * [Software Development](/category/software-development/) ## Site * [Home](/) * [Tutorials](/tutorials/) * [Instructors](/author/) * [Advertising](/advertising/) * [Recommended Resources](/resources/) * [About Adam](/about-adam/) Copyright 2026© ATA Learning | [Privacy Policy](/privacy/)