Lesson 15 advanced

Roles, Privileges and Security

Give Orbit a least-privilege application role and a read-only reporting role, grant access with GRANT and ALTER DEFAULT PRIVILEGES, isolate each user's notes with row-level security, control who may connect in pg_hba.conf, use SCRAM passwords and TLS, and keep SQL injection and leaked credentials out of your database.

So far everything ran as orbit, the role that owns every table. An application connected as the owner can drop tables, disable triggers and read everything, so one SQL injection bug would be a disaster. In this lesson Orbit gets proper roles, permissions and per-user isolation.

Roles: users and groups in one concept

Postgres has a single concept, the role. A role with LOGIN is what other systems call a user; a role without it acts as a group. Roles can be members of other roles and inherit their privileges. Roles are shared by all databases of a cluster.

Create the roles with your own administrator role from lesson 2 (it has CREATEROLE):

CREATE ROLE orbit_app LOGIN PASSWORD 'app-secret-change-me' CONNECTION LIMIT 50;
CREATE ROLE orbit_readonly NOLOGIN;
CREATE ROLE grafana LOGIN PASSWORD 'grafana-secret-change-me' IN ROLE orbit_readonly;
CREATE ROLE
CREATE ROLE
CREATE ROLE
RolePurposeCan log in
orbitOwns the schema, runs migrationsYes, from deploy jobs only
orbit_appThe application: reads and writes dataYes
orbit_readonlyGroup for reporting toolsNo
grafanaA dashboard that inherits orbit_readonlyYes

Granting privileges

Privileges are granted at several levels. Connected as orbit, the owner, grant what each role needs and nothing more:

GRANT CONNECT ON DATABASE orbit TO orbit_app, orbit_readonly;
GRANT USAGE ON SCHEMA public TO orbit_app, orbit_readonly;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO orbit_app;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO orbit_readonly;
  • CONNECT lets a role open a connection to the database.
  • USAGE on a schema lets it look up objects inside; without it, table privileges are useless.
  • ON ALL TABLES IN SCHEMA covers every existing table, view and materialized view.

Inserting into identity columns needs no extra grant. Tables that use the older serial type also need GRANT USAGE ON ALL SEQUENCES IN SCHEMA public.

Future tables

ALL TABLES only affects tables that exist now. The next migration creates a table, and the application cannot read it. Default privileges fix that for objects created later by a given role:

ALTER DEFAULT PRIVILEGES FOR ROLE orbit IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO orbit_app;
ALTER DEFAULT PRIVILEGES FOR ROLE orbit IN SCHEMA public
  GRANT SELECT ON TABLES TO orbit_readonly;

FOR ROLE orbit matters: default privileges apply to objects created by that role, and migrations run as orbit. Check the result with \ddp, and a table’s privileges with \dp notes.

What the app cannot do

Connect as the application and try something it should not:

$ psql -h localhost -U orbit_app -d orbit
DROP TABLE notes;
ERROR:  must be owner of table notes

Only the owner (or a superuser) can drop or alter a table, which is exactly why applications should not connect as the owner.

Predefined roles

Postgres ships roles for common jobs, so you rarely need superuser:

RoleAllows
pg_read_all_data / pg_write_all_dataRead or write every table in every database
pg_monitorRead monitoring views such as pg_stat_activity in full
pg_maintain (17+)VACUUM, ANALYZE, REINDEX, REFRESH MATERIALIZED VIEW on all tables
pg_signal_backendCancel or terminate other sessions
pg_read_server_filesServer-side COPY FROM a file

GRANT pg_monitor TO grafana; lets the dashboard see every session’s queries without any data access.

Row-level security

Privileges work per table. Orbit needs something finer: a user must see only their own notes. Row-level security (RLS) adds a filter to every query on a table, enforced by the database.

The application tells Postgres which Orbit user it is acting for by setting a custom parameter at the start of each request. As orbit:

ALTER TABLE notes ENABLE ROW LEVEL SECURITY;

CREATE POLICY notes_owner ON notes
  USING      (user_id = nullif(current_setting('app.user_id', true), '')::bigint)
  WITH CHECK (user_id = nullif(current_setting('app.user_id', true), '')::bigint);

CREATE POLICY notes_reporting ON notes FOR SELECT TO orbit_readonly USING (true);
  • USING filters which existing rows are visible (and can be updated or deleted).
  • WITH CHECK validates new or changed rows.
  • current_setting('app.user_id', true) returns NULL instead of failing when the setting is missing, and nullif(..., '') turns an empty setting into NULL too. A NULL user id matches nothing, so a request that forgot to set it sees no notes at all, the safe failure.
  • The second policy lets the reporting group read every note. Policies are combined with OR.

Now, as orbit_app, act for Ben:

SET app.user_id = '2';
SELECT id, title FROM notes WHERE id <= 5 ORDER BY id;
 id |     title
----+---------------
  3 | Book ideas
  4 | Postgres tips
(2 rows)

Ana’s notes are simply not there, even though the query did not filter by user. Writing a note for someone else fails:

INSERT INTO notes (user_id, title) VALUES (1, 'Sneaky note');
ERROR:  new row violates row-level security policy for table "notes"

With a connection pooler in transaction mode, use SET LOCAL app.user_id = '2'; inside each transaction so the value cannot leak to the next request on the same connection.

Warning: Table owners and superusers bypass RLS. That is why the application must not connect as orbit. Add ALTER TABLE notes FORCE ROW LEVEL SECURITY; if you want policies to apply to the owner too.

Who may connect: pg_hba.conf

Before any privilege is checked, pg_hba.conf decides whether a connection is allowed at all and how it authenticates. Lines are read top to bottom and the first match wins:

# TYPE   DATABASE   USER        ADDRESS          METHOD
local    all        postgres                     peer
local    all        all                          peer
hostssl  orbit      orbit_app   10.0.1.0/24      scram-sha-256
hostssl  orbit      grafana     10.0.9.15/32     scram-sha-256
hostssl  orbit      orbit       10.0.2.0/24      scram-sha-256
host     all        all         0.0.0.0/0        reject
MethodMeaning
peerLocal socket only: the OS user name must match the role
scram-sha-256Password, sent as a secure challenge-response
md5Older password method, deprecated since PostgreSQL 18
certTLS client certificate
trustNo check at all: never on a reachable server
rejectAlways refuse

After editing, reload with SELECT pg_reload_conf(); and check how the server parsed the file in the pg_hba_file_rules view. To accept remote connections at all, listen_addresses in postgresql.conf must include the server’s address (that one needs a restart).

Passwords and TLS

  • Keep password_encryption = 'scram-sha-256' (the default). Find roles still on old MD5 hashes with SELECT rolname FROM pg_authid WHERE rolpassword LIKE 'md5%'; and reset their passwords.
  • Set passwords with psql’s \password orbit_app, which hashes the password before sending it, so it never appears in logs.
  • Turn on TLS (ssl = on with a certificate and key), use hostssl lines, and have clients connect with sslmode=verify-full so they also verify the server’s identity.
  • VALID UNTIL '2027-06-30' on a role makes its password expire.

SQL injection

Never build SQL by gluing user input into a string:

query = "SELECT * FROM notes WHERE title = '" + userInput + "'"     -- vulnerable

Input such as ' OR true -- changes the meaning of the query. Every driver supports parameters, which send values separately from the SQL text so they can never become code:

SELECT id, title FROM notes WHERE title = $1          -- value sent as a parameter

Inside PL/pgSQL, dynamic SQL must quote with format(): %I for identifiers and %L for literals, or better, pass values with EXECUTE ... USING. Least privilege and RLS limit the damage if a bug slips through anyway.

More good habits

  • Give each application, job and person its own role, so logs and pg_stat_activity show who did what.
  • Keep secrets out of code and images: use your platform’s secret store and rotate passwords.
  • Revoke the default CONNECT that PUBLIC (every role) has on new databases: REVOKE CONNECT ON DATABASE orbit FROM PUBLIC;.
  • For detailed audit logs of who read or changed what, look at the pgaudit extension.
  • SECURITY DEFINER functions run with their owner’s rights; always add SET search_path = pg_catalog, public to them so callers cannot hijack them with look-alike objects.

Quick reference

TaskSQL
Login roleCREATE ROLE app LOGIN PASSWORD '...';
Group role and memberCREATE ROLE readers NOLOGIN; GRANT readers TO alice;
Database and schema accessGRANT CONNECT ON DATABASE db TO r; GRANT USAGE ON SCHEMA s TO r;
Table accessGRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA s TO r;
Future tablesALTER DEFAULT PRIVILEGES FOR ROLE owner IN SCHEMA s GRANT ... TO r;
Check privileges\dp table, \ddp, has_table_privilege(...)
Enable RLSALTER TABLE t ENABLE ROW LEVEL SECURITY;
PolicyCREATE POLICY p ON t USING (...) WITH CHECK (...);
Reload pg_hba.confSELECT pg_reload_conf();
Change a password safely\password role

Exercise

Migrations run as orbit and create a new table every week. The application role keeps getting “permission denied” on new tables. What fixes it permanently?

  1. Run GRANT … ON ALL TABLES after every migration by hand
  2. ALTER DEFAULT PRIVILEGES FOR ROLE orbit IN SCHEMA public GRANT … ON TABLES TO orbit_app
  3. Make orbit_app a superuser
  4. Grant CREATE on the schema to orbit_app
Show answer

Answer: B. Default privileges are attached to objects created later by the named role. GRANT ON ALL TABLES only covers tables that exist at the moment you run it.

Exercise

The application connects as the table owner and RLS policies seem to have no effect. Why?

  1. Policies only apply to SELECT
  2. RLS needs a server restart
  3. current_setting does not work inside policies
  4. Table owners bypass row-level security unless FORCE ROW LEVEL SECURITY is set
Show answer

Answer: D. Owners and superusers are exempt from policies by default. Connect the application with a separate role, or force RLS on the table.

Exercise

pg_hba.conf contains host all all 0.0.0.0/0 reject on line 3 and hostssl orbit orbit_app 10.0.1.0/24 scram-sha-256 on line 6. What happens when the app connects from 10.0.1.20?

  1. It is rejected, because the first matching line wins
  2. It connects, because the more specific line wins
  3. It connects without a password
  4. The server refuses to start with conflicting lines
Show answer

Answer: A. pg_hba.conf is read top to bottom and the first line that matches type, database, user and address decides. Put specific rules before catch-all reject lines.

Key takeaways

  • Roles are users and groups; give each application and person their own, with only the privileges they need.
  • Grant CONNECT, schema USAGE and table privileges, and use ALTER DEFAULT PRIVILEGES for future tables.
  • Applications must not connect as the owner: owners can drop tables and bypass RLS.
  • Row-level security filters rows per request inside the database; make a missing setting match nothing.
  • pg_hba.conf (first match wins), SCRAM passwords, TLS and parameterised queries close the remaining doors.

FAQ

What is the difference between a user and a role?

Since PostgreSQL 8.1 there is only the role. CREATE USER is the same as CREATE ROLE ... LOGIN, kept for compatibility.

How do I see what a role can do?

\du lists roles and memberships, \dp table shows table privileges and has_table_privilege('orbit_app', 'notes', 'DELETE') answers a specific question. \drg (PostgreSQL 16) shows role grants in detail.

Does RLS slow down queries?

The policy condition is added to each query like an extra WHERE clause. With an index on the policy column (notes.user_id already has one from lesson 11), the overhead is small.

Can I hide a column, such as an email, from some roles?

Yes. Grant SELECT on specific columns (GRANT SELECT (id, name) ON users TO support;) or give the role access to a view that leaves the column out.

How do I safely remove a role that owns objects?

Run REASSIGN OWNED BY old_role TO orbit; and DROP OWNED BY old_role; in every database where it owns objects or has privileges, then DROP ROLE old_role;.

↑ ↓ navigate Enter open Esc close