Auth

Auth, RLS & Multi-Tenancy

Authentication, authorization, and tenant isolation — all enforced by the database.

Security Model — four layers, each with one job

1. Schema isolation (the table boundary) — Only api.* functions are exposed to clients; public tables are unreachable via the Data API (.from() cannot touch them). This is what actually protects the tables.
2. RPC permission guard (the permission gate — PRIMARY) — Every mutating api.* RPC calls public.auth_verify_access('.') as its first statement; it raises HTTP 403 when the caller's active workspace lacks the permission. This is where permission/action authz lives.
3. RLS, isolation-only (the backstop) — Every table has a cheap policy scoping rows to tenant_id = _auth_tenant_id() and/or user_id = auth.uid(). It is the safety net against a forgotten WHERE — in an agent-built codebase, the worst-case multi-tenant bug. Never put permission checks in RLS — isolation only.
4. Frontend guard (UX only) — useHasPermission() / route guards hide or redirect. Never security: the backend guard is the real gate; a user who bypasses the UI still hits the 403.

Prerequisite under all of this — GRANTs (explicit, default-deny). api.* RPCs are SECURITY INVOKER, so they touch public tables as the caller, which needs a Postgres table grant just to access the table at all — a separate layer from RLS (grant = "can this role touch the table?"; RLS = "which rows?"). Supabase stopped auto-granting in 2026, and AgentLink keeps default-deny: every table you want reachable needs an explicit GRANT SELECT, INSERT, UPDATE, DELETE … TO authenticated, service_role (bundled with ENABLE ROW LEVEL SECURITY); an ungranted table stays private. anon is never granted on tables (anon-facing RPCs are SECURITY DEFINER). For a read-only table, grant SELECT to authenticated only. Functions are default-deny too, granted per object (no schema-wide GRANT ON ALL FUNCTIONS) — each function REVOKEs the built-in PUBLIC EXECUTE and GRANTs only its roles: api.* client RPCs → authenticated, service_role; api._admin_* → service_role; RLS helpers → authenticated. See the database skill's table-privileges rule and the rpc skill's Grants section.

Client → api.member_update(...)
            1. PERFORM auth_verify_access('membership.update')  → 403 if denied   (permission gate)
            2. UPDATE ... WHERE tenant_id = _auth_tenant_id()                      (explicit scope)
                 ↓ isolation RLS on memberships still filters by tenant            (backstop)

When you add a capability you touch three layers: declare the permission key (in supabase/database/rbac/), guard the RPC, gate the frontend. RLS only ever does isolation. See the checklist near the end of this file.

---

The identity-only model — how a workspace is resolved

The JWT proves identity only. It carries no tenant and no permissions. The active workspace is asserted per request by the client via an x-workspace-id header, validated server-side, and pinned into a transaction-local GUC that every helper reads. This is the canonical model — a fresh AgentLink scaffold is this. Nothing tenant-related lives in the token: there is no access-token claim hook, no per-device workspace-pin table, and no server-side "select workspace" RPC. If a project still has those objects, they're stale machinery to remove.

The request lifecycle:

1. The client attaches Authorization: Bearer (identity) and x-workspace-id: (active workspace) to every Data-API request.
2. PostgREST runs public._auth_pre_request() once per request — the db-pre-request hook, wired by ALTER ROLE authenticator SET pgrst.db_pre_request = 'public._auth_pre_request' (shipped in supabase/database/config/db_pre_request.sql). It validates that auth.uid() is a member of the asserted workspace, then set_config('request.tenant_id', , true) — transaction-local. No header → reset the GUC and return (deny by default). Non-member → RAISE … ERRCODE '42501' → HTTP 403.
3. public._auth_tenant_id() reads that GUC; RLS USING (tenant_id = _auth_tenant_id()) scopes every row; auth_verify_access() / _auth_has_permission() gate writes — both derive fresh from (auth.uid(), resolved workspace).
4. The client reads role + permissions from api.session_context() for the active workspace — not from the token. It returns { tenant_id, name, slug, role, permissions[] }, or an empty context when no workspace is asserted (fresh sign-in → render the workspace picker). Switching workspace = sending a different header + re-fetching session_context. No token re-mint, no session refresh.

The 8 security non-negotiables

Each is a hard rule — the places a bug leaks data across tenants — with the WHY. Do not relax them.

1. **Workspace context lives in a transaction-local GUC (set_config(…, true) / SET LOCAL), never a session GUC. A plain SET / false third arg persists on the pooled connection and bleeds one request's workspace into the next user's** request — the worst failure in the system, gated by a single boolean. This is user-A-reads-user-B.
2. One GUC is the single source of truth. Only _auth_pre_request writes request.tenant_id; everything else reads it via _auth_tenant_id(). If row-scoping and the permission guard resolved the workspace independently they could disagree (permission checked against A, rows scoped to B → confused-deputy write). Never read the header in app code.
3. The asserted workspace is membership-validated server-side. x-workspace-id is client-asserted and trusted only after _auth_pre_request confirms memberships(user = auth.uid(), tenant = header). Never trust it off the wire.
4. Fail closed. A malformed/non-UUID header aborts the request — it never falls through to "some other workspace." Let the exception propagate; never swallow it.
5. Resolved-NULL means deny, not allow. No header → _auth_tenant_id() is NULL → RLS matches no rows and _auth_has_permission() is false. Never let an empty/NULL workspace slip past a guard.
6. Aggregate / cross-workspace reads go through SECURITY DEFINER RPCs that explicitly filter tenant_id IN (SELECT tenant_id FROM memberships WHERE user_id = auth.uid()) — not broad client table reads, and never by overloading the single-tenant RLS predicate with a second mode.
7. The MCP server forwards the user's JWT; service_role is NEVER the user-scoped path. With the user JWT, PostgREST + RLS + auth_verify_access stay the enforcement boundary. With service_role, RLS is bypassed and isolation reduces to "the MCP TypeScript is bug-free" — a confused-deputy waiting to happen. No supabaseAdmin in a tool.
8. No header → short-circuit with zero extra DB work. A request that asserts no workspace does no membership read — it resets the GUC and returns. Cheap and unmistakably deny (rule 5).

The guard helpers (public/_authz.sql)

• public.auth_verify_access(p_permission text) — raises (SQLSTATE 42501 → HTTP 403). Call as the first statement of every mutating RPC.
• public.auth_has_access(p_permission text) — boolean, for conditional branching inside an RPC (e.g. return a richer payload to admins).

Both wrap public._auth_has_permission, which derives the answer fresh on every request from (caller, active workspace) — one indexed probe into memberships ⋈ role_permissions, no JWT claim, nothing to go stale. Evaluated against the caller's active workspace (the one resolved from the x-workspace-id header). Do not call _auth_has_permission in policies; use auth_verify_access in the RPC.

-- Canonical: isolation-only RLS + permission guard in the RPC
CREATE POLICY widgets_tenant_isolation ON public.widgets
  FOR ALL TO authenticated
  USING      (tenant_id = (SELECT public._auth_tenant_id()))
  WITH CHECK (tenant_id = (SELECT public._auth_tenant_id()));

CREATE FUNCTION api.widget_update(p_id uuid, p_name text)
RETURNS jsonb LANGUAGE plpgsql SECURITY INVOKER SET search_path = '' AS $$
BEGIN
  PERFORM public.auth_verify_access('widget.update');      -- primary deny (403)
  UPDATE public.widgets SET name = p_name
   WHERE id = p_id
     AND tenant_id = (SELECT public._auth_tenant_id());     -- explicit scope; RLS backstops
  RETURN jsonb_build_object('id', p_id, 'name', p_name);
END; $$;
-- then declare 'widget.update' in supabase/database/rbac/{permissions,role_permissions}.sql

Grants on the api schema

USAGE on the api schema is granted to anon, authenticated, and service_role. That is NOT the security boundary — it just lets each role resolve the schema name so PostgREST can find the function you're calling. Pages that render before the session attaches (public home, marketing content) need anon to have USAGE or every RPC reply is permission denied for schema api.

EXECUTE on each function IS the security boundary, and it's granted per object — there is no schema-wide GRANT ON ALL FUNCTIONS (a blanket grant gets applied after the per-function REVOKEs on db apply, overriding them and exposing api._admin_* on dev). Every api function carries its own grant:

-- client RPC (default for authenticated users; RLS filters rows)
REVOKE ALL ON FUNCTION api.() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION api.() TO authenticated, service_role;

anon never receives EXECUTE unless you add it. When a function is intentionally public (a status page, public metrics, an unauthenticated signup-adjacent RPC), grant it explicitly and make it SECURITY DEFINER:

REVOKE ALL ON FUNCTION api.public_metrics() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION api.public_metrics() TO anon, authenticated, service_role;

Think of it this way: USAGE opens the door; EXECUTE decides who walks through per function.

---

Auth Patterns

Supabase Auth is the single identity provider

• Use auth.uid() and auth.jwt() in SQL — never trust client-sent user IDs
• Session management is the frontend's responsibility
• The database only cares about the JWT — it verifies identity, not sessions

Profile creation on sign-up

> Scaffolded by the CLI. Profiles, tenants, and memberships are created automatically on signup via the _internal_admin_handle_new_user trigger. The SQL below is for reference — it already exists in your project. If missing, run pnpm exec agentlink --force-update — do not recreate manually.

User metadata belongs in a profiles table, not in Supabase Auth metadata. The trigger (_internal_admin_handle_new_user, AFTER INSERT on auth.users) creates the profile and — for direct signups only — a default tenant + owner membership. Invited users (created via generateLink({ type: 'invite' }), so invited_at IS NOT NULL) get only a profile; invitation_accept() adds them to the inviter's tenant. The trigger writes no raw_app_meta_data — nothing tenant-related lives in the JWT in 2.0; the active workspace is asserted per request.

-- supabase/database/schemas/public/tables/profiles.sql (scaffolded)
CREATE TABLE IF NOT EXISTS public.profiles (
  id uuid PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  email text,
  display_name text,
  avatar_url text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;

> Zero-touch, no picker for solo users. After a direct signup the user has exactly one membership. The client asserts that workspace via x-workspace-id (the frontend resolves it from session_context / the user's memberships) — no selection UI and no session refresh. There's no "JWT minted before the membership row" race to paper over, because the token never carried the tenant.

Need to customize signup logic? Edit the function body in supabase/database/schemas/public/functions/_internal_admin_handle_new_user.sql (keep the same name — the update flow preserves your edits here), then pnpm exec agentlink db apply. The full scaffolded body + the profile_get / profile_update RPCs are in [RLS Patterns → Signup trigger & profile RPCs](./references/rls_patterns.md) — don't recreate them; they already exist.

---

RLS Policies

RLS is always enabled on every table. Policies filter rows based on who's asking.

Policy naming — snake_case only, never quoted

Always name policies with bare snake_case identifiers following {role}_{action}_{scope} (e.g., users_read_own_charts, admins_delete_memberships). Never wrap a policy name in double quotes, never include spaces, mixed case, or reserved words.

-- ❌ NOT THIS — quoted name with spaces breaks `pnpm exec agentlink db apply`
CREATE POLICY "Members can read own tenant" ON public.tenants ...

-- ✅ THIS
CREATE POLICY members_read_own_tenant ON public.tenants ...

Reason: db apply re-serializes every statement and strips surrounding double quotes from identifiers when it does. The resulting SQL reaches Postgres unquoted and fails with a syntax error on the spaces. Snake_case bare identifiers round-trip cleanly.

Choosing a policy pattern

| Scenario | Pattern | Example | |----------|---------|---------| | User owns the row | user_id = auth.uid() | Personal data (profiles, settings) | | User is a member of the tenant | _auth_* helper function | Team/org data | | Public read, auth write | true for SELECT, auth.uid() for INSERT | Blog posts, public listings | | Admin only | _auth_* checks role | Admin operations |

Simple: user-owns-row

When the table has a user_id column and each row belongs to one user:

-- supabase/database/schemas/public/tables/charts.sql
DROP POLICY IF EXISTS users_read_own_charts ON public.charts;
CREATE POLICY users_read_own_charts
ON public.charts FOR SELECT
USING (user_id = auth.uid());

DROP POLICY IF EXISTS users_insert_own_charts ON public.charts;
CREATE POLICY users_insert_own_charts
ON public.charts FOR INSERT
WITH CHECK (user_id = auth.uid());

DROP POLICY IF EXISTS users_update_own_charts ON public.charts;
CREATE POLICY users_update_own_charts
ON public.charts FOR UPDATE
USING (user_id = auth.uid());

DROP POLICY IF EXISTS users_delete_own_charts ON public.charts;
CREATE POLICY users_delete_own_charts
ON public.charts FOR DELETE
USING (user_id = auth.uid());

This is the simplest pattern. Use it when there's no tenant/team concept — the data is purely personal.

With auth helper functions

When access checks are more complex than a single column comparison, use _auth_* functions:

-- supabase/database/schemas/public/functions/_auth_chart_can_read.sql
CREATE OR REPLACE FUNCTION public._auth_chart_can_read(p_chart_id uuid)
RETURNS boolean
LANGUAGE plpgsql
SECURITY DEFINER  -- required: called by RLS on the table it queries
SET search_path = ''
AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 FROM public.charts
    WHERE id = p_chart_id
    AND (user_id = auth.uid() OR is_public = true)
  );
END;
$$;

-- Policy uses the function
DROP POLICY IF EXISTS users_read_own_or_public_charts ON public.charts;
CREATE POLICY users_read_own_or_public_charts
ON public.charts FOR SELECT
USING (public._auth_chart_can_read(id));

When to use helpers vs inline: Use inline user_id = auth.uid() when the check is a single column comparison. Use _auth_* helpers when the check involves joins, multiple conditions, or tenant membership lookups. Don't over-abstract — a simple USING clause doesn't need a function.

> Load [RLS Patterns](./references/rls_patterns.md) for tenant-scoped policies, role-based access, and the multi-tenancy model.

---

Multi-Tenancy Overview

> Scaffolded by the CLI. The CLI scaffolds a complete multi-tenancy model including tables, RLS policies, auth helpers, and API RPCs. If missing, run `pnpm exec

…

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: tomaspozo
• Source: tomaspozo/agentlink
• License: MIT
• Homepage: https://agentlink.sh

Install and usage instructions live in the source repository linked above.