<!--
Author: Simon-Pierre Boucher
Contact: contact@spboucher.ai
-->

# Patterns — Implementing Authorization

## Contents
- Permission matrix and RBAC tables
- Central authorize() and deny-by-default middleware
- Ownership scoping (IDOR prevention)
- Multi-tenant isolation with RLS backstop
- Audit logging
- Gotchas

## Permission matrix and RBAC tables

```sql
CREATE TABLE roles        (id serial PRIMARY KEY, name text UNIQUE NOT NULL);
CREATE TABLE permissions  (id serial PRIMARY KEY, name text UNIQUE NOT NULL); -- "reports:read"
CREATE TABLE role_permissions (
  role_id int REFERENCES roles, permission_id int REFERENCES permissions,
  PRIMARY KEY (role_id, permission_id));
CREATE TABLE user_roles (
  user_id bigint REFERENCES users, role_id int REFERENCES roles,
  PRIMARY KEY (user_id, role_id));
```

Name permissions `resource:action`; keep the matrix in a checked-in doc so
reviews see permission changes as diffs.

## Central authorize() and deny-by-default middleware

```python
class Forbidden(Exception): ...

def authorize(caller, action: str, resource=None):
    if action not in caller.permissions:
        raise Forbidden(action)
    if resource is not None and hasattr(resource, "owner_id"):
        if resource.owner_id != caller.id and "override:ownership" not in caller.permissions:
            raise Forbidden(f"{action} on foreign resource")

# Deny-by-default: routes must declare a policy or be rejected.
@app.middleware("http")
async def enforce_policy(request, call_next):
    endpoint = request.scope.get("endpoint")
    if endpoint is None or not getattr(endpoint, "_policy", None):
        return JSONResponse({"detail": "no policy declared"}, status_code=403)
    return await call_next(request)

def require(perm: str):
    def deco(fn):
        fn._policy = perm
        @wraps(fn)
        async def inner(request, *a, **kw):
            authorize(request.state.caller, perm)
            return await fn(request, *a, **kw)
        return inner
    return deco
```

## Ownership scoping (IDOR prevention)

```python
# Scope in the query itself — a forgotten route check then returns 404, not a leak.
def get_document(db, doc_id: int, caller):
    row = db.execute(
        "SELECT * FROM documents WHERE id = :id AND owner_id = :owner",
        {"id": doc_id, "owner": caller.id}).fetchone()
    if row is None:
        raise NotFound          # don't reveal existence of others' docs
    return row
```

## Multi-tenant isolation with RLS backstop

```python
# tenant_id comes from the verified session/token — NEVER from the request.
tenant_id = caller.tenant_id
db.execute("SET app.tenant_id = :t", {"t": tenant_id})
```

```sql
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.tenant_id')::bigint);
-- Backstop: even a query missing the WHERE clause cannot cross tenants.
```

## Audit logging

```python
def audit(caller, action, target, request):
    audit_log.insert(                      # append-only table / stream
        actor=caller.id, action=action, target=target,
        ip=request.client.host, at=utcnow())

# Call on: role grants, exports, deletions, impersonation, settings changes.
```

## Gotchas

- **Checking the route but not the query** — a second entry point (GraphQL, admin API, background job) reuses the unscoped query and leaks. Scope at the data layer.
- **tenant_id taken from the URL/body** — attacker just edits it. Only the token/session is trusted.
- **Caching authorization results too long** — demoted admins keep power; cap policy caches at ~60 s or bust on role change.
- **`is_admin` boolean creep** — one flag becomes god-mode everywhere and can't be audited; use named permissions even for admins.
- **RLS silently disabled for table owners** — Postgres table owners bypass RLS unless `FORCE ROW LEVEL SECURITY` is set; app roles must not own the tables.
- **404-vs-403 inconsistency** — mixing them per route lets attackers map which IDs exist; decide per resource class and enforce in one place.