In this tutorial, you will install PostgreSQL on Ubuntu, create a dedicated application database and role instead of using the superuser, install pgAdmin as a web-based administration UI, and connect the two while keeping PostgreSQL's network exposure at its safe, localhost-only default wherever possible.
Prerequisites
An Ubuntu 22.04 or 24.04 server with a non-root sudo user
ufw or another firewall already enabled and allowing SSH
Docker Engine installed, if using the Docker route for pgAdmin
Basic familiarity with the command line and a text editor
Let an AI agent do this for you
Copy a ready-made prompt for an AI coding assistant with terminal access to your server (Claude Code, Cursor, or similar) — it can carry out the steps below for you. Review what it plans to run before it executes anything.
PostgreSQL is a production-grade relational database, and pgAdmin is the most widely used web-based GUI for administering it. This tutorial installs PostgreSQL from Ubuntu's own repositories, walks through the least-privilege way to set up a database and role for an application, then adds pgAdmin so you can browse and query that database from a browser instead of the psql shell exclusively.
Every step defaults to the smallest network exposure that still gets the job done. PostgreSQL ships listening on localhost only, and this tutorial keeps it that way unless you have a genuine reason to open it up — in which case it shows you how to do that narrowly, not by opening the database to the whole internet.
Prerequisites
An Ubuntu 22.04 or 24.04 server with a non-root user that has sudo privileges
ufw (or another firewall) enabled and configured to allow SSH, so you don't lock yourself out
Docker Engine installed, if you plan to use the Docker route for pgAdmin (see Docker's official install docs for Ubuntu — the setup steps change between releases, so use their current instructions rather than a copy-pasted one)
Basic comfort with the command line and a text editor (this tutorial uses nano)
Step 1 — Install PostgreSQL
Update your package index and install PostgreSQL along with the postgresql-contrib package, which adds a handful of useful extensions and utilities that aren't in the base package:
Ubuntu 22.04 installs PostgreSQL 14 by default; Ubuntu 24.04 installs PostgreSQL 16. Both work identically for the steps below — only the config file paths differ, and you'll confirm the exact path in Step 5 rather than guessing it.
Step 2 — Verify the service is running
The apt package starts and enables the service automatically. Confirm it:
bash
systemctl status postgresql
Tricknowtech VPS Hosting
Dedicated KVM resources and full root access — deployed in under 60 seconds, no ticket required.
You should see active (running). Press q to exit the status pager if it doesn't return you to the prompt automatically.
Step 3 — Connect with the default peer authentication
On a fresh install, PostgreSQL creates a superuser role named postgres and trusts the Linux system user postgres to log in as it, without a password, when connecting locally. This is called peer authentication: PostgreSQL checks the OS username of whoever is connecting over the local Unix socket and maps it to a matching Postgres role. It only works for local connections made as that system user — it is not a backdoor reachable from anywhere else.
Use it by switching to the postgres system user with sudo, then launching psql:
bash
sudo -u postgres psql
Your prompt should change to postgres=#. You're now in an interactive PostgreSQL session as the superuser.
Step 4 — Create an application database and a dedicated role
Never point an application at the postgres superuser. Create a database and a separate, unprivileged role scoped to that one database — if that role's credentials ever leak, the blast radius is one database, not the whole cluster. Still inside the psql prompt from Step 3, run:
text
CREATE DATABASE appdb;
CREATE USER appuser WITH PASSWORD 'a_strong_password_here';
GRANT ALL PRIVILEGES ON DATABASE appdb TO appuser;
\q
“Replace a_strong_password_here with a long, randomly generated password (openssl rand -base64 24 works well) and treat it as a secret from this point on: never commit it to source control, and if you later store it in a config file (an application .env file, a .pgpass file, and so on), lock that file down with chmod 600 so only its owner can read it.”
By default, PostgreSQL only listens for TCP connections on localhost. That setting lives in postgresql.conf, whose exact path depends on your version — find it rather than guessing:
bash
sudo -u postgres psql -c "SHOW config_file;"
It will print something like /etc/postgresql/16/main/postgresql.conf (or 14/main on 22.04). Inside, you'll find:
ini
listen_addresses = 'localhost'
Leave this alone unless something outside this server genuinely needs to reach PostgreSQL directly. If you follow the pgAdmin-via-Docker route in Step 6 using --network=host, pgAdmin runs on the same host and can reach Postgres over localhost without any change here at all — that's the setup this tutorial recommends specifically because it avoids opening the database to the network.
If you do need remote access — for example an application server on a different machine — change the setting narrowly, never broadly. Rather than hardcoding a version number, open the exact file you just found in Step 5 by substituting its own output back into the command:
This opens the correct postgresql.conf for whatever version apt installed — 14 on 22.04, 16 on 24.04 — without you needing to type the version number yourself. Find the listen_addresses line and change it:
ini
listen_addresses = '*'
Then edit pg_hba.conf in the same directory and add a rule that permits only the specific host(s) that need access — never 0.0.0.0/0, which would let any host on the internet attempt a password-authenticated connection:
text
# TYPE DATABASE USER ADDRESS METHOD
host appdb appuser 203.0.113.10/32 scram-sha-256
Pair that with a firewall rule restricting port 5432 to that same trusted source, then restart PostgreSQL:
bash
sudo ufw allow from 203.0.113.10 to any port 5432 proto tcp
sudo systemctl restart postgresql
Step 6 — Install pgAdmin
pgAdmin is a web application, not a system package with a long-term-stable apt entry — the pgAdmin project's own apt repository is the correct native install path if you want one, but its setup commands (repo URL, signing key) change between releases, so get those from pgAdmin's own documentation at pgadmin.org rather than a copy-pasted command here.
The simpler, version-drift-free option — and the one this tutorial uses — is pgAdmin's official Docker image. On the same server as PostgreSQL, run it with --network=host. This puts the container directly on the host's network stack (it shares the host's network namespace, so there is no port-mapping layer between the two — it behaves, network-wise, as if pgAdmin were installed natively). The upside is that pgAdmin can reach PostgreSQL over localhost:5432, meaning you don't have to open listen_addresses or the firewall at all. The tradeoff is that the container no longer has its own isolated network namespace, so only use this on a host you already trust with that container's other processes.
PGADMIN_LISTEN_PORT is required here because --network=host bypasses Docker's normal -p port-publishing mechanism entirely — the container binds straight to the host's port, so you tell pgAdmin itself which port to bind rather than remapping one afterward.
“PGADMIN_DEFAULT_EMAIL and PGADMIN_DEFAULT_PASSWORD are pgAdmin's own login credentials, separate from the appuser password created in Step 4. Use a real address you control and a strong, unique password, and if you move this docker run command into a docker-compose.yml with an accompanying .env file, chmod 600 that .env file.”
If you'd rather keep pgAdmin on Docker's default bridge network instead — for example because you're running several containers with their own networking already — use the mapped-port form instead: docker run -p 8080:80 -e "[email protected]" -e "PGADMIN_DEFAULT_PASSWORD=a_strong_password" -d dpage/pgadmin4. In that case, inside pgAdmin's UI you must connect to PostgreSQL using the host machine's real IP address, not localhost — localhost inside a bridged container refers to the container itself, not the host it's running on. That path also requires opening PostgreSQL to at least the Docker bridge, which is more exposure than --network=host needs, so prefer --network=host on a single-server setup.
Finally, restrict who can reach pgAdmin's web UI at all. Rather than opening port 8080 to the public internet, allow it only from your own IP, or better, don't open it in the firewall and reach it over an SSH tunnel instead:
bash
# Option A: allow only your own IP through the firewall
sudo ufw allow from 203.0.113.50 to any port 8080 proto tcp
# Option B (preferred): don't open 8080 at all — tunnel over SSH
ssh -L 8080:localhost:8080 your_user@your_server_ip
With the SSH tunnel running, browsing to http://localhost:8080 on your own machine reaches pgAdmin on the server without exposing the port publicly at all.
Step 7 — Verify pgAdmin can reach PostgreSQL
Open http://<server-ip>:8080 (or http://localhost:8080 if you're tunneling) and log in with the PGADMIN_DEFAULT_EMAIL / PGADMIN_DEFAULT_PASSWORD you set in Step 6.
In the pgAdmin UI, right-click Servers → Register → Server and fill in:
General tab — Name: any label, e.g. appdb-local
Connection tab — Host name/address: localhost (if you used --network=host) or the server's real IP (if you used bridge networking and opened remote access in Step 5)
Connection tab — Port: 5432
Connection tab — Maintenance database: appdb
Connection tab — Username: appuser, Password: the password you set in Step 4
Save, then expand Servers → appdb-local → Databases → appdb in the left-hand tree. If you can see it and browse into Schemas → public → Tables (empty for now, since nothing has created tables yet), the connection is verified end to end.
Troubleshooting: "connection refused" from pgAdmin
This is the failure you'll hit most often, and it almost always has one of two causes. First, check which networking mode pgAdmin is running in: if you used the bridge/-p form but then typed localhost as the host in pgAdmin's Add Server dialog, that resolves to the pgAdmin container itself, which has no Postgres running in it — use the host's actual IP address instead, or switch to --network=host and use localhost. Second, if you're trying to connect from a genuinely separate machine, confirm pg_hba.conf (Step 5) has a host line matching that machine's IP and the database/user it's connecting as — a missing or mismatched pg_hba.conf entry produces the same connection-refused-style error as a closed port, even when listen_addresses is already set correctly. Check both before assuming the firewall is at fault.
