Data Control Language (DCL)

Overview

This lesson covers PostgreSQL's access control system. Students learn to create roles, grant and revoke privileges on databases, schemas, and tables, and implement a least-privilege model. Real-world scenarios are used to motivate the concepts.

Lesson Plan

Time	Activity
0–10 min	Intro: why access control matters, PostgreSQL security model
10–30 min	CREATE ROLE / CREATE USER, GRANT on database and schema
30–50 min	GRANT on tables: SELECT, INSERT, UPDATE, DELETE
50–65 min	REVOKE, role inheritance, and GRANT OPTION
65–75 min	Guided demo: set up read-only analyst and read-write developer roles
75–80 min	Wrap-up

Key Concepts

CREATE ROLE name and CREATE USER name WITH PASSWORD '...'
GRANT privilege ON object TO role
REVOKE privilege ON object FROM role
Role inheritance: GRANT child_role TO parent_role
GRANT ALL vs individual privilege grants
Principle of least privilege: grant only what is needed

✏️Practice

Short in-class exercises:

Create a readonly_user role and grant SELECT on all tables in a schema.
Revoke INSERT privilege from a user on a specific table.
Grant a role to another role and verify the effective permissions.