supabase-rpc-functions

Files (1)

• SKILL.md

SKILL.md (first lines)

# Supabase RPC Functions

> Defends against **OWASP ASI02 (Tool Misuse)** -- specifically, the
> case where a Supabase RPC function with `SECURITY DEFINER` privileges
> becomes a privileged backdoor that bypasses the row-level security
> policies the application otherwise relies on. Supabase exposes any
> Postgres function in the `public` schema as an HTTP RPC endpoint via
> PostgREST; that exposure surface is BROADER than most authors
> appreciate. Without careful authoring, an RPC intended for internal
> server-side use becomes callable by any authenticated client.

**Tradeoff:** RPC functions are powerful for encapsulating multi-step
operations as atomic Postgres transactions. The cost: every
`SECURITY DEFINER` function is a privilege-escalation gateway whose
inputs and authorization must be explicitly defended. RLS protects
tables; RPCs sit outside RLS unless authored carefully.

---

## Why this matters

PostgREST's default behavior:

- Every function in the `public` schema is exposed as an HTTP endpoint.
- Functions inherit the caller's role unless declared `SECURITY DEFINER`.
- `SECURITY DEFINER` functions run with the function-creator's
  privileges, which means an `auth.uid()`-aware function written by
  a superuser-equivalent admin executes WITH ADMIN PRIVILEGES even
  when called by an anonymous user.
- `search_path` for `SECURITY DEFINER` functions is the calling
  session's `search_path` by default -- a malicious client can prepend
  a schema they control, hijacking unqualified table references inside
  the function body.

The combination "exposed by default + privileged execution + caller-
controlled search_path" is the canonical Postgres RPC vulnerability
class. The patterns below close each piece.

---

## Pattern 1 -- `SECURITY DEFINER` with explicit `SET search_path`

When a function MUST run with creator privileges (typical case:
crossing a security boundary like updating a counter on behalf of a
user who can read but not directly modify the table), declare
`SECURITY DEFINER` AND pin the `search_path`:

```sql
CREATE OR REPLACE FUNCTION public.increment_post_view_count(p_post_id uuid)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
BEGIN
  -- All table references are now resolved against public + pg_temp,
  -- regardless of what the caller's session search_path is.
  UPDATE public.posts
    SET view_count = view_count + 1
    WHERE id = p_post_id;
END;
$$;
```

`SET search_path = public, pg_temp` pins the schema resolution at
function-definition time. `pg_temp` last is the canonical safe pattern --
temporary objects the caller might create can't shadow the public
schema's tables.

A function declared `SECURITY DEFINER` WITHOUT `SET search_path` is a
critical defect.

---

## Pattern 2 -- `REVOKE EXECUTE FROM public` after creating the function

PostgreSQL grants `EXECUTE` on functions to `PUBLIC` (every role) by
default, and Supabase's default privileges also grant `EXECUTE` on new
functions in `public` directly to `anon`, `authenticated`, and
`service_role`. Revoking from `PUBLIC` alone therefore leaves `anon`
and `authenticated` able to call the function. Revoke from `PUBLIC`,
`anon`, and `authenticated`, then grant back only to the role(s) that
should call it:

```sql
-- Create the function.
CREATE OR REPLACE FUNCTION public.admin_grant_role(p_user_id uuid, p_role text)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$ ... $$;

-- Immediately revoke the Postgres default (PUBLIC) and Supabase's direct grants.
REVOKE EXECUTE ON FUNCTION public.admin_grant_role(uuid, text) FROM public, anon, authenticated;

-- Grant only to the role(s) that should call it. An admin operation is service-role-only:
GRANT EXECUTE ON FUNCTION public.admin_grant_role(uuid, text) TO service_role;

-- A function any signed-in user may call (scoped inside by auth.uid()) would instead get:
-- GRANT EXECUTE ON FUNCTION public.some_user_function(...) TO authenticated;
```

The `REVOKE ... FROM public, anon, authenticated` is the load-bearing
step. Without it, the `anon` role (unauthenticated users) can call the
function. PostgREST
exposes the endpoint; clients hit it; the SECURITY DEFINER body runs
with admin privileges on behalf of an unauthenticated attacker.

---

## Pattern 3 -- Defensive JSON-arg parsing for RPCs accepting JSONB

RPCs that accept a JSONB argument must parse the JSON defensively
inside the function body. A client can pass any shape; the function's
parameter type is just `jsonb`, not a struct contract.

```sql
CREATE OR REPLACE FUNCTION public.bulk_update_settings(p_settings jsonb)

From MILTONADINA/DevOPs, MIT.

More from MILTONADINA/DevOPs