spb/ultra-sharp-agent-skills Public

1<!--2Author: Simon-Pierre Boucher3Contact: contact@spboucher.ai4-->56# Patterns — Implementing Authorization78## Contents9- Permission matrix and RBAC tables10- Central authorize() and deny-by-default middleware11- Ownership scoping (IDOR prevention)12- Multi-tenant isolation with RLS backstop13- Audit logging14- Gotchas1516## Permission matrix and RBAC tables1718```sql19CREATE TABLE roles        (id serial PRIMARY KEY, name text UNIQUE NOT NULL);20CREATE TABLE permissions  (id serial PRIMARY KEY, name text UNIQUE NOT NULL); -- "reports:read"21CREATE TABLE role_permissions (22  role_id int REFERENCES roles, permission_id int REFERENCES permissions,23  PRIMARY KEY (role_id, permission_id));24CREATE TABLE user_roles (25  user_id bigint REFERENCES users, role_id int REFERENCES roles,26  PRIMARY KEY (user_id, role_id));27```2829Name permissions `resource:action`; keep the matrix in a checked-in doc so30reviews see permission changes as diffs.3132## Central authorize() and deny-by-default middleware3334```python35class Forbidden(Exception): ...3637def authorize(caller, action: str, resource=None):38    if action not in caller.permissions:39        raise Forbidden(action)40    if resource is not None and hasattr(resource, "owner_id"):41        if resource.owner_id != caller.id and "override:ownership" not in caller.permissions:42            raise Forbidden(f"{action} on foreign resource")4344# Deny-by-default: routes must declare a policy or be rejected.45@app.middleware("http")46async def enforce_policy(request, call_next):47    endpoint = request.scope.get("endpoint")48    if endpoint is None or not getattr(endpoint, "_policy", None):49        return JSONResponse({"detail": "no policy declared"}, status_code=403)50    return await call_next(request)5152def require(perm: str):53    def deco(fn):54        fn._policy = perm55        @wraps(fn)56        async def inner(request, *a, **kw):57            authorize(request.state.caller, perm)58            return await fn(request, *a, **kw)59        return inner60    return deco61```6263## Ownership scoping (IDOR prevention)6465```python66# Scope in the query itself — a forgotten route check then returns 404, not a leak.67def get_document(db, doc_id: int, caller):68    row = db.execute(69        "SELECT * FROM documents WHERE id = :id AND owner_id = :owner",70        {"id": doc_id, "owner": caller.id}).fetchone()71    if row is None:72        raise NotFound          # don't reveal existence of others' docs73    return row74```7576## Multi-tenant isolation with RLS backstop7778```python79# tenant_id comes from the verified session/token — NEVER from the request.80tenant_id = caller.tenant_id81db.execute("SET app.tenant_id = :t", {"t": tenant_id})82```8384```sql85ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;86CREATE POLICY tenant_isolation ON invoices87  USING (tenant_id = current_setting('app.tenant_id')::bigint);88-- Backstop: even a query missing the WHERE clause cannot cross tenants.89```9091## Audit logging9293```python94def audit(caller, action, target, request):95    audit_log.insert(                      # append-only table / stream96        actor=caller.id, action=action, target=target,97        ip=request.client.host, at=utcnow())9899# Call on: role grants, exports, deletions, impersonation, settings changes.100```101102## Gotchas103104- **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.105- **tenant_id taken from the URL/body** — attacker just edits it. Only the token/session is trusted.106- **Caching authorization results too long** — demoted admins keep power; cap policy caches at ~60 s or bust on role change.107- **`is_admin` boolean creep** — one flag becomes god-mode everywhere and can't be audited; use named permissions even for admins.108- **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.109- **404-vs-403 inconsistency** — mixing them per route lets attackers map which IDs exist; decide per resource class and enforce in one place.110