published
priority 5
25 min. 45 XP.
PostgreSQL. listen_addresses and pg_hba.conf
Postgres Access was the #2 most frequent failure at regional (5144 occurrences). The two config knobs that drive 95% of failures are `listen_addresses` (can clients reach Postgres at all?) and `pg_hba.conf` (is this specific client allowed in?). This lesson separates them.
DCWF roles:
IT-411 Technical Support Specialist
IT-421 Database Administrator
IT-451 System Administrator
CS-462 Control Systems Security Specialist
Services:
Postgres Access
Objectives
- Locate postgresql.conf and pg_hba.conf on Debian/Ubuntu Postgres installs
- Understand the difference between `listen_addresses` (network) and `pg_hba.conf` (auth)
- Configure Postgres to accept external TCP connections
- Interpret `pg_hba.conf` auth methods: trust / md5 / scram-sha-256 / peer / ident
- Use `\du`, `\l`, and `\dt` in psql to audit roles, databases, and tables
- Preserve the scoring user `torch_bearer` / `torch_of_the_light!` without rotating
Quick reference
| Command | Purpose |
|---|---|
| systemctl status postgresql | Postgres service status |
| ss -tlnp | grep :5432 | Is Postgres listening on 5432? |
| sudo -u postgres psql | Superuser psql shell |
| \du | List all roles (in psql) |
| \l | List all databases (in psql) |
| \c <db> | Connect to a database (in psql) |
| \dt | List tables in current database |
| ALTER USER torch_bearer WITH PASSWORD 'torch_of_the_light!'; | Reset the scoring user password |
| grep listen_addresses /etc/postgresql/*/main/postgresql.conf | Where does Postgres listen? |
| cat /etc/postgresql/*/main/pg_hba.conf | grep -v '^#' | Show active pg_hba rules |
| pg_ctlcluster 16 main reload | Reload without restart (Debian) |
Common pitfalls
- Changing `torch_bearer`'s password during hardening. breaks scoring for the rest of the competition
- Setting `listen_addresses = 'localhost'` when the scoring engine comes over the network
- `pg_hba.conf` rules are evaluated **top to bottom, first match wins**. order matters
- Adding a rule but forgetting to `reload` Postgres. changes only apply after reload
- Using `md5` in `pg_hba.conf` when the user's password was set under `scram-sha-256`. silent mismatch
- Leaving a wide-open `trust` rule that red team discovers. grants passwordless access
How it works (walkthrough)
# /etc/postgresql/16/main/postgresql.conf (key lines) listen_addresses = '*' # * = every interface, '' = none, 'localhost' = only lo port = 5432 max_connections = 100 # /etc/postgresql/16/main/pg_hba.conf (key lines) # TYPE DATABASE USER ADDRESS METHOD local all postgres peer # unix socket, matches OS user host all all 127.0.0.1/32 md5 # localhost TCP host all torch_bearer 0.0.0.0/0 scram-sha-256 # scoring engine from anywhere host db torch_bearer 0.0.0.0/0 md5 # backward-compat rule if needed # Auth methods, shortest strictest: # trust no password required (dangerous. red team uses this) # peer match OS user to DB role (local-only) # md5 MD5-hashed password (legacy; still works) # scram-sha-256 modern password auth (Postgres 10+, preferred)
Skill drills
-
1. File controlling which networks Postgres accepts connections from?pg_hba.conf
-
2. File setting which interface Postgres listens on?postgresql.conf
-
3. Default Postgres port?5432
-
4. In psql, what command lists all databases?\l
-
5. Auth method that matches OS user to DB role, no password?peer
-
6. Modern password auth method (Postgres 10+)?scram-sha-256
-
7. NCAE scoring-engine user + password + database?torch_bearer / torch_of_the_light! / db