Install PostgreSQL on an Ubuntu VPS With Safe Remote Access

calendar Nov 2, 2026 / · 13 min read · postgresql ubuntu vps database security ufw  ·
Share on: linkedin copy

A fresh PostgreSQL install on Ubuntu accepts connections from the local system only. The listen_addresses setting defaults to localhost, which allows TCP connections over the loopback interface and nothing else. On a VPS with a public IP address, that is the right state to start from.

Opening the database to a remote application server takes four changes, in this order: a non-superuser role with a SCRAM password and its own database, a listener on the right interface, a pg_hba.conf rule that requires TLS from one known address, and a firewall rule for port 5432. The postgres superuser stays local.

Choose a Package Source: Ubuntu Archive or PGDG

Ubuntu snapshots one PostgreSQL version per release, and that version stays supported for the lifetime of the Ubuntu release. Running apt install postgresql from the Ubuntu archive gives you that version. You stay on it until you upgrade Ubuntu itself.

The PostgreSQL project runs its own apt repository at apt.postgresql.org (PGDG). It provides automatic updates for all supported PostgreSQL versions for as long as each version is supported. Use it when the application needs a newer major version than your Ubuntu release ships, or when you want to pick the major version yourself instead of inheriting it from the OS.

PostgreSQL 18 is the current major release, and 17, 16, 15 and 14 are still supported. PostgreSQL 14 stops receiving fixes on November 12, 2026, under the project's versioning policy, so a new install should not start on it. As of October 15, 2026, PostgreSQL 19 has reached Release Candidate 1, so it is not a final release yet and does not belong on a production VPS.

PGDG builds for these Ubuntu releases:

CodenameVersionType
stonking26.10non-LTS
resolute26.04LTS
noble24.04LTS
jammy22.04LTS

amd64 builds cover all four. arm64 and ppc64el builds exist for the LTS releases only, so an arm64 VPS running 26.10 gets no PGDG packages. Check the VPS architecture before you choose this route.

Install PostgreSQL

From the Ubuntu archive:

1sudo apt install postgresql

From PGDG, the postgresql-common package includes a script that sets up the repository. After that, install the major version by name:

1sudo apt install -y postgresql-common
2sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
3
4sudo apt update
5sudo apt install postgresql-18

If you add the repository by hand instead of using the script, check the signing key before apt trusts it. The fingerprint is:

1pub   rsa4096 2011-10-13 [SC]
2      B97B0AFCAA1A47F044F244A07FCC7D46ACCC4CF8

PGDG suites take the Ubuntu codename plus a -pgdg suffix. A 24.04 server uses noble-pgdg, for example.

Whichever source you use, the configuration files are in /etc/postgresql/<version>/main. With postgresql-18 installed, that is /etc/postgresql/18/main. You edit both postgresql.conf and pg_hba.conf in that directory below.

Set a SCRAM Password Before Opening Anything

On Ubuntu, local connections (the Unix socket) use peer authentication by default, and host connections (TCP) use scram-sha-256. Until Ubuntu 21.10, the host default was md5. Peer authentication on the socket lets the postgres system user open psql with no password. Open psql that way:

1sudo -u postgres psql template1

At the psql prompt, set a password for the postgres role:

1ALTER USER postgres with encrypted password 'your_password';

This role is only for local administration. None of the remote rules below name it.

Admin Access From Your Desktop: Use an SSH Tunnel

For your own admin work, such as running psql or a GUI client from a workstation, the database port does not need to be open at all. A database should almost never be reachable from the internet, so keep 5432 closed, as the default-deny ufw setup on this site does, and forward the port through the SSH access you already have, with ssh -L:

1ssh -L 5432:localhost:5432 user@your-vps-ip

While the tunnel runs, a client on the workstation connects to localhost:5432, and the traffic travels inside the encrypted SSH session. On the VPS, the connection arrives as a TCP connection from the loopback interface, which the default listen_addresses = 'localhost' already accepts and Ubuntu's default host rules authenticate with scram-sha-256. Nothing in postgresql.conf, pg_hba.conf or ufw changes. The SQL Workbench/J setup guide uses the same tunnel to connect a desktop SQL client to a VPS database.

The rest of this post covers the other case: an application server on another machine that has to connect to the database directly.

The hash type is fixed when the password is set, by whatever password_encryption is set to at that moment. The default is scram-sha-256. If someone changed password_encryption to md5 on this server earlier, any password set under that value stays an MD5 hash until it is set again.

SCRAM-SHA-256 is a challenge-response scheme that prevents password sniffing. It is the most secure password method PostgreSQL currently provides, but older client libraries do not support it. If a legacy application cannot log in after the switch, check its client library version first.

Falling back to MD5 is not a long-term fix. Support for MD5-encrypted passwords is deprecated and will be removed in a future PostgreSQL release.

Create an Application Role and Database

The remote client logs in as its own role, to its own database:

1sudo -u postgres createuser -P appuser
2sudo -u postgres createdb -O appuser appdb

-P makes createuser prompt for the new role's password. With no other options, the role can log in but is not a superuser and cannot create databases or roles; all three are the defaults. createdb -O sets the owner of the new database, so appuser owns appdb.

A new database grants the CONNECT privilege to PUBLIC by default, so any role that gets past pg_hba.conf can connect to it. Restrict it to the application role. From sudo -u postgres psql:

1REVOKE CONNECT ON DATABASE appdb FROM PUBLIC;
2GRANT CONNECT ON DATABASE appdb TO appuser;

The GRANT ... ON DATABASE form takes CONNECT, CREATE and TEMPORARY. CONNECT is checked at connection startup, in addition to the pg_hba.conf rules. Replace appuser and appdb with your own names throughout the rest of the post.

Change listen_addresses (Restart Required)

Edit postgresql.conf:

1# /etc/postgresql/*/main/postgresql.conf
2#listen_addresses = 'localhost'
3listen_addresses = '*'

With '*', PostgreSQL listens on every available interface, IPv4 and IPv6. '0.0.0.0' covers all IPv4 interfaces only, and '::' covers all IPv6 interfaces only.

On a VPS, a narrower value is better. listen_addresses takes a comma-separated list of host names and numeric IP addresses. It decides which interfaces accept connection attempts in the first place, which helps against repeated malicious connection requests on interfaces you never meant to expose. If the application server reaches the database over a private network, list loopback and the VPS's private address:

1listen_addresses = 'localhost,192.168.1.5'

This value assumes the VPS and the application server share a private network. Every example below uses 192.168.1.0/24 for that network, 192.168.1.5 for the VPS and 192.168.1.10 for the application server. Replace them with your own addresses. With this value, PostgreSQL never listens on the public interface, whatever pg_hba.conf or the firewall allow.

If the application server can reach the VPS only over the public internet, the database port has to sit on the public interface, so open it to that one server and nothing else: keep '*', use the application server's public IP wherever 192.168.1.10 appears below, and keep the ufw rule and the hostssl line limited to that single address.

listen_addresses can only be set at server start, so a reload does not apply it. The server listens on TCP port 5432 by default. Wait to restart until you have edited pg_hba.conf, so one restart applies both files.

Write pg_hba.conf Rules: hostssl vs host

Add this rule:

1# /etc/postgresql/*/main/pg_hba.conf
2hostssl appdb appuser 192.168.1.10/32 scram-sha-256

The fields are connection type, database, user, client address and method. This line accepts SSL connections over TCP to appdb, as appuser, from 192.168.1.10 only, and authenticates them with SCRAM. A mask length of 32 means a single IPv4 host, and 128 means a single IPv6 host.

host records match connections with or without SSL, and with or without GSSAPI encryption. hostssl matches SSL connections only, so a client that connects in plain text matches no record and is refused. Ubuntu's PostgreSQL package has SSL built in and configured by default, so hostssl works right after install.

Builds that keep the upstream default are different: there, ssl is off. With SSL off, a hostssl record is ignored apart from a logged warning that it cannot match any connection. A file that allows remote access only through hostssl lines then turns away every TCP client.

Four points about this file:

  • The first match wins. PostgreSQL uses the first record whose connection type, client address, database and user name all match. If authentication fails on that record, later records are not tried. If no record matches, access is denied.
  • IPv4 and IPv6 entries are separate. An IPv4-format entry matches only IPv4 connections, and an IPv6-format entry matches only IPv6 connections. A client that arrives over IPv6 never reaches the /32 line above.
  • 0.0.0.0/0 matches every IPv4 address, and ::0/0 matches every IPv6 address. Combine either one with trust, and anyone who can reach the port can log in as any PostgreSQL user with no password.
  • md5 lines rely on deprecated MD5 support. Replace them with scram-sha-256, and set the passwords again so the stored hashes are SCRAM as well.

If several application servers on the subnet need access, a subnet rule works too, and a reject line above it blocks one machine inside it:

1host    all   all     192.168.1.66/32 reject
2hostssl appdb appuser 192.168.1.0/24  scram-sha-256

Because the first match wins, 192.168.1.66 hits the reject line and never reaches the subnet rule. The reject line uses host, so it applies whether that machine connects with SSL or without. In a subnet address, the bits to the right of the mask length should be zero: write 192.168.1.0/24, not 192.168.1.1/24.

Once the server is running, later edits to pg_hba.conf only need a reload. The server re-reads the file on pg_ctl reload, on a call to pg_reload_conf(), or on kill -HUP. From psql as a superuser:

1SELECT pg_reload_conf();

Check the Rules Before Restarting

The pg_hba_file_rules view shows one row per non-empty, non-comment line in pg_hba.conf, and notes whether each rule could be applied. It reads the current file on disk, not the rules the server last loaded, so you can check an edit before you restart or reload. By default, only superusers can read it.

1SELECT rule_number, line_number, type, database, user_name,
2       address, netmask, auth_method, error
3FROM pg_hba_file_rules;

A row with a non-null error is a line the server cannot process. Such a row usually has values in line_number and error only, which tells you which line to fix. When error is empty on every row, restart PostgreSQL to apply both listen_addresses and the new rules:

1sudo systemctl restart postgresql.service

Passing pg_hba.conf is only part of the check. The role also needs the CONNECT privilege on the target database, which appuser has on appdb from the grant above. A role you add later gets no access to appdb until you grant it CONNECT.

Firewall Port 5432 With ufw

ufw (Uncomplicated Firewall) manages a netfilter firewall from the command line. It is the default firewall configuration tool on Ubuntu, and it starts out disabled. Its rules do nothing until you enable it.

Allow PostgreSQL from the application server's address only, using ufw's per-host syntax with port 5432:

1sudo ufw allow proto tcp from 192.168.1.10 to any port 5432
2sudo ufw status verbose

To allow the whole subnet that the pg_hba.conf subnet rule covers, replace 192.168.1.10 with 192.168.1.0/24. Add --dry-run to any ufw command to print the resulting rules without applying them.

If ufw is not enabled yet, open SSH with sudo ufw allow 22 before you turn the firewall on with sudo ufw enable, so the session you are working in keeps a way in. Then run sudo ufw status verbose again and confirm the 5432 rule shows the client address you meant.

Keep the ufw source addresses and the pg_hba.conf addresses the same. When you add or retire an application server later, edit its ufw rule and its pg_hba.conf line in the same change, then run SELECT pg_reload_conf(); so PostgreSQL picks up the new pg_hba.conf.

Replace the Test Certificate and Set sslmode on Clients

Ubuntu's PostgreSQL uses the certificate created by the ssl-cert package. That certificate is fine for testing. For production, replace it with a certificate from a trusted Certificate Authority.

libpq's default sslmode is prefer, which makes no sense from a security point of view. With hostssl, the server refuses plain-text connections, but the client's sslmode decides how far the client checks the server it reaches:

  • verify-ca checks the server certificate chain up to the root certificate stored on the client. Use it when the server certificate comes from a local CA you run or is self-signed, so that root signs nothing but your own servers.
  • verify-full also checks that the server host name matches the name stored in the server certificate. Use it with a certificate from a public CA: there, verify-ca would also accept a server that someone else registered with the same CA.

Both modes need the root certificate on the client. By default it goes in ~/.postgresql/root.crt in the user's home directory. You can point to a different file with the sslrootcert connection parameter or the PGSSLROOTCERT environment variable.

To make the server check the client as well, add the clientcert option to the hostssl record:

1hostssl appdb appuser 192.168.1.10/32 scram-sha-256 clientcert=verify-full

clientcert works in any hostssl record and takes verify-ca or verify-full. Both values require the client to present a valid, trusted SSL certificate. verify-full also requires the cn (Common Name) in the certificate to match the user name or an applicable mapping, so the client certificate here needs appuser as its CN. With this line in place, a stolen password is not enough to log in from that address.

Test From a Remote Machine

Install the PostgreSQL client on the remote machine. From the Ubuntu archive, the package is postgresql-client. From PGDG, postgresql-client-18 provides the client libraries and binaries for PostgreSQL 18. Then connect:

1sudo apt install postgresql-client
2psql --host your-servers-dns-or-ip --username appuser --password --dbname appdb

Replace your-servers-dns-or-ip with the server's DNS name or IP address. On a private network, that is the address in listen_addresses, 192.168.1.5 in these examples.

If the connection fails, check each layer in the order a connection passes through them: the ufw rule, listen_addresses after the restart, the first pg_hba.conf line that matches the client, and the CONNECT privilege. A client that tries plain text against a hostssl rule fails at the pg_hba.conf step.

References

Posts in this series