Roles, Privileges & Row-Level Security — SQL Course

What you'll learn

Apply least privilege with roles, ownership, grants, default privileges, schemas, and row-level security policies. The lab uses PostgreSQL while identifying the semantics that transfer to other relational systems.

By the end of this lesson, you'll be able to:

Apply Roles and membership to a realistic data question
Apply GRANT and REVOKE to a realistic data question
Apply Schema privileges to a realistic data question
Apply Default privileges to a realistic data question

Core mental model

SQL is declarative: describe the result or invariant you need, then let the database choose a physical execution strategy. Use this table to connect syntax to design decisions.

Concept	What it means	Decision rule
Role	A database identity or permission bundle	Separate login roles from reusable privilege roles
Default privilege	Permissions applied to future objects created by one owner	Configure it for the actual migration owner
RLS policy	Row visibility and change rules evaluated by PostgreSQL	Treat as defense in depth and test every command/role

Professional workflow

Work from a defined question and result grain, then verify correctness before performance.

State the least-privilege data access question and the exact grain of the expected result.
Inspect table definitions, keys, constraints, representative values, and row counts.
Write the smallest correct query with explicit columns, aliases, and predicates.
Test missing, duplicate, boundary, and NULL cases before trusting the result.
Inspect the execution plan or affected rows when cost or data change matters.
Save the query with its assumptions, parameters, verification, and recovery notes.

Make results explainable

Keep each query in a saved SQL file with a short statement of its purpose, expected grain, assumptions, and verification query.

Guided SQL lab

Create a tenant row policy

The application sets a trusted transaction-local tenant and the policy constrains both reads and writes.

tenant_rls.sql

ALTER TABLE sales.orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_orders ON sales.orders
  USING (tenant_id = current_setting('app.tenant_id')::bigint)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::bigint);

REVOKE ALL ON sales.orders FROM PUBLIC;
GRANT SELECT, INSERT, UPDATE ON sales.orders TO app_writer;

Production practice

Contract

Define the expected row grain, inputs, output columns, invariants, and failure or empty-result behavior before writing SQL.

Verification

Use representative fixtures and independent row-count, uniqueness, NULL, and boundary checks; compare plans when cost matters.

Operations

Save reviewed SQL with explicit schema names where appropriate, bounded scope, least privilege, observability, and a recovery path for changes.

Common failure mode

Table owners and roles with BYPASSRLS can bypass row-level security. Test using the same role and settings as production.

Independent workshop

Build a review-ready least-privilege data access lab against the course commerce dataset.

Your finished workshop must include:

Roles and membership
GRANT and REVOKE
Schema privileges
Default privileges
Row-level security
Verification notes and edge-case evidence

Definition of done

Run the expected case and at least two edge cases, verify row counts and grain, and add comments explaining any vendor-specific behavior.

Recap & quick check

Key takeaways

Role: Separate login roles from reusable privilege roles
Default privilege: Configure it for the actual migration owner
RLS policy: Treat as defense in depth and test every command/role

Quick check

1. Which rule best applies to Role?

2. Which rule best applies to Default privilege?

3. Which rule best applies to RLS policy?

Next: Migrations & Zero-Downtime Schema Evolution