QRSETU Dev Portal

Appearance

ADR-0014 · Public data exposure & least-privilege grants ​

Status: 🟢 Accepted — adopted 2026-07-25 in response to a live production exposure · Depends on: — · Related: ADR-0006 (authorization model), ADR-0001 (identity/tenancy), Edge Function kit standards

The one-line thesis

Row-Level Security filters rows, never columns. Anonymous roles therefore get explicit column-level GRANT SELECT on a reviewed allow-list — never blanket table privileges — and every policy names its audience with a TO clause, because an unqualified policy silently applies to PUBLIC.

Context — the incident that forced this ​

While reviewing the authentication architecture on 2026-07-25, an audit of the baseline schema found that public.profiles was readable by the anon role:

sql
GRANT ALL ON TABLE public.profiles TO anon;

CREATE POLICY "Public can view profiles by slug" ON public.profiles
  FOR SELECT USING (is_deleted = false AND slug IS NOT NULL);   -- ← no TO clause

Two independent mistakes compounded:

  1. GRANT ALL … TO anon gave the anonymous role every privilege on the table, gated only by RLS.
  2. The policy carried no TO clause. Postgres defaults such a policy to PUBLIC — every role, including anon. Nothing in the statement says "anonymous", which is why it survived review.

Because RLS restricts rows but not columns, any holder of the publishable anon key — which ships inside every client build and is not a secret — could run:

sql
select email, mobile_number, gstin, city, pin_code from profiles where slug is not null;

and exfiltrate every merchant's contact details and tax identifier from qr-setu-prod, which holds real user data. Under India's DPDP Act that is a personal-data breach, not merely a hardening gap.

Blast radius (measured, not assumed) ​

Of 45 SELECT policies lacking a TO clause, 29 are saved by an auth.uid() predicate that is null for anon. 16 tables are genuinely anonymous-readable; 15 of those are intentionally public — published menus, bio/setu pages, templates, business domains, subscription tiers — and were verified as deliberate. profiles was the only leak. But GRANT ALL … TO anon exists on 56 tables, so the next policy written without a TO clause becomes public instantly. The systemic problem is the standing over-grant, not the single policy.

Why the intended design was already correct — and bypassed ​

CLAUDE.md already bans supabase.from() in app code and requires public reads to go through SECURITY DEFINER RPCs precisely so that projections are explicit. The leak exists because the table was reachable directly with the anon key, which makes the application-layer rule unenforceable: an attacker never runs our code.

Decision ​

  1. Anonymous roles receive column-level grants only.REVOKE ALL ON <table> FROM anon, then GRANT SELECT (col, col, …) ON <table> TO anon naming every column. The column list is the security boundary and extending it requires an ADR amendment. Never GRANT SELECT on a whole table to anon.
  2. Every policy names its audience. TO anon / TO authenticated / TO service_role is mandatory. Relying on the PUBLIC default is prohibited.
  3. authenticated gets verbs, not ALL. Grant only the verbs actually used (typically SELECT, INSERT, UPDATE); rows remain gated by RLS. Do not rely on "no DELETE policy exists" — refuse it at the privilege layer too.
  4. Anonymous writes are exceptional and must be named. Today exactly one exists: digital_menu_qr_scan_analytics ("Public can insert scan analytics") for QR scan counting. Any addition needs an ADR amendment. This is why a blanket write-revoke across all tables is not the remediation.
  5. The end state for public reads is a SECURITY DEFINER RPC with an explicit projection (get_public_profile_by_slug), after which direct table access for anon is removed entirely. Column grants are the immediate, zero-breakage stopgap — not the destination.
  6. Two enforcement layers, because review demonstrably failed:
    • Static: tools/check-sql-grants.js (npm run check:sql, wired into backend-ci) fails any migration with GRANT ALL … TO anon or a policy lacking TO. The pre-remediation baseline dump is exempt by design — it is the subject of this ADR, and failing on it would force a permanent ignore that teaches everyone to distrust the gate.
    • Runtime: pgTAP asserts privileges, not rows — supabase/tests/database/profiles_anon_exposure_test.sql uses has_column_privilege() to prove anon cannot reach email/mobile_number/gstin/role, can still reach the public card fields, and that no policy on profiles applies to PUBLIC. A row-level test would pass while the table was wide open, which is exactly the trap here.

Options considered ​

A — Column-level grants now, RPC migration next · [adopted] ​

  • For: closes a live exposure in a single migration with zero frontend breakage (verified: the only anonymous reader in the tree, useStatusBanner.js, selects id, business_hours — both on the allow-list); the privilege system rejects a bad query before RLS is consulted; leaves the cleaner RPC end-state intact.
  • Against: column grants are easy to overlook in review — mitigated by the pgTAP privilege test.

B — Drop the public policy immediately, move all public reads to an RPC ​

  • For: the correct end state in one step.
  • Against: breaks any live anonymous consumer the moment it ships, and consumers must change first. Right destination, wrong first move for a live exposure.

C — Tighten the row predicate (e.g. add is_published = true) ​

  • Against: does nothing about columns — the actual defect — and silently changes which cards render publicly, which is a product decision. Rejected as a security measure.

D — Rely on the from() ban plus RPC discipline ​

  • Against: application-layer rules cannot bind an attacker holding the anon key. Rejected.

Consequences ​

  • Migration 20260725173747_restrict_anon_profile_columns.sql revokes the blanket grants, grants the reviewed public column allow-list to anon, narrows authenticated to SELECT, INSERT, UPDATE, and recreates the policy with an explicit TO anon, authenticated. Must be promoted to both projects (Dev → Prod) per the promotion runbook — Prod is where the exposed data lives, so this is not a Dev-first-then-whenever change.
  • The public column allow-list is now a reviewed artefact. Adding email, mobile_number, gstin, pin_code, role, permissions, attributes, subscription_tier or the reminder_* columns to it re-opens the breach.
  • ✅ Done 2026-07-26 — 20260726093952_least_privilege_anon_all_tables.sql + 20260726095831_get_public_profile_by_slug_rpc.sql. The remaining 57 tables' GRANT ALL … TO anon (the true count, measured — not the 55 estimated when this ADR was first written) are narrowed: the ALTER DEFAULT PRIVILEGES root cause is closed first, then anon is re-granted SELECT-only on the 15 tables that back public pages, plus INSERT-only (never SELECT) on digital_menu_qr_scan_analytics for QR scan counting. Two holes that were actively exploitable (not merely defence-in-depth gaps, like profiles before it) are closed: digital_menu_item_favorites (FOR ALL USING (true) + a blanket grant — any anonymous caller could read, write and delete every row) and vw_public_page_ops_cron_job_status (an ungoverned view exposing internal cron/ops state). get_public_profile_by_slug ships alongside as the intended SECURITY DEFINER end state, though the column grant on profiles is not yet retired — that is gated on migrating legacy/.../useStatusBanner.js and any other direct anon reader onto the RPC first. Full detail: dev-tracker.
  • Disclosure assessment is required, not optional. Whether the exposure was exercised is a log question, and the DPDP notification obligation follows from the answer. Recorded in the dev-tracker with a timeline.
  • The pgTAP privilege-test pattern (has_column_privilege) becomes the template for every future public-facing table.
  • ADR-0006 — the authorization model this defends in depth.
  • supabase/docs/PROMOTION_RUNBOOK.md — Dev → Prod promotion + parity verification SQL.
  • CLAUDE.md — "Migrations & DB rules" (RLS on every table) and the supabase.from() ban.