Authorization queries

This is the SQL engine behind Authorization: the three queries every check resolves to, and a worked walkthrough of how Allowed answers — read it alongside the model and Check API on that page.

The three queries

Every authz answer comes from one of three parameterised queries:

Two fragments are factored into shared constants so the queries cannot drift:

• userGrantsCTE — the union of direct-assignment and group-binding permissions. Shared verbatim by allowedQuery and accessibleScopeQuery.
• preconditionCases — the super-admin / org-missing / org-deleted / not-member ladder. Shared verbatim by allowedQuery and canActQuery. accessibleScopeQuery evaluates the same preconditions but as boolean columns (it returns a multi-field result, not a single reason), so a new precondition must be added in both shapes — the regression test TestAuthz_PreconditionsAgree_* guards the agreement.

How Allowed answers a check

allowedQuery has two parts: a WITH user_grants table, and a final SELECT CASE … END:

WITH user_grants AS ( ... )            -- every permission tuple the user holds
SELECT CASE
    WHEN u.is_super_admin         THEN 'granted'
    WHEN o.id IS NULL             THEN 'org_not_found'
    WHEN o.deleted_at IS NOT NULL THEN 'org_deleted'
    WHEN NOT EXISTS ( ...active membership... ) THEN 'not_member'
    WHEN EXISTS ( ...a user_grants row matches the check... ) THEN 'granted'
    ELSE 'forbidden'
END
FROM users u
LEFT JOIN organizations o ON o.id = $2
WHERE u.id = $1

Three things to know:

• The FROM users u WHERE u.id = $1 returns 0 or 1 rows. Zero rows means the user does not exist — Go turns that into Denied / user_not_found.
• The LEFT JOIN attaches the org if it exists; if it doesn’t, the user row survives with o.* NULL. That’s how the query tells “missing user” (0 rows) apart from “missing org” (1 row, o.id NULL).
• The CASE is an ordered ladder: WHEN branches are checked top to bottom and the first true one wins. So the path of any check is “the first WHEN that fires” — everything below it is dead. And user_grants is only evaluated if the ladder reaches the 5th WHEN.

Worked examples

Below, each scenario shows the query reduced to the path it actually takes — dead branches are ..., and ✓/✗ mark how each WHEN evaluated.

1 — user does not exist

WITH user_grants AS ( ... )          -- never evaluated: no row to evaluate against
SELECT CASE ... END
FROM users u LEFT JOIN organizations o ON o.id = $2
WHERE u.id = $1                      -- ✗ matches nothing → query returns 0 rows

→ Go receives sql.ErrNoRows → Denied / user_not_found.

2 — super admin (granted even if the org is missing or deleted)

WITH user_grants AS ( ... )          -- never evaluated
SELECT CASE
    WHEN u.is_super_admin THEN 'granted'   -- ✓ → 'granted'
    WHEN ... THEN ...                      -- dead
END FROM users u LEFT JOIN organizations o ON o.id = $2 WHERE u.id = $1

3 — regular user, org does not exist

WITH user_grants AS ( ... )          -- never evaluated
SELECT CASE
    WHEN u.is_super_admin THEN ...          -- ✗
    WHEN o.id IS NULL     THEN 'org_not_found'   -- ✓
    WHEN ... THEN ...                       -- dead
END FROM ...

4 — org exists but is soft-deleted

WITH user_grants AS ( ... )          -- never evaluated
SELECT CASE
    WHEN u.is_super_admin         THEN ... -- ✗
    WHEN o.id IS NULL             THEN ... -- ✗
    WHEN o.deleted_at IS NOT NULL THEN 'org_deleted'   -- ✓
    WHEN ... THEN ...                      -- dead
END FROM ...

5 — regular user, not a member (also the path for an expired membership)

WITH user_grants AS ( ... )          -- never evaluated
SELECT CASE
    WHEN u.is_super_admin         THEN ... -- ✗
    WHEN o.id IS NULL             THEN ... -- ✗
    WHEN o.deleted_at IS NOT NULL THEN ... -- ✗
    WHEN NOT EXISTS (
        SELECT 1 FROM user_memberships
        WHERE user_id = u.id AND org_id = o.id
          AND (expires_at IS NULL OR expires_at > NOW())
    ) THEN 'not_member'                    -- ✓ (no live membership row)
    WHEN ... THEN ...                      -- dead
END FROM ...

6 — member, granted by a direct unscoped role

Now the ladder reaches the 5th WHEN, so user_grants is computed:

WITH user_grants AS (
    -- direct assignments  ← supplies the matching row
    SELECT rp.resource_type AS perm_rt, rp.action AS perm_act,
           rp.resource_pattern AS perm_pat, rp.authz_group AS perm_group
    FROM user_role_assignments ura
    JOIN role_permissions rp ON rp.role_id = ura.role_id
    WHERE ura.user_id = $1 AND ura.org_id = $2
    UNION ALL
    -- group bindings  ← still computed, just not needed for this match
    SELECT ... FROM org_identity_group_role_bindings gr ...
)
SELECT CASE
    WHEN ... THEN ...                      -- first 4 WHENs all ✗
    WHEN EXISTS (
        SELECT 1 FROM user_grants g
        WHERE (g.perm_rt  = $3 OR g.perm_rt  = '*')   -- ✓
          AND (g.perm_act = $4 OR g.perm_act = '*')   -- ✓
          AND (g.perm_pat = '' AND g.perm_group = '')  -- ✓ untargeted → the $5 / $6 arms are dead
    ) THEN 'granted'                       -- ✓
    ELSE 'forbidden'                       -- dead
END FROM ...

7 — member, granted by a group binding with a matching pattern

Same outer ladder (✗✗✗✗); the matching row comes from the binding branch, and the pattern half of the grant condition is what carries it:

WITH user_grants AS (
    SELECT ... FROM user_role_assignments ura ...     -- direct: no matching row
    UNION ALL
    -- group bindings  ← supplies the matching row
    SELECT rp.resource_type, rp.action, rp.resource_pattern, rp.authz_group
    FROM org_identity_group_role_bindings gr
    JOIN org_identity_sources ois       ON ois.id = gr.identity_source_id AND ois.enabled
    JOIN user_external_entitlements uee ON uee.user_id = $1
                                       AND uee.provider_key = ois.provider_key
                                       AND (ois.provider_key <> 'oidc' OR uee.issuer = ois.oidc_issuer)
                                       AND uee.group_key = gr.group_key
                                       AND uee.expires_at > NOW()
    JOIN LATERAL (
        SELECT role_id FROM org_identity_group_role_binding_roles
        WHERE binding_id = gr.id ORDER BY role_id LIMIT 5
    ) brr ON true
    JOIN role_permissions rp ON rp.role_id = brr.role_id
    WHERE gr.org_id = $2
)
SELECT CASE
    WHEN ... THEN ...                      -- first 4 WHENs all ✗
    WHEN EXISTS (
        SELECT 1 FROM user_grants g
        WHERE (g.perm_rt  = $3 OR g.perm_rt  = '*')   -- ✓
          AND (g.perm_act = $4 OR g.perm_act = '*')   -- ✓
          AND (g.perm_pat <> '' AND $5 IS NOT NULL
               AND $5 LIKE g.perm_pat ESCAPE '\')     -- ✓ name matches pattern
    ) THEN 'granted'                       -- ✓
    ELSE 'forbidden'                       -- dead
END FROM ...

7b — member, granted by an authz group

The stack family carries a second targeting axis. $6 is the stack’s authz_groups (its cloud integration passes the same; a create passes the request’s), NULL for every other family. A group grant matches by exact membership — no LIKE:

    WHEN EXISTS (
        SELECT 1 FROM user_grants g
        WHERE (g.perm_rt  = $3 OR g.perm_rt  = '*')   -- ✓
          AND (g.perm_act = $4 OR g.perm_act = '*')   -- ✓
          AND (g.perm_group <> ''
               AND g.perm_group = ANY($6::text[]))    -- ✓ 'env-prod' ∈ {'team-payments','env-prod'}
    ) THEN 'granted'                       -- ✓

The full targeting condition is the OR of the three arms — untargeted, pattern, group — so one EXISTS serves every mode.

8 — member, no matching grant → forbidden

Identical outer ladder; user_grants is computed but the grant EXISTS finds no satisfying row:

WITH user_grants AS ( ...both branches computed... )
SELECT CASE
    WHEN ... THEN ...                      -- first 4 WHENs all ✗
    WHEN EXISTS ( ...no row matches all three conditions... )
         THEN 'granted'                    -- ✗ EXISTS is empty → this WHEN is false
    ELSE 'forbidden'                       -- ✓ fallthrough
END FROM ...

This is also the path for a family-level check, where $5 and $6 are NULL: a patterned grant’s $5 IS NOT NULL AND … is false and a group grant’s = ANY(NULL) is not true, so a targeted grant never satisfies “can I do X anywhere?” — only an untargeted (perm_pat = '' AND perm_group = '') grant does.

The pattern across all nine

Cases 1–5 never touch user_grants — the ladder returns first. Cases 6–8 share the identical outer ladder and differ only inside the grant EXISTS. So a reader only ever holds two shapes in mind: “a precondition fired” or “we reached the grant check”.

CanAct is Cases 1–5 with the 5th WHEN replaced by ELSE 'granted' — it has no user_grants and no grant check at all. It exists for credential validators (PATs, future API tokens) that need “can this credential act in this org?” without a specific resource to ask about.