supabase-rls-always-true-no-policy-investigation-4665

Supabase RLS investigation — rls_policy_always_true + rls_enabled_no_policy (#4665)

Date: 2026-07-30 Project: mwkqmgadqnkkihjdeqsi (shared p24 Supabase Postgres 17.6) Method: direct catalog + pg_stat_statements inspection as postgres via the bms-4 pooler (docs/playbooks/supabase-migrations.md Method B), plus live REST probes with the public publishable (anon) key. Same probe-first method as pr4646 and #4663 — measure real consumers, do not grep-and-guess. Related: #4645 (RLS disabled on 7 tables), #4647 (23 definer views — closed, complete), #4663 (revoke anon EXECUTE)

---

Headline — the “correct by design” hypothesis is FALSE for the always_true class

The issue hypothesised that both advisor patterns were probably intentional: internal agent_*/audit.* tables where only service_role holds a grant, so USING (true) or policy-less RLS is harmless. That hypothesis is confirmed for rls_enabled_no_policy and disproved for rls_policy_always_true.

anon does not lack grants. Supabase’s default privileges granted anon full DML on almost every public table, and 81 of those tables also carry a permissive USING (true) policy that names anon or PUBLIC. The anon role is reached by the publishable key, which is public by design — NEXT_PUBLIC_SUPABASE_ANON_KEY is shipped in the et-operational-platform browser bundle on Vercel.

Measured, live, reproduced from an unauthenticated HTTP client:

Measurement	Value
public tables anon-reachable with an always-true policy + a grant	81
…of those, anon holds DELETE	79
…of those, currently holding data	37
Rows readable by anon across those 37	98,448
Tables where anon sees 100 % of rows	37 of 37
Tables with a measured production anon consumer	4

Confirmed by REST probe (apikey: <publishable>, no Authorization header, HTTP 200 + rows): pin_login_attempts, user_roles, profiles, agent_sessions.

Write access confirmed at the DB level inside a rolled-back transaction:

SET LOCAL ROLE anon; DELETE FROM public.p24_car_param_categories;
→ ROW_COUNT = 9 of 9   (ROLLBACK; post-rollback count = 9 — production untouched)
SET LOCAL ROLE anon; INSERT INTO public.p24_car_param_categories DEFAULT VALUES;
→ rejected only by a NOT NULL constraint (23502), not by RLS — a valid payload would be accepted

Exposed content includes PII and security-relevant tables: profiles (52 rows: email, full_name, phone), user_roles (59), n8n_chat_histories (390 — WhatsApp message bodies), p24_l_cars_positions (259 — GPS), proc_log (93,727), plus ai_api_keys, share_tokens, login_attempts, pin_login_attempts, audit_logs, p24_employes, p24_l_drivers (all currently empty, but equally readable the moment a row lands).

Per the issue’s own instruction, no fix ships from this issue. The remediation is batched into a follow-up (see Recommended remediation). This document is the categorized report that item 3 of the issue asked for.

---

Advisor-count reconciliation

Advisor rule	Advisor count	Catalog-verified	Match
rls_enabled_no_policy	11	11 (4 audit.* + 7 public.*)	exact
rls_policy_always_true	169	272 policies / 149 distinct tables in public with polqual = 'true'; 209 of those policies name anon/authenticated/PUBLIC	approximate

The 169 does not reproduce under any aggregation tried (per-policy 272, per-policy-restricted-to- anon/authenticated 209, distinct tables 149, distinct table×role pairs 188, SELECT/ALL-cmd only 138). Treat the advisor number as a lagging, differently-aggregated snapshot; the catalog numbers below are the authoritative ones. The exact match on rls_enabled_no_policy confirms the sweep query is sound.

public.dev_r_supabase_advisor_findings currently holds zero rows for either rule name, so the weekly advisor audit has not yet registered them — the tracking gap is noted, not fixed here (that table is owned by the weekly-supabase-advisor-audit skill).

---

Categories

Category 1 — REAL GAP: anon-reachable, USING (true) (81 tables) → needs a real fix

RLS enabled, a permissive always-true policy applies to anon or PUBLIC, and anon holds at least one privilege. Internet-reachable via PostgREST with the public publishable key.

Split by measured consumer (pg_stat_statements, role = anon):

Sub-category	Tables	Action
1a — ≥ 20 measured anon calls: real production consumer	4	Preserve access until the caller moves to service_role; revoke second
1b — 1–19 measured anon calls: ad-hoc/exploratory, no production consumer	13	Revoke anon; the calls are themselves misuse
1c — zero measured anon calls	64	Revoke anon — provably non-breaking on the measured window

1a, the only four with real traffic:

Table	anon privileges	calls	What it is
p24_wap_pipeline_events	INSERT only	1659	Deliberate anon-INSERT telemetry sink (WhatsApp-Android puller). SELECT is not granted — this is the one correctly-designed anon pattern in the set.
p24_l_cars	SELECT INSERT UPDATE DELETE	25	Fleet vehicle list, read with embedded p24_l_cars_pallets
p24_l_cars_metrics_last_upd	SELECT INSERT UPDATE DELETE	20	Metrics watermark
p24_l_cars_positions	SELECT INSERT UPDATE DELETE	20	GPS positions

Note even 1a’s three p24_l_cars* tables grant anon full DML where a read is all the caller does.

1b — the sensitive tables land here. 1–15 calls is not a production consumer; several are almost certainly a caller that sent only apikey: <publishable> and silently got the anon role: agent_tasks (15), agent_sessions (11, incl. UPDATE), profiles (10), p24_l_cars_service_requests (7), user_roles (7), p24_l_cars_service (5), p24_l_cars_dispatch_centers (2), ai_api_keys (1), login_attempts (1), p24_employes (1), p24_l_drivers (1), pin_login_attempts (1), share_tokens (1).

The worker fleet is not at risk from revoking anon on agent_*: SUPA_HB_KEY was verified to resolve to service_role (it reads public.app_config, which is RLS-enabled with zero policies, so only a bypassrls role returns a row). dev_r_worker_queue already refuses the publishable key outright (HTTP 401 / 42501) — it is the model of a correctly-locked table in this database.

Full 81-row table in Appendix A.

Category 2 — authenticated-only USING (true) (37 tables) → needs design, not a blanket fix

Always-true policy applies to authenticated but not anon/PUBLIC. Not internet-reachable without a valid login, so this is a horizontal-access question, not an open door: any logged-in et-operational-platform user reads every row regardless of office/workspace.

Whether that is a gap depends on the app’s tenancy model, and the presence of organizations, workspaces, workspace_users and office_id/workspace_id columns says the model is not flatly single-tenant. Deciding it needs per-table access-pattern measurement against the real app — exactly the work the issue says must not be short-cut with a blanket policy. Deferred to a follow-up with a design step, no migration proposed here.

Tables: audit_logs, document_flow_configs, documentation_versions, inspection_orders, inspection_sticker_events, migration_log, p24_atrax_daily_reports, p24_atrax_driver_changes, p24_car_handover_required, p24_car_inspections, p24_dictionaries, p24_fleet_positions_history, p24_geofence_events, p24_geofences, p24_gps_current_state, p24_gps_daily_stats, p24_gps_events, p24_gps_fuel_hourly, p24_gps_notifications, p24_l_cars_atrax, p24_l_cars_service_notes, p24_l_cars_status_reports, p24_l_cars_tech_alerts, p24_l_emails_inbox_audit, p24_l_invoices, p24_l_invoices_positions, p24_notifications, p24_tacho_download_log, p24_vehicle_maintenance_baselines, p24_wap_plan_limits, p24_whatsapp_threads, pending_document_actions, platform_inboxes, scanner_heartbeat, server_mode_status, server_schedules, vehicle_documents.

Category 3 — ACKNOWLEDGED SAFE: always-true for trusted roles only → no action

The always-true policy names only service_role and/or grafana_readonly; anon/authenticated get no policy from it at all.

Scope	Always-true policies	Why safe
devops.* (18 tables)	18 × service_role_all + 18 × grafana_readonly_select	Exactly the model #4647 verified: service_role has rolbypassrls, grafana_readonly is a trusted internal DB login not reachable over PostgREST. No anon/authenticated policy.
public.*	34 × service_role, 32 × grafana_readonly	Same reasoning

Document as acknowledged, no action — not silently ignored. This is the subset the issue’s hypothesis correctly described.

Category 4 — ACKNOWLEDGED SAFE: pgmq (25 tables) → no action

All 25 pgmq.* queue tables have RLS enabled with an always-true policy TO anon, and anon holds full DML. However the pgmq schema is not exposed to PostgREST — verified live:

GET /rest/v1/q_wa_ingest       → HTTP 404 PGRST205
GET /rest/v1/pgmq.q_wa_ingest  → HTTP 404 PGRST205

No publishable-key path exists to these tables, so the grant+policy pair is unreachable. Extension- managed objects; leave them alone.

Category 5 — rls_enabled_no_policy (advisor’s 11) → 10 acknowledged, 2 flagged as a functional bug

Fails closed: RLS on with no policy returns 0 rows silently for any non-bypassrls role.

audit.* (4 tables — actions, projects, runs, workbooks): the issue’s hypothesis, confirmed. anon and authenticated hold no grant at all (has_table_privilege = false on both), only service_role does, and audit is not a PostgREST-exposed schema. Two independent barriers. Acknowledged, no action.

public.* (7 tables): anon and authenticated do hold grants here, but with zero policies they read 0 rows — measured:

Table	rows visible to anon	total rows	anon/authenticated calls	Verdict
app_config	0	1	0	acknowledged — fails closed, no consumer
maintenance_log	0	1	0	acknowledged
telegram_inspection_messages	0	6	0	acknowledged
telegram_inspection_sessions	0	11	0	acknowledged
thread_counter	0	1	0	acknowledged
p24_vehicle_driver_history	0	0	15	⚠️ consumer reads 0 rows
p24_vehicle_parking_history	0	0	12	⚠️ consumer reads 0 rows

The last two have real anon/authenticated traffic against a table that can only ever return nothing. Both are currently empty so the symptom is masked; once rows land, the caller keeps seeing zero. That is a functional bug, not a security gap — logged for the follow-up, not fixed here.

Not in scope: the zero-policy tables in auth (16), storage (7) and realtime (1). All Supabase-managed with no anon/authenticated grants (except the storage tables that Supabase governs through its own API); the advisor does not count them in the 11.

---

Root cause

ALTER DEFAULT PRIVILEGES on this project grants anon full DML on new public tables, and the prevailing table-creation habit adds a USING (true) policy naming anon or PUBLIC to satisfy “RLS enabled” without expressing an actual access rule. Every new table therefore ships world-readable/writable by default. Closing the 81 current tables without changing the default privileges means the next migration re-opens the hole.

Scope decision — #4647

Keep fully split. Nothing to fold in. #4647 is closed and verified complete: all 23 definer views were flipped to security_invoker = true (PR #4648, migration 20260730080917_rls_hardening_definer_views_4647.sql, applied by run 30525745550, catalog sweep re-verified at 0). Its base tables are devops.*, which fall into Category 3 here — always-true policies for service_role/grafana_readonly only, no anon/authenticated policy — so they are acknowledged-safe under this investigation and need no further work. There were never “22 remaining views”; that framing predates #4647’s completion.

Recommended remediation

Ordered by risk-adjusted value. Item 1 is the whole security fix and is provably non-breaking on the measured window.

1. Revoke anon on Category 1b + 1c (77 tables). REVOKE ALL ON public.<t> FROM anon; — nothing else. A missing privilege denies access (42501) regardless of policy, so this closes the hole without touching any policy that authenticated also depends on. Do not drop the PUBLIC-role policies: they serve authenticated too. Anon-only policies become inert and can be dropped in the same migration to clear the advisor finding.
2. Category 1a (4 tables): migrate the caller to service_role first, then revoke. Keep p24_wap_pipeline_events’s INSERT-only grant — it is the intended design. Narrow the three p24_l_cars* tables to SELECT immediately (they need no write path) as a cheap partial fix.
3. Fix the default privileges so new tables do not inherit anon DML — otherwise item 1 regresses on the next migration.
4. Category 2 (37 tables): separate design issue — measure per-table access patterns against et-operational-platform and write tenant-scoped policies. No blanket policy.
5. p24_vehicle_driver_history / p24_vehicle_parking_history: decide whether the measured consumer is legitimate; if so add a policy, if not revoke the grant. Either way stop the silent 0-row read.
6. Register both advisor rules in dev_r_supabase_advisor_findings with the acknowledged/needs-fix status from this report so the weekly audit stops re-surfacing the Category 3/4/5 rows as new.

Reproducing this investigation

export PGPASSWORD=$(grep -m1 '^SUPABASE_DB_PASSWORD=' /opt/p24-infra/monitoring/.env | cut -d= -f2- | tr -d '"')
export PGSSLMODE=require
psql -h 54.36.123.110 -p 15432 -U postgres.mwkqmgadqnkkihjdeqsi -d postgres   # 127.0.0.1 on bms-4

The Category 1 sweep (always-true policy applies to anon ∧ anon holds a grant):

WITH at AS (
  SELECT p.polrelid FROM pg_policy p
  WHERE p.polpermissive
    AND coalesce(pg_get_expr(p.polqual, p.polrelid), 'true') = 'true'
    AND coalesce(pg_get_expr(p.polwithcheck, p.polrelid), 'true') = 'true'
    AND (p.polroles = '{0}'::oid[]
         OR EXISTS (SELECT 1 FROM unnest(p.polroles) r WHERE pg_get_userbyid(r) = 'anon'))
  GROUP BY 1
)
SELECT c.relname,
       has_table_privilege('anon', c.oid, 'SELECT') AS anon_select,
       has_table_privilege('anon', c.oid, 'DELETE') AS anon_delete
FROM at JOIN pg_class c ON c.oid = at.polrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relrowsecurity AND n.nspname = 'public'
  AND (has_table_privilege('anon', c.oid, 'SELECT')
       OR has_table_privilege('anon', c.oid, 'INSERT')
       OR has_table_privilege('anon', c.oid, 'UPDATE')
       OR has_table_privilege('anon', c.oid, 'DELETE'))
ORDER BY 1;

Two gotchas that produce a false all-clear:

• LEFT JOIN (… count(*) npol …) ON polrelid = c.oid then WHERE npol = 0 never matches — a table with no policies yields NULL, not 0. Use coalesce(npol, 0) = 0.
• polroles = '{0}'::oid[] means PUBLIC. Filtering only for a literal anon OID misses every TO PUBLIC policy, which is 41 of the 81 tables here.
• DELETE … WHERE false is not a valid RLS probe: with RLS on and no applicable policy Postgres deletes 0 rows silently instead of erroring, so “no error” proves only the privilege. Use a real DELETE inside BEGIN … ROLLBACK and read ROW_COUNT.

Consumer measurement (pg_stat_statements survives as long as the instance is not restarted, so treat a zero as “no consumer in the retained window”, not “never”):

SELECT s.calls, left(regexp_replace(s.query, '\s+', ' ', 'g'), 150)
FROM pg_stat_statements s JOIN pg_roles r ON r.oid = s.userid
WHERE r.rolname = 'anon' ORDER BY s.calls DESC;

---

Appendix A — Category 1 full list

policy roles = which grantee the always-true policy names. measured anon calls from pg_stat_statements at 2026-07-30.

Table	always-true policy applies to	anon privileges	measured anon calls	Verdict
p24_wap_pipeline_events	anon	INSERT	1659	1a — preserve, needs app migration
p24_l_cars	anon	SELECT INSERT UPDATE DELETE	25	1a — preserve, needs app migration
p24_l_cars_metrics_last_upd	PUBLIC	SELECT INSERT UPDATE DELETE	20	1a — preserve, needs app migration
p24_l_cars_positions	anon	SELECT INSERT UPDATE DELETE	20	1a — preserve, needs app migration
agent_tasks	PUBLIC	SELECT INSERT UPDATE DELETE	15	1b — revoke
agent_sessions	PUBLIC	SELECT INSERT UPDATE DELETE	11	1b — revoke
profiles	anon+PUBLIC	SELECT INSERT UPDATE DELETE	10	1b — revoke
p24_l_cars_service_requests	PUBLIC	SELECT INSERT UPDATE DELETE	7	1b — revoke
user_roles	anon	SELECT INSERT UPDATE DELETE	7	1b — revoke
p24_l_cars_service	anon+PUBLIC	SELECT INSERT UPDATE DELETE	5	1b — revoke
p24_l_cars_dispatch_centers	anon+PUBLIC	SELECT INSERT UPDATE DELETE	2	1b — revoke
ai_api_keys	PUBLIC	SELECT INSERT UPDATE DELETE	1	1b — revoke
login_attempts	PUBLIC	SELECT INSERT UPDATE DELETE	1	1b — revoke
p24_employes	anon	SELECT INSERT UPDATE DELETE	1	1b — revoke
p24_l_drivers	anon	SELECT INSERT UPDATE DELETE	1	1b — revoke
pin_login_attempts	anon+PUBLIC	SELECT INSERT UPDATE DELETE	1	1b — revoke
share_tokens	anon	SELECT INSERT UPDATE DELETE	1	1b — revoke
agent_errors	PUBLIC	SELECT INSERT UPDATE DELETE	0	1c — revoke
agent_task_metrics	PUBLIC	SELECT INSERT UPDATE DELETE	0	1c — revoke
ai_job_events	PUBLIC	SELECT INSERT UPDATE DELETE	0	1c — revoke
ai_jobs	PUBLIC	SELECT INSERT UPDATE DELETE	0	1c — revoke
ai_models	PUBLIC	SELECT INSERT UPDATE DELETE	0	1c — revoke
ai_rate_limits	PUBLIC	SELECT INSERT UPDATE DELETE	0	1c — revoke
ai_usage	PUBLIC	SELECT INSERT UPDATE DELETE	0	1c — revoke
debug_log	anon	SELECT INSERT UPDATE DELETE	0	1c — revoke
dev_r_ops_events	PUBLIC	SELECT INSERT UPDATE DELETE	0	1c — revoke
dev_r_orchestrator_runs	PUBLIC	SELECT INSERT UPDATE DELETE	0	1c — revoke
documents	anon	SELECT INSERT UPDATE DELETE	0	1c — revoke
n8n_chat_histories	anon	SELECT INSERT UPDATE DELETE	0	1c — revoke
organizations	anon	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_addressbook	anon	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_atrax_drivers_daily	PUBLIC	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_car_group_assignments	anon	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_car_param_categories	anon	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_car_param_defs	anon	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_car_param_group_values	anon	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_car_param_groups	anon	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_company_documents	anon	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_dispo_driver_card_log	PUBLIC	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_dispo_route_assignments	PUBLIC	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_dispo_route_changes	PUBLIC	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_dispo_route_changes_log	PUBLIC	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_dispo_route_login_confirmations	PUBLIC	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_dispo_route_logins	PUBLIC	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_dispo_routes	anon+PUBLIC	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_dispo_schedule	anon	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_dispo_schedule_history	anon	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_dispo_vehicle_acceptance_reports	PUBLIC	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_dispo_vehicle_current_state	PUBLIC	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_driver_ecodriving_daily	PUBLIC	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_files	anon	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_forms	anon	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_l_cars_insurance	anon	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_l_cars_metrics	PUBLIC	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_l_cars_sensors	anon	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_l_cars_service_requests_items	anon	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_l_cars_service_usedparts	anon	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_l_cars_service_workcost	anon	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_l_cars_tech_check	anon	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_l_dispatchers	anon	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_l_driver_vehicle_sessions	PUBLIC	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_l_events	anon	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_l_service_checklist_templates	PUBLIC	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_l_service_checklists	anon+PUBLIC	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_l_service_checklists_tpl	anon+PUBLIC	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_l_vehicle_notes	PUBLIC	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_l_warehouse_inventory	PUBLIC	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_offices	anon	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_pinbox_exports	PUBLIC	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_register_records	anon	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_registries	anon	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_vehicle_config	PUBLIC	SELECT INSERT UPDATE DELETE	0	1c — revoke
p24_wap_ingest_queue	anon	INSERT	0	1c — revoke
proc_log	anon	SELECT INSERT UPDATE DELETE	0	1c — revoke
regrecord	anon	SELECT INSERT UPDATE DELETE	0	1c — revoke
telegram_bot_sessions	anon	SELECT INSERT UPDATE DELETE	0	1c — revoke
test_runs	PUBLIC	SELECT INSERT UPDATE DELETE	0	1c — revoke
user_notifications	anon	SELECT INSERT UPDATE DELETE	0	1c — revoke
workers	anon+PUBLIC	SELECT INSERT UPDATE DELETE	0	1c — revoke
workspace_users	anon	SELECT INSERT UPDATE DELETE	0	1c — revoke
workspaces	anon	SELECT INSERT UPDATE DELETE	0	1c — revoke

---

Investigation for #4665. Worker: i1-cw-1, vps-i1.