Roles, privileges and pg_hba.conf

Why this lesson exists

"The app can't connect to the database" is the most common Postgres page of all, and it is almost never the database being down. It is a new subnet that is not in pg_hba.conf, a rotated password, a role without LOGIN, a missing GRANT after a migration created a new table, or the server listening only on localhost. Every one of those has a precise error message - this lesson teaches you to read it and fix the right file. It also builds the roles a real service should have, because "the app connects as postgres" is how one SQL injection becomes the loss of a whole cluster.

What you need to know already: peer auth and pg_hba.conf's location, reload vs restart (lesson 2), psql connection options (lesson 3), CIDR notation (the networking chapter).

The words you need first

• Role attributes - what a role may do in the cluster: LOGIN, SUPERUSER, CREATEDB, CREATEROLE, REPLICATION, BYPASSRLS, CONNECTION LIMIT n, VALID UNTIL.
• Privileges - what a role may do with an object: CONNECT on a database, USAGE / CREATE on a schema, SELECT / INSERT / UPDATE / DELETE / TRUNCATE on a table. Given with GRANT, taken with REVOKE.
• Owner - the role that created an object (or was given it). The owner can do anything with it, including DROP, and only the owner (or a superuser) can ALTER it.
• Membership - a role can be granted to another: GRANT orders_ro TO alice makes alice a member and (by default) she inherits its privileges.
• pg_hba.conf - host-based authentication: an ordered list of rules. The first rule that matches the connection decides how it authenticates, or whether it is rejected.
• SCRAM-SHA-256 - the password method to use: the server stores a salted verifier, the password never crosses the wire. md5 is its weak predecessor - deprecated in 18.

Roles

$ sudo -u postgres psql -c '\du'
                               List of roles
  Role name   |                         Attributes
--------------+------------------------------------------------------------
 orders_app   |
 orders_owner | Cannot login
 orders_ro    |
 postgres     | Superuser, Create role, Create DB, Replication, Bypass RLS

A fresh cluster has one role you see (postgres, superuser) plus built-in pg_* roles that \du hides (\duS shows them). The pattern for an application is three roles:

The app can then never DROP TABLE, TRUNCATE or ALTER anything, and a leaked read-only password cannot change data. Nobody logs in as the owner: it is a role to own things, not a user.

The lab already has them (ordersDb in the setup made them); look:

$ sudo -u postgres psql -c '\du orders*'
        List of roles
  Role name   |  Attributes
--------------+--------------
 orders_app   |
 orders_owner | Cannot login
 orders_ro    |
$ sudo -u postgres psql -d orders -c '\dp customers'
                                       Access privileges
 Schema |   Name    | Type  |         Access privileges          | Column privileges | Policies
--------+-----------+-------+------------------------------------+-------------------+----------
 public | customers | table | orders_owner=arwdDxtm/orders_owner+|                   |
        |           |       | orders_app=arwd/orders_owner      +|                   |
        |           |       | orders_ro=r/orders_owner           |                   |
(1 row)

The access privileges column is an ACL: grantee=privileges/grantor. Letters: a INSERT (append), r SELECT (read), w UPDATE (write), d DELETE, D TRUNCATE, x REFERENCES, t TRIGGER, m MAINTAIN (new in 17: VACUUM, ANALYZE, REINDEX...). arwdDxtm = everything = the owner.

How they were made:

CREATE ROLE orders_owner NOLOGIN;
CREATE ROLE orders_app LOGIN PASSWORD '...';
CREATE ROLE orders_ro  LOGIN PASSWORD '...';
CREATE DATABASE orders OWNER orders_owner;
REVOKE CONNECT ON DATABASE orders FROM PUBLIC;
GRANT CONNECT ON DATABASE orders TO orders_app, orders_ro;
-- in the orders database:
GRANT USAGE ON SCHEMA public TO orders_app, orders_ro;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO orders_app;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO orders_ro;
ALTER DEFAULT PRIVILEGES FOR ROLE orders_owner IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO orders_app;
ALTER DEFAULT PRIVILEGES FOR ROLE orders_owner IN SCHEMA public
  GRANT SELECT ON TABLES TO orders_ro;

Three traps in there:

1. PUBLIC is every role. By default every role may CONNECT to every database (and, before 15, create tables in public). Revoke what you do not want.
2. GRANT ... ON ALL TABLES is a snapshot: tables created tomorrow get nothing. The classic incident: a migration adds a table, the app gets ERROR: permission denied for table refunds. ALTER DEFAULT PRIVILEGES FOR ROLE <the creator> fixes it for the future - and it only applies to objects that role creates, which is why migrations must run as the owner.
3. Sequences and functions are separate objects. An identity column or serial uses a sequence; with GENERATED ... AS IDENTITY (as here) privileges on the table are enough, with old serial columns the app also needs USAGE on the sequence.

Logging in over TCP

The app connects over TCP with a password, so that is what to test - not the socket:

$ PGPASSWORD=ro-lab-pw psql -h 127.0.0.1 -U orders_ro -d orders -c 'select count(*) from customers'
 count
-------
  4000
(1 row)
$ PGPASSWORD=ro-lab-pw psql -h 127.0.0.1 -U orders_ro -d orders -c "delete from customers where id = 1"
ERROR:  permission denied for table customers
$ PGPASSWORD=wrong psql -h 127.0.0.1 -U orders_ro -d orders -c 'select 1'
psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL:  password authentication failed for user "orders_ro"

The client only ever says password authentication failed. The server log says why, and which pg_hba.conf line matched - that is where you look:

$ sudo tail -n 3 /var/log/postgresql/postgresql-18-main.log
2026-09-22 20:01:52.250 UTC [17418] orders_ro@orders STATEMENT:  delete from customers where id = 1
2026-09-22 20:01:52.500 UTC [17419] orders_ro@orders FATAL:  password authentication failed for user "orders_ro"
2026-09-22 20:01:52.500 UTC [17419] orders_ro@orders DETAIL:  Connection matched file "/etc/postgresql/18/main/pg_hba.conf" line 125: "host    all             all             127.0.0.1/32            scram-sha-256"

A plain wrong password over SCRAM logs only the rule that matched (line 125 here). When something else is wrong, the DETAIL: names it first: Role "x" does not exist., User "x" has no password assigned., User "x" has an expired password. (VALID UNTIL passed), User "x" does not have a valid SCRAM secret. (an old md5 password with a scram-sha-256 rule), or Password does not match for user "x". (md5/password rules). The client deliberately gets none of it, so an attacker cannot probe which role names exist.

Change a password the safe way - from psql, so it never appears in a shell history or the server log (\password sends the already-hashed verifier):

orders=# \password orders_ro
Enter new password for user "orders_ro":
Enter it again:

pg_hba.conf

$ sudo grep -v '^\s*#' /etc/postgresql/18/main/pg_hba.conf | grep -v '^$'
local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

Each line: type, database, user, address (not for local), method, [options]. The server reads them top to bottom and the first match wins - a later, better rule is never reached.

Changes to pg_hba.conf need a reload, never a restart, and a broken file is refused as a whole - the server keeps the old rules and logs the error. Check what the server will actually use before you reload:

$ sudo -u postgres psql -c "select line_number, type, database, user_name, address, netmask, auth_method, error from pg_hba_file_rules"
 line_number | type  |   database    | user_name  |  address  |                 netmask                 |  auth_method  | error
-------------+-------+---------------+------------+-----------+-----------------------------------------+---------------+-------
         118 | local | {all}         | {postgres} |           |                                         | peer          |
         123 | local | {all}         | {all}      |           |                                         | peer          |
         125 | host  | {all}         | {all}      | 127.0.0.1 | 255.255.255.255                         | scram-sha-256 |
         127 | host  | {all}         | {all}      | ::1       | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | scram-sha-256 |
         130 | local | {replication} | {all}      |           |                                         | peer          |
         131 | host  | {replication} | {all}      | 127.0.0.1 | 255.255.255.255                         | scram-sha-256 |
         132 | host  | {replication} | {all}      | ::1       | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | scram-sha-256 |
(7 rows)

pg_hba_file_rules parses the file on disk now, so a typo shows up in the error column before your reload throws it away.

The errors you will see, one per cause:

Opening the server to the network

Two separate gates, and both must be open:

1. listen_addresses (default localhost): which interfaces the server binds. A postmaster setting - restart. '*' = all interfaces; better, the specific address.
2. pg_hba.conf: which clients may authenticate on them - reload.

$ sudo ss -ltn 'sport = :5432'
State  Recv-Q Send-Q Local Address:Port Peer Address:PortProcess
LISTEN 0      200            [::1]:5432         [::]:*
LISTEN 0      200        127.0.0.1:5432      0.0.0.0:*

Only 127.0.0.1 and ::1: pods on 10.244.x.x get connection refused long before pg_hba.conf is consulted. With listen_addresses = '*' and a restart you would see 0.0.0.0:5432 and [::]:5432.

A good rule for an app on a Kubernetes pod network, in its own place in the file (before any broader rule):

# TYPE  DATABASE  USER        ADDRESS         METHOD
host    orders    orders_app  10.244.0.0/16   scram-sha-256

Narrow on all four: one database, one role, the pod CIDR, a real password. Never host all all 0.0.0.0/0 trust. Use hostssl when the traffic crosses a network you do not control (and sslmode=verify-full on the client side: require only encrypts, it does not check who answered).

In an interview: "A new app on Kubernetes can't connect to Postgres - how do you debug it?" - read the exact error: connection refused means nothing listens there (listen_addresses, needs a restart, or a firewall); no pg_hba.conf entry for host ... means add a narrow host <db> <role> <pod CIDR> scram-sha-256 rule and reload; password authentication failed means read the server log's DETAIL (wrong password, no role, expired). Check pg_hba_file_rules before reloading.

What you can do now

• Create owner / app / read-only roles with only the privileges each needs, including default privileges for tables created later.
• Read an ACL (arwdDxtm) and check privileges with has_table_privilege.
• Read and order pg_hba.conf rules, validate them with pg_hba_file_rules, reload.
• Map every connection error to its cause, using the server log's DETAIL for passwords.
• Open the server to a network: listen_addresses (restart) and pg_hba.conf (reload).