# Sample Supabase Grants/RLS Report

Fictional project: `Acme Notes Launch`

This sample shows the report shape for one redacted Supabase launch packet. It can be a policy/grant excerpt, Security Advisor function warning, signup trigger failure note, or RPC/view snippet. It is not a security certification, penetration test, legal opinion, compliance review, or incident response.

## Executive Summary

The pasted launch packet suggests a Data API permission failure was being fixed with a broad grant plus a permissive read policy. A second redacted note shows a signup trigger failure around `public.handle_new_user` and a Security Advisor `Function Search Path Mutable` warning on the same function. The immediate launch risk is not only that signup or browser reads fail; it is that quick fixes may widen role access or leave a privileged trigger hard to reason about before users touch real data.

Recommended launch decision: **pause public launch until the grant is narrowed, the trigger patch is smoke-tested, and the role matrix passes.**

## Findings

### High: `grant_missing_for_data_api`

Evidence: the redacted notes mention `42501 permission denied for table project_notes`, but the first migration excerpt did not show a narrow role grant for the table.

Why it matters: missing grants can look like broken RLS. Changing policy logic before fixing the role privilege can hide the real failure mode.

Fix direction:

```sql
-- Review exact operation first. Example shape only.
grant select on table public.project_notes to authenticated;
```

Regression check: reproduce the exact browser Data API call as `authenticated` after the grant, then confirm rows outside the user or tenant boundary are still denied by RLS.

### High: `broad_grant_quick_fix`

Evidence: the proposed quick fix grants all operations to both `anon` and `authenticated`.

Why it matters: broad grants make every matching policy more dangerous. If a permissive policy exists now or is added later, the role can reach too much.

Fix direction: split privileges by operation and role. For a private notes table, `anon` usually should not receive table privileges at all.

Regression check: unauthenticated request returns no rows and no write access; anonymous sign-in request is tested separately from a real upgraded account.

### High: `policy_allows_every_matching_role`

Evidence: `using (true)` appears in a temporary read policy.

Why it matters: this authorizes every row reachable by the granted role. RLS is technically enabled, but the policy does not enforce ownership, membership, tenant scope, or published-content state.

Fix direction:

```sql
create policy notes_owner_read
on public.project_notes
for select
to authenticated
using (
  auth.uid() is not null
  and owner_id = auth.uid()
);
```

Regression check: user A cannot read user B rows through the Data API, even when the table grant exists.

### Medium: `authenticated_policy_anonymous_boundary_missing`

Evidence: the policy targets `authenticated`, but the packet does not describe whether Supabase anonymous sign-in is enabled or whether the `is_anonymous` claim is checked.

Why it matters: anonymous sign-ins use the authenticated role. Sensitive writes or membership transitions can accidentally authorize temporary users.

Fix direction: if anonymous sign-in is enabled, add a separate test column for anonymous-session behavior and require non-anonymous identity for sensitive writes.

Regression check: fresh anonymous session cannot accept invites, change ownership, edit billing state, or join private teams unless that behavior is explicitly intended.

### High: `signup_trigger_side_effect_failure`

Evidence: the redacted auth note mentions `Database error saving new user` after a change to `public.handle_new_user`.

Why it matters: Supabase auth user creation can roll back when a trigger side effect fails. The fix should identify the failing insert, constraint, ownership, permission, stale trigger reference, or metadata assumption before widening grants.

Fix direction:

```sql
-- Example shape only. Keep exact table/column names redacted in intake.
create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
begin
  insert into public.profiles (id)
  values (new.id);
  return new;
end;
$$;
```

Regression check: create one non-sensitive test signup, confirm the profile row exists, and record only redacted evidence: trigger name, function config, and pass/fail result.

### Medium: `function_search_path_mutable`

Evidence: the Security Advisor packet flags the signup helper function for mutable `search_path`.

Why it matters: adding `set search_path` inside the function body is not the same as configuring it on the function declaration. A mutable function search path can make function behavior depend on caller or session state.

Fix direction: set the function-level `search_path`, schema-qualify object references, and rerun Security Advisor. If a non-empty search path is intentionally required for extension/operator behavior, document the reason and keep an explicit regression check.

Regression check: confirm `pg_proc.proconfig` or the advisor rerun shows the intended function-level setting, then rerun the signup smoke test.

## Role Matrix To Run

| Scenario | Expected result |
| --- | --- |
| No session, browser Data API request | No private rows and no writes |
| Anonymous sign-in session | Only explicitly public or onboarding-safe rows |
| Upgraded authenticated account | Own rows only |
| Different authenticated account | Denied for other user or tenant rows |
| Signup trigger smoke test | Auth insert succeeds and expected profile side effect is created |
| Direct trigger helper execution by browser-facing roles | Denied unless intentionally callable and separately reviewed |
| Server-only service role path | Used only behind trusted server code, never as a browser workaround |

## Safe Intake Boundary

The paid fixed-scope report should receive only redacted SQL/policy snippets, redacted table names if needed, Security Advisor warning text, and non-sensitive error summaries. Do not send secrets, private connection strings, real user data, payment records, private screenshots, full names, private handles, credential values, or full transaction identifiers.

## Approval Recommendation

Ship only after the narrow grant, RLS ownership checks, signup trigger smoke test, and Security Advisor rerun pass. Do not treat a green dashboard RLS indicator or a successful local signup as proof that all role boundaries protect real user data.

This report is a planning aid for one redacted Supabase launch-risk packet.