Install and Secure PostgreSQL
Install PostgreSQL 18 from the official repository on Debian or Ubuntu: roles and privileges, remote access over TLS, automated backups and a restore test.
On this page
Your values
Enter them once and the commands in this guide update.
This guide explains how to install PostgreSQL on your VPS or dedicated server from the project's official repository, create roles with limited privileges, open encrypted remote access if needed, and set up backups that you will know how to restore.
By the end, PostgreSQL listens on the local machine only, each application has its own role, and a daily backup is produced and checked with a restore test.
Order a server
To host your database, HostMyServers offers several plans:
Prerequisites
- A server running Debian 12 or 13, or Ubuntu 22.04 or 24.04
- SSH access with a user that has
sudo - An up-to-date system (
sudo apt update && sudo apt upgrade) - For direct remote access only: a domain name pointing to the server
Ports to open
| Use | Port | Open it? |
|---|---|---|
| Applications on the same server | 5432/tcp, local only | No |
| Remote access through an SSH tunnel (recommended) | 22/tcp, or your SSH port | Already open for administration |
| Direct remote access over TLS | 5432/tcp | Yes, only from your clients' IP address |
Install PostgreSQL from the official repository
Debian and Ubuntu ship PostgreSQL, but the version stays frozen for the whole life of the distribution. The PGDG repository (apt.postgresql.org) offers every maintained version.
At the time of writing, the latest major version is PostgreSQL 18 (18.7). See the versioning policy to find the current version.
-
Install the required tools and download the repository key:
Terminalsudo apt install -y curl ca-certificates gpg sudo install -d /usr/share/postgresql-common/pgdg sudo curl -fsSL -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc https://www.postgresql.org/media/keys/ACCC4CF8.asc -
Check the key fingerprint:
Terminalgpg --show-key /usr/share/postgresql-common/pgdg/apt.postgresql.org.ascIt must match
B97B0AFCAA1A47F044F244A07FCC7D46ACCC4CF8. If it does not, stop here. -
Add the repository:
Terminal. /etc/os-release sudo tee /etc/apt/sources.list.d/pgdg.sources > /dev/null <<EOF Types: deb URIs: https://apt.postgresql.org/pub/repos/apt Suites: ${VERSION_CODENAME}-pgdg Architectures: $(dpkg --print-architecture) Components: main Signed-By: /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc EOF -
Install the server:
Terminalsudo apt update sudo apt install -y postgresql-18The package automatically creates and starts an instance named
18/main.
Check the installation
pg_lsclusters
sudo systemctl status postgresql@18-main --no-pager
sudo -u postgres psql -c "SELECT version();"
sudo ss -tlnp | grep 5432pg_lsclusters must show the 18 main instance with the online status. ss must show a listener on 127.0.0.1:5432 and [::1]:5432, not on 0.0.0.0.
| Item | Location |
|---|---|
| Configuration | /etc/postgresql/18/main/ |
| Additional settings | /etc/postgresql/18/main/conf.d/ |
| Access rules | /etc/postgresql/18/main/pg_hba.conf |
| Data | /var/lib/postgresql/18/main/ |
| Log | /var/log/postgresql/postgresql-18-main.log |
Understand authentication
The pg_hba.conf file decides who can connect, to which database and with which method. PostgreSQL reads its lines from top to bottom and applies the first one that matches. If that method fails, the following lines are not tried.
Display the active rules:
sudo -u postgres psql -c "SELECT line_number, type, database, user_name, address, auth_method, error FROM pg_hba_file_rules;"On a fresh installation, you should find two methods:
peerfor local socket connections: the system user name must match the role name. This is whysudo -u postgres psqlworks without a password.scram-sha-256for TCP connections from127.0.0.1and::1: a password is requested.
Create roles and a database
Do not use the postgres superuser in your applications. The following example creates an appdb database, an app_owner owner role and an app_reader read-only role.
-
Create the roles. Each command asks for a password, so it does not stay in your shell history:
Terminalsudo -u postgres createuser --pwprompt app_owner sudo -u postgres createuser --pwprompt app_reader -
Create the database:
Terminalsudo -u postgres createdb --owner=app_owner appdb -
Restrict access to this database:
Terminalsudo -u postgres psql -d appdbSQLREVOKE ALL ON DATABASE appdb FROM PUBLIC; GRANT CONNECT ON DATABASE appdb TO app_reader; GRANT USAGE ON SCHEMA public TO app_reader; GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_reader; ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public GRANT SELECT ON TABLES TO app_reader; \qThe last statement also gives
app_readerread access to future tables created byapp_owner. -
Check the password connection:
Terminalpsql -h 127.0.0.1 -U app_owner -d appdb -c "CREATE TABLE restore_check (id int PRIMARY KEY, note text);" psql -h 127.0.0.1 -U app_owner -d appdb -c "INSERT INTO restore_check VALUES (1, 'sauvegarde OK');" psql -h 127.0.0.1 -U app_reader -d appdb -c "SELECT * FROM restore_check;"A write attempt with
app_readermust fail withpermission denied for table restore_check. Therestore_checktable will be used later to check your restores.
Access the database remotely
Recommended option: SSH tunnel
The tunnel requires no change to PostgreSQL or the firewall. From your computer:
ssh -N -L 5433:127.0.0.1:5432 YOUR_USER@YOUR_SERVER_IPLeave this command running, then connect your client to local port 5433:
psql -h 127.0.0.1 -p 5433 -U app_owner -d appdbAdvanced option: direct connection over TLS
Use this option if an external service cannot go through SSH. It requires a domain name, for example db.your-domain.com, and a valid certificate: see our guide Install an SSL certificate with Let's Encrypt to get one with Certbot.
-
Create a script that copies the certificate for PostgreSQL and runs at every renewal:
Terminalsudo nano /etc/letsencrypt/renewal-hooks/deploy/postgresql.shTerminal#!/bin/sh set -e DOMAIN="db.your-domain.com" [ "$RENEWED_LINEAGE" = "/etc/letsencrypt/live/$DOMAIN" ] || exit 0 install -o postgres -g postgres -m 644 "$RENEWED_LINEAGE/fullchain.pem" /etc/postgresql/18/main/server.crt install -o postgres -g postgres -m 600 "$RENEWED_LINEAGE/privkey.pem" /etc/postgresql/18/main/server.key systemctl reload postgresql@18-main -
Make it executable and run it once:
Terminalsudo chmod 755 /etc/letsencrypt/renewal-hooks/deploy/postgresql.sh sudo RENEWED_LINEAGE=/etc/letsencrypt/live/db.your-domain.com /etc/letsencrypt/renewal-hooks/deploy/postgresql.sh -
Create the configuration file:
Terminalsudo nano /etc/postgresql/18/main/conf.d/90-remote-access.confConfigurationlisten_addresses = '*' ssl = on ssl_cert_file = '/etc/postgresql/18/main/server.crt' ssl_key_file = '/etc/postgresql/18/main/server.key' ssl_min_protocol_version = 'TLSv1.2' -
At the end of
/etc/postgresql/18/main/pg_hba.conf, add a rule limited to one database, one role and one address:Texthostssl appdb app_owner YOUR_IP/32 scram-sha-256hostsslrejects any unencrypted connection. For an IPv6 address, use the/128suffix. -
Allow only your address in the firewall. With UFW:
Terminalsudo ufw allow from YOUR_IP to any port 5432 proto tcp -
Restart PostgreSQL, because
listen_addressesis not picked up by a simple reload:Terminalsudo systemctl restart postgresql@18-main sudo -u postgres psql -c "SELECT line_number, error FROM pg_hba_file_rules WHERE error IS NOT NULL;"The second command must return no rows.
-
Connect from your computer with a
psqlclient version 16 or later:Terminalpsql "host=db.your-domain.com port=5432 dbname=appdb user=app_owner sslmode=verify-full sslrootcert=system"Then check that the session is encrypted:
SQLSELECT ssl, version FROM pg_stat_ssl WHERE pid = pg_backend_pid();
Automate backups
pg_dump exports a database without stopping it, but it does not back up roles or their passwords. The following script therefore also exports these global objects with pg_dumpall --globals-only.
-
Create the destination folder:
Terminalsudo install -d -o postgres -g postgres -m 700 /var/backups/postgresql -
Create the script:
Terminalsudo nano /usr/local/sbin/postgresql-backup.shTerminal#!/bin/bash set -euo pipefail umask 077 DEST="/var/backups/postgresql" RETENTION_DAYS=14 STAMP="$(date +%F_%H%M%S)" TARGET="$DEST/$STAMP" mkdir -p "$TARGET" pg_dumpall --globals-only -f "$TARGET/globals.sql" psql -AtX -d postgres -c "SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate" | while IFS= read -r db; do pg_dump -Fc -d "$db" -f "$TARGET/$db.dump" done find "$DEST" -mindepth 1 -maxdepth 1 -type d -mtime +"$RETENTION_DAYS" -exec rm -rf {} + -
Create the service:
Terminalsudo chmod 755 /usr/local/sbin/postgresql-backup.sh sudo nano /etc/systemd/system/postgresql-backup.serviceConfiguration[Unit] Description=PostgreSQL backup After=postgresql.service [Service] Type=oneshot User=postgres Group=postgres ExecStart=/usr/local/sbin/postgresql-backup.sh -
Create the timer:
Terminalsudo nano /etc/systemd/system/postgresql-backup.timerConfiguration[Unit] Description=Daily PostgreSQL backup [Timer] OnCalendar=*-*-* 02:30:00 RandomizedDelaySec=10min Persistent=true [Install] WantedBy=timers.target -
Enable it and run a first backup:
Terminalsudo systemctl daemon-reload sudo systemctl enable --now postgresql-backup.timer sudo systemctl start postgresql-backup.service sudo systemctl status postgresql-backup.service --no-pager sudo -u postgres ls -lR /var/backups/postgresql
Test the restore
A backup is only worth something if you have already restored it successfully. This test uses a temporary database and does not touch your production database.
STAMP="$(sudo -u postgres ls -1 /var/backups/postgresql | tail -n 1)"
sudo -u postgres createdb restore_test
sudo -u postgres pg_restore --no-owner --no-privileges --exit-on-error -d restore_test "/var/backups/postgresql/$STAMP/appdb.dump"
sudo -u postgres psql -d restore_test -c "SELECT * FROM restore_check;"
sudo -u postgres dropdb restore_testYou must find the sauvegarde OK row. Repeat this check regularly, and also compare a few tables that matter for your application.
Restore after an incident
On a new server where PostgreSQL is installed, or after stopping the applications and deleting the damaged database:
STAMP="2026-11-18_023000"
sudo -u postgres psql -X -f "/var/backups/postgresql/$STAMP/globals.sql" postgres
sudo -u postgres dropdb --if-exists appdb
sudo -u postgres pg_restore --create --exit-on-error -d postgres "/var/backups/postgresql/$STAMP/appdb.dump"
sudo -u postgres psql -d appdb -c "ANALYZE;"Replace the value of STAMP with the folder you want. The role "postgres" already exists error when restoring the roles is normal.
Update PostgreSQL
Minor versions (for example from 18.6 to 18.7) fix bugs and vulnerabilities. They are installed with the system and restart the service:
sudo apt update
sudo apt upgradeA new major version is installed alongside the old one and requires a migration. Run a backup first, then adjust the version numbers:
OLD=18
NEW=19
sudo systemctl start postgresql-backup.service
sudo apt install -y "postgresql-$NEW"
pg_lsclusters
sudo pg_dropcluster --stop "$NEW" main
sudo pg_upgradecluster -v "$NEW" "$OLD" main
pg_lsclustersHere, pg_dropcluster deletes the empty instance created by the new package: check with pg_lsclusters that it contains nothing useful. pg_upgradecluster then places the new version on the original port and keeps the old one, stopped. Once you have checked your applications, remove it:
sudo pg_dropcluster "$OLD" main
sudo apt purge "postgresql-$OLD" "postgresql-client-$OLD"Remember to adjust the version number in the certificate renewal script if you use it.
Troubleshooting
FATAL: no pg_hba.conf entry for host
No rule matches the address, role, database or encryption. The message shows the values the server received. Check that they match your hostssl line, including your client's real public IP address, then reload the configuration:
sudo systemctl reload postgresql@18-mainFATAL: password authentication failed for user
The password is wrong or the role does not have one. Set it again from an administration session:
sudo -u postgres psql -c "\password app_owner"FATAL: Peer authentication failed for user
You are connecting through the local socket with a role different from your system user. Add -h 127.0.0.1 to use the password, or run the command as the right user with sudo -u.
Connection refused or timeout
pg_lsclusters
sudo ss -tlnp | grep 5432
sudo ufw status
sudo tail -n 50 /var/log/postgresql/postgresql-18-main.logConnection refused usually means PostgreSQL is stopped or is not listening on that address. A timeout rather points to a firewall.
permission denied for table or for schema public
The role does not have the required privileges. Replay the GRANT statements while connected to the right database. For existing tables created by another role, ALTER DEFAULT PRIVILEGES is not enough: it only applies to future tables.
The service no longer starts after a change
The log shows the file and line at fault:
sudo journalctl -u postgresql@18-main -n 50 --no-pager
sudo tail -n 50 /var/log/postgresql/postgresql-18-main.logWith a custom certificate, also check that server.key belongs to postgres with 600 permissions.
could not change directory to "/root"
This message appears when you run sudo -u postgres from a folder that postgres cannot read. It is harmless: move to /tmp to make it disappear.