Whitepaper

PostgreSQL security
made easy
Contents

Executive summary 3

Introduction 4

Minimum requirements 4

Sandboxing 4

Securing your supply chain  8

Overview of the configuration files for PostgreSQL 10

Prepare your storage layout 10

Logging 16

Installation 17

Conclusion 18

Annexes 19

2
Executive summary

PostgreSQL is the most popular database among professionals according to

StackOverflow’s 2025 developer survey1. Its widespread adoption is rooted in decades-
worth of advancements and successful deployments across all IT sectors.

This undeniable popularity among organizations goes hand-in-hand with

interest from cybercriminals, who will target improperly configured
PostgreSQL databases to steal, alter, or damage their content.

In this first of a series of PostgreSQL security whitepapers, we dive into the technical
measures you need to implement to harden your PostgreSQL databases. Hardening your
PostgreSQL deployments is essential for your enterprise to protect its data and achieve
compliance with security standards like ISO 27k, PCI-DSS, CIS, DISA-STIG, and FedRAMP.

1 StackOverflow’s 2025 developer survey, accessed 3rd September, 2025,

[Link]/2025/technology#most-popular-technologies-database
3
Introduction

In our “How to secure your database” blog, we provided an overview on the typical
measures to implement to protect your database deployments, the common cyber
attacks, and the unfortunate consequences of improperly protected databases.

In this whitepaper, we will dig deeper into database security and provide a detailed guide to
implementing hardening controls at the database level. We will cover the following areas:

• Sandboxing
• Securing your supply chain
• Storage layout
• Logging
• Installation

Let’s start our journey by detailing the minimum hardware and software requirements to
successfully implement all the commands and concepts explained in this document.

Minimum requirements
We will assume that you have a machine powered by Ubuntu 24.04 LTS or later, that you
have an internet connection, and that your system has the following usable resources:

• 9 GB of storage
• 4 GB of RAM
• 2 vCPUs

Let’s now dive into sandboxing, its benefits and types.

Sandboxing
What is sandboxing?
Sandboxing is about isolating a workload from the others running on
the same physical node. Isolation is achieved by limiting the:

• Permissions of the sandboxed workloads (e.g. syscalls).

• Resources allocated to the workload (cpu, memory, storage…).
• Visibility of the other running programs to the sandboxed workload.

Why use sandboxing technologies?

Sandboxing can improve the security of your workloads by preventing some
vulnerabilities from leaking into other workloads. This is even more important
when a physical node is hosting heterogeneous workloads (e.g. databases,
web applications) or workloads belonging to different customers.

4
 What are we sandboxing?
Before digging further into sandboxing, we will first depict the stack
of dependencies for a classical application (think a deb packaged
application) installed on a modern OS such as Ubuntu:

Application 1 Application n
(App specific binaries) ... (App specific binaries)

Runtime dependencies
(+ libraries, language runtimes)

OS Distro
(+ kernel patches, init system, package manager, shell, utilities)

Kernel

Hardware

At the bottom of the software stack, we have the kernel that handles all the interactions
with the physical layer via device drivers. Next, we find the distro that contains a
number of utilities and systems that makes the kernel usable to its users and installed
applications. Finally, we find the application binaries with all its dependencies.

In an ideal scenario, we would prefer that every application is running on its own hardware,
its own kernel and using only its dedicated dependencies. Yet, doing this will lead to an
explosion in costs, wasted resources, and an operational nightmare. So all sandboxing
solutions try to find a balance between isolation and efficiency by making compromises on
the size of the sandbox, its overhead and convenience. Let’s now dig into the sandbox types.

Type of sandboxes
Virtual machines

As the name suggests, this kind of virtualization emulates the full stack of dependencies
including the hardware. A running virtual machine (VM) looks conceptually as follows:

Application 1 Application i Application 1 Application j

Dependencies Dependencies
Guest distro ... Guest distro
Guest kernel Guest kernel

vm1 Vm2

VM Manager

Host distro

Host kernel

Hardware

5
 The VM manager provides a hardware “emulation” layer, often assisted by kernel modules
(such as KVM) and native hardware virtualization, while providing facilities to manage
them. We note that every VM is shipped with its own kernel that can be completely
different from the host kernel (e.g. running Windows VM on top of Ubuntu).

Containers

Containers are designed as a lightweight virtualization technology. Unlike virtual

machines, containers do not require a guest OS or hardware emulation. Linux
containers rather rely on the host OS’ built-in sandboxing technologies, such as:

Technology Operates at level of Acts on

Cgroups processes Quotas of allocated resources (cpu, memory …)

Namespaces processes Visibility of resources (pid, mount, net, ipc …)
Capabilities threads Permissions (~ group of syscalls)
granted to the running thread
Seccomp processes Allowed syscalls
Pivot_root processes Visibility of hosting filesystem

The container manager will also typically use a “layered” filesystem, such as OverlayFS, to
avoid duplicating common dependencies between containers running on the same host.

Based on what you embed within a container, we can distinguish 2 types of containers:

• System containers
• Application containers

System containers

System containers are designed as lightweight VMs, so you can pack any number of applications
together in a system container image. OS containers look conceptually like the following:

Common Application 1 Application i Application 1 Application j

dependencies
Dependencies Dependencies
Guest distro ... Guest distro

OS container 1 OS container n

Layered filesystem OS container manager

Host distro

Host kernel

Hardware

6
 Application containers

Application containers are designed to pack a single application in a container image

while typically stripping down the distro part or removing it completely (a.k.a distroless
container images). Application containers look conceptually like the following:

Common Application Application

dependencies
Dependencies Dependencies
...
App container 1 App container n

Layered filesystem Container manager (e.g. dockerd, lxd)

Host distro

Host kernel

Hardware

Snaps
Snaps are similar to application containers. Yet, snaps add a number of features such as:

• Atomic updates and rollbacks

• Delta updates
• Easy desktop integration
• … more here

Snaps look conceptually as follows:

Core snaps Application Application

Dependencies Dependencies
...
Snap 1 Snap n

Snap manager (i.e. snapd)

Host distro

Host kernel

Hardware

The core snaps are used to factor out the most common dependencies across snaps. They will
automatically be pulled by snapd when needed.

We’ve established a good conceptual model of the main sandboxing

types. Let’s discuss the most pertinent options for databases.

7
What type of sandboxing for a database?
Database management systems often need to change a few kernel
parameters (like disabling transparent huge pages).

Therefore, it is recommended to run your database servers on dedicated “kernels” that are
not shared with other applications. We can then choose among system containers, application
containers, and snaps in order to isolate database servers running on the same kernel.

Additionally, we often need to colocate several components along with the database server,
like backup tools and monitoring agents. Therefore, system containers or snaps are the most
suitable options. Both allow you to easily pack any number of components in a single snap or
container. The snap ecosystem also enables you to connect different snaps in a structured way.

Creating a sandbox
Let’s start by installing LXD, our Canonical tool to easily
manage virtual machines and containers.

sudo snap install lxd

sudo snap list | awk '/lxd/ {print $3}' #--revision = 33110 in this
guide
sudo lxd init --minimal # configure LX

Let’s now create a virtual machine to host our PostgreSQL database.

# spawn a VM to contain the PostgreSQL installation

sudo lxc launch ubuntu:24.04 pg --vm -c [Link]=2 -c limits.
memory=4GiB
sudo lxc exec pg bash
su -l ubuntu

Securing your supply chain

Check your binaries’ sources
Before installing PostgreSQL or any other software, you need to ensure
that you are using binaries from trusted sources. We therefore need
to check the configured deb repositories in our Ubuntu OS:

We can check the list of repositories using the following command:

grep "URI.*http" /etc/apt/[Link].d/[Link] | grep -v

\.ubuntu\.com

Ideally the above command should not yield any result (so basically, all configured
sources should point to the official [Link] repositories). Otherwise, you can check
the non-ubuntu sources of your deb packages using the following command:

dpkg-query -W -f='${Package}\n' | while read pkg; do

src=$(apt-cache policy "$pkg" | grep -m 1 http | awk '{print $2}')
if [[ -n "$src" && "$src" != *[Link]* ]]; then
echo "$pkg -> $src"
fi
done

If the above does not yield any result then you can remove any line reported by
the grep command on /etc/apt/[Link].d/[Link].

8
Otherwise, you need to ensure that you trust the provider of those packages and that you are
fine with its policy and commitment regarding security fixes.

Please note that the previous instructions cover deb packages. You need to do the same kind
of research for all the other types of packages or images you are using (containers, snaps …).

Check that you are subscribed

to all security updates
The following command should yield a strictly positive number, if you are already subscribed.

pro api [Link].enabled_services.v1 | grep -c esm

If Expanded Security Maintenance is not enabled then you can subscribe to Ubuntu Pro (free
for personal use for up to 5 machines). Once you get your token, just type the command:

sudo pro attach <token>

Update your packages

sudo apt -y update && sudo apt -y upgrade
# In case your VM exits after the upgrade, enter again by taping the
following:
# sudo lxc exec pg bash
# su -l ubuntu

Configure automatic updates

We recommend enabling automatic updates of your packages. Within Ubuntu,
this is managed by the unattended-upgrades service. It should be activated by
default and you can check its status by running the following command:

systemctl is-active unattended-upgrades

# active # <- returned when it is the case

If it is not running then you just need to issue the following command:

sudo systemctl start [Link]

You can also select the update types that you would like to automatically apply and the ones
to perform manually by editing the file /etc/apt/[Link].d/50unattended-upgrades.

In order to enable automatic security updates, you need to have the following section:

Unattended-Upgrade::Allowed-Origins {
"${distro_id}:${distro_codename}";
"${distro_id}:${distro
codename}-security";
"${distro_id}ESMApps:${distro_codename}-apps-security";
"${distro_id}ESM:${distro_codename}-infra-security";
};

9
You can exclude specific packages by adding them to the Package-Blacklist section:

Unattended-Upgrade::Package-Blacklist {
"postgres";
};

For example, this can be useful to orchestrate the upgrade flow of your PostgreSQL fleet by
testing your changes in a test system before applying them in a production environment.

You can also configure the time and frequency of your updates by editing the following files:

• /etc/apt/[Link].d/20auto-upgrades to specify the periodicity:

APT::Periodic::Update-Package-Lists "1"; // 0 = disable, 1..n every n

days
APT::Periodic::Unattended-Upgrade "1";

• /etc/[Link]/apt-compat to specify a precise time.

Overview of the configuration

files for PostgreSQL
In order to harden a PostgreSQL installation, we will need to create or
alter the content of a number of files. Let’s start by having an overview of
each one of them before diving on the details of their content:

• [Link] is used by the pg_createcluster utility

when adding a new PostgreSQL instance
• [Link] is read upon restart of the main postgresql process (i.e. the postmaster
process) or when it receives a SIGHUP signal. It contains a number of parameters that cover a
variety of areas from logging to resource management (e.g. memory, connections settings)
• pg_hba.conf is used to configure supported client authentication methods and can also be
used to filter out allowed clients based on their IPs

Prepare your storage layout

As detailed in our infrastructure hardening guide, it is recommended to
place non-root writable files in dedicated storage pools or partitions.

This prevents an attacker from rendering the system inoperable by filling

out all directories where the attacker managed to have access.

Having different storage pools can also help optimize your storage performance/cost
ratio as detailed in this guide to database cloud migration. The guide also details the
types and benefits of encryption which we will use throughout this white paper.

We will use 5 storage pools :

• OS pool containing the OS installation

• LOGS containing all logs
• DATA containing the actual database files
• TEMP containing all temporary files
• RECO containing archives and backups

10
Creating storage pools

Let’s add 4 encrypted loop devices to emulate the 4 additional storage

pools. The encryption keys will be stored in the boot OS volume.

base_dir=/var/lib/luks/
keys_dir=/root/keys/
sudo install -d -m 700 "${base_dir}" "${keys_dir}"

mounts=(
"temp ,noexec"
"logs ,noexec"
"data"
"archive ,noexec"
)

for mount in "${mounts[@]}"; do

# Parse the mount array item into {mount_folder additional_mount_
opts}
IFS=' ' read -r mount_label mount_opts <<< "${mount}"

echo "#... 0) Creating mount for ${mount_label}"

echo "#... 1) Create a 1GB sized file"
loop_file="$(sudo mktemp -p "${base_dir}" "${mount_label}"-[Link])"
sudo truncate -s 1G "${loop_file}" # Make the temp file 1GB of size
echo "#... 2) Generate random keys"
key_file="${keys_dir}/luks-key-${mount_label}"
sudo openssl rand -out "${key_file}" 32
sudo chmod 400 "${key_file}" # Restricting access to the key
echo "#... 3) Encrypt the loop file"
sudo cryptsetup luksFormat "${loop_file}" --key-file "${key_file}"
--type luks2 --batch-mode
echo "#... 4) Mount the encrypted file (under /dev/mapper/, by
default)"
sudo cryptsetup open --type luks2 "${loop_file}" "${mount_label}"
--key-file "${key_file}"
echo "#... 5) Create an xfs filesystem"
sudo [Link] -L "${mount_label}" "/dev/mapper/${mount_label}"
echo "#... 6) Mount the filesystem"
mount_folder="${base_dir}${mount_label}"
sudo mkdir -p "${mount_folder}"
options="rw,nosuid,nodev${mount_opts}"
sudo mount -L "${mount_label}" -o "${options}" "${mount_folder}"
echo "#... 7) Persists the changes across reboots"
echo "${mount_label} ${loop_file} ${key_file} luks" | sudo tee -a /
etc/crypttab > /dev/null
echo "LABEL=${mount_label} ${mount_folder} xfs ${options} 0 2" | sudo
tee -a /etc/fstab > /dev/null
done

Check the output of the following commands to ensure our new pools are correctly configured:

lsblk # Check the mount points

more /etc/crypttab # Check that the correct entries were added
more /etc/fstab # Check that the correct entries were added
sudo systemctl daemon-reload # Ensures systemd is picking the changes
sudo findmnt --verify --verbose # Check all mounted filesystem
sudo umount "${base_dir}"*
sudo mount -av # Remount using fstab entries to check for their
coorectness
lsblk # Check, once more, the /mnt/<> points

11
Let’s now check that our mounts are persistent across reboots:

exit
exit
sudo lxc restart pg
sudo lxc list
sudo lxc exec pg bash
su -l ubuntu
lsblk

Now that we successfully encrypted the added devices, we need to

further protect the encryption keys. Let’s check our options:

Securing the encryption keys

It is obvious that we also need to protect the encryption keys. For the protection to be
effective, we need to rely on a mechanism that is outside the OS “boundaries” so that
compromising the OS does not necessarily compromise our storage. For example, we can use:

• A TPM which is a hardware component, independent from the CPU,

specifically designed to carry out cryptographic operations including securing
user supplied secrets. Additionally, using a TPM, we can pin the encrypted
components to the host where the secret was first issued and protected.
• A secret manager that holds the encryption key or some of its derivatives. The
tang server is an interesting example of such a solution as it does not hold the
encryption key, but helps in, securely, rebuilding it. Together, tang (the server part)
and clevis (the client part) can help protect your encryption keys while allowing
automatic decryption during boot. Using a secret manager will pin the encrypted
volumes to the networks from within the secret manager is reachable.
• Disk level encryption which encrypts at the disk level (not at filesystem level
as with LUKS). The encryption key of the disk can be managed by the solution
provider or by yourself (a.k.a. customer managed key). The combination of Azure’s
Disk Encryption set and KeyVault are an example of such a solution. Disk level
encryption typically pins the VM to the region where the VM was first launched.

We will provide in the Annexes section more details that might help
you implement some of the above techniques in your environment. For
now, we will focus on using the newly created storage pools.

Using the storage pools

We will start by moving non-PostgreSQL specific files to the new location.

Generic files

To make the transition as smooth as possible you can follow these generic steps:

1. Check the processes using a given <folder> with:

sudo lsof +D /<path-to>/<folder> | awk '!/COMMAND/{print $1 | "sort -u"}'
2. For every returned result, if you are fine with stopping it, run:
sudo systemctl --type=service --state=running # to identify the service
systemctl stop <service-name>.service # to stop it
3. Check that no output is returned by the previous lsof command (else retry)
4. Proceed with the instructions to move the concerned <folder>
5. Start again the previously stopped services or restart the node

12
Let’s start by moving the relevant temporary files to the new storage pool.

Configuring TEMP

Let’s start by /tmp

tmp_dir="${base_dir}"/temp/tmp
sudo mkdir -p "${tmp_dir}"
# Ensuring permissions are properly setup
sudo chmod 1777 "${tmp_dir}"
sudo chown root:root "${tmp_dir}"
ll /tmp/
sudo lsof +D /tmp/ | awk '!/COMMAND/{print $1 | "sort -u"}'
# stop listed services using: sudo systemctl stop <>.service
sudo rsync -avz /tmp/ "${tmp_dir}"
# sudo umount /tmp # if it is mounted already elsewhere
sudo mount --rbind "${tmp_dir}" /tmp
# start previously stopped services using: sudo systemctl start
<>.service
echo "${tmp_dir}" /tmp none rbind 0 0 | sudo tee -a /etc/fstab > /dev/
null

Let’s now do the same with /var/tmp

var_tmp_dir="${base_dir}"/temp/var/tmp
sudo mkdir -p "${var_tmp_dir}"
sudo chmod 1777 "${var_tmp_dir}"
sudo chown root:root "${var_tmp_dir}"
ll /var/tmp/
sudo lsof +D /var/tmp/ | awk '!/COMMAND/{print $1 | "sort -u"}'
# stop listed services using: sudo systemctl stop <>.service
sudo rsync -avz /var/tmp/ "${var_tmp_dir}"
sudo mount --rbind "${var_tmp_dir}" /var/tmp
# start previously stopped services using: sudo systemctl start
<>.service
echo "${var_tmp_dir}" /var/tmp none rbind 0 0 | sudo tee -a /etc/fstab
> /dev/null

Configuring LOGS

Let’s now do the same for /var/log

logs_dir="${base_dir}"/logs/var/log
sudo mkdir -p "${logs_dir}"
sudo chmod 1777 "${logs_dir}"
sudo chown root:syslog "${logs_dir}"
ll /var/log/
sudo lsof +D /var/log/ | awk '!/COMMAND/{print $1 | "sort -u"}'
# stop listed services using: sudo systemctl stop <>.service
sudo rsync -avz /var/log/ "${logs_dir}"
sudo mount --rbind "${logs_dir}" /var/log
echo "${logs_dir}" /var/log none rbind 0 0 | sudo tee -a /etc/fstab >
/dev/null

13
Let’s now restart the VM again to check that everything went fine:

sudo systemctl daemon-reload

exit
exit
sudo lxc restart pg
sudo lxc list
sudo lxc exec pg bash
su -l ubuntu
lsblk # check that all the mounts are present
journalctl # to check that no errors are showing up in your start-up
logs

PostgreSQL files

Now, we can progress to moving PostgreSQL-specific files:

New postgresql clusters

# If you have a fresh environment (as it is the case in this VM), it is

important to start by installing ONLY the following package:
sudo apt install -y postgresql-common
# Then we create specific folders that will be used by our database
clusters
sudo mkdir -p /mnt/data/pg /mnt/logs/pg /etc/postgresql-common/conf.d /
etc/postgresql-common/createcluster.d

Let’s create a template [Link] file (named [Link] below) that all newly
created databases will inherit from:

templ="/etc/postgresql-common/createcluster.d/[Link]"
sudo touch ${templ}
echo data_directory = "'/mnt/data/pg/%v/%c'" | sudo tee -a ${templ} > /
dev/null
echo log_directory = "'/mnt/logs/pg/%v/%c/'" | sudo tee -a ${templ} > /
dev/null
echo waldir = "'/mnt/logs/pg/%v/%c/pg_wal'" | sudo tee -a ${templ} > /
dev/null

Let’s now ensure that permissions are set correctly

sudo chown -R postgres:postgres /mnt/data/pg /mnt/logs/pg /etc/

postgresql-common/conf.d /etc/postgresql-common/createcluster.d
sudo chmod -R 770 /mnt/data/pg /mnt/logs/pg /etc/postgresql-common/
conf.d /etc/postgresql-common/createcluster.d

14
Existing PostgreSQL installations

Second, we need to move existing installations to the new locations. Ubuntu makes
installing PostgreSQL so easy that you basically just need to install one package (e.g.
postgresql-<version>) to get a fully functional PostgreSQL instance. Using the latter
installation method will configure postgresql to use the default locations for the
various directories it needs. Let’s start by checking the location of your files.

#Assuming the following was executed: sudo apt install -y postgresql-

<version>
sudo -i -u postgres
psql
# Note the output of the following command
SHOW data_directory;
exit
exit

From now on, we will assume that data_directory points to /

var/lib/postgresql/16/main. Please note that:

• main is the default name for a newly created cluster installed using postgresql-
<version>. You might need to replace it with the existing cluster names
• running the following instructions will result in a downtime that you need to plan for

sudo systemctl stop [Link]

sudo mkdir -p /mnt/data/pg/16/main
ls -ad /var/lib/postgresql/16/main/* # Checking current content
sudo rsync -avz /var/lib/postgresql/16/main/ /mnt/data/pg/16/main
sudo chown -R postgres:postgres /mnt/data/pg/16/main
sudo -i -u postgres
ls -ad /mnt/data/pg/16/main/*
mv /mnt/data/pg/16/main/pg_wal/ /mnt/logs/pg/16/main/pg_wal
ln -s /mnt/logs/pg/16/main/pg_wal/ /mnt/data/pg/16/main/pg_wal

Let’s change the [Link] to point to

the new location:

vi /etc/postgresql/16/main/[Link]
# Change the following lines as shown below
data_directory = '/mnt/data/pg/16/main'

Let’s now start back the PostgreSQL service:

sudo systemctl start [Link]

sudo -i -u postgres
psql
SHOW data_directory;
exit
exit

Now that we have successfully configured our storage, let’s address logging and
auditing – both of which are an essential part of a hardening strategy.

15
Logging
Let’s create a template config file that we will place at:

templ="/etc/postgresql-common/conf.d/[Link]"
sudo touch ${templ}
sudo vi ${templ}

Let’s copy/paste the following content:

# Ensure logs are collected and rotated

logging_collector = on
log_destination = 'stderr'
log_rotation_age = 1
log_rotation_size = 0
log_truncate_on_rotation = on
log_filename = 'postgresql-%w_%H%[Link]'
log_timezone = 'UTC'
log_file_mode = '0600'
lc_messages = 'en_US.UTF8'
log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h,line=%l '
# Ensure enough logs are collected
log_statement = 'ddl'
log_connections = on
log_disconnections = on
log_min_messages = warning
log_error_verbosity = verbose
log_min_error_statement = warning
log_temp_files = 1
log_checkpoints = on
log_min_duration_sample = -1
log_statement_sample_rate = 1
log_replication_commands = on
log_recovery_conflict_waits = on
# Prevent leaking data or overloading/slowing down the system
log_hostname = off
debug_print_plan = off
debug_print_parse = off
debug_print_rewritten = off
log_statement_stats = off #wq to save and exit

Let’s now make sure that the above settings will be picked by
the to-be created clusters by doing the following:

echo add_include_dir = "'/etc/postgresql-common/conf.d'" | sudo tee -a

/etc/postgresql-common/[Link] > /dev/null

Please note that while the above settings should fit most organizations' needs,
you might want to customize some values to comply with your internal policies.
Now that all future PostgreSQL deployments will comply with our common
configuration, let’s proceed with the installation of a sample PostgreSQL server.

16
Installation
Hardening is all about making the task of a malicious user as difficult as possible.
One way to achieve this is to avoid predictable values (like default ones) even
if it might look harmless at a first look. We will apply this principle to:

Database port
We will use the following function to get a randomly chosen open port:

free_port="$(comm -23 <(seq 1024 65535 | sort) <(sudo ss -atunH | awk

'{print $5}' | awk -F":" '{print $NF}' | sort | uniq) | shuf | head -n
1)"
echo ${free_port}

When we run the database in a sandbox (as we are doing within

a VM in our case), we need to remember to:

• Choose, randomly, an open port in the host node

• Forward the host’s node port traffic to the sandbox’s port

Database cluster name

Similarly to ports, we will avoid using the default cluster name “main” when
installing a postgresql version. We will therefore add the following line:

echo create_main_cluster = false | sudo tee -a /etc/postgresql-common/

createcluster.d/[Link] > /dev/null

Database username
We can also customize the OS username by using the option -u when
adding a PostgreSQL cluster using the pg_createcluster utility as we
will do in the next section. Let’s create the OS user first:

sudo adduser pg1 --system --ingroup postgres --shell /bin/bash --home /

home/mypg1 --comment 'User for postgresql cluster pg1'

Adding a PostgreSQL cluster

Finally, we can proceed with the installation:

sudo apt install -y postgresql-16

sudo pg_createcluster 16 pg1 -u pg1 -p "${free_port}"

After the above steps, we recommend restarting the node. Then we can
use pg_lsclusters to check that the installation went successfully:

exit
exit
sudo lxc restart pg
sudo lxc exec pg bash
su -l ubuntu
pg_lsclusters
more /var/log/postgresql/[Link] # Check startup logs
# sudo vi /mnt/logs/pg/16/pg1/<log_file_name>.log # Check the first log
file 17
Now that we have a running PostgreSQL cluster, we can try to connect to it as follows:

sudo -iu pg1 # To connect as the database user

psql -d postgres -p <allocated-port> # Check pg_lsclusters for the port
SELECT version(); # Will display the installed PG version
\q # Will make you exit the prompt
exit

Conclusion
In this whitepaper, we covered the hardening measures you need to implement for your
PostgreSQL fleet before and during the installation. We established that hardening is about
making the task of a malicious user as difficult as possible. We demonstrated that hardening
requires discipline in many layers, from your supply chain to your database management system.

In the next whitepaper, we focus on post-deployment hardening

tasks such as auditing, authorization, and authentication.

In the meantime, if you need professional help with your PostgreSQL deployments
then you can rely on our services at Canonical, the open source experts:

Security maintenance
We can provide you with regularly maintained PostgreSQL packages either
in deb, snap or container format for up to 10 years per release track. We
cover not only the PostgreSQL server but also the extensions and tools you
typically install with PostgreSQL, such as timescale and pgbackrest.

Ticket and phone support

In addition to security maintenance, our experts will respond to your critical
support requests in under an hour to help you keep your system up and
running. You can choose between 24/7or 24/5 to better suit your needs.

Firefighting support
With firefighting support, our dedicated staff will get on a video call
with you to help you diagnose and resolve your PostgreSQL issues
while you keep full control over your infrastructure.

Managed PostgreSQL
Our database team manages all your Day 2 operations, from observability to upgrades.
We handle all issues, and consistently reinforce the security of your environment.

Data solutions advisory

We provide training and workshops for a range of data solutions. Together,
we will develop and deploy solutions tailored to your specific needs.

Get in touch
Send us a message to discuss your specific use case.

Author
Mohamed Wadie Nsiri 18
Annexes
As promised earlier, you can find in the following sections some instructions
that can help you implement secure and compliant encryption at-rest.

Trusted Platform Module

The idea is to bind the encryption key to the TPM of the host. We

exit
exit
sudo lxc stop pg
sudo lxc list
sudo lxc config device add pg vtpm tpm path=/dev/tpm0 pathrm=/dev/
tpmrm0
sudo lxc start pg
sudo lxc exec pg bash
ll /dev/tpm* # Check that you have a tpm0 and a tmprm0 added

First, we will create a master encryption key that we will use to encrypt the individual
volume keys. This is a typical pattern that is used in many Transparent Data Encryption
implementations such as Oracle database’s TDE. Let’s proceed with the key generation:

keys_dir="/root/keys/"
master_key_file="${keys_dir}luks-key-master"
openssl rand -out "${master_key_file}" 32
chmod 400 "${master_key_file}"
ll "${keys_dir}"

We will now encrypt the storage keys using the newly created master key.

# The following should be executed as sudo

for key_file in "${keys_dir}"luks-key-*; do
if [ -f "${key_file}" ] && [ "${key_file}" != "${master_key_file}"
]; then
name=$(basename "${key_file}" | sed 's/^luks-key-//')
encrypted_file="${keys_dir}luks-key-${name}.enc"
openssl enc -aes-256-cbc -pbkdf2 -salt \
-in "${key_file}" \
-out "${encrypted_file}" \
-pass file:"${master_key_file}"
if [ -f "${encrypted_file}" ]; then
chmod 400 "${encrypted_file}"
shred --remove --zero "${key_file}"
echo "Key $name is now encrypted"
fi
fi
done
ll "${keys_dir}"

We will now seal the master key using the TPM emulator we previously attached
to our VM. This operation involves several steps that are summarized below:

• First, we will create a policy that defines under which conditions the sealed-
key can be retrieved from the TPM. This is done by linking the state of some
registers to the sealed key. The key can then be unsealed only if the state of
chosen registers matches the one measured at the time of sealing.
19
• Second we will create a primary key under the TPM’s hierarchy. This can
be understood as an isolated vault within the TPM. Objects sealed using
this key can be unsealed only using the same primary keys’ identity.
• Create a sealed version of our master key
• Load the sealed version in TPM memory
• Persist the sealed version in the TPM

In order to test that our operation worked as expected, we will also:

• Retrieve the sealed object

• Ensure the sealed object matches the original key
• Clean-up all non-necessary objects

Let’s start by installing some tpm related tools:

sudo apt -y install tpm2-tools # Installs TPM tools

We will now perform the previously described operations:

# Init some variables for later use

keys_dir="/root/keys/"
master_key_file="${keys_dir}luks-key-master"
tpm_handle="0x81010001"
handle_file="${keys_dir}[Link]"
temp_key="${keys_dir}temp-luks-key-master"
policy="sha256:0" # <algo>:<pcrs>
set -eu # Fail on first error
if [ -f "${master_key_file}" ]; then
echo "# Create a policy to bind the key to the state of specific
PCRs"
tpm2_createpolicy --policy-pcr -l "${policy}" -L "${keys_dir}pcr.
policy"
echo "# Create a TPM primary key"
tpm2_createprimary -Q -C o -g sha256 -G rsa -c "${keys_dir}[Link]"
echo "# Seal our master encryption key to the TPM"
tpm2_create -Q -g sha256 -C "${keys_dir}[Link]" \
-i "${master_key_file}" -L "${keys_dir}[Link]" \
-u "${keys_dir}[Link]" -r "${keys_dir}[Link]"
echo "# Load the sealed object into the TPM memory"
tpm2_load -Q -C "${keys_dir}[Link]" \
-u "${keys_dir}[Link]" -r "${keys_dir}[Link]" \
-n "${keys_dir}[Link]" -c "${keys_dir}[Link]"
echo "# Load the sealed object into the TPM persistent store"
tpm2_evictcontrol -C o -c "${keys_dir}[Link]" "${tpm_handle}"
echo "${tpm_handle}" | sudo tee "${handle_file}" > /dev/null
echo "# Unseal the key from the TPM for validation"
tpm2_startauthsession --policy-session -S [Link]
tpm2_policypcr -S [Link] -l "${policy}"
tpm2_unseal -c "${tpm_handle}" -p "session:[Link]" > "${temp_
key} tpm2_flushcontext [Link]
if [ -s "${temp_key}" ]; then
echo "Verify integrity of the sealed key"
if cmp -s "${master_key_file}" "${temp_key}"; then
echo "Cleanup"
shred --remove --zero "${master_key_file}" "${temp_key}"
shred --remove --zero "${keys_dir}[Link]"
rm -f "${keys_dir}/[Link]" \
"${keys_dir}/[Link]" \
"${keys_dir}/[Link]" \
"${keys_dir}/[Link]"
fi
fi
fi
ll "${keys_dir}"
20
Let’s now create a script that will:

• Unseal the primary key into the system memory

• Decrypt the volume keys into the system memory

sudo tee -a "${keys_dir}/[Link]" > /dev/null <<"EOT"

!/bin/sh
set -eu # Abort on first error
# Init some variables
keys_dir="/root/keys"
runtime_key_dir="/run/luks-keys" # <- ! Typically mounted in memory
master_key="${runtime_key_dir}/luks-key-master"
tpm_handle=$(cat "${keys_dir}/[Link]")
policy_file="${keys_dir}/[Link]"
policy="sha256:0" # <algo>:<pcrs>
logfile="/run/[Link]"
# Create directories for logs, unseled master key and decrypted
volume keys
mkdir -p "$(dirname "$logfile")"
mkdir -m 700 -p "${runtime_key_dir}"
# Check if tpm is visible at this stage
ls -l /dev/tpm* >> "$logfile" 2>&1
log() {
echo "[$(date +'%Y-%m-%d %H:%M:%S')] $*" >> "$logfile"
}
log "Starting TPM unseal process..."
if [ ! -f "$policy_file" ]; then
log "Missing PCR policy file: $policy_file"
exit 1
fi
if [ ! -f "${keys_dir}/[Link]" ]; then
log "Missing TPM handle file: [Link]"
exit 1
fi
if [ ! -d /run/initramfs ]; then
log "Not running inside initramfs — skipping unseal."
exit 0
fi
# Start and use a TPM policy session
tpm2_flushcontext [Link] 2>/dev/null || true
if ! TPM2TOOLS_TCTI=device:/dev/tpm0 tpm2_startauthsession
--policy-session -S [Link]; then
log "Failed to start TPM policy session"
exit 1
fi
if ! tpm2_policypcr -S [Link] -l "$policy" ; then
log "Failed to apply PCR policy"
tpm2_flushcontext [Link]
exit 1
fi
if ! tpm2_unseal -c "$tpm_handle" -p "session:[Link]" >
"$master_key"; then
log "TPM unseal failed with policy session"
tpm2_flushcontext [Link]
exit 1
fi
tpm2_flushcontext [Link]
chmod 400 "$master_key"
log "Master key unsealed to ${master_key}"
# Decrypt each per-volume key for enc_key in "${keys_dir}"/luks-
key-*.enc; do
name=$(basename "${enc_key}" | sed 's/^luks-key-//' | sed 's/\.
enc$//')

21
 decrypted_key="${runtime_key_dir}/luks-key-${name}"
ln_target="${keys_dir}/luks-key-${name}"
if openssl enc -aes-256-cbc -d -pbkdf2 -in "${enc_key}" \
-out "${decrypted_key}" -pass file:"${master_key}"; then
chmod 400 "${decrypted_key}"
ln -sf "${decrypted_key}" "${ln_target}"
log "Key ${name} decrypted and linked at ${ln_target}"
else
log "Failed to decrypt key: ${enc_key}"
fi
done
# Remove unsealed master key from memory
rm -f "${master_key}"
log "Unseal process completed."
EOT

You can test the above setup by running:

sudo chmod +x "${keys_dir}/[Link]"

${keys_dir}/[Link]
ll "${keys_dir}"

You will still need to integrate the script in your own boot process which depends
on your environment and is outside the scope of this document. Let’s now check
how we can implement encryption at-rest using tang and clevis components.

A secret manager
Let’s start by spawning a VM to host the Tang server:

exit
exit
sudo lxc launch ubuntu:24.04 tang --vm -c [Link]=2 -c limits.
memory=4GiB
sudo lxc exec tang bash
su -l ubuntu
sudo apt-get update
sudo apt install -y tang jose
sudo systemctl enable [Link]
sudo systemctl start [Link]
tang-show-keys # Note the provided signing key

22
Let’s now deploy some prerequisites in our postgresql VM:

exit
exit
# Check the ipv4 of the tang server using
sudo lxc list tang -c 4 | awk '/\./ {print $2}'
sudo lxc exec pg bash
su -l ubuntu
tang_ip=<ip noted previously>
# Check we have connectivity with tang server
curl -sfg [Link] -o /tmp/[Link]
# Check that the following corresponds to the signature of the tang
server
more /tmp/[Link]
sudo apt install -y clevis clevis-luks

Let’s now bind the encrypted devices to the tang server:

tconf="{\"url\": \"[Link] \"adv\": \"/tmp/[Link]\"}"

while IFS= read -r line; do
if [[ "$line" == *"luks"* ]]
then
IFS=' ' read -r name device key_file type <<< "$line"
sudo clevis luks bind -f -d "$device" -k "$key_file" tang
"$tconf"
# Check that the bound was successful
sudo clevis luks list -d "$device"
fi
done < /etc/crypttab

Again, you will still need to integrate the script in your own boot process which depends
on your environment and is therefore outside the scope of this document.

23
© 2025 Canonical Limited. Ubuntu, Kubuntu, Canonical and Canonical Limited
their associated logos are the registered trademarks of
Canonical Ltd. All other trademarks are the properties of Registered in Isle of Man,
their respective owners. Any information referred to in this Company number 110334C
document may change without notice and Canonical will not
be held responsible for any such changes. Registered office

2nd Floor
Clarendon House
Victoria Street
Douglas
IM1 2LN
Isle of Man