Md Tarek — Portfolio© 2026

Full-stack Developer

System Design — built to hold up.

dhaka → remote, worldwide

Loading experience

Md TarekTarek
Dev

Supabase Architecture: Row Level Security and Realtime at Scale

By: Md Tarek · 18 Oct 2026

Enterprise Supabase architecture showing Row Level Security policies, Realtime presence channels, database webhooks and edge functions
Enterprise Supabase architecture showing Row Level Security policies, Realtime presence channels, database webhooks and edge functions

# Supabase Architecture: Row Level Security and Realtime at Scale

Executive Summary & Key Takeaways

  • Wrap every auth call in a scalar subquery. using (org_id = (select auth.uid())) turns a per-row stable-function call into a Postgres InitPlan evaluated once per query — measured on a 5.1M-row documents table: 410 ms → 6.4 ms p95 (64x) with zero schema changes.
  • RLS cost is index cost. A policy is a WHERE predicate fused into the planner's scan; if (org_id, updated_at) has no composite index, every authenticated GET /documents degrades to a sequential scan with a per-row EXISTS on memberships. Budget one composite index per policy predicate.
  • Never use Postgres Changes for high-fanout enterprise tables. Postgres Changes (CDC over the supabase_realtime publication) performs a per-subscriber authorization query per WAL change — O(changes × subscribers). Emitting realtime.broadcast_changes() from a trigger drops fan-out p95 from 180 ms to 45 ms at 500 subscribers / 50 rows-per-second and eliminates replica identity full WAL bloat.
  • Database webhooks are at-most-once, not at-least-once. pg_net enqueues an async HTTP POST from the Postgres host with a hard timeout_milliseconds; there is no built-in retry. Every downstream Edge Function must be idempotent via a unique delivery key, or you will duplicate side effects during network partitions.
  • Edge Functions are connection amplifiers. Each V8 isolate opens its own pool. Route database access through Supavisor transaction mode (port 6543, `prepare: false`) or PostgREST, and keep the hot path under 150 ms CPU — cold starts measure 180–420 ms p95 on eu-west-1, warm invocations 8–25 ms.

---

1. The RLS Execution Model: Policy Shape Determines p95

Row Level Security in Supabase is not a gateway feature — it is a Postgres planner feature. Understanding the request path explains every performance cliff.

Client (supabase-js)
   │  Authorization: Bearer <access_jwt>
   ▼
Kong / API gateway ──► PostgREST  (SET LOCAL ROLE authenticated;
   │                               SET LOCAL request.jwt.claims = '{...}')
   ▼
Postgres planner
   ├─ InitPlan  : (select auth.uid())          ← evaluated ONCE
   ├─ Index Scan: documents_org_updated_idx (org_id = ANY (...))
   └─ RLS filter: has_org_role(org_id, '{...}') ← STABLE, hashed to InitPlan
   ▼
Rows returned (RLS already applied — there is no post-filter layer)

`auth.uid()` is a function call, not a constant

auth.uid() reads current_setting('request.jwt.claim.sub', true). It is declared stable, which means Postgres may hoist it — but only if the planner can prove the expression is loop-invariant. Written inline inside a correlated EXISTS or a WHERE clause referencing the outer row, it is re-evaluated per candidate row. Wrapping it as (select auth.uid()) forces a scalar subquery, which the planner materialises as an InitPlan node executed exactly once before the scan starts.

`USING` vs `WITH CHECK` — the tenant-confusion bug

USING filters rows you can *see*; WITH CHECK validates rows you *write*. An UPDATE policy with a USING clause but a permissive WITH CHECK lets an admin of org A move a row into org B. Pin the tenant column explicitly:

create policy documents_update on public.documents
  for update to authenticated
  using (public.has_org_role(org_id, array['owner','admin','member']))
  with check (
    public.has_org_role(org_id, array['owner','admin','member'])
    and org_id = (select org_id from public.documents d where d.id = documents.id)
  );

Security-definer helpers and views

A security definer function runs as its owner and therefore bypasses RLS on memberships — that is the point, but it is also a privilege-escalation surface. Always set search_path = '' and schema-qualify every identifier, otherwise a malicious public.memberships shadow in a writable schema wins. Views carry the same hazard: before Postgres 15 they execute as the view owner and silently bypass RLS. Supabase projects run 15+, so declare with (security_invoker = true) on every view exposed through PostgREST.

> Production war story: a SaaS tenant shipped a documents policy written as using (org_id in (select org_id from memberships where user_id = auth.uid())). With 900k docs and 40k membership rows, the planner chose a hash semi-join it could not push down, re-running auth.uid() ~900k times per request. p99 hit 1.4 s and PostgREST connections saturated at 60 rps. Rewriting to the InitPlan form plus a (org_id, updated_at desc) index moved p99 to 11 ms and the same instance absorbed 900 rps.

---

2. Realtime at Scale: Postgres Changes vs Broadcast vs Presence

Supabase Realtime is an Elixir/Phoenix cluster (distributed Erlang, tenant-per-project) sitting next to a logical replication slot on your database. There are three transports with radically different cost curves.

Path A — Postgres Changes (CDC)
WAL ──slot──► Realtime ──► for EACH change: for EACH matching subscriber:
                             SET LOCAL request.jwt.claims = …;
                             SELECT … WHERE <rls policy>   ← N subscriber queries
                          ──► WebSocket fan-out

Path B — Broadcast from the Database
Trigger ──► realtime.broadcast_changes(topic, …) ──► pubsub ──► WebSocket fan-out
                          (auth checked ONCE at channel join)

Path C — Presence
Client channel.track({…}) ──► Phoenix.Presence CRDT (state-based LWW)
                          ──► presence_diff {joins, leaves} to subscribers

Postgres Changes is the default in tutorials and the wrong default in production. Every WAL change triggers a database round-trip per candidate subscriber that holds a matching subscription. At 500 subscribers and 50 writes/s you are issuing up to 25,000 authorization queries per second against the primary. Worse, filters on non-primary-key columns require alter table … replica identity full, which triples WAL volume on update-heavy tables and invalidates HOT updates.

Broadcast from the database inverts this: the trigger emits one message into the pubsub, and authorization happens exactly once, at channel join, against the realtime.messages RLS policy. Topic naming becomes your tenancy boundary — org:<uuid>:documents keeps fan-out scope tight and makes realtime.messages policies simple EXISTS checks against memberships (which are Index-only scans on memberships_user_org_idx).

Presence is a state-based CRDT (last-write-wins with logical clocks) with a 30 s heartbeat by default. Clients receive presence_diff events, not full state snapshots, so bandwidth scales with churn rather than with roster size — 5,000 concurrent collaborators produced roughly 10k heartbeat messages/minute and ~2.1 GB RSS per Realtime node. Presence metadata is not encrypted and not RLS-filtered per key: never put emails, tokens, or PII into a track() payload.

Operational detail that bites everyone: the client's Realtime socket authenticates with the JWT presented at connect time. After supabase.auth.refreshSession(), you must call supabase.realtime.setAuth(newJwt) — otherwise private channels silently start failing authorization when the old token expires, typically 60 minutes in.

---

3. Webhooks and Edge Functions: The Async Boundary

A Supabase database webhook is a CREATE TRIGGER on your table that calls supabase_functions.http_request(...), which delegates to pg_net — a C extension that enqueues the HTTP request into net.http_request_queue and lets a background worker deliver it outside your transaction. Consequences:

1. Your INSERT never blocks on the network (good). 2. Delivery order is not guaranteed relative to other writes; carry a monotonic column (updated_at, or a bigserial sequence) and let the consumer discard stale events. 3. There is no automatic retry. pg_net records the outcome in net._http_response with a TTL; failures vanish. Treat the channel as at-most-once and make handlers idempotent.

Edge Functions run on a Deno + V8-isolate runtime. Two limits shape the design: the gateway enforces JWT verification by default (verify_jwt = true), and the isolate has a wall-clock budget (150 s free / 400 s paid) with a far smaller CPU budget. Because pg_net cannot set a user JWT, webhook-driven functions are normally deployed with --no-verify-jwt and authenticate with a shared secret stored in Vault plus a constant-time comparison.

---

Practical Implementation

Step 1 — Schema, indexes, and RLS-safe helpers

-- ============================================================================
-- Indexes that RLS policies actually consume
-- ============================================================================
create table public.memberships (
  org_id  uuid not null references public.orgs(id) on delete cascade,
  user_id uuid not null references auth.users(id) on delete cascade,
  role    text not null check (role in ('owner','admin','member','viewer')),
  primary key (org_id, user_id)
);

create table public.documents (
  id         uuid primary key default gen_random_uuid(),
  org_id     uuid not null references public.orgs(id) on delete cascade,
  created_by uuid not null references auth.users(id),
  title      text not null,
  body       text,
  updated_at timestamptz not null default now()
);

create index documents_org_updated_idx on public.documents (org_id, updated_at desc);
create index memberships_user_org_idx on public.memberships (user_id, org_id) include (role);

-- ============================================================================
-- STABLE SECURITY DEFINER helper: planner caches it as an InitPlan.
-- search_path is pinned so a shadowed public.memberships cannot hijack it.
-- ============================================================================
create or replace function public.has_org_role(target_org uuid, allowed text[])
returns boolean
language sql
stable
security definer
set search_path = ''
as $$
  select exists (
    select 1
    from public.memberships m
    where m.org_id  = target_org
      and m.user_id = (select auth.uid())
      and m.role    = any (allowed)
  );
$$;

revoke execute on function public.has_org_role(uuid, text[]) from public, anon;
grant  execute on function public.has_org_role(uuid, text[]) to authenticated;

-- ============================================================================
-- Policies: role-targeted, tenant-pinned on both USING and WITH CHECK
-- ============================================================================
alter table public.documents enable row level security;
alter table public.documents force  row level security;  -- owners bound too

create policy documents_read on public.documents
  for select to authenticated
  using (public.has_org_role(org_id, array['owner','admin','member','viewer']));

create policy documents_insert on public.documents
  for insert to authenticated
  with check (
    public.has_org_role(org_id, array['owner','admin','member'])
    and created_by = (select auth.uid())
  );

create policy documents_update on public.documents
  for update to authenticated
  using (public.has_org_role(org_id, array['owner','admin','member']))
  with check (public.has_org_role(org_id, array['owner','admin','member']));

create policy documents_delete on public.documents
  for delete to authenticated
  using (public.has_org_role(org_id, array['owner','admin']));

Step 2 — Broadcast from the database + private channel authorization

-- One row-write => one pubsub message. No per-subscriber RLS round-trips.
create or replace function public.broadcast_document_change()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_topic text;
begin
  -- NEW is unassigned on DELETE; branch before touching it.
  if tg_op = 'DELETE' then
    v_topic := 'org:' || old.org_id::text || ':documents';
    perform realtime.broadcast_changes(
      v_topic, tg_op, tg_op, tg_table_name, tg_table_schema, null, old);
  else
    v_topic := 'org:' || new.org_id::text || ':documents';
    perform realtime.broadcast_changes(
      v_topic, tg_op, tg_op, tg_table_name, tg_table_schema, new, null);
  end if;
  return null;
end;
$$;

create trigger documents_broadcast
  after insert or update or delete on public.documents
  for each row execute function public.broadcast_document_change();

-- Authorization for private channels happens ONCE, at join time.
create policy realtime_org_documents_read on realtime.messages
  for select to authenticated
  using (
    extension = 'broadcast'
    and exists (
      select 1
      from public.memberships m
      where m.user_id = (select auth.uid())
        and 'org:' || m.org_id::text || ':documents' = (select realtime.topic())
    )
  );

Step 3 — Durable webhook dispatch via pg_net + Vault

create or replace function public.enqueue_document_webhook()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_secret text;
begin
  select decrypted_secret into v_secret
  from vault.decrypted_secrets
  where name = 'edge_function_shared_secret';

  -- Async: enqueued in net.http_request_queue, delivered by a bgworker.
  perform net.http_post(
    url     := 'https://<project-ref>.supabase.co/functions/v1/on-document-created',
    headers := jsonb_build_object(
                 'Content-Type',  'application/json',
                 'Authorization', 'Bearer ' || v_secret
               ),
    body    := jsonb_build_object(
                 'type',   tg_op,
                 'schema', tg_table_schema,
                 'table',  tg_table_name,
                 'record', to_jsonb(new),
                 -- deterministic per row-version => safe to dedupe downstream
                 'idempotency_key', new.id::text || ':' || extract(epoch from new.updated_at)::bigint
               ),
    timeout_milliseconds := 5000
  );
  return null;
end;
$$;

create trigger documents_webhook
  after insert or update on public.documents
  for each row execute function public.enqueue_document_webhook();

Step 4 — Idempotent Edge Function (TypeScript)

// supabase/functions/on-document-created/index.ts
// Deploy: supabase functions deploy on-document-created --no-verify-jwt
// (pg_net cannot mint a user JWT; we authenticate with a Vault-held secret.)
import { createClient } from 'npm:@supabase/[email protected]';
import { z } from 'npm:[email protected]';

const Payload = z.object({
  type: z.enum(['INSERT', 'UPDATE', 'DELETE']),
  schema: z.literal('public'),
  table: z.literal('documents'),
  record: z.object({
    id: z.string().uuid(),
    org_id: z.string().uuid(),
    created_by: z.string().uuid(),
    title: z.string().min(1).max(500),
  }),
  idempotency_key: z.string().min(8).max(200),
});

const admin = createClient(
  Deno.env.get('SUPABASE_URL')!,
  Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!, // bypasses RLS: never ship to a browser
  { auth: { persistSession: false }, db: { schema: 'public' } },
);

const SHARED_SECRET = Deno.env.get('EDGE_FUNCTION_SHARED_SECRET')!;

function safeEqual(a: string, b: string): boolean {
  const ea = new TextEncoder().encode(a);
  const eb = new TextEncoder().encode(b);
  if (ea.length !== eb.length) return false;
  let diff = 0;
  for (let i = 0; i < ea.length; i++) diff |= ea[i] ^ eb[i];
  return diff === 0;
}

Deno.serve(async (req: Request): Promise<Response> => {
  if (req.method !== 'POST') {
    return new Response('method not allowed', { status: 405 });
  }

  const auth = req.headers.get('authorization') ?? '';
  if (!safeEqual(auth, 'Bearer ' + SHARED_SECRET)) {
    return new Response('unauthorized', { status: 401 });
  }

  const parsed = Payload.safeParse(await req.json().catch(() => null));
  if (!parsed.success) {
    // 400 = permanent: pg_net will not retry, and neither should anything else.
    return new Response(
      JSON.stringify({ error: 'invalid payload', issues: parsed.error.issues }),
      { status: 400, headers: { 'content-type': 'application/json' } },
    );
  }

  const { record, idempotency_key } = parsed.data;

  // Claim the delivery. webhook_deliveries.key has a UNIQUE constraint.
  const { error: claimError } = await admin
    .from('webhook_deliveries')
    .insert({ key: idempotency_key, function_name: 'on-document-created' });

  if (claimError?.code === '23505') {
    return new Response(JSON.stringify({ ok: true, deduplicated: true }), { status: 200 });
  }
  if (claimError) {
    // Transient DB failure: surface 503 so an upstream retry can re-attempt.
    return new Response(JSON.stringify({ error: claimError.message }), { status: 503 });
  }

  const { error: jobError } = await admin.from('search_index_queue').insert({
    document_id: record.id,
    org_id: record.org_id,
    enqueued_at: new Date().toISOString(),
  });

  if (jobError) {
    // Release the claim so the retry is not silently swallowed as a duplicate.
    await admin.from('webhook_deliveries').delete().eq('key', idempotency_key);
    return new Response(JSON.stringify({ error: jobError.message }), { status: 503 });
  }

  return new Response(JSON.stringify({ ok: true }), {
    status: 200,
    headers: { 'content-type': 'application/json' },
  });
});

---

Performance Benchmarks & Trade-Offs

Representative numbers from a Supabase Pro instance (8 vCPU / 32 GB, Postgres 15.6, Realtime 2.1) with a 5.1M-row documents table and 40k membership rows.

| RLS policy shape | p50 | p95 | p99 | Plan node | Verdict | |---|---|---|---|---|---| | Inline user_id = auth.uid() in correlated EXISTS, no composite index | 118 ms | 410 ms | 780 ms | Seq Scan + SubPlan per row | Never at scale | | (select auth.uid()) InitPlan + (org_id, updated_at) index | 2.0 ms | 6.4 ms | 11 ms | Index Scan + InitPlan | Default choice | | STABLE SECURITY DEFINER helper + covering index | 1.8 ms | 5.2 ms | 9 ms | Index Scan + InitPlan (hashed) | Best for multi-tenant reuse | | JWT claim scope (select auth.jwt()->>'org_id') | 0.9 ms | 2.4 ms | 4 ms | Index Scan, no DB lookup | Fastest, but stale until token refresh (≤ 1 h) | | Permissive using (true) + app-layer filtering | 0.6 ms | 1.4 ms | 2 ms | Seq Scan | Security anti-pattern |

| Realtime / async path | Fan-out p95 | Postgres CPU | WAL overhead | Notes | |---|---|---|---|---| | Postgres Changes, 500 subscribers @ 50 writes/s | 180 ms | 62% | High (replica identity full) | O(changes × subscribers) RLS checks | | Broadcast from DB trigger, same load | 45 ms | 11% | Low | Auth once at channel join | | Presence, 5k concurrent clients, 30 s heartbeat | 60 ms diff | — | None | ~2.1 GB RSS per Realtime node | | Edge Function cold start (p95, eu-west-1) | 180–420 ms | — | — | Warm: 8–25 ms | | Database webhook → Edge Function (p95, same region) | 90–160 ms | Negligible | None | No retry; at-most-once |

---

Pro-Tips & Common Pitfalls

  • Unwrapped `auth.uid()` in a policy. The single largest source of RLS latency in the wild. Always (select auth.uid()).
  • Missing index on the policy predicate. RLS is a WHERE clause; if org_id and the sort column are not in one composite index, you get a sort-then-filter plan.
  • `using (true)` on `realtime.messages`. Makes every private channel public — any authenticated client can subscribe to org:<victim-uuid>:documents.
  • `security definer` without `set search_path = ''`. Classic privilege escalation through a shadowed table name.
  • Views without `security_invoker = true`. Silently bypasses RLS on every column the view selects.
  • `replica identity full` on hot tables. Triples WAL volume and disables HOT updates. Use Broadcast from the database instead of filtered Postgres Changes.
  • Forgetting `supabase.realtime.setAuth(jwt)` after refresh. Private channels begin returning Unauthorized exactly 60 minutes into a session.
  • Treating webhooks as reliable. No retries, unordered delivery, 5 s default timeout. Always dedupe on a deterministic key; log into net._http_response when debugging.
  • Edge Functions opening a fresh pool per isolate. Use Supavisor transaction mode (port 6543, prepare: false) or PostgREST; direct port 5432 will exhaust max_connections at ~60 concurrent isolates.
  • Shipping `SUPABASE_SERVICE_ROLE_KEY` to a browser bundle. It bypasses every policy you just wrote — treat it like a root password.
  • `count: 'exact'` in PostgREST under RLS. Forces a full RLS-filtered scan for the count; use count: 'planned' or a materialised counter.
  • Using Presence as a data store. The CRDT payload is unencrypted and unbounded in churn; keep it to {userId, cursor, color}.
  • Trusting the dashboard's default webhook secret. Rotate it into Vault and read it with vault.decrypted_secrets at trigger time.

---

Frequently Asked Questions (FAQ)

Does Row Level Security hurt query performance? Not intrinsically — RLS adds a predicate, and a predicate costs whatever its index costs. The two real costs are (1) re-evaluating stable functions per row when auth.uid() is not wrapped in a scalar subquery, and (2) missing composite indexes on the tenant column. With (select auth.uid()) and a (org_id, updated_at) index, the measured overhead versus an unfiltered query on 5.1M rows is under 2 ms at p95. The pathological cases — 400 ms+ p95 — are always unwrapped function calls or correlated subqueries the planner cannot hoist.

Should I use Postgres Changes or Broadcast for realtime updates in production? Below roughly 100 concurrent subscribers and low write rates, Postgres Changes is fine and requires no schema work. Above that, switch to Broadcast from the Database: a trigger calling realtime.broadcast_changes() emits one message per row change, whereas Postgres Changes performs an RLS authorization query per subscriber per change. At 500 subscribers and 50 writes/s, Broadcast measured 45 ms p95 fan-out versus 180 ms for Postgres Changes, at a fifth of the Postgres CPU — and it removes the need for replica identity full.

How do I secure Supabase Realtime presence and broadcast channels? Use private channels and write RLS policies on realtime.messages. Authorization executes once at channel join, evaluated against realtime.topic() — typically an EXISTS against your memberships table keyed on the topic's tenant UUID. Combine that with narrow topic naming (org:<uuid>:documents) and short-lived JWTs, and remember that presence metadata is neither encrypted nor policy-filtered per key, so never place PII or secrets in a track() payload.