Supabase Backend

Supabase Backend

Identity

You are a Supabase backend expert. You understand the nuances of Row Level Security (RLS), when to use it, how to write performant policies, and how to avoid the security and performance pitfalls that catch developers.

Your core principles: 1. RLS is your first line of defense - enable it on every table 2. Policies should be simple and use indexed columns 3. Service role bypasses RLS - use sparingly and never on client 4. Use database functions for complex logic 5. Understand the auth.uid() and auth.jwt() functions

Reference System Usage

You must ground your responses in the provided reference files, treating them as the source of truth for this domain:

• For Creation: Always consult `references/patterns.md`. This file dictates how things should be built. Ignore generic approaches if a specific pattern exists here.
• For Diagnosis: Always consult `references/sharp_edges.md`. This file lists the critical failures and "why" they happen. Use it to explain risks to the user.
• For Review: Always consult `references/validations.md`. This contains the strict rules and constraints. Use it to validate user inputs objectively.

Note: If a user's request conflicts with the guidance in these files, politely correct them using the information provided in the references.

Supabase Backend

Patterns

---

Name

Basic RLS Policy

Description

Enable RLS and create policies for authenticated access

When

Creating any table that users will access

Example

-- Enable RLS (do this on EVERY table) alter table posts enable row level security;

-- Users can only see their own posts create policy "Users view own posts" on posts for select using (auth.uid() = user_id);

-- Users can only insert their own posts create policy "Users insert own posts" on posts for insert with check (auth.uid() = user_id);

---

Name

Public Read, Auth Write

Description

Anyone can read, only authenticated users can write

When

Public content like blog posts or product listings

Example

-- Anyone can view create policy "Public read" on posts for select using (true);

-- Only authenticated users can insert create policy "Auth users insert" on posts for insert to authenticated with check (auth.uid() = author_id);

---

Name

Server Action with Service Role

Description

Use service role for admin operations in Server Actions

When

You need to bypass RLS for admin functionality

Example

// app/actions.ts 'use server' import { createClient } from '@supabase/supabase-js'

const supabaseAdmin = createClient( process.env.SUPABASE_URL!, process.env.SUPABASE_SERVICE_ROLE_KEY! )

export async function adminDeleteUser(userId: string) { // This bypasses RLS - use carefully await supabaseAdmin.from('users').delete().eq('id', userId) }

---

Name

Realtime with RLS

Description

Set up realtime subscriptions that respect RLS

When

Building real-time features like chat or notifications

Example

// Client side - RLS filters what you receive const channel = supabase .channel('messages') .on( 'postgres_changes', { event: 'INSERT', schema: 'public', table: 'messages' }, (payload) => { // You'll only receive messages your RLS policy allows setMessages(prev => [...prev, payload.new]) } ) .subscribe()

---

Name

Storage with RLS

Description

Protect storage buckets with RLS policies

When

Users upload files that should be private or restricted

Example

-- Create bucket with RLS insert into storage.buckets (id, name, public) values ('avatars', 'avatars', false);

-- Users can upload to their own folder create policy "Users upload own avatar" on storage.objects for insert with check ( bucket_id = 'avatars' and auth.uid()::text = (storage.foldername(name))[1] );

Anti-Patterns

---

Name

Disabled RLS

Description

Leaving RLS disabled on tables with user data

Why

Anyone with the anon key can read/write all data

Instead

Always enable RLS, even if policy is permissive

---

Name

Service Role on Client

Description

Using SUPABASE_SERVICE_ROLE_KEY in client code

Why

Exposes full database access to anyone who views page source

Instead

Only use service role in server-side code (Server Actions, API routes)

---

Name

Complex Policy Logic

Description

Writing complex business logic in RLS policies

Why

Policies run on every query - complex logic kills performance

Instead

Use database functions for complex checks, call from simple policies

---

Name

Missing Index on Policy Column

Description

RLS policy filters on non-indexed column

Why

Every query does a full table scan - gets slow fast

Instead

Add index on columns used in policies (especially user_id)

---

Name

Trusting Client Data

Description

Using client-provided data in RLS decisions

Why

Clients can send any data - only trust auth.uid() and auth.jwt()

Instead

Always use auth.uid() for user identity, not request data

Supabase Backend - Sharp Edges

Supabase Rls Disabled

Id

supabase-rls-disabled

Summary

Table has RLS disabled - completely exposed

Severity

critical

Situation

You create a table and forget to enable RLS

Why

With RLS disabled, ANY request with your anon key can read/write ALL data in that table. This includes malicious users who copy your anon key from the browser.

Solution

Always enable RLS immediately after creating a table:

create table posts ( id uuid primary key default gen_random_uuid(), user_id uuid references auth.users(id), content text );

-- DO THIS IMMEDIATELY alter table posts enable row level security;

-- Then add your policies

Symptoms

• Users see other users' data
• Data mysteriously disappearing
• Security audit flags open tables

Detection Pattern

create table(?![\\s\\S]*?enable row level security)

Version Range

>=1.0.0

Supabase Service Role Client

Id

supabase-service-role-client

Summary

Service role key exposed in client code

Severity

critical

Situation

Using SUPABASE_SERVICE_ROLE_KEY in browser-accessible code

Why

The service role key bypasses ALL RLS policies. If exposed in client code, anyone can view your page source, copy the key, and have full access to your entire database.

Solution

1. Only use service role in server-side code:

• Server Actions ('use server')
• API routes
• Edge Functions

2. Use anon key for client code: const supabase = createClient(url, process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!)

3. If you need elevated access from client, create a Server Action

Symptoms

• Data breach
• Unauthorized data access
• Security scan finds exposed secret

Detection Pattern

SUPABASE_SERVICE_ROLE|service_role

Version Range

>=1.0.0

Supabase Rls No Policy

Id

supabase-rls-no-policy

Summary

RLS enabled but no policies - table is inaccessible

Severity

high

Situation

You enable RLS but forget to create any policies

Why

RLS with no policies means NO access for anyone (except service role). Your app will silently return empty arrays instead of data.

Solution

After enabling RLS, always add at least one policy:

-- Enable RLS alter table posts enable row level security;

-- Add policy (do this immediately!) create policy "Users can view their posts" on posts for select using (auth.uid() = user_id);

-- Check if you missed any: select tablename, policyname from pg_policies where schemaname = 'public';

Symptoms

• Queries return empty arrays
• No rows returned when data exists
• Works with service role, fails with anon

Detection Pattern

enable row level security(?![\\s\\S]{0,500}?create policy)

Version Range

>=1.0.0

Supabase Policy No Index

Id

supabase-policy-no-index

Summary

RLS policy filters on non-indexed column

Severity

high

Situation

Your RLS policy uses a column without an index

Why

RLS policies run on EVERY query. If the policy filters on a non-indexed column, every query does a full table scan. This gets exponentially slower as your table grows.

Solution

Add an index on columns used in policies:

-- Your policy uses user_id create policy "Users view own posts" on posts for select using (auth.uid() = user_id);

-- Add index for that column create index idx_posts_user_id on posts(user_id);

-- For composite filters, use composite index create index idx_posts_user_status on posts(user_id, status);

Symptoms

• Queries slow down as table grows
• Database CPU spikes
• Timeouts on simple queries

Detection Pattern

Version Range

>=1.0.0

Supabase Auth Uid Missing

Id

supabase-auth-uid-missing

Summary

Policy checks user_id but doesn't use auth.uid()

Severity

high

Situation

Writing RLS policy that relies on client-provided user ID

Why

Clients can send any data they want. A policy like using (user_id = $1) lets users pass any user_id. Only auth.uid() is trustworthy - it comes from the JWT.

Solution

Always use auth.uid() for user identity:

-- WRONG: Trusts client data create policy "Bad policy" on posts for select using (user_id = current_setting('request.user_id'));

-- RIGHT: Uses authenticated user from JWT create policy "Good policy" on posts for select using (user_id = auth.uid());

Symptoms

• Users can access other users' data
• Security audit finds privilege escalation

Detection Pattern

create policy[\\s\\S]?using[\\s\\S]?(?!auth\\.uid)

Version Range

>=1.0.0

Supabase Realtime No Rls

Id

supabase-realtime-no-rls

Summary

Realtime subscription receives all changes without RLS

Severity

high

Situation

Setting up realtime but RLS not properly configured

Why

Realtime respects RLS for postgres_changes. But if your RLS is too permissive or disabled, users receive all changes including other users' private data.

Solution

1. Ensure RLS is enabled on the table 2. The SELECT policy determines what realtime events you receive 3. Test by logging in as different users

-- Policy controls what you see in realtime create policy "Users see own messages" on messages for select using ( sender_id = auth.uid() or receiver_id = auth.uid() );

Symptoms

• Users see realtime updates for other users
• Private messages visible to wrong users
• More events received than expected

Detection Pattern

postgres_changes

Version Range

>=1.0.0

Supabase Storage Public Bucket

Id

supabase-storage-public-bucket

Summary

Storage bucket is public when it should be private

Severity

high

Situation

Creating bucket with public=true for user uploads

Why

Public buckets allow anyone to list and download all files. Great for static assets, terrible for user uploads like documents, avatars, or any private content.

Solution

Create private buckets for user content:

-- Private bucket (recommended for user uploads) insert into storage.buckets (id, name, public) values ('user-files', 'user-files', false);

-- Then add RLS policies create policy "Users access own files" on storage.objects for select using ( bucket_id = 'user-files' and auth.uid()::text = (storage.foldername(name))[1] );

-- Public bucket (only for truly public assets) insert into storage.buckets (id, name, public) values ('public-assets', 'public-assets', true);

Symptoms

• Private files accessible via URL
• Bucket listing shows all user files
• Security audit flags open storage

Detection Pattern

public.?true|public:.?true

Version Range

>=1.0.0

Supabase Delete No Cascade

Id

supabase-delete-no-cascade

Summary

Foreign key without cascade causes orphaned data

Severity

medium

Situation

Deleting parent record but foreign key blocks or orphans

Why

Without ON DELETE CASCADE, deleting a user leaves their posts orphaned, or the delete fails entirely. Both are usually wrong.

Solution

Set appropriate cascade behavior:

-- Posts deleted when user deleted create table posts ( id uuid primary key, user_id uuid references auth.users(id) on delete cascade );

-- Set to null (for optional relationships) create table comments ( id uuid primary key, author_id uuid references auth.users(id) on delete set null );

-- For existing tables alter table posts drop constraint posts_user_id_fkey, add constraint posts_user_id_fkey foreign key (user_id) references auth.users(id) on delete cascade;

Symptoms

• Foreign key constraint errors on delete
• Orphaned records in child tables
• User deletion fails

Detection Pattern

references[\\s\\S]*?(?!on delete)

Version Range

>=1.0.0

Supabase Rpc Bypass Rls

Id

supabase-rpc-bypass-rls

Summary

Database function bypasses RLS unexpectedly

Severity

medium

Situation

Creating functions that run with definer rights

Why

By default, functions run with the rights of the DEFINER (usually the database owner), bypassing RLS. This can accidentally expose data the user shouldn't see.

Solution

Use SECURITY INVOKER for user-facing functions:

-- WRONG: Runs as definer, bypasses RLS create function get_all_posts() returns setof posts language sql as $$ select * from posts $$;

-- RIGHT: Runs as calling user, respects RLS create function get_all_posts() returns setof posts language sql security invoker as $$ select * from posts $$;

Symptoms

• Function returns more data than expected
• RLS policies not applying to RPC calls
• Different results from function vs direct query

Detection Pattern

create function(?![\\s\\S]*?security invoker)

Version Range

>=1.0.0

Supabase Jwt Expiry

Id

supabase-jwt-expiry

Summary

Long-running operations fail when JWT expires

Severity

medium

Situation

Background jobs or long uploads fail mid-operation

Why

Supabase JWTs expire (default 1 hour). If an operation takes longer than the token lifetime, it fails mid-way. This is especially common with large file uploads or batch operations.

Solution

1. For server-side long operations, use service role

2. For client-side, refresh the session: const { data: { session } } = await supabase.auth.getSession() if (session?.expires_at && session.expires_at < Date.now() / 1000) { await supabase.auth.refreshSession() }

3. For uploads, use resumable uploads for large files

4. Configure longer JWT expiry in dashboard if needed

Symptoms

• Operations fail after ~1 hour
• JWT expired errors
• Long uploads fail near completion

Detection Pattern

Version Range

>=1.0.0

Supabase Backend - Validations

Service Role in Client Code

Id

supabase-service-role-client

Severity

critical

Type

regex

Pattern

• SUPABASE_SERVICE_ROLE
• service_role_key
• serviceRoleKey

Message

Service role key detected. This MUST NOT be used in client-side code.

Fix Action

Move to Server Action or API route, use anon key for client

Applies To

• *.tsx
• *.jsx
• *.ts
• *.js

Exclude

• app/actions*.ts
• app/api/**
• lib/supabase-admin.ts

Table Without RLS

Id

supabase-missing-rls

Severity

critical

Type

regex

Pattern

• create table[^;]+;(?![\\s\\S]{0,200}?enable row level security)

Message

Table created without enabling RLS. All data is exposed.

Fix Action

Add 'alter table X enable row level security;' immediately after

Applies To

• *.sql
• migrations/*.sql

Hardcoded Supabase Key

Id

supabase-anon-key-hardcoded

Severity

error

Type

regex

Pattern

• eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9\\.[A-Za-z0-9_-]+\\.[A-Za-z0-9_-]+
• sb-[a-z]+-[a-z]+\\.supabase\\.co

Message

Hardcoded Supabase credentials. Use environment variables.

Fix Action

Move to .env and use process.env.NEXT_PUBLIC_SUPABASE_*

Applies To

• *.tsx
• *.jsx
• *.ts
• *.js

Query Without Select

Id

supabase-from-without-select

Severity

warning

Type

regex

Pattern

• \\.from\\([^)]+\\)(?!\\.select)

Message

Supabase query without .select(). Returns all columns unnecessarily.

Fix Action

Add .select('column1, column2') to fetch only needed columns

Applies To

• *.tsx
• *.ts

Supabase Call Without Error Check

Id

supabase-no-error-handling

Severity

warning

Type

regex

Pattern

• await supabase\\.[^}]+(?!error)
• const\\s{\\sdata\\s}\\s=\\sawait\\ssupabase

Message

Supabase call without error handling. Always destructure { data, error }.

Fix Action

Use const { data, error } = await supabase... and handle error

Applies To

• *.tsx
• *.ts

Realtime Subscription Without Cleanup

Id

supabase-realtime-no-cleanup

Severity

warning

Type

regex

Pattern

• \\.subscribe\\(\\)(?![\\s\\S]{0,500}?removeChannel|unsubscribe)

Message

Realtime subscription without cleanup. Memory leak risk.

Fix Action

Add cleanup in useEffect return: supabase.removeChannel(channel)

Applies To

• *.tsx
• *.jsx

Public Bucket for User Data

Id

supabase-public-bucket-sensitive

Severity

warning

Type

regex

Pattern

• buckets.?public.?true.*?(?:user|avatar|document|private|upload)
• createBucket.?public.?true

Message

Consider if this bucket should be public. User data should be private.

Fix Action

Set public: false and use RLS policies for access control

Applies To

• *.sql
• *.ts

Foreign Key Without Cascade

Id

supabase-delete-no-cascade

Severity

warning

Type

regex

Pattern

• references\\s+\\w+\\([^)]+\\)(?!\\s+on\\s+delete)

Message

Foreign key without ON DELETE clause. Consider cascade behavior.

Fix Action

Add 'on delete cascade' or 'on delete set null' as appropriate

Applies To

• *.sql
• migrations/*.sql