In this tutorial we will go through how to self-host PostgreSQL Database and setup TLS to securely connect.

Table of Contents

• Installing PostgreSQL
• Setting Up SSL

Installing PostgreSQL

Postgres APT repository configuration:

sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

Install PostgreSQL 16

sudo apt install postgresql-16

After installing PostgreSQL, it creates a new user postgres, which is the superuser of the database. We can switch to postgres user in ubuntu to access postgresql command prompt:

sudo -i -u postgres

Now to create new user with passowrd and with privilege to create new db. It will ask you to enter your password, which will not be logged into history hence this is the recommended way to set password.

createuser your_db_user --pwprompt --createdb

Now we can exit from postgres account

exit

We will add new user in our Ubuntu with the same name as the new user we created in postgresql. (Optional) Add -m to create home directory for the user.

sudo useradd -s /bin/bash your_db_user

Accessing psql as the new user we just created. Since we do not have any database associated with our user therefore we are using ‘-d postgres’ to use postgres db for now.

sudo -u your_db_user psql -U your_db_user -d postgres

Create your database.

CREATE DATABASE your_db_name;

This is how you can connect with your db if not already connected.

c your_db_name;

Setting Up SSL

It’s important that we secure our communication with the database while accessing it remotely. We are going to setup SSL certificate using certbot from LetsEncrypt.

Install certbot

sudo snap install core; sudo snap refresh core
sudo apt-get remove certbot
sudo snap install --classic certbot
sudo ln -s /snap/bin/certbot /usr/bin/certbot

Obtain certificate

sudo certbot certonly --standalone -d psql.example.com

Find PostgreSQL data directory

sudo -u postgres psql -U postgres -c 'SHOW data_directory'

Create a renewal hook file

sudo nano /etc/letsencrypt/renewal-hooks/deploy/postgresql.deploy

Paste the following

#!/bin/bash
umask 0177
DOMAIN=psql.example.com
DATA_DIR=/var/lib/postgresql/16/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

Save and exit the file.

Give file executable permission

sudo chmod +x /etc/letsencrypt/renewal-hooks/deploy/postgresql.deploy

Get the path of PostgreSQL configuration file

sudo -u postgres psql -U postgres -c 'SHOW config_file'

Open the file in edit mode and update the SSL section of the file as below:

sudo nano /etc/postgresql/16/main/postgresql.conf

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_prefer_server_ciphers = on

Locate the Connection Settings section and verify the listen_address is to * for all addresses. Make sure the line is not commented out.

listen_address = '*'

Edit pg_hba.conf file which is in the same dir as config file:

sudo nano /etc/postgresql/16/main/pg_hba.conf

Add the following line to enable connection over ssl for any user and database:

hostssl all all 0.0.0.0/0 md5
hostssl all all ::/0 md5

Renew the SSL certificate

sudo certbot renew --force-renewal

Restart PostgreSQL

sudo service postgresql restart

Connect to your db remotely

psql -d "dbname=your_db_name sslmode=require" -h psql.example.com -U your_db_user