# database hardening basics: Postgres edition

## TL;DR

Postgres ships open for convenience, not for production. Switch pg_hba off trust and onto scram-sha-256, bind only to the interfaces you need, give the app a least-privilege role instead of a superuser, turn on TLS, and revoke the default public permissions. Fifteen minutes of config now beats explaining an open database later.

```text
database hardening basics: Postgres edition
```

## Use this when

- You are putting a new Postgres instance into production
- You inherited a database and suspect default settings
- A security review flagged the database layer
- You need a Postgres hardening checklist with verification steps

## Not for this skill when

- You run MySQL, MongoDB, or another database (concepts overlap, commands dont)
- The task is query performance or indexing
- You only need backups (thats necessary but not hardening)

## Steps

### 1. Check what the server is listening on

Find the config file location, then confirm listen_addresses is scoped, not a wildcard facing the world.

```bash
psql -U postgres -c "SHOW config_file;" && psql -U postgres -c "SHOW listen_addresses;"
```

Expected: the config path, and listen_addresses showing specific interfaces or the private address, not everything. If the database doesnt need remote connections at all, the socket alone is the safest binding.

### 2. Kill trust auth in pg_hba.conf

Trust means anyone who can reach the port is whoever they claim to be. Replace every trust line with scram-sha-256, reload, and verify.

```bash
sudo grep -v "^#" /etc/postgresql/[version]/main/pg_hba.conf | grep -v "^$"
```

Expected: a short list of active rules with no `trust` entries. Edit the file to use `scram-sha-256` for host connections, then `SELECT pg_reload_conf();` from psql. Test that the app still connects before you call it done.

### 3. Stop giving the app a superuser

Create a dedicated role that owns nothing it doesnt need: connect and usage on the database and schema, CRUD on its tables, nothing else. No superuser, no createdb, no replication for app roles.

```bash
psql -U postgres -c "CREATE ROLE app_svc WITH LOGIN PASSWORD '[strong-password]'; GRANT CONNECT ON DATABASE myapp TO app_svc;"
```

Expected: the role exists and the app connects with it. If the app breaks, grant the missing privilege narrowly (one table, one schema) instead of reaching for superuser.

### 4. Revoke the defaults nobody remembers

Fresh Postgres lets every role create objects in the public schema and read some system views. Tighten that: revoke create on schema public from public, and audit what the public pseudo-role can still touch.

```bash
psql -U postgres -d myapp -c "REVOKE CREATE ON SCHEMA public FROM PUBLIC;"
```

Expected: `REVOKE` confirmation. Your migrations should run as a separate owner role, not the app role, so the app never needs DDL in production.

### 5. Turn on TLS and logging

Set ssl to on with a real certificate, and enable connection plus statement logging so you can see who connected and what ran. Logs are your only witness after an incident.

```bash
psql -U postgres -c "ALTER SYSTEM SET ssl = 'on';" && psql -U postgres -c "ALTER SYSTEM SET log_connections = 'on';" && psql -U postgres -c "SELECT pg_reload_conf();"
```

Expected: settings applied after reload; verify with `SHOW ssl;` returning on. Point the app at the TLS endpoint and confirm it negotiates encryption before you consider this closed.

### Variant: postgres security best practices

The wider list: keep Postgres patched (minor releases carry security fixes), separate backup credentials from app credentials, encrypt backups, restrict superuser to local socket peer auth, and review pg_hba after every major upgrade since installers can reset it.

### Variant: secure postgresql on a cloud managed service

RDS, Cloud SQL, and friends handle the OS and TLS certs; your job is the same pg_hba-equivalent (security groups and auth rules), least-privilege roles, no public IP on the instance, and storage encryption turned on. Dont assume "managed" means "hardened".

### Variant: pg_hba.conf explained

Each line is type, database, user, address, method. Order matters: first match wins, so specific rules go before general ones. When auth mysteriously fails after your edit, you almost certainly have an earlier line matching first.

## Why this happens

Postgres defaults optimize for a developer getting started in five minutes: trust auth on local sockets, a superuser named postgres, public schema open to everyone. Those defaults are fine on a laptop and dangerous on a network. Hardening is just walking back each convenience default to the narrowest setting your app still works with.

## Edge cases and pitfalls

- Reload vs restart: pg_hba changes need only a reload, but listen_addresses and ssl need a restart; check which you changed.
- Peer auth surprises: local socket connections may use peer auth, mapping OS users to DB roles; service accounts need entries too.
- Password in shell history: passing the password on the psql command line writes it to history; use a .pgpass file with 600 perms or a prompt instead.
- Upgrades resetting pg_hba: major-version upgrades can install a fresh default file; diff yours against the new one after upgrading.
- App role creep: every "just grant superuser to unblock the deploy" becomes permanent; make the migration role separate so nobody needs it.
- Logging volume: statement logging on a busy database is heavy; start with connections and DDL, add more only if you can store it.

## Provenance

Resolved from the public thread: https://vectle.com/posts/pst_s5WYfS8fp0yzOuqnGG-5Gw