Backend & Databases · Security Deep-Dive

Case Study — Authorization in the database: Auth & Row-Level Security (PartnerOS)

What: the security model behind PartnerOS — where authorization lives in PostgreSQL Row-Level Security, not just the application, so an app bug can't leak another tenant's data. Role: designer & engineer · Where it lives: ~/PartnerOS/1/supabase/migrations/* Stack: PostgreSQL · Supabase Auth · RLS · SECURITY DEFINER functions · audit triggers Prepared 2026-07-12

Status: shipped in the migration history and running against the app. Same honest pre-customer stage as the rest of PartnerOS; this deep-dive documents the security engineering, grounded in the real SQL.

1. Summary & thesis

Most apps enforce "can this user see this row?" in application code. That's fragile: one missing where owner_id = … in one of 141 route handlers leaks data. PartnerOS instead pushes authorization down into the database with PostgreSQL Row-Level Security — the rule lives on the table, so it holds no matter which code path (or raw connection) touches it.

The footprint is real: 66 tables with RLS enabled, 96 policies, 105 uses of auth.uid(), 17 SECURITY DEFINER functions, and 17 audit triggers. This write-up covers the model, the core pattern, a genuinely subtle RLS recursion bug and its fix, performance-aware policy design, a tamper-proof audit trail, and how the one public path is hardened.

2. The model

Identity — Supabase Auth issues a JWT; inside Postgres, auth.uid() resolves the current user from current_setting('request.jwt.claim.sub'). profiles is 1:1 with auth.users.
Tenancy — data belongs to an organization, with organization_members (RBAC) and a plan tier. Each owned row also carries an owner_id.
The primary predicate — for the current single-admin orgs, owner_id = auth.uid() is the authorization rule. It's equivalent to an org check, faster, and — as §5 shows — safe from a recursion trap that the org-membership subquery caused.

3. The core RLS pattern

Every owned table gets four policies — one per operation — so reads and writes are constrained, including WITH CHECK on insert so a user can't create a row owned by someone else:

CREATE POLICY "Owners read inventory"
  ON inventory FOR SELECT USING (owner_id = auth.uid());
CREATE POLICY "Owners insert inventory"
  ON inventory FOR INSERT WITH CHECK (owner_id = auth.uid());
CREATE POLICY "Owners update inventory"
  ON inventory FOR UPDATE USING (owner_id = auth.uid());
CREATE POLICY "Owners delete inventory"
  ON inventory FOR DELETE USING (owner_id = auth.uid());

Child tables inherit ownership through their parent rather than duplicating owner_id everywhere:

CREATE POLICY "Owners read package items"
  ON inventory_package_items FOR SELECT USING (
    package_id IN (SELECT id FROM inventory_packages WHERE owner_id = auth.uid())
  );

4. War story — the RLS infinite-recursion bug

The first tenancy design scoped inventory by organization membership: the inventory SELECT policy subqueried organization_members — but organization_members itself has an RLS policy that subqueries organization_members. PostgreSQL's recursion guard made the inner subquery return empty, so:

no inventory was visible to authenticated users despite the data being perfectly correct.

A silent, data-hiding failure with no error — the hardest kind to diagnose. The fix (migration 016) replaced the recursive org-subquery policies with direct owner_id = auth.uid() checks: equivalent for single-admin orgs, faster (no subquery), and unambiguous. Crucially, multi-tenant team access can be re-added later as a separate additive policy without reintroducing the recursion.

The lesson, captured in the migration: RLS predicates should be flat and self-contained; a policy that subqueries another RLS-protected table (especially itself, transitively) invites recursion and silent emptiness. Prefer a denormalized ownership column over a clever join.

5. Performance-aware RLS

RLS predicates run on every row of every query, so their cost is not incidental. The audit-log policy originally joined back to inventory on each read. Migration 010 denormalized owner_id onto inventory_audit_log and indexed it, turning the policy into a flat column comparison:

ALTER TABLE inventory_audit_log ADD COLUMN owner_id UUID;      -- denormalized
CREATE INDEX inventory_audit_owner_idx ON inventory_audit_log(owner_id);
CREATE POLICY "Org members read audit log" ON inventory_audit_log
  FOR SELECT USING (auth.uid() = user_id OR auth.uid() = owner_id);   -- no subquery

Security and performance treated as the same problem.

6. A tamper-proof audit trail

Application-layer audit logging is fire-and-forget — it can be skipped, and a direct DB write bypasses it entirely. PartnerOS moved it into an AFTER INSERT/UPDATE/DELETE trigger so every write is captured atomically and unbypassably, correctly attributed to auth.uid():

CREATE FUNCTION log_inventory_change()
  RETURNS TRIGGER LANGUAGE plpgsql SECURITY DEFINER AS $$ … $$;

The subtle, correct details:

SECURITY DEFINER runs the trigger as the function owner (postgres), so it can insert into the audit table without granting authenticated users an INSERT policy on it — the log can't be forged from the client.
auth.uid() still resolves correctly inside the definer context, because it reads a session-level JWT setting that the role switch doesn't change — a nuance that's easy to get wrong.
The trigger computes field-level diffs (changed_fields, old/new JSON), excluding noise (updated_at, updated_by) so the log is signal.

7. Hardening the one public path — share links

Share links are the only unauthenticated surface, so they get their own defenses via SECURITY DEFINER functions (17 across the schema) that expose exactly one narrow capability each:

-- View-count increment, capped so bots/scraping can't inflate metrics.
CREATE FUNCTION increment_share_link_view(link_token TEXT) …
  SET view_count = LEAST(view_count + 1, 10000), last_viewed_at = NOW()
  WHERE token = link_token AND is_active = true;

Plus an expiry index and a nightly cron (/api/cron/cleanup-share-links) that calls deactivate_expired_share_links() to auto-close anything past expires_at. The public can use a token through a tightly-scoped function; it can never touch the underlying tables directly.

8. Secret hygiene at the app boundary

RLS protects the anon path; the privileged path is separated by construction. Two Supabase clients:

Client	Key	Bounded by
browser / SSR	anon / publishable	RLS (`auth.uid()`)
edge server only	service-role	bypasses RLS — never shipped to the client, `process.env` only

The service-role key lives only in server env — it can't leak into a client bundle, which is exactly the mistake that would defeat every policy above.

9. Compliance

A dedicated compliance layer (migration 040) adds GDPR deletion_requests (status-tracked: pending → approved → completed / rejected) and a consent_log, both RLS-scoped so users read only their own:

CREATE POLICY "users read own deletion requests"
  ON deletion_requests FOR SELECT USING (user_id = auth.uid());

10. Principles & what it demonstrates

Authorization belongs in the database. RLS makes "who can see/change this row" a table-level invariant that no application bug or raw connection can bypass.
Flat predicates beat clever ones. The recursion bug is the proof — a denormalized owner_id is safer and faster than a subquery into another RLS table.
Security is a performance problem too. Policies run per-row; denormalize + index accordingly.
Make the audit trail unforgeable. SECURITY DEFINER triggers capture every write atomically and attribute it correctly — even against direct DB access.
Isolate and narrow the public surface. The one unauthenticated path is exposed only through capped, single-purpose definer functions with expiry + cleanup.
Protect the escape hatch. The RLS-bypassing service-role key is structurally confined to the server.

What this demonstrates: database-level security engineering — RLS policy design at scale (96 policies), a real and subtly-diagnosed recursion bug, performance-aware policies, tamper-proof auditing, hardened public access, and compliance — the depth that separates "I used Supabase" from "I designed the authorization model."

11. What's next

Add the multi-tenant team-access policies (the additive layer the recursion fix deliberately left room for), with a non-recursive membership check (e.g. a SECURITY DEFINER is_org_member() helper).
A pgTAP test suite asserting each table's policies (positive + negative cases) so RLS can't silently regress.
Extend audit triggers beyond inventory to the deal-lifecycle tables.

Appendix · Code map (real paths)

Migration	Security concern
`009_organizations.sql`	Tenancy: organizations + members (RBAC)
`016_rls_owner_id.sql`	The RLS recursion fix → `owner_id = auth.uid()` model
`008_integrity_constraints.sql`	Data-integrity constraints + `set_updated_at` trigger
`010_audit_triggers.sql`	`SECURITY DEFINER` audit trigger; denormalized-owner RLS
`011_share_link_hardening.sql`	Public share-link functions: view cap, expiry, cleanup
`040_compliance.sql`	GDPR deletion requests + consent log (RLS-scoped)
`lib/supabase*.ts`	anon vs service-role client split

Footprint: 66 RLS-enabled tables · 96 policies · 105 auth.uid() uses · 17 SECURITY DEFINER functions · 17 audit triggers.

Grounded entirely in the migration SQL on file in ~/PartnerOS/1/supabase/migrations as read on 2026-07-12 (the RLS/policy/trigger counts are from the repository). Same pre-customer status as the rest of PartnerOS; no live-traffic claims are made.