Lesson 6 · Authorization — what may you do?

The granular permission model

Below the coarse role gate: named capabilities, granted to users through a chain of join tables.

Your win: explain what a permission is, and trace the join chain that connects a user to a named permission like user.user.read — the tables in order, and what each one does.

Why roles aren't enough

A role (Lesson 5) is a blunt instrument — "Teacher" either can or can't call a whole method. But real authorization needs finer statements: "this user may read users but not write them," "…and only at these centers." That's what permissions add. A permission is a dotted capability string:

user.user.read user.user.write master.location.read master.location.write └──────┬──────┘ domain.entity.action

This is the shift from pure RBAC (roles) toward ABAC-style authorization (attributes: which permission, on which location).¹ Roles are still the entry point — but a role now carries a set of permissions.

The join chain — user to permission

Connecting a user to a permission takes a chain of tables (all created in migrations/bob/1182_migrate.up.sql, all tenant-isolated by RLS). Read it as a path:

user │ user_group_member (which groups am I in?) :104 ▼ user_group :51 │ granted_role (which roles does the group get?) :67 ▼ role :17 │ permission_role (which permissions has the role?) :33 ← M:N ▼ permission (permission_name = "user.user.read") :1 …and separately, each granted_role is pinned to a place: granted_role ──▶ granted_role_access_path ──▶ location :86

Table	Answers
`permission`	what capabilities exist (`permission_name`)
`role`	what roles exist
`permission_role`	which permissions each role has (many-to-many)
`user_group`	a named bundle of role-grants
`granted_role`	which roles a user-group is granted
`granted_role_access_path`	which location that grant applies to (Lesson 7)
`user_group_member`	which users are in a user-group

Read the chain as a sentence "A user is a member of a user-group, which is granted a role (pinned to a location), and that role carries a set of permissions." Users never get permissions directly — always through a user-group and a role. That indirection is what makes bulk administration tractable (change the group, not a thousand users).

The materialized query — the chain in one join

You rarely walk those tables by hand — the repo has a query that joins the whole chain to answer "where does user U hold permission P?":

internal/bob/repositories/granted_permission.go:19-40 (condensed)

SELECT l1.location_id
FROM user_group_member ugm
  JOIN user_group ug            ON ugm.user_group_id = ug.user_group_id
  JOIN granted_role gr          ON ug.user_group_id  = gr.user_group_id
  JOIN role r                   ON gr.role_id        = r.role_id
  JOIN permission_role pr       ON r.role_id         = pr.role_id
  JOIN permission p             ON p.permission_id   = pr.permission_id
  JOIN granted_role_access_path grap ON gr.granted_role_id = grap.granted_role_id
  JOIN locations l              ON l.location_id     = grap.location_id
  JOIN locations l1             ON l1.access_path ~~ (l.access_path || '%')  ← subtree (Lesson 7)
WHERE ugm.user_id = $1 AND p.permission_name = $2 AND (…all deleted_at IS NULL…)

That's the entire model in one statement — user → group → role → permission, plus the location join. (The last two lines are the location subtree, which is Lesson 7's whole subject.) We'll return to using this query in Lesson 8.

The fast path: granted_permission + the view Walking seven joins per check is expensive, so the repo also keeps a denormalized table granted_permission (migrations/bob/1309) holding (user_group, role, permission_name, location_id, resource_path) pre-joined, plus a granted_permissions view that does the whole chain including the subtree expansion. Same information, cheaper to query. Both are RLS-protected (Part 3).

RBAC vs ABAC — roles vs fine-grained permissions

Why a role alone is too coarse, and how attribute/permission-based models add the granularity this chain provides.

→ OneLogin — RBAC vs ABAC
→ NIST SP 800-162 — ABAC · in-repo migrations/bob/1182_migrate.up.sql

Check yourself (from memory)

Q1. How does a user come to hold a permission?

user → user_group_member → user_group → granted_role → role → permission_role → permission. Always through a group and a role, never directly.

Q2. What is a permission, concretely?

A named capability (domain.entity.action) in the permission table, attached to roles via permission_role.

Q3. Why does a denormalized granted_permission table exist alongside the join chain?

It pre-joins the chain (user_group, role, permission, location) so checks are cheap. Same info, fewer joins — still RLS-protected.

Recall: the user→permission join chain.

name the tables in order, then reveal

A permission = dotted capability (user.user.read, master.location.write) in the permission table. Chain (all in migrations/bob/1182): user → user_group_member → user_group → granted_role → role → permission_role (M:N) → permission; and each granted_role is pinned to a place via granted_role_access_path → location. Users never hold permissions directly — always through a group + role. The GrantedPermissionRepo query joins the whole chain (+ subtree) to answer "where does user U hold permission P?"; a denormalized granted_permission table + the granted_permissions view are the cheap fast-path. This is RBAC → ABAC (permission + location).

🎯 Interview one-liner "How do you do fine-grained permissions?" → "Named permissions like user.user.read attached to roles, and users get roles through user-groups — a join chain from user_group_member down to permission. Each grant is also pinned to a location, so a permission is scoped to a place. We denormalize it into a fast-path table because checking it live is a seven-way join."

Next: the location half of that chain — the location tree and the access_path that lets one grant cover a whole subtree. Ask me to walk the join tables again if the chain is fuzzy.

1. RBAC vs ABAC, NIST ABAC. In-repo: migrations/bob/1182_migrate.up.sql, internal/bob/repositories/granted_permission.go:15.