EN

Linux and networking

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.

9 min read, updated on 18 November 2026

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.

  1. Install the required tools and download the repository key:

    Terminal
    sudo 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
  2. Check the key fingerprint:

    Terminal
    gpg --show-key /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc

    It must match B97B0AFCAA1A47F044F244A07FCC7D46ACCC4CF8. If it does not, stop here.

  3. 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
  4. Install the server:

    Terminal
    sudo apt update
    sudo apt install -y postgresql-18

    The package automatically creates and starts an instance named 18/main.

Check the installation

Terminal
pg_lsclusters
sudo systemctl status postgresql@18-main --no-pager
sudo -u postgres psql -c "SELECT version();"
sudo ss -tlnp | grep 5432

pg_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:

Terminal
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:

  • peer for local socket connections: the system user name must match the role name. This is why sudo -u postgres psql works without a password.
  • scram-sha-256 for TCP connections from 127.0.0.1 and ::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.

  1. Create the roles. Each command asks for a password, so it does not stay in your shell history:

    Terminal
    sudo -u postgres createuser --pwprompt app_owner
    sudo -u postgres createuser --pwprompt app_reader
  2. Create the database:

    Terminal
    sudo -u postgres createdb --owner=app_owner appdb
  3. Restrict access to this database:

    Terminal
    sudo -u postgres psql -d appdb
    SQL
    REVOKE 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;
    \q

    The last statement also gives app_reader read access to future tables created by app_owner.

  4. Check the password connection:

    Terminal
    psql -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_reader must fail with permission denied for table restore_check. The restore_check table will be used later to check your restores.

Access the database remotely

The tunnel requires no change to PostgreSQL or the firewall. From your computer:

Terminal
ssh -N -L 5433:127.0.0.1:5432 YOUR_USER@YOUR_SERVER_IP

Leave this command running, then connect your client to local port 5433:

Terminal
psql -h 127.0.0.1 -p 5433 -U app_owner -d appdb

Advanced 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.

  1. Create a script that copies the certificate for PostgreSQL and runs at every renewal:

    Terminal
    sudo nano /etc/letsencrypt/renewal-hooks/deploy/postgresql.sh
    Terminal
    #!/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
  2. Make it executable and run it once:

    Terminal
    sudo 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
  3. Create the configuration file:

    Terminal
    sudo nano /etc/postgresql/18/main/conf.d/90-remote-access.conf
    Configuration
    listen_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'
  4. At the end of /etc/postgresql/18/main/pg_hba.conf, add a rule limited to one database, one role and one address:

    Text
    hostssl appdb app_owner YOUR_IP/32 scram-sha-256

    hostssl rejects any unencrypted connection. For an IPv6 address, use the /128 suffix.

  5. Allow only your address in the firewall. With UFW:

    Terminal
    sudo ufw allow from YOUR_IP to any port 5432 proto tcp
  6. Restart PostgreSQL, because listen_addresses is not picked up by a simple reload:

    Terminal
    sudo 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.

  7. Connect from your computer with a psql client version 16 or later:

    Terminal
    psql "host=db.your-domain.com port=5432 dbname=appdb user=app_owner sslmode=verify-full sslrootcert=system"

    Then check that the session is encrypted:

    SQL
    SELECT 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.

  1. Create the destination folder:

    Terminal
    sudo install -d -o postgres -g postgres -m 700 /var/backups/postgresql
  2. Create the script:

    Terminal
    sudo nano /usr/local/sbin/postgresql-backup.sh
    Terminal
    #!/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 {} +
  3. Create the service:

    Terminal
    sudo chmod 755 /usr/local/sbin/postgresql-backup.sh
    sudo nano /etc/systemd/system/postgresql-backup.service
    Configuration
    [Unit]
    Description=PostgreSQL backup
    After=postgresql.service
    
    [Service]
    Type=oneshot
    User=postgres
    Group=postgres
    ExecStart=/usr/local/sbin/postgresql-backup.sh
  4. Create the timer:

    Terminal
    sudo nano /etc/systemd/system/postgresql-backup.timer
    Configuration
    [Unit]
    Description=Daily PostgreSQL backup
    
    [Timer]
    OnCalendar=*-*-* 02:30:00
    RandomizedDelaySec=10min
    Persistent=true
    
    [Install]
    WantedBy=timers.target
  5. Enable it and run a first backup:

    Terminal
    sudo 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.

Terminal
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_test

You 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:

Terminal
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:

Terminal
sudo apt update
sudo apt upgrade

A new major version is installed alongside the old one and requires a migration. Run a backup first, then adjust the version numbers:

Terminal
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_lsclusters

Here, 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:

Terminal
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:

Terminal
sudo systemctl reload postgresql@18-main

FATAL: password authentication failed for user

The password is wrong or the role does not have one. Set it again from an administration session:

Terminal
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

Terminal
pg_lsclusters
sudo ss -tlnp | grep 5432
sudo ufw status
sudo tail -n 50 /var/log/postgresql/postgresql-18-main.log

Connection 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:

Terminal
sudo journalctl -u postgresql@18-main -n 50 --no-pager
sudo tail -n 50 /var/log/postgresql/postgresql-18-main.log

With 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.

References