Roles, Privileges & Row-Level Security

Applications should never connect as a superuser. Create roles with only the privileges they need: a read-only role for reports, an app role that can read and write specific tables, and an owner role for migrations.

Group roles (NOLOGIN) hold privileges; login roles inherit them by membership. GRANT and REVOKE control access per table, column or function.

Row-level security (RLS) adds automatic WHERE filters per user: once enabled on a table, each role only sees rows its policies allow. A common pattern stores the current user's id in a session setting that policies read with current_setting.

security.sqlSQL

-- Group roles hold privileges
CREATE ROLE readonly NOLOGIN;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON students, subjects, results TO readonly;

CREATE ROLE teacher_app NOLOGIN;
GRANT USAGE ON SCHEMA public TO teacher_app;
GRANT SELECT ON students, subjects TO teacher_app;
GRANT SELECT, INSERT, UPDATE ON results TO teacher_app;

-- A login role that inherits teacher_app
CREATE ROLE ms_lyimo LOGIN PASSWORD 'change-me' IN ROLE teacher_app;

-- Row-level security: teachers only see results for their own subjects
ALTER TABLE results ENABLE ROW LEVEL SECURITY;

CREATE POLICY teacher_own_subjects ON results
    FOR ALL TO teacher_app
    USING (subject_id IN (
        SELECT id FROM subjects
        WHERE teacher_id = current_setting('app.teacher_id', true)::bigint
    ));

-- Try it: act as the teacher (Ms. Lyimo teaches Biology, teacher id 2)
SET ROLE ms_lyimo;
SET app.teacher_id = '2';
SELECT DISTINCT subject_id FROM results;          -- only 2 (Biology)
UPDATE results SET score = score WHERE subject_id = 1;   -- UPDATE 0: Maths rows are invisible
RESET ROLE;

SELECT count(DISTINCT subject_id) FROM results;   -- the owner still sees everything

Runs in your browser · PostgreSQL

Key points

Apps connect as least-privilege roles, never as a superuser.
Grant privileges to group roles; add login roles as members.
RLS policies filter rows per role automatically — even for direct SQL access.

Exercise

Create a parent_portal role and an RLS policy on students and results so a parent session (with app.student_id set) can only read their own child's records. Test it with SET ROLE.

Show solution

Try the exercise yourself first — then compare your approach with this one.

The parent_portal role may only read the two tables, and the RLS policies add an automatic filter based on the app.student_id setting the application sets for each parent's session. nullif(..., '') makes an unset value mean "no rows" instead of an error.

parent-portal.sqlSQL

CREATE ROLE parent_portal NOLOGIN;
GRANT USAGE ON SCHEMA public TO parent_portal;
GRANT SELECT ON students, results, subjects TO parent_portal;

ALTER TABLE students ENABLE ROW LEVEL SECURITY;   -- results already has RLS enabled

CREATE POLICY parent_own_child ON students
    FOR SELECT TO parent_portal
    USING (id = nullif(current_setting('app.student_id', true), '')::bigint);

CREATE POLICY parent_own_results ON results
    FOR SELECT TO parent_portal
    USING (student_id = nullif(current_setting('app.student_id', true), '')::bigint);

-- Act as Neema's parent (student id 3)
SET ROLE parent_portal;
SET app.student_id = '3';
SELECT full_name, form FROM students;                    -- only Neema Kimaro
SELECT count(*) AS result_rows FROM results;             -- only Neema's 6 results
UPDATE students SET form = 1 WHERE id = 3;               -- ERROR: permission denied (read-only role)
RESET ROLE;
RESET app.student_id;

Runs in your browser · PostgreSQL

Key points

Exercise

Check your understanding