This guide shows how to install PostgreSQL on your own server, create a separate user and database for your application, and connect to it from your workstation without exposing the database port to the internet. The current version as of October 2026 is PostgreSQL 18 (18.6).

Tested on Ubuntu 24.04 and 26.04. The server address in the examples is 203.0.113.10; the address of your computer or a second server is 198.51.100.7.

Step 1. Install PostgreSQL

Ubuntu 26.04

The Ubuntu 26.04 repository already ships version 18:

sudo apt update
sudo apt install -y postgresql

Ubuntu 24.04

Ubuntu 24.04 installs PostgreSQL 16 from its own repository. It is supported until November 2028, which is enough for an existing project. For a new installation, it is better to take version 18 from the official PostgreSQL repository (PGDG), which a script from the postgresql-common package adds for you:

sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh   # press Enter to confirm
sudo apt install -y postgresql-18

Support dates for each version are on the versioning policy page.

Check:

pg_lsclusters
sudo -u postgres psql -c "select version();"

pg_lsclusters should show the cluster 18 main 5432 online. On Ubuntu and Debian, the configuration lives in /etc/postgresql/18/main/ and the data in /var/lib/postgresql/18/main/.

Step 2. Create a user and database for the application

Do not connect your application as the postgres superuser. Create a separate role and a database owned by it:

sudo -u postgres createuser --pwprompt app     # prompts for a password for the app role
sudo -u postgres createdb -O app appdb

The database owner gets all privileges in it, including creating tables in the public schema. Since PostgreSQL 15, regular users no longer get that privilege by default, so -O app matters here.

Check by connecting as the application over the network on localhost:

psql -h 127.0.0.1 -U app appdb -c "create table check_t(i int); drop table check_t;"

The password is stored as a SCRAM-SHA-256 hash, which is the default (show password_encryption;).

Step 3. Connect from your computer over an SSH tunnel

By default, PostgreSQL listens only on localhost, and that is the right default. The safest way to work with the database from DBeaver, DataGrip or psql on your computer is to forward the port over SSH, without opening anything on the server:

ssh -N -L 5433:127.0.0.1:5432 admin@203.0.113.10

While the command is running, the server's database is available on your machine at 127.0.0.1:5433:

psql -h 127.0.0.1 -p 5433 -U app appdb

In GUI clients, you set up the same thing on the SSH tab: the server host, user and key, with localhost:5432 as the database address. If you have not set up key login yet, start with the SSH key article.

Step 4. Allow access from one address (if needed)

If another server needs to reach the database and a tunnel is inconvenient, open the port only for that address and only over TLS.

Make PostgreSQL listen on external interfaces with a separate file in conf.d (the directory is included in postgresql.conf by the include_dir line):

echo "listen_addresses = '*'" | sudo tee /etc/postgresql/18/main/conf.d/10-listen.conf

Allow the app role to log in to the appdb database from one address, only over an encrypted connection and with a password:

echo "hostssl appdb app 198.51.100.7/32 scram-sha-256" | sudo tee -a /etc/postgresql/18/main/pg_hba.conf
sudo systemctl restart postgresql

TLS is enabled out of the box in the Ubuntu packages (ssl = on with a self-signed certificate), so hostssl works without extra setup. listen_addresses takes effect only after a restart; changes to pg_hba.conf take effect after sudo systemctl reload postgresql.

Most important is the firewall. Open the port only for this address:

sudo ufw allow from 198.51.100.7 to any port 5432 proto tcp

Check from the 198.51.100.7 server:

psql "host=203.0.113.10 dbname=appdb user=app sslmode=require" \
  -c "select ssl, version from pg_stat_ssl where pid = pg_backend_pid();"

A result of t | TLSv1.3 means the connection is encrypted. An attempt without encryption (sslmode=disable) should fail with no pg_hba.conf entry … no encryption.

sslmode=require encrypts the connection but does not verify that you connected to your own server. For full verification, issue a certificate for the server's hostname and connect with sslmode=verify-full.

If the connection fails

What you see Cause and fix
connection to server … failed: Connection refused The server is not listening on that address: check listen_addresses and that you restarted; sudo ss -ltnp should show a 0.0.0.0:5432 line
The connection hangs and times out The firewall blocks the port: run sudo ufw status and check for a rule for your address
no pg_hba.conf entry for host … No matching line in pg_hba.conf: check the address, database and role names, hostssl and the client's sslmode; run reload after editing
password authentication failed for user "app" Wrong password. To change it: sudo -u postgres psql -c "\password app"
permission denied for schema public The role does not own the database. Make it the owner: ALTER DATABASE appdb OWNER TO app; and ALTER SCHEMA public OWNER TO app; in that database

Server log: sudo tail -n 50 /var/log/postgresql/postgresql-18-main.log. The reason for a rejected connection is almost always there.

What's next

  • Before going to production, set up backups: a regular pg_dump with copies stored off the server.
  • If the database runs next to the application on a small server, estimate memory: the initial server setup article has a section on the swap file.
  • How much memory the database and the application need together: sizing by workload; the database in a container alongside the application: Docker Compose in production.
  • Tuning the database's own memory (shared_buffers, work_mem, PgBouncer connection pooling): PostgreSQL memory tuning.
  • Scheduled backups stored off the server: PostgreSQL backups with pg_dump, a systemd timer and restic.
  • Connecting a Django or FastAPI application to the database: deploying a Python application to a VPS.