Views, Roles, and the Principle of Least Privilege
Naming a reusable query as a view, and the security discipline of granting every role exactly the access it needs — never more.
What you'll learn
- Explain what a view is and what it does and doesn't provide over a saved query
- Explain the principle of least privilege and why a shared superuser role violates it
- Design a role's privileges to match exactly what a specific application component needs
Prerequisites
Explanation
This lesson covers PostgreSQL's role and privilege system, which SQLite has no equivalent of at all — SQLite is a single-file, single-user-process database with no server-side authentication or per-role permission model, so this lesson's content is shown for reading, and connects directly to work you'll do in this module's final guided local lab.
A view (CREATE VIEW active_enrollments AS SELECT ... WHERE ...) is a named, saved query that can be queried exactly like a table (SELECT * FROM active_enrollments) — it's genuinely just the underlying query, re-run fresh every time the view is referenced, not a separately-stored copy of the data (that's a materialized view, a related but different PostgreSQL feature with its own explicit refresh step). A view's main value is abstraction: it lets you name and hide a complex, multi-join query behind a simple, stable interface, and — combined with role privileges — it can expose a restricted, filtered subset of a table's columns or rows to a role that shouldn't see the underlying table directly.
A role in PostgreSQL is both what other databases might separately call a "user" and a "group" — the same underlying concept, distinguished only by whether it's granted LOGIN privilege. The principle of least privilege is the security discipline of granting a role exactly the permissions it actually needs to do its specific job, and nothing more: an application component that only ever reads course data should have a role granted SELECT on exactly the tables it reads, not broad ALL PRIVILEGES, and certainly not superuser access. GRANT SELECT ON course TO readonly_app_role; grants precisely one narrow capability; REVOKE removes a previously-granted privilege.
The honest, important reason this matters: every account or connection sharing one broad, overprivileged role is a single point of failure — if that role's credentials are ever compromised (a leaked connection string, a SQL injection vulnerability in application code, a misconfigured service), the attacker inherits every privilege that role holds, whether or not the compromised component actually needed most of them. A narrowly-scoped role limits the blast radius of exactly that kind of compromise: a read-only reporting role's credentials leaking is a meaningfully smaller incident than a superuser's leaking, precisely because the former structurally cannot do most of the damage the latter could. Row-level security (RLS) takes this further, restricting which specific rows a role can see or modify within a table it does have access to (a Learner role that can only see its own enrollment rows, for example) — a real, valuable PostgreSQL feature genuinely relevant to this platform's own architecture, though implementing it is beyond this introductory lesson's scope, and this platform's own Supabase-backed tables use it in exactly this spirit (see docs/SECURITY.md).
Example
Real PostgreSQL views, roles, and GRANT statements, shown for reading -- SQLite has no equivalent role/privilege system to run this against.
-- A view abstracting a multi-table join behind a simple, stable name:
CREATE VIEW active_learner_enrollments AS
SELECT
learner.email,
course.title,
enrollment.enrolled_at
FROM enrollment
JOIN learner ON enrollment.learner_id = learner.id
JOIN course ON enrollment.course_id = course.id;
-- Querying the view is exactly like querying a table:
SELECT * FROM active_learner_enrollments WHERE email = 'alice@example.com';
-- Least privilege: a role for a reporting service that ONLY needs read access:
CREATE ROLE reporting_service LOGIN PASSWORD '...';
GRANT SELECT ON active_learner_enrollments TO reporting_service;
-- reporting_service can query the view, but has NO access to learner, course, or enrollment
-- directly, and cannot INSERT/UPDATE/DELETE anything at all.
-- A DIFFERENT role for the application's write path -- narrowly scoped to exactly what it needs:
CREATE ROLE enrollment_app LOGIN PASSWORD '...';
GRANT SELECT, INSERT ON enrollment TO enrollment_app;
GRANT SELECT ON learner, course TO enrollment_app;
-- enrollment_app can create enrollments and look up learners/courses, but cannot
-- UPDATE or DELETE anything, and has no access at all to any other table in the database.Guided exercise
Guided exercise
Write hasRequiredPrivilege(rolePrivileges, table, requiredAction) modeling a GRANT check: rolePrivileges is an object like { course: ['SELECT'], enrollment: ['SELECT','INSERT'] }. Return true only if rolePrivileges[table] exists and includes requiredAction.
Checks: recognizes a granted privilege · denies an ungranted action on a table the role has some access to · denies any action on a table with no grants at all
Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.
Stuck? Get a hint.
Independent exercise
Independent exercise
Write blastRadius(rolePrivileges) returning the total number of distinct (table, action) grant pairs a role has -- a simple, concrete proxy for 'how much damage could this role's compromised credentials do.' Then write violatesLeastPrivilege(actualNeeds, grantedPrivileges) where both are objects like { table: [actions] } -- return true if grantedPrivileges includes ANY (table, action) pair not present in actualNeeds (over-provisioned access).
Checks: correctly counts total grant pairs across tables · no grants means zero blast radius · privileges exactly matching actual needs do not violate least privilege · an unneeded extra action on a needed table violates least privilege · access to an entirely unneeded table violates least privilege
Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.
Stuck? Get a hint.
Common mistakes
- Giving an application's database connection broad, superuser-equivalent privileges 'to avoid permission errors' -- this means a single compromised connection string or SQL injection vulnerability grants an attacker access to the ENTIRE database, not just what that component actually uses.
- Confusing a plain view with a materialized view -- a plain view re-runs its underlying query every time it's referenced (always current, no extra storage); a materialized view stores a snapshot that must be explicitly refreshed and can become stale.
- Assuming a view alone provides security -- a view restricts what columns/rows a query SHOWS, but without also restricting the underlying table's own privileges for that role, a role could still query the base table directly and bypass the view's restriction entirely.
Knowledge check
Takeaway
Grant every role exactly the privileges its specific job requires, never more — a broad, shared, overprivileged role turns any single compromise into full database access, while narrowly-scoped roles structurally limit how much damage a leaked credential can do.
Summary
A view names a reusable query, re-run fresh each time (unlike a materialized view's stored snapshot). PostgreSQL roles combine what other systems call users and groups. GRANT/REVOKE assign specific privileges per table and action. The principle of least privilege — granting exactly what's needed, nothing more — limits the blast radius of a compromised credential; row-level security restricts access further, down to specific rows.
References
Your notes
Notes save automatically.
Finished this lesson?
Mark it complete to track your progress and schedule a future review.
AI tutor
The optional AI tutor isn't enabled in this deployment. All lessons, exercises, quizzes, and search work fully without it.