Row Level Security for Self-Hosted Supabase: A Complete Guide

Row Level Security (RLS) is one of the most powerful features of Supabase, but it's also one of the most misunderstood. When you're self-hosting Supabase, getting RLS right is even more critical—there's no managed platform catching your mistakes. One misconfigured table can expose your entire user dataset.

This guide covers everything you need to know about implementing RLS in self-hosted Supabase: from basic policies to advanced performance optimization, testing strategies, and the common pitfalls that catch even experienced developers.

Why RLS Matters More for Self-Hosted Deployments

In Supabase Cloud, the dashboard provides visual indicators when tables lack RLS policies. Self-hosted instances don't always have the same guardrails enabled by default, making it easier to accidentally create tables without any access control.

Here's what the Supabase team has been emphasizing: the number one cause of security issues in Supabase apps is forgetting to enable RLS on new tables. When you create a table via SQL Editor or migrations—which is common in production deployments—RLS is disabled by default.

-- This table is publicly accessible via the Data API
CREATE TABLE user_data (
  id uuid PRIMARY KEY,
  user_id uuid REFERENCES auth.users(id),
  sensitive_info text
);

-- Without this line, every row is exposed
ALTER TABLE user_data ENABLE ROW LEVEL SECURITY;

The critical detail: enabling RLS without policies means deny all. No one can access the data—not even authenticated users. You must create at least one policy to allow access.

Understanding RLS Policy Basics

RLS policies act as WHERE clauses that PostgreSQL automatically appends to every query. They define who can SELECT, INSERT, UPDATE, and DELETE specific rows.

Policy Structure

CREATE POLICY "Users can view own data"
ON user_data
FOR SELECT
USING (auth.uid() = user_id);

This policy allows users to select only rows where user_id matches their authenticated user ID. Supabase provides the auth.uid() function that extracts the user ID from the JWT token.

Common Policy Patterns

User-owned data:

CREATE POLICY "Users manage own data"
ON user_profiles
FOR ALL
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);

Team-based access:

CREATE POLICY "Team members can view team data"
ON team_documents
FOR SELECT
USING (
  team_id IN (
    SELECT team_id FROM team_members 
    WHERE user_id = auth.uid()
  )
);

Public read, authenticated write:

CREATE POLICY "Anyone can read"
ON blog_posts
FOR SELECT
USING (published = true);

CREATE POLICY "Authors can manage posts"
ON blog_posts
FOR ALL
USING (auth.uid() = author_id)
WITH CHECK (auth.uid() = author_id);

The Performance Trap: Indexing RLS Columns

One of the most common mistakes in self-hosted Supabase deployments is forgetting to index columns used in RLS policies. Without proper indexes, query performance degrades dramatically as your tables grow.

Community benchmarks have shown over 100x improvement on large tables simply by adding the right indexes:

-- If your RLS uses auth.uid() = user_id
CREATE INDEX idx_user_data_user_id ON user_data(user_id);

-- For team-based access patterns
CREATE INDEX idx_team_members_user_id ON team_members(user_id);
CREATE INDEX idx_team_members_team_id ON team_members(team_id);

When tuning PostgreSQL performance, RLS-related indexes should be your first consideration.

The InitPlan Optimization

For policies that combine function calls with row-level checks, wrapping functions in subqueries allows PostgreSQL to cache results:

-- Slower: function called per row
CREATE POLICY "Admins or owners"
ON sensitive_data
FOR SELECT
USING (is_admin() OR auth.uid() = user_id);

-- Faster: function result cached
CREATE POLICY "Admins or owners optimized"
ON sensitive_data
FOR SELECT
USING ((SELECT is_admin()) OR (SELECT auth.uid()) = user_id);

This optimization only works when the function result doesn't depend on row data. For row-dependent functions, you can't use this pattern.

Security Pitfalls to Avoid

Never Trust user_metadata in Policies

The JWT contains a user_metadata claim that users can modify themselves. Creating RLS policies based on this data is a security vulnerability:

-- DANGEROUS: users can set their own metadata
CREATE POLICY "Premium users only"
ON premium_content
FOR SELECT
USING (auth.jwt() -> 'user_metadata' ->> 'is_premium' = 'true');

-- SAFE: use app_metadata (only modifiable server-side)
CREATE POLICY "Premium users only"
ON premium_content
FOR SELECT
USING (auth.jwt() -> 'app_metadata' ->> 'is_premium' = 'true');

Always Specify Role Restrictions

Policies that only check auth.uid() don't automatically exclude anonymous users:

-- Potential issue: 'anon' role might match if auth.uid() is somehow set
CREATE POLICY "Authenticated users"
ON protected_data
FOR SELECT
USING (auth.uid() = user_id);

-- Better: explicitly require authenticated role
CREATE POLICY "Authenticated users only"
ON protected_data
FOR SELECT
TO authenticated
USING (auth.uid() = user_id);

Views Bypass RLS by Default

This catches many developers off guard. Views created with the postgres superuser have security definer set automatically, bypassing RLS:

-- This view exposes all data, ignoring RLS
CREATE VIEW all_user_data AS
SELECT * FROM user_data;

-- PostgreSQL 15+: Make view respect RLS
CREATE VIEW safe_user_data 
WITH (security_invoker = true) AS
SELECT * FROM user_data;

If you're running PostgreSQL 14 or earlier, you'll need to handle view security differently—either through careful role management or by avoiding views on RLS-protected tables.

Testing RLS Policies

The SQL Editor in Supabase Studio runs as the postgres superuser, which bypasses all RLS. Testing from the SQL Editor gives false confidence.

Test from Client SDKs

Always verify policies using the actual client SDK that your application uses:

import { createClient } from '@supabase/supabase-js'

const supabase = createClient(SUPABASE_URL, SUPABASE_ANON_KEY)

// Sign in as test user
await supabase.auth.signInWithPassword({
  email: '[email protected]',
  password: 'testpassword'
})

// This should return only the user's own data
const { data, error } = await supabase
  .from('user_data')
  .select('*')

console.log('Rows returned:', data?.length)

Test with Role Switching in SQL

For SQL-based testing, explicitly set the role:

-- Test as anonymous user
SET ROLE anon;
SELECT * FROM user_data; -- Should fail or return nothing

-- Test as authenticated user (simulate JWT)
SET ROLE authenticated;
SET request.jwt.claim.sub = 'user-uuid-here';
SELECT * FROM user_data; -- Should return only matching rows

-- Reset to superuser
RESET ROLE;

Automated Policy Testing

For CI/CD pipelines, include RLS verification in your test suite:

-- Verify all public tables have RLS enabled
SELECT tablename 
FROM pg_tables 
WHERE schemaname = 'public' 
  AND tablename NOT IN (
    SELECT tablename 
    FROM pg_tables t
    JOIN pg_policies p ON t.tablename = p.tablename
  );
-- This should return no rows

Automatic RLS Enforcement

For self-hosted deployments where multiple developers create tables, consider using an event trigger to automatically enable RLS:

CREATE OR REPLACE FUNCTION public.enable_rls_on_new_table()
RETURNS event_trigger AS $$
DECLARE
  obj record;
BEGIN
  FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
  LOOP
    IF obj.object_type = 'table' AND obj.schema_name = 'public' THEN
      EXECUTE format('ALTER TABLE %s ENABLE ROW LEVEL SECURITY', obj.object_identity);
      RAISE NOTICE 'RLS enabled on %', obj.object_identity;
    END IF;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER enable_rls_trigger
ON ddl_command_end
WHEN TAG IN ('CREATE TABLE')
EXECUTE FUNCTION public.enable_rls_on_new_table();

This ensures no table in the public schema is accidentally left without RLS. Tables will be locked down by default, requiring explicit policy creation.

Using Private Schemas

For tables that should never be exposed through the Data API, create them in a private schema:

CREATE SCHEMA private;

-- This table is not accessible via Supabase API
CREATE TABLE private.internal_data (
  id uuid PRIMARY KEY,
  data jsonb
);

-- Access from Edge Functions or server-side code only

The Supabase Data API only exposes schemas listed in the PGRST_DB_SCHEMAS environment variable (default: public, storage, graphql_public). Tables in private schema remain accessible to your server-side code but invisible to client applications.

For managing environment variables in self-hosted deployments, you can customize which schemas are exposed.

Multi-Tenant RLS Patterns

If you're building a multi-tenant application, RLS is your primary isolation mechanism:

-- Store tenant_id in JWT claims
CREATE POLICY "Tenant isolation"
ON tenant_data
FOR ALL
USING (tenant_id = (auth.jwt() -> 'app_metadata' ->> 'tenant_id')::uuid)
WITH CHECK (tenant_id = (auth.jwt() -> 'app_metadata' ->> 'tenant_id')::uuid);

Set the tenant ID server-side when creating or updating user metadata to prevent users from accessing other tenants' data.

Debugging RLS Issues

When queries return unexpected results, check these common causes:

RLS not enabled: SELECT relrowsecurity FROM pg_class WHERE relname = 'table_name';
No matching policy: Review policies with SELECT * FROM pg_policies WHERE tablename = 'table_name';
Wrong role: Check which role the connection uses
JWT claims missing: Verify the token contains expected claims

For monitoring RLS-related issues in production, enable query logging temporarily to see the actual queries PostgreSQL executes:

-- Enable logging for current session (debugging only)
SET log_statement = 'all';
SET log_min_duration_statement = 0;

Remember to disable verbose logging after debugging—it can fill disk space quickly, especially in high-traffic deployments.

Simplifying RLS with Supascale

Managing RLS policies across multiple self-hosted Supabase projects gets complex. Each project needs its own policies, and keeping them synchronized during upgrades requires careful planning.

Supascale simplifies self-hosted Supabase management with one-click project creation, automated backups, and custom domain configuration. While RLS policies are defined at the database level, having a reliable management layer means you can focus on security implementation rather than operational overhead.

Check out Supascale's pricing—$39.99 one-time purchase for unlimited projects—and spend your time on application security instead of infrastructure management.

Conclusion

Row Level Security is non-negotiable for production Supabase deployments. The key takeaways:

Always enable RLS on public tables—use event triggers to enforce this automatically
Index every column referenced in RLS policies
Test policies from client SDKs, not the SQL Editor
Use app_metadata instead of user_metadata for security-critical claims
Consider private schemas for internal tables that shouldn't be API-accessible
Monitor query performance—RLS adds overhead that grows with table size

RLS done right provides defense-in-depth that protects your data even if application code has bugs. RLS done wrong creates a false sense of security while leaving data exposed.