Thuta Learning
Search
IntermediateData & Databasesbeginner

Roles, Privileges, and Row Security

Relax. We'll talk through this in plain words — no textbook voice.

What you'll walk away with

  • Explain the core ideas behind Roles, Privileges, and Row Security
  • Run the sample SQL or command and verify its output
  • Apply the technique to the Tutorial Platform and production scenarios

Build the mental model

PostgreSQL uses one role model for both users and groups. An application runtime role should not own the schema or be superuser; grant only required CONNECT, USAGE, and table actions. Row-Level Security can enforce ownership even when a query forgets a WHERE clause, but owner and bypass behavior must be understood.

Connect it to a real scenario

Separate a migration role that owns DDL from an application runtime role that performs only DML. Add an example RLS policy that permits progress rows matching a current user ID stored in the session.

Try the working example

sql
CREATE ROLE tutorial_app LOGIN PASSWORD 'replace-me';
GRANT CONNECT ON DATABASE tutorial_platform TO tutorial_app;
GRANT USAGE ON SCHEMA app TO tutorial_app;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA app TO tutorial_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO tutorial_app;

ALTER TABLE app.lesson_progress ENABLE ROW LEVEL SECURITY;
CREATE POLICY progress_by_user ON app.lesson_progress
USING (EXISTS (
  SELECT 1 FROM app.enrollments e
  WHERE e.enrollment_id = lesson_progress.enrollment_id
    AND e.user_id = current_setting('app.user_id', true)::bigint
))
WITH CHECK (EXISTS (
  SELECT 1 FROM app.enrollments e
  WHERE e.enrollment_id = lesson_progress.enrollment_id
    AND e.user_id = current_setting('app.user_id', true)::bigint
));
You should see
The app role can use only granted objects/actions and policy-permitted rows.

5-minute try-it

Create a read-only reporting role with SELECT access to tables in the `app` schema.

One important caution

Never use the sample password. Without suitable `ALTER DEFAULT PRIVILEGES`, future tables may not receive expected grants.

PostgreSQL — Database Roles — PostgreSQL Global Development Group

Easy traps

  • Never use the sample password. Without suitable `ALTER DEFAULT PRIVILEGES`, future tables may not receive expected grants.
  • Validate sample code on a local or test database with recoverable backups before applying it to production data.

Exercise

Create a read-only reporting role with SELECT access to tables in the `app` schema.

You'll know it worked when: The app role can use only granted objects/actions and policy-permitted rows.

Roles, Privileges, and Row Security | Thuta Learning