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
| Role | Purpose | Can log in |
|---|---|---|
orbit | Owns the schema, runs migrations | Yes, from deploy jobs only |
orbit_app | The application: reads and writes data | Yes |
orbit_readonly | Group for reporting tools | No |
grafana | A dashboard that inherits orbit_readonly | Yes |
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;
CONNECTlets a role open a connection to the database.USAGEon a schema lets it look up objects inside; without it, table privileges are useless.ON ALL TABLES IN SCHEMAcovers 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:
| Role | Allows |
|---|---|
pg_read_all_data / pg_write_all_data | Read or write every table in every database |
pg_monitor | Read monitoring views such as pg_stat_activity in full |
pg_maintain (17+) | VACUUM, ANALYZE, REINDEX, REFRESH MATERIALIZED VIEW on all tables |
pg_signal_backend | Cancel or terminate other sessions |
pg_read_server_files | Server-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);
USINGfilters which existing rows are visible (and can be updated or deleted).WITH CHECKvalidates new or changed rows.current_setting('app.user_id', true)returns NULL instead of failing when the setting is missing, andnullif(..., '')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
| Method | Meaning |
|---|---|
peer | Local socket only: the OS user name must match the role |
scram-sha-256 | Password, sent as a secure challenge-response |
md5 | Older password method, deprecated since PostgreSQL 18 |
cert | TLS client certificate |
trust | No check at all: never on a reachable server |
reject | Always 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 withSELECT 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 = onwith a certificate and key), usehostssllines, and have clients connect withsslmode=verify-fullso 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_activityshow who did what. - Keep secrets out of code and images: use your platform’s secret store and rotate passwords.
- Revoke the default
CONNECTthatPUBLIC(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
pgauditextension. SECURITY DEFINERfunctions run with their owner’s rights; always addSET search_path = pg_catalog, publicto them so callers cannot hijack them with look-alike objects.
Quick reference
| Task | SQL |
|---|---|
| Login role | CREATE ROLE app LOGIN PASSWORD '...'; |
| Group role and member | CREATE ROLE readers NOLOGIN; GRANT readers TO alice; |
| Database and schema access | GRANT CONNECT ON DATABASE db TO r; GRANT USAGE ON SCHEMA s TO r; |
| Table access | GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA s TO r; |
| Future tables | ALTER DEFAULT PRIVILEGES FOR ROLE owner IN SCHEMA s GRANT ... TO r; |
| Check privileges | \dp table, \ddp, has_table_privilege(...) |
| Enable RLS | ALTER TABLE t ENABLE ROW LEVEL SECURITY; |
| Policy | CREATE POLICY p ON t USING (...) WITH CHECK (...); |
| Reload pg_hba.conf | SELECT 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?
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?
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?
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, schemaUSAGEand table privileges, and useALTER DEFAULT PRIVILEGESfor 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;.