Supabase Security

[GitAlboBis]

Select Topic Area

Question

Body

When scaling a serverless web application backed by Supabase, you might encounter database connection exhaustion and significant latency spikes during high-concurrency periods. This is often exacerbated by complex Row Level Security (RLS) policies that perform EXISTS checks or joins on secondary tables. Considering Supabase's underlying architecture (PostgreSQL, PostgREST, and Supavisor/PgBouncer), how would you architect your connection pooling strategy for serverless functions versus long-lived server connections? Furthermore, how would you optimize those expensive RLS policies—specifically by leveraging custom JWT claims or database functions—to eliminate the need for secondary table lookups and reduce the performance overhead on every query?

[tagberli]

The two things that usually cause problems under load are:

1. connection storms from serverless environments
2. RLS policies that do extra table lookups on every query

Both can create latency spikes once concurrency gets high enough.

Connection pooling strategy

The important distinction is serverless vs long-lived services.

Serverless (Vercel, Lambda, etc.)

Serverless functions can spin up hundreds of instances during traffic spikes, and if each instance opens its own PostgreSQL connection you can hit the database connection limit very quickly.

For these workloads it's best to:

• use the Supabase pooler endpoint (Supavisor / PgBouncer)
• or rely on PostgREST via the Supabase client, which avoids persistent DB connections entirely

In practice that means queries go through the transaction pooler instead of creating session-based connections.

So the flow becomes:

Serverless Function --> PostgREST / pooled connection --> PgBouncer --> Postgres

The key idea is that connections are borrowed per transaction, rather than held by each function instance.

Long-lived servers

If you're running a persistent backend (Node, Go, Python, etc.), you can maintain your own small connection pool in the application.

Typical setup:

• app-level pool (10–20 connections)
• connect through Supabase normally
• avoid creating connections per request

This keeps connection counts stable while still allowing good throughput.

Optimizing RLS policies

The other big performance issue tends to come from RLS policies that do permission checks through joins or EXISTS queries.

For example:

USING (
  EXISTS (
    SELECT 1
    FROM team_members
    WHERE team_members.user_id = auth.uid()
      AND team_members.team_id = projects.team_id
  )
)

Because RLS runs during query evaluation, that lookup can end up happening repeatedly when scanning rows.

Under load, this can become surprisingly expensive.

Using JWT claims instead of table lookups

One approach that works well is pushing some authorization data into custom JWT claims during authentication.

For example, the token could contain something like:

{
  "sub": "user_id",
  "team_ids": ["12", "15", "19"]
}

Then the RLS policy can check the claim directly instead of querying a membership table:

USING (
  team_id = ANY (
    SELECT jsonb_array_elements_text(auth.jwt() -> 'team_ids')
  )
)

This removes the secondary table lookup and turns the policy into a simple comparison.

Using functions to reduce repeated lookups

If embedding everything in the JWT isn't practical, another option is to wrap permission logic in a STABLE function:

CREATE FUNCTION auth.user_team_ids()
RETURNS uuid[]
LANGUAGE sql
STABLE
AS $$
  SELECT array_agg(team_id)
  FROM team_members
  WHERE user_id = auth.uid()
$$;

Policy:

USING (team_id = ANY(auth.user_team_ids()))

Because the function is marked STABLE, Postgres can cache the result within a statement, which avoids repeatedly running the subquery.

Other small things that help

• index columns used in RLS conditions (like team_id)
• keep policies simple where possible
• avoid joins in RLS unless they're unavoidable

In general the pattern that scales best is:

• serverless → transaction pooler or PostgREST
• long-lived services → small application connection pool
• RLS → simple checks based on JWT claims instead of joins

That combination usually removes both the connection pressure and the RLS overhead that show up during high concurrency.

[RainyGhaith]

Honestly, the biggest headache with serverless and Supabase is the connection limit. If you're using Vercel or Lambda, you have to use the connection pooler (port 6543) in transaction mode. If you go direct, you'll hit FATAL: too many connections the second you get a traffic spike.

For the RLS part, don't do joins in your USING clause. It’s a silent killer for performance. The best move is to bake your user roles/org IDs into the JWT claims using a trigger. That way, the RLS check is just reading a string from the token instead of hitting the disk. If it’s still too slow, just wrap the logic in a security definer RPC and skip the RLS entirely for that specific query.