Supabase Row Level Security

What is Row Level Security?

Row Level Security (RLS) is PostgreSQL's native mechanism for controlling access to individual rows in a database table. Unlike application-level authorization that lives in your codebase, RLS enforces security policies directly at the database layer, creating a defense-in-depth strategy that protects your data even if the application layer is compromised.

When you enable RLS on a table, every query against that table is automatically filtered through the security policies you've defined. This means that whether data is accessed through the Supabase API, directly via PostgREST, or through any other means, the same security rules apply consistently. The database becomes the single source of truth for authorization, eliminating the risk of accidentally exposing data through misconfigured API endpoints or forgotten access controls.

RLS operates at the database engine level, which means it cannot be bypassed by any client application or API call. Even if someone gains access to your database credentials or finds a way to execute raw SQL, your security policies remain in force. This provides a powerful security guarantee that application-level authorization simply cannot match. For teams building multi-tenant applications, SaaS platforms, or any system handling sensitive user data, RLS represents an essential tool for ensuring data isolation and preventing unauthorized access.

Why RLS Matters for Modern Applications

The modern application landscape presents unique security challenges. Frontend applications communicate directly with backend APIs, mobile apps bypass traditional server-side logic, and distributed systems create numerous points where authorization could be misconfigured. RLS addresses these challenges by centralizing access control at the data layer, where it cannot be circumvented.

Consider a typical web application without RLS. The backend API includes authorization checks, but what happens when a developer adds a new endpoint and forgets to implement those checks? What happens when a mobile team rebuilds the iOS app and introduces an authorization bug? With RLS, these scenarios become much lower risk because the database itself enforces the rules regardless of how the data is accessed.

For Supabase users specifically, RLS is particularly important because the platform encourages direct client-to-database access through the auto-generated API. This architectural choice provides excellent developer experience and performance, but it requires robust database-level security to ensure that clients can only access the data they're supposed to see. RLS makes this possible while maintaining the convenience of direct database access.

To build a complete full-stack application with Supabase, understanding RLS is essential for implementing secure data access patterns from the ground up.

How RLS Works in Supabase

The Policy Model

At its core, RLS works by attaching policies to tables. Each policy defines rules that determine which rows a user can access and what operations they can perform. When a query is executed, PostgreSQL evaluates all applicable policies and automatically adds the appropriate conditions to filter the results.

Think of a policy as an invisible WHERE clause that gets appended to every query against the table. If a user tries to access rows they don't have permission to see, those rows are filtered out before the results are returned. Similarly, when inserting or updating data, policies validate that the operation complies with your security rules.

Policies can be configured for four distinct operations: SELECT (reading data), INSERT (adding new data), UPDATE (modifying existing data), and DELETE (removing data). You can create different policies for each operation, allowing for fine-grained control over how users interact with your data. For example, you might allow all authenticated users to view public records while restricting edits to the original creator of each record.

Enabling Row Level Security

RLS must be explicitly enabled on each table you want to protect. Tables created through the Supabase dashboard have RLS enabled by default, but tables created through raw SQL require manual RLS activation. This distinction is important because many developers prefer to manage their schema through migration files and SQL scripts.

-- Enable RLS on a table
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

-- Verify RLS is enabled
SELECT relname, relrowsecurity FROM pg_class WHERE relname = 'profiles';

Once RLS is enabled on a table, all access through the Supabase API is blocked until you create policies. This is a critical security feature--if you enable RLS but forget to add policies, no one can access the table at all, rather than everyone having full access. This fail-secure approach prevents accidental data exposure during development. As noted in the Supabase RLS documentation, this default-deny behavior ensures that security is opt-in rather than opt-out.

For a comprehensive understanding of Supabase database fundamentals, including schema design and table configuration, be sure to review our dedicated database guide.

Understanding auth.uid() and Authentication Context

Supabase integrates RLS with its authentication system through special JWT functions that provide access to the current user's identity and claims. The most commonly used function is auth.uid(), which returns the UUID of the currently authenticated user. This function is part of Supabase's authentication integration with RLS policies.

When a request is made through the Supabase client libraries, the authentication token is automatically included and validated. The database can then extract user information from this token and use it in policy expressions. This integration means your policies can reference the current user's identity without requiring any additional application code.

It's crucial to understand that auth.uid() returns NULL when no user is authenticated. This behavior has important implications for policy design. A common mistake is writing a policy like auth.uid() = user_id without considering what happens when the user is unauthenticated. In SQL, NULL = anything evaluates to NULL, which is treated as false in boolean contexts. This means the policy will silently deny access to unauthenticated users, which may or may not be the intended behavior.

For policies that should explicitly handle both authenticated and unauthenticated users, it's better to write explicit checks:

-- Clearer policy that handles unauthenticated users explicitly
CREATE POLICY "Public profiles are viewable by everyone"
ON profiles FOR SELECT
USING (
 is_public = true
 OR auth.uid() IS NOT NULL AND auth.uid() = user_id
);

Policy Design Patterns

Ownership-Based Access Control

The most common RLS pattern is ownership-based access control, where each record is associated with a user who has special privileges over that record. This pattern appears in countless application scenarios: a user's posts, their personal settings, their private messages, or any data that should be accessible only to the creator.

The ownership pattern typically involves adding a user_id column to your table that references the authenticated user's ID. Policies then use auth.uid() to compare against this column, ensuring users can only access their own records. This pattern is straightforward to implement and provides strong security guarantees.

-- Users can view their own todos
CREATE POLICY "Individuals can view their own todos"
ON todos FOR SELECT
USING (auth.uid() = user_id);

-- Users can insert their own todos
CREATE POLICY "Individuals can insert their own todos"
ON todos FOR INSERT
WITH CHECK (auth.uid() = user_id);

-- Users can update their own todos
CREATE POLICY "Individuals can update their own todos"
ON todos FOR UPDATE
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);

-- Users can delete their own todos
CREATE POLICY "Individuals can delete their own todos"
ON todos FOR DELETE
USING (auth.uid() = user_id);

Notice that UPDATE policies have both a USING clause and a WITH CHECK clause. The USING clause controls which rows can be selected for update, while the WITH CHECK clause validates that the new values being set comply with the policy. This separation allows for scenarios where a user might be allowed to view certain rows but not modify them, or where they can modify some columns but not others.

Role-Based Access Control

Beyond individual ownership, many applications require role-based access where permissions are determined by a user's role or membership in specific groups. This pattern is common in multi-tenant applications, admin dashboards, and systems with hierarchical organizational structures. Role-based access is especially important when building SaaS applications that serve multiple organizations.

Implementing role-based access in RLS typically involves checking for the existence of a relationship between the current user and the data they're trying to access. This might be a direct role assignment, membership in a team or organization, or a more complex relationship graph.

-- Admin can view all records
CREATE POLICY "Admins can view all"
ON sensitive_data FOR SELECT
USING (
 EXISTS (
 SELECT 1 FROM user_roles
 WHERE user_roles.user_id = auth.uid()
 AND user_roles.role = 'admin'
 )
);

-- Team members can view team data
CREATE POLICY "Team members can view team data"
ON projects FOR SELECT
USING (
 EXISTS (
 SELECT 1 FROM team_members
 WHERE team_members.team_id = projects.team_id
 AND team_members.user_id = auth.uid()
 )
);

Role-based policies often require joins to other tables that store role information. These joins can have performance implications, which we'll address in the performance optimization section. The key consideration is designing your schema to support efficient membership checks without requiring expensive operations on every query.

Multi-Tenant Data Isolation

Multi-tenant applications present unique security challenges because they must isolate data from different customers or organizations while maintaining efficient resource usage. RLS provides an elegant solution by allowing you to embed tenant context in policies and automatically filter data based on the requesting user's tenant. This is a critical pattern for any cloud application serving multiple customers.

-- Set tenant context for the session
ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;
ALTER TABLE customers ENABLE ROW LEVEL SECURITY;

-- Create a function to get current tenant ID from JWT claims
CREATE OR REPLACE FUNCTION current_tenant_id()
RETURNS uuid AS $$
 SELECT NULLIF(current_setting('request.jwt.claim:tenant_id', true), '')::uuid;
$$ LANGUAGE sql SECURITY DEFINER;

-- Customers can only see their own organization's data
CREATE POLICY "Tenant isolation for customers"
ON customers FOR SELECT
USING (organization_id = current_tenant_id());

-- Apply to all tenant-scoped tables using row-level security
CREATE POLICY "Tenant isolation for organizations"
ON organizations FOR ALL
USING (id = current_tenant_id());

This pattern works by storing tenant_id in the user's JWT token and using a session-level setting to make it available in policy expressions. When the application authenticates a user, it includes the tenant_id in the JWT claims. The database can then extract this value and use it to filter queries appropriately. As documented in Supabase's RLS performance guide, this approach provides strong isolation while maintaining query performance when properly indexed.

For teams building AI-powered platforms that handle sensitive data across multiple tenants, implementing robust multi-tenant isolation with RLS is essential for maintaining data privacy and compliance requirements.

Performance Optimization

The Critical Role of Indexes

RLS policies often filter data based on specific column values, and without proper indexes, these filters can force PostgreSQL to perform expensive sequential scans across entire tables. For large tables with millions of rows, the difference between an indexed and non-indexed policy can be dramatic--benchmarks show improvements of 100x or more for common ownership patterns.

-- Create an index on the user_id column used in ownership policies
CREATE INDEX idx_profiles_user_id ON profiles USING btree (user_id);

-- For tenant isolation, index the tenant_id column
CREATE INDEX idx_customers_tenant_id ON customers USING btree (tenant_id);

-- For role-based access, index the columns used in role lookups
CREATE INDEX idx_user_roles_user_id ON user_roles USING btree (user_id);
CREATE INDEX idx_user_roles_role ON user_roles USING btree (role);

The key is to identify columns that appear in policy expressions and ensure they have appropriate indexes. This includes not just the columns being compared directly but also foreign key columns used in joins for role or membership checks. Use PostgreSQL's EXPLAIN ANALYZE command to identify which queries are performing sequential scans and prioritize indexing those columns.

Function Wrapping for Performance

When policies call functions like auth.uid() or auth.jwt(), PostgreSQL may evaluate these functions for every row in a query, leading to significant overhead. Fortunately, there's a powerful optimization technique called function wrapping that allows PostgreSQL to cache function results using its initPlan mechanism.

The insight is that if a function's result doesn't depend on row data, it only needs to be called once per query rather than once per row. By wrapping the function call in a SELECT statement, you give PostgreSQL the opportunity to optimize.

-- Before optimization: function called on every row
CREATE POLICY "Optimized ownership policy"
ON todos FOR SELECT
USING (auth.uid() = user_id);

-- After optimization: function result is cached
CREATE POLICY "Optimized ownership policy"
ON todos FOR SELECT
USING ((SELECT auth.uid()) = user_id);

This simple change can improve query performance by an order of magnitude or more, especially on tables with many rows. The wrapped version allows PostgreSQL to evaluate auth.uid() once at the start of the query and reuse that result throughout the scan.

For more complex policies involving custom functions, the same technique applies:

-- Before optimization
CREATE POLICY "Admin access policy"
ON sensitive_data FOR SELECT
USING (is_admin() OR auth.uid() = user_id);

-- After optimization
CREATE POLICY "Admin access policy"
ON sensitive_data FOR SELECT
USING ((SELECT is_admin()) OR (SELECT auth.uid()) = user_id);

This optimization works for any function that doesn't take row data as input. Be careful not to apply it to functions that depend on the specific row being evaluated, as this would change the policy's semantics.

For comprehensive database performance optimization strategies in your web applications, our experts can help you implement best practices across your entire stack.

Strategic Use of Security Definer Functions

When policies need to check conditions across related tables, the join operations themselves may be subject to RLS, creating a cascade of policy evaluations that can severely impact performance. Security definer functions provide an escape hatch by executing with elevated privileges that bypass RLS on the tables they access.

-- A security definer function to check user roles
CREATE OR REPLACE FUNCTION has_role(p_role text)
RETURNS boolean AS $$
BEGIN
 RETURN EXISTS (
 SELECT 1 FROM user_roles
 WHERE user_roles.user_id = auth.uid()
 AND user_roles.role = p_role
 );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Policy using the security definer function
CREATE POLICY "Role-based access policy"
ON documents FOR SELECT
USING ((SELECT has_role('viewer')) OR (SELECT has_role('editor')));

The security definer function can access the roles table without triggering its own RLS policies, making the check much faster. However, this power comes with responsibility--security definer functions should be carefully designed to avoid leaking information, and they should be placed in schemas that aren't directly accessible through the API. As highlighted in Pentestly's security guide, security definer functions must be implemented carefully to maintain the security benefits of RLS.

Testing and Debugging RLS

Using EXPLAIN ANALYZE

PostgreSQL's EXPLAIN ANALYZE command is essential for understanding how RLS affects your queries. It shows the actual execution plan and timing, revealing whether policies are using indexes effectively or forcing expensive sequential scans. This is particularly important when optimizing database performance for applications with RLS enabled.

-- Set up the authenticated role context for testing
SET LOCAL role TO authenticated;
SET LOCAL request.jwt.claims TO
 '{"sub": "user-uuid-here", "role": "authenticated"}';

-- Analyze a query with RLS in effect
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM todos WHERE user_id = auth.uid();

The output shows whether the query is using indexes, how many rows are being filtered by RLS policies, and how much time is spent on different parts of the execution. Look for sequential scans on large tables as a sign that indexes are missing or policies need optimization.

Supabase also provides a client-side EXPLAIN method that works through the API:

const { data, error } = await supabase
 .from('todos')
 .select('*')
 .eq('user_id', userId)
 .explain({ analyze: true });

console.log(data);

This returns the same execution plan information but executes through the PostgREST API, making it useful for testing the actual query paths used by your application.

Testing Policies with Different Contexts

Comprehensive testing requires verifying policies with multiple user contexts: unauthenticated users, authenticated users with different roles, users who should have access, and users who should be denied. Each scenario should be tested explicitly.

-- Test as unauthenticated user (anon role)
SET LOCAL role TO anon;
SELECT * FROM profiles; -- Should return only public profiles

-- Test as authenticated user
SET LOCAL role TO authenticated;
SET LOCAL request.jwt.claims TO
 '{"sub": "test-user-uuid", "role": "authenticated"}';
SELECT * FROM profiles; -- Should return own profile + public profiles

-- Test as admin
SET LOCAL role TO authenticated;
SET LOCAL request.jwt.claims TO
 '{"sub": "admin-user-uuid", "role": "authenticated"}';
SET LOCAL request.jwt.claim:is_admin TO 'true';
SELECT * FROM profiles; -- Should return all profiles

Create a comprehensive test suite that exercises all your policies under various conditions. This testing is especially important when modifying policies, as even small changes can inadvertently grant or revoke access in unexpected ways.

Real-World Example: SaaS Application with Teams

A project management application where users belong to teams, teams own projects, and team members have different roles within each project. This scenario demonstrates layered access control with RLS. This pattern is foundational for building enterprise applications with multi-level access control.

-- Schema setup for the example
CREATE TABLE teams (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
 name text NOT NULL,
 created_at timestamptz DEFAULT now()
);

CREATE TABLE team_members (
 team_id uuid REFERENCES teams(id),
 user_id uuid NOT NULL,
 role text NOT NULL CHECK (role IN ('owner', 'admin', 'member')),
 PRIMARY KEY (team_id, user_id)
);

CREATE TABLE projects (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
 team_id uuid REFERENCES teams(id) NOT NULL,
 name text NOT NULL,
 status text DEFAULT 'active',
 created_at timestamptz DEFAULT now()
);

CREATE TABLE tasks (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
 project_id uuid REFERENCES projects(id) NOT NULL,
 title text NOT NULL,
 assigned_to uuid,
 status text DEFAULT 'todo',
 created_at timestamptz DEFAULT now()
);

-- Enable RLS on all tables
ALTER TABLE teams ENABLE ROW LEVEL SECURITY;
ALTER TABLE team_members ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;

-- Indexes for performance
CREATE INDEX idx_team_members_user ON team_members(user_id);
CREATE INDEX idx_projects_team ON projects(team_id);
CREATE INDEX idx_tasks_project ON tasks(project_id);
CREATE INDEX idx_tasks_assignee ON tasks(assigned_to);

-- Teams: members can view, owners can modify
CREATE POLICY "Team members can view teams"
ON teams FOR SELECT
USING (
 EXISTS (
 SELECT 1 FROM team_members
 WHERE team_members.team_id = teams.id
 AND team_members.user_id = auth.uid()
 )
);

CREATE POLICY "Team owners can modify teams"
ON teams FOR ALL
USING (
 EXISTS (
 SELECT 1 FROM team_members
 WHERE team_members.team_id = teams.id
 AND team_members.user_id = auth.uid()
 AND team_members.role = 'owner'
 )
);

-- Projects: all team members can view, admins can modify
CREATE POLICY "Team members can view projects"
ON projects FOR SELECT
USING (
 EXISTS (
 SELECT 1 FROM team_members
 WHERE team_members.team_id = projects.team_id
 AND team_members.user_id = auth.uid()
 )
);

CREATE POLICY "Team admins can modify projects"
ON projects FOR ALL
USING (
 EXISTS (
 SELECT 1 FROM team_members
 WHERE team_members.team_id = projects.team_id
 AND team_members.user_id = auth.uid()
 AND team_members.role IN ('owner', 'admin')
 )
);

-- Tasks: team members can view, assigned user or admin can update
CREATE POLICY "Team members can view tasks"
ON tasks FOR SELECT
USING (
 EXISTS (
 SELECT 1 FROM team_members
 JOIN projects ON team_members.team_id = projects.team_id
 WHERE projects.id = tasks.project_id
 AND team_members.user_id = auth.uid()
 )
);

CREATE POLICY "Assignee or admin can update tasks"
ON tasks FOR UPDATE
USING (
 EXISTS (
 SELECT 1 FROM team_members
 JOIN projects ON team_members.team_id = projects.team_id
 WHERE projects.id = tasks.project_id
 AND team_members.user_id = auth.uid()
 AND (
 team_members.role IN ('owner', 'admin')
 OR tasks.assigned_to = auth.uid()
 )
 )
);

This example demonstrates how RLS can handle complex multi-tenant scenarios with proper data isolation. The policies ensure that users can only access data within their teams, with role-based permissions determining what operations they can perform.

Security Best Practices

Defense in Depth

RLS provides an additional security layer but should not be your only protection. Defense in depth means implementing security at multiple levels: network security, application authentication, API authorization, and database-level access control. RLS excels as the last line of defense but doesn't replace other security measures. For comprehensive security, consider our cybersecurity services that cover all aspects of application security.

Keep your application layer's authorization logic even when using RLS. The application logic provides user experience benefits like showing appropriate error messages and controlling what features are visible. RLS ensures that even if application logic is bypassed, the data remains protected.

Service Role and When to Use It

Supabase provides a service role key that bypasses RLS entirely. This key should be used only in server-side contexts where you need full access to all data, such as administrative functions, background jobs, or data migrations. Never expose the service role key in client-side code. When you must use the service role, isolate its usage to specific API routes or server-side functions. Regular application code should use the anon key, which is subject to RLS policies.

Policy Review and Auditing

As applications evolve, RLS policies need to be reviewed and updated. New features may require new policies, changed requirements may need policy modifications, and accumulated policies may need consolidation. Regular security audits should include reviewing all RLS policies for correctness and performance.

Consider implementing policy version control, treating RLS policy SQL files as part of your codebase. This practice enables code review for security changes, makes it easy to roll back problematic policies, and provides an audit trail of when policies were modified and why.

Sources

1. Supabase Row Level Security Documentation - Official documentation covering policy syntax, authentication integration, and security definer functions
2. RLS Performance and Best Practices - Comprehensive performance optimization guide with benchmarks and testing strategies
3. PostgreSQL Row Level Security - Underlying PostgreSQL documentation for RLS concepts
4. Pentestly Supabase Security Best Practices 2025 - Real-world pentest insights on RLS configuration