# Supabase `SECURITY DEFINER` function audit

Project: `BUDDY` (`tkrxmdonmxpeockpbazl`)<br>
Audit date: 2026-08-12<br>
Remediation verification: 2026-08-17<br>
Function ACL result: **PASS**<br>
Overall security result: **HOLD — additional advisor findings remain open**

The original audit identified 34 `SECURITY DEFINER` functions requiring
fail-closed RPC permissions. Live verification on 2026-08-17 measured the
corrected state: `anon` can execute 0 of 34, `authenticated` can execute exactly
1 of 34 (`create_message_thread`), and `service_role` can execute all 34.
Future `postgres`-owned functions in `public` now default to no execution grant
for `PUBLIC`, `anon`, or `authenticated`.

## Remediation receipt

| Measurement | Verified value |
|---|---:|
| Audited functions | 34 |
| Anonymous executable | 0 |
| Authenticated executable | 1 |
| Unexpected authenticated executable | 0 |
| Service-role executable | 34 |
| Default browser-role function grants | Revoked |

Applied migration: `lock_security_definer_rpc_execution`.

## Individual decisions

| Function | Observed behavior | Decision |
|---|---|---|
| `activity_stream(integer)` | Reads cross-platform receipt/decision events; no caller ownership check | Service-only pending a redacted owner-scoped view |
| `add_creator_as_thread_owner()` | Trigger inserts the creator into membership | Revoke RPC access; trigger remains functional |
| `broadcast_receipt_changes()` | Trigger broadcasts old/new receipt rows | Revoke RPC access; separately review realtime topic authorization |
| `broadcast_thread_message_changes()` | Trigger broadcasts message rows | Revoke RPC access; separately review topic authorization |
| `broadcast_thread_reads_changes()` | Trigger resolves thread and broadcasts read rows | Revoke RPC access; separately review topic authorization |
| `buddy_bring_online(text)` | Reads node/gateway status without caller ownership | Service-only |
| `buddy_certify_capacity(...)` | Mints certification data from caller-supplied measurements | Service-only until evidence authorization is bound |
| `buddy_certify_outcome(...)` | Mints outcome certification from caller JSON | Service-only until evidence authorization is bound |
| `buddy_certify(text,text)` | Produces certification from internal node evidence | Service-only |
| `buddy_consent_summary(text,text)` | Reads plan material by caller-supplied identity | Service-only until identity is derived from authenticated context |
| `buddy_consent(uuid,boolean)` | Mutates plan status without `auth.uid()` ownership check | Service-only; high priority |
| `buddy_intent(text,text)` | Builds plans for caller-supplied owner identity | Service-only until owner binding is added |
| `buddy_observe(uuid)` | Reads and mutates observation/certification state without ownership check | Service-only |
| `buddy_outcome_receipt(text,text)` | Creates a receipt from node state without ownership check | Service-only |
| `buddy_plan(text,text,text[])` | Creates plans and hashes typed actions without caller ownership check | Service-only |
| `buddy_reproducible_receipt(text,text)` | Reads system/node evidence and creates receipt material | Service-only |
| `buddy_voice(text,text,text)` | Routes queries by supplied owner/node identity | Service-only until authenticated ownership is enforced |
| `create_message_thread(text,uuid[])` | Checks `auth.uid()` and creates a user-owned thread | Authenticated only; revoke anonymous/PUBLIC |
| `deployment_receipt(text)` | Reads node state and emits deployment receipt material | Service-only |
| `fabric_real_nodes()` | Exposes fleet inventory and gate state | Service-only |
| `mint_provider_receipt(...)` | Hashes caller input/output and writes provider receipt | Service-only; high priority |
| `node_fetch_config(text)` | Returns node configuration using a supplied token | Service-only; replace direct RPC with an authenticated Edge Function |
| `node_provision(text,text)` | Creates/updates node configuration and returns a token | Service-only; critical |
| `node_scoped_auth_check(text,text)` | Compares a supplied token to stored hash | Service-only; keep secret verification off public RPC |
| `platform_at(timestamptz)` | Reads historical platform snapshots without ownership boundary | Service-only |
| `record_governed_inference(...)` | Calls receipt minting with caller-supplied model activity | Service-only |
| `sail_emit_receipt(text)` | Writes SAIL receipt/decision state | Service-only |
| `sail_measured_state()` | Exposes platform-wide counts | Service-only pending explicitly public aggregate contract |
| `sail_next_action(text)` | Reads platform-wide state and computes next action | Service-only |
| `sail_platform_pulse()` | Exposes platform-wide operational counts | Service-only pending redaction decision |
| `sail_state()` | Exposes platform milestones and operational state | Service-only |
| `since_you_were_away(timestamptz)` | Exposes cross-platform changes since arbitrary time | Service-only |
| `take_platform_snapshot()` | Writes a privileged platform snapshot | Service-only; high priority |
| `verify_receipt(uuid)` | Reads and updates any receipt by supplied UUID, bypassing RLS | Service-only; critical; replace with owner-scoped invoker path |

## Applied correction

1. Revoke `EXECUTE` from `PUBLIC`, `anon`, and `authenticated` for all 34.
2. Re-grant `authenticated` only for `create_message_thread`, whose body checks
   `auth.uid()` and creates an owner-bound thread.
3. Preserve `service_role` execution for backend compatibility.
4. Do not expose trigger functions as RPC endpoints.
5. Reintroduce any additional end-user RPC only after it derives identity from
   `auth.uid()`, enforces row ownership, and has negative authorization tests.

This is deliberately fail-closed. It may reveal undocumented frontend
dependencies during branch testing; those dependencies must be made explicit
rather than preserving anonymous privileged execution.

Additional advisor findings remain open, so the overall screen remains HOLD:
leaked-password protection is disabled; multiple RLS-enabled tables have no
policies; `artifactgptm_set_updated_at` has a mutable search path; and `http`,
`pg_net`, and `pgjwt` are installed in `public`.