Install PostgreSQL 18 on Ubuntu — PGDG Repository, Security, First Steps

Ubuntu's default apt repository ships PostgreSQL 14 on Noble. If you apt install postgresql and walk away, you get a version that's two major releases behind, missing features like improved BRIN indexes, incremental backups, and the performance work that landed in 15 through 18. The fix is a single script from the PostgreSQL Global Development Group — but the script is only the start. Most guides stop at "service is running." This one covers what you actually need to make that database safe to leave running on a VPS: authentication modes, remote access with real constraints, a dedicated application user, and the configuration knobs that matter.

What you are actually installing

PostgreSQL's release cadence is one major version per year. Ubuntu LTS ships whatever was stable at freeze time and backports security fixes but not features. The PGDG apt repository — maintained by the PostgreSQL project itself — carries every major version from 11 through 18 for all active Ubuntu releases, updated the day a new version ships.

When you add PGDG, apt gets a new source alongside Ubuntu's default packages. PGDG's PostgreSQL packages take precedence by pin priority, and the package manager handles upgrades normally from that point on. You are not bypassing apt — you are extending it.

Prerequisites

• Ubuntu 24.04 VPS with SSH access
• A user account with sudo privileges

Verify your release codename before anything else — it determines which repository line the script adds:

lsb_release -cs

Expected output on Noble:

noble

Step 1: Update the Server

sudo apt update && sudo apt upgrade -y

Step 2: Add the PGDG Repository

Install the packages needed to configure the repository:

sudo apt install -y postgresql-common ca-certificates

Run the official PGDG setup script. It detects your Ubuntu release, adds the correct signing key, and writes the apt source — no manual keyring handling:

sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh -y

Update the package index to include the new repository:

sudo apt update

Step 3: Install PostgreSQL 18

sudo apt install -y postgresql-18

This installs the server, initialises the default cluster at /var/lib/postgresql/18/main, and starts the service. The installer also creates a system user named postgres that owns the cluster.

Step 4: Verify the Installation

Check that the cluster is online:

pg_lsclusters

Expected output:

Ver  Cluster  Port  Status  Owner     Data directory
18   main     5432  online  postgres  /var/lib/postgresql/18/main

Confirm the server version:

sudo -u postgres psql -c "SELECT version();"

Check the service status:

sudo systemctl status postgresql@18-main

Step 5: Set a Password for the postgres Superuser

The postgres OS user can connect to the database cluster without a password by default — that is fine for a local socket, but you need a database password before exposing anything to a network.

sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'your-strong-password';"

Step 6: Understand Authentication Before Changing It

PostgreSQL's authentication is controlled by two files:

Both live at /etc/postgresql/18/main/. pg_hba.conf is the one most guides skip past, and it is the most important for security.

The default pg_hba.conf on Ubuntu allows:

• Local socket connections from the postgres OS user via peer auth — this is how sudo -u postgres psql works without a password.
• Local socket connections for all other users via scram-sha-256.
• No remote connections at all.

That default is deliberately conservative. Do not open remote access unless you actually need it.

Step 7: Create a Database and User

You should never connect your application as postgres. Create a dedicated user and database with only the permissions the application needs.

sudo -u postgres psql

CREATE USER myuser WITH ENCRYPTED PASSWORD 'strong-password';
CREATE DATABASE mydb OWNER myuser;
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
\q

Test that the new user can connect:

psql -h 127.0.0.1 -U myuser -d mydb

Step 8: Enable Remote Access (Only If Needed)

By default, PostgreSQL only listens on the loopback interface. If your application server is on the same machine, you do not need this — stay on localhost. Only proceed if you have a separate app server or need to connect from your local machine.

1. Allow listening on all interfaces

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

Find the listen_addresses line and update it:

listen_addresses = '*'

2. Add a remote authentication rule

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

To allow a specific IP only:

host  all  all  203.0.113.10/32  scram-sha-256

To allow a subnet (for example, a private network between your servers):

host  all  all  10.0.0.0/24  scram-sha-256

3. Restart PostgreSQL

sudo systemctl restart postgresql@18-main

4. Open the firewall for that IP only

sudo ufw allow from 203.0.113.10 to any port 5432

Getting it right in production

This is the part the copy-paste tutorials skip.

Use scram-sha-256 everywhere, never md5

Ubuntu 24.04's PGDG packages default to scram-sha-256 for new clusters — that is correct. If you see md5 in your pg_hba.conf, replace it. MD5 password hashing in PostgreSQL predates modern cryptography standards: it is unsalted, fast to brute-force, and has been deprecated since PostgreSQL 14.

Check your current authentication method:

grep "^host\|^local" /etc/postgresql/18/main/pg_hba.conf

Every line should end with scram-sha-256, not md5.

Grant only what the application needs

GRANT ALL PRIVILEGES ON DATABASE gives the user the right to connect and create objects within the database. It does not automatically grant read/write access to existing tables — that is a separate step.

For a typical web application with full read/write on all tables:

\c mydb
GRANT USAGE ON SCHEMA public TO myuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myuser;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO myuser;
 
-- Make these grants apply to future tables too.
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO myuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO myuser;

Set a connection limit

PostgreSQL uses one process per connection. Without a connection limit, a misbehaving application can exhaust the server's max_connections and lock out everything else, including administrative access.

Set a per-user connection limit when you create the user:

ALTER USER myuser CONNECTION LIMIT 50;

And review postgresql.conf's max_connections (default: 100). Each connection consumes ~5–10 MB of RAM at idle — size accordingly, or use a connection pooler like PgBouncer in front of PostgreSQL for high-concurrency workloads.

Enable query logging for slow queries

One line in postgresql.conf gives you visibility into what is making your application slow:

log_min_duration_statement = 1000   # log queries taking over 1 second

Reload after changing:

sudo systemctl reload postgresql@18-main

Slow query logs land at /var/log/postgresql/postgresql-18-main.log.

Troubleshooting

psql: error: connection to server ... failed: FATAL: password authentication failed

The user's database password is wrong, or pg_hba.conf is routing the connection to a different auth method than you expect. Check which rule matches:

sudo grep "^host\|^local" /etc/postgresql/18/main/pg_hba.conf

Rules are evaluated top-to-bottom; the first match wins.

psql: error: connection to server ... failed: Connection refused

Either PostgreSQL is not running, or it is not listening on the address you are connecting to:

sudo systemctl status postgresql@18-main
sudo ss -tlnp | grep 5432

If the second command shows 127.0.0.1:5432 and you are connecting from a remote host, you have not set listen_addresses = '*' and restarted.

FATAL: remaining connection slots are reserved for non-replication superuser connections

You have hit max_connections. Either raise the limit in postgresql.conf (restart required), kill idle connections, or add PgBouncer. Short-term:

SELECT pid, usename, application_name, state, query_start
FROM pg_stat_activity
WHERE state = 'idle'
ORDER BY query_start;
 
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle';

pg_lsclusters shows status down

The cluster failed to start. Check the log:

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

The most common cause on a fresh install is a port conflict — something else is on 5432.

Useful Commands

Conclusion

The PGDG script does the hard work — one command and you have a current, maintained PostgreSQL 18 instead of whatever Ubuntu decided to freeze years ago. What turns a running database into one you would actually trust with production data is everything after that: scram-sha-256 authentication everywhere, a dedicated application user with scoped grants, connection limits that cannot bring the server down, and remote access locked to the specific IP that needs it rather than the whole internet.

PostgreSQL is conservative by default and loud when you get it wrong — lean on that. The logs are clear, pg_stat_activity shows you exactly what is connected, and pg_hba.conf gives you precise control over who can reach what. Get those three things right and the database becomes the boring, reliable part of your stack — which is the only acceptable outcome.