Set up PostgreSQL on a VPS securely

This guide installs PostgreSQL on an Ubuntu/Debian VPS the secure, production-ready way: install it, create a database and user, open remote access under control (one exact IP + required SSL), and finish with an explanation of transaction isolation so you can pick the right level.

Replace <username>, <password>, <database_name>, <server-ip> with your real values. The example IP 203.0.113.42 is a placeholder.

1. Install PostgreSQL

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql.service

sudo systemctl enable postgresql
sudo service postgresql status

2. Create a database and user

Open the PostgreSQL prompt:

sudo -u postgres psql

Create the user, set a few defaults (encoding, isolation, timezone), then create the database:

CREATE USER <username> WITH ENCRYPTED PASSWORD '<password>';

ALTER ROLE <username> SET client_encoding TO 'utf8';
ALTER ROLE <username> SET default_transaction_isolation TO 'read committed';
ALTER ROLE <username> SET timezone TO 'UTC';

CREATE DATABASE <database_name>
    WITH OWNER = <username>
    ENCODING = 'UTF8'
    TEMPLATE = template0;
\q

3. Open remote access safely

If you need to connect from your local machine or another server, follow these steps. The principle: allow one exact IP, require SSL, and block at both the firewall and pg_hba.conf.

On the machine that needs to connect, get its public IP:

curl ifconfig.me

Note that IP (for example 203.0.113.42).

Configure the listen address

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

Replace 16 with your PostgreSQL version (ls /etc/postgresql/ to check). Set:

# Listen only on localhost and the server's public IP
listen_addresses = 'localhost,<server-ip>'
port = 5432

# REQUIRED: enable SSL to encrypt connections
ssl = on

Configure pg_hba.conf (most important)

This file decides who is allowed to connect.

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

Remove/comment any dangerous line, then add a rule for the exact IP with required SSL at the end of the file:

# DANGEROUS - allows any IP: host all all 0.0.0.0/0 md5
# Safe rule: one exact IP + required SSL
hostssl    all    all    203.0.113.42/32    scram-sha-256

What each column means:

Tip: replace all with a specific database and user to narrow access further.

UFW firewall + restart

# Allow only that IP to reach port 5432
sudo ufw allow from 203.0.113.42 to any port 5432
# Deny every other IP
sudo ufw deny 5432
sudo ufw status

sudo systemctl restart postgresql

The two layers (UFW + pg_hba.conf) complement each other.

4. Verify the connection

Confirm PostgreSQL is listening on the right IP:

sudo ss -lntp | grep 5432

Test the connection from the local machine:

psql -h <server-ip> -U <username> -d <database_name>

Appendix: transaction isolation

When many requests/users read and write the database at once, you can hit: dirty read (reading uncommitted data), non-repeatable read (reading the same row and getting different results), phantom read (a later query returns extra "ghost" rows), and lost update (two requests overwrite each other). The isolation level decides which of these are allowed or prevented.

PostgreSQL defaults to READ COMMITTED - fine for about 90% of applications. At this level, each SELECT only sees committed data, never data another transaction is updating but hasn't committed:

Table accounts: id=1, balance=1000

Transaction A                     Transaction B
BEGIN;
UPDATE accounts
  SET balance = 500
  WHERE id = 1;
-- not yet COMMIT
                                  SELECT balance FROM accounts WHERE id = 1;
                                  --> 1000 (cannot see uncommitted data)
COMMIT;
                                  SELECT balance FROM accounts WHERE id = 1;
                                  --> 500

Which level to use:

Why set isolation on the ROLE? PostgreSQL still defaults to READ COMMITTED, but apps/migrations/scripts can each override it. Pinning it on the role guarantees every connection from that user behaves consistently, prevents accidental overrides, and makes behavior easier to debug and predict:

ALTER ROLE app_user SET default_transaction_isolation TO 'read committed';

References

• PostgreSQL documentation
• pg_hba.conf