PostgreSQL¶

PostgreSQL's two configuration files do different jobs: postgresql.conf says where it listens and what it logs; pg_hba.conf says who may connect from where and how they prove it. Both are in /etc/postgresql/<version>/main/. ls /etc/postgresql/ shows the version. After changes: sudo systemctl restart postgresql.

postgresql.conf¶

Setting	Value	Why
`listen_addresses`	`'localhost'`	Only local connections. `'*'` or `'0.0.0.0'` exposes the database to the network; only a README-named remote client changes that, and then to its address.
`ssl`	`on`	Connections are encrypted; Mint and Debian ship a snakeoil certificate so this works out of the box
`logging_collector`	`on`	Logs go to files under `log_directory` instead of vanishing
`log_connections`, `log_disconnections`	`on`	Every session is recorded
`log_line_prefix`	`'%m [%p] %u@%d '`	Timestamps, user, and database in each line
`password_encryption`	`scram-sha-256`	Stored password hashes aren't MD5

V=$(ls /etc/postgresql/ | head -1); F=/etc/postgresql/$V/main/postgresql.conf
sudo sed -i -E "s/^#?\s*listen_addresses\s*=.*/listen_addresses = 'localhost'/; s/^#?\s*ssl\s*=.*/ssl = on/; s/^#?\s*logging_collector\s*=.*/logging_collector = on/; s/^#?\s*log_connections\s*=.*/log_connections = on/; s/^#?\s*log_disconnections\s*=.*/log_disconnections = on/; s/^#?\s*password_encryption\s*=.*/password_encryption = scram-sha-256/" $F

pg_hba.conf¶

Each line is type database user address method. The method is the important column.

Method	Meaning
`peer`	The Linux user must match the database user; fine for local `postgres` maintenance
`scram-sha-256`	Password, properly hashed. Use this for everything else.
`md5`	Password with a weak hash; change to `scram-sha-256`
`trust`	No authentication at all. Anyone who reaches the socket or address is that user. Remove every `trust` line, especially one with `0.0.0.0/0` or `all all`.
`password`	Password in clear text over the wire

sudo grep -vE '^\s*(#|$)' /etc/postgresql/$V/main/pg_hba.conf
sudo sed -i -E 's/\b(trust|md5|password)\s*$/scram-sha-256/' /etc/postgresql/$V/main/pg_hba.conf
sudo systemctl restart postgresql

Keep the local all postgres peer line so sudo -u postgres psql still works for you.

Roles¶

sudo -u postgres psql -c "\du"

postgres is the superuser. Any other role with Superuser in its attributes is a second administrator; unless the README names it, ALTER ROLE name NOSUPERUSER;. Roles with no password (SELECT rolname FROM pg_authid WHERE rolpassword IS NULL AND rolcanlogin;) get one: ALTER ROLE name PASSWORD '…';. Roles the README doesn't describe: DROP ROLE name;.

Verify¶

sudo ss -tlnp | grep 5432                       # 127.0.0.1:5432
sudo grep -E '^(listen_addresses|ssl|logging_collector)' /etc/postgresql/*/main/postgresql.conf
sudo grep -E 'trust|md5' /etc/postgresql/*/main/pg_hba.conf   # nothing
sudo -u postgres psql -c "SHOW ssl;"

Example¶

listen_addresses = '*' and pg_hba.conf has host all all 0.0.0.0/0 trust: anyone on the network is any database user with no password. ssl = off, logging_collector = off. Set localhost, on, on; replace the trust line with scram-sha-256 restricted to 127.0.0.1/32; restart. \du shows backup as a superuser the README doesn't mention; ALTER ROLE backup NOSUPERUSER;.

Try it¶

Set a trust line in pg_hba.conf, connect with psql -U postgres -h localhost with no password, then fix it.
Read \du and explain each attribute.

Build it¶

A pg-check.sh: ss for 5432, grep both config files for the settings on this page, \du output.

Next¶

vsftpd