Supabase Row Level Security Guide 2026: Real Examples

Key Takeaways

• RLS is what makes Supabase safe for direct client access. Without it, any client with your anon key can read/write any row; with it, the database enforces row-level access.
• Enabling RLS defaults to deny-all. After turning it on, nothing is accessible until you add explicit policies, which is safe but often surprises teams.
• Six common patterns cover most apps: user-scoped access, multi-tenant SaaS, shared resources, role-based access, public read/authenticated write, and soft-delete visibility.
• SECURITY DEFINER helper functions are better for recursive permission checks. They avoid costly per-row subqueries and scale better than naive EXISTS clauses.
• JWT custom claims avoid extra database lookups for role checks. Storing the role in app_metadata lets you read it from the token instead of querying a roles table for every row.
• The biggest mistake is exposing the service_role key in client code. It bypasses RLS completely and often fails silently until data is already exposed.
• Supabase Realtime respects RLS automatically. Clients only receive change events for rows they can SELECT, with no extra setup needed.

Introduction

Row-level security is the feature that makes Supabase safe to use with direct client access. Without it, any client with your anon key can read and write any row in any table. With it properly configured, your database enforces access control at the row level — no app-layer middleware required.

The problem: RLS is powerful, but the documentation examples are minimal. Production applications have nuanced requirements — multi-tenant access, role-based permissions, sharing, admin overrides — that require more than the basic "users can see their own rows" pattern.

At AgileSoftLabs, we have implemented RLS for SaaS platforms, marketplaces, and enterprise applications. This guide covers real-world patterns with working SQL.

Cloud Development Services and Custom Software Development Services implement these RLS architectures as part of production Supabase deployments — designing the policy structure alongside the application logic rather than retrofitting security after the schema is built.

RLS Fundamentals

Enable RLS on every table exposed to clients:

-- Always do this before writing any policies
ALTER TABLE your_table ENABLE ROW LEVEL SECURITY;

Once enabled, the default is deny all — no rows are accessible until you add policies.

Policy anatomy:

CREATE POLICY "policy name"
ON table_name
FOR operation -- SELECT, INSERT, UPDATE, DELETE, or ALL
TO role -- authenticated, anon, service_role, or a custom role
USING (boolean_expression)       -- Applied to existing rows (SELECT, UPDATE, DELETE)
WITH CHECK (boolean_expression); -- Applied to new/modified rows (INSERT, UPDATE)

How auth.uid() works: When a client makes a request with a Supabase JWT:

const supabase = createClient(url, key, {
  global: { headers: { Authorization: `Bearer ${userJwt}` } }
});

PostgreSQL sets auth.uid() to the user's UUID from the JWT. Your policies can reference this function directly.

The anon vs. authenticated Role

Role	Who Uses It	When
anon	Unauthenticated requests	No session / not logged in
authenticated	Logged-in users	Valid Supabase session
service_role	Server-side code only	Admin operations, bypasses RLS

-- Grant read on public data to anonymous users
CREATE POLICY "Public read"
ON products FOR SELECT
TO anon, authenticated  -- Both can read
USING (is_published = true);

-- Only authenticated users can create content
CREATE POLICY "Authenticated create"
ON posts FOR INSERT
TO authenticated
WITH CHECK (true);

Pattern 1: User-Scoped Access (Basic)

The foundation pattern — users can only access their own rows:

-- Table structure
CREATE TABLE user_profiles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  display_name TEXT,
  bio TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY;

-- SELECT: users see only their own profile
CREATE POLICY "Users see own profile"
ON user_profiles FOR SELECT
TO authenticated
USING (user_id = auth.uid());

-- INSERT: users can only create their own profile
CREATE POLICY "Users create own profile"
ON user_profiles FOR INSERT
TO authenticated
WITH CHECK (user_id = auth.uid());

-- UPDATE: users can only update their own profile
CREATE POLICY "Users update own profile"
ON user_profiles FOR UPDATE
TO authenticated
USING (user_id = auth.uid())
WITH CHECK (user_id = auth.uid());

-- DELETE: users can only delete their own profile
CREATE POLICY "Users delete own profile"
ON user_profiles FOR DELETE
TO authenticated
USING (user_id = auth.uid());

Pattern 2: Multi-Tenant SaaS (Organization-Scoped)

Most SaaS apps have organizations or workspaces where multiple users share data:

-- Organization members table
CREATE TABLE org_members (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL REFERENCES organizations(id),
  user_id UUID NOT NULL REFERENCES auth.users(id),
  role TEXT NOT NULL DEFAULT 'member' -- 'owner', 'admin', 'member'
);

-- Projects belong to organizations
CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL REFERENCES organizations(id),
  name TEXT NOT NULL,
  created_by UUID REFERENCES auth.users(id)
);

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Helper function: check if user is member of org
CREATE OR REPLACE FUNCTION is_org_member(p_org_id UUID)
RETURNS BOOLEAN AS $$
  SELECT EXISTS (
    SELECT 1 FROM org_members
    WHERE org_id = p_org_id
    AND user_id = auth.uid()
  );
$$ LANGUAGE sql SECURITY DEFINER;

-- Projects: members of the org can see all org projects
CREATE POLICY "Org members see org projects"
ON projects FOR SELECT
TO authenticated
USING (is_org_member(org_id));

-- Only org members can create projects in their org
CREATE POLICY "Org members create projects"
ON projects FOR INSERT
TO authenticated
WITH CHECK (is_org_member(org_id));

-- Only project creator or org admin can delete
CREATE POLICY "Project creator or admin can delete"
ON projects FOR DELETE
TO authenticated
USING (
  created_by = auth.uid()
  OR EXISTS (
    SELECT 1 FROM org_members
    WHERE org_id = projects.org_id
    AND user_id = auth.uid()
    AND role IN ('owner', 'admin')
  )
);

Performance note: Helper functions with SECURITY DEFINER bypass RLS for their internal queries, which is necessary for recursive permission checks. Cache them using STABLE or by storing membership in a JWT claim.

Custom Help Desk Software multi-tenant support platforms implement exactly this organization-scoped pattern — support agents see only tickets belonging to their assigned organization, while admin roles within org_members can access cross-organization reporting views through the same is_org_member helper function pattern.

Pattern 3: Shared Resources

Documents that can be owned by one user but shared with others:

CREATE TABLE documents (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_id UUID NOT NULL REFERENCES auth.users(id),
  title TEXT NOT NULL,
  content TEXT,
  is_public BOOLEAN DEFAULT FALSE
);

CREATE TABLE document_shares (
  document_id UUID REFERENCES documents(id) ON DELETE CASCADE,
  shared_with UUID REFERENCES auth.users(id),
  permission TEXT DEFAULT 'view', -- 'view', 'edit'
  PRIMARY KEY (document_id, shared_with)
);

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Owner sees document"
ON documents FOR SELECT
TO authenticated
USING (
  owner_id = auth.uid()   -- Owner
  OR is_public = TRUE     -- Public documents
  OR EXISTS (             -- Shared with user
    SELECT 1 FROM document_shares
    WHERE document_id = documents.id
    AND shared_with = auth.uid()
  )
);

CREATE POLICY "Owner or editor can update"
ON documents FOR UPDATE
TO authenticated
USING (
  owner_id = auth.uid()
  OR EXISTS (
    SELECT 1 FROM document_shares
    WHERE document_id = documents.id
    AND shared_with = auth.uid()
    AND permission = 'edit'
  )
)
WITH CHECK (
  owner_id = auth.uid()
  OR EXISTS (
    SELECT 1 FROM document_shares
    WHERE document_id = documents.id
    AND shared_with = auth.uid()
    AND permission = 'edit'
  )
);

Non-Profit AI Donor Management Software uses this exact sharing pattern for donor records shared across development team members — a donor record owned by one fundraiser can be shared with edit permission to a co-fundraiser working on a joint major-gift solicitation, without granting them access to the entire donor database.

Pattern 4: Role-Based Access (Admin, Member, Viewer)

For role-based permissions without a separate permissions table:

CREATE TABLE user_roles (
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  role TEXT NOT NULL, -- 'admin', 'moderator', 'user'
  PRIMARY KEY (user_id)
);

-- Helper: get current user's role
CREATE OR REPLACE FUNCTION auth.user_role()
RETURNS TEXT AS $$
  SELECT role FROM user_roles WHERE user_id = auth.uid();
$$ LANGUAGE sql SECURITY DEFINER STABLE;

-- Admins can see all users; users see only themselves
CREATE POLICY "Admin sees all, users see themselves"
ON user_profiles FOR SELECT
TO authenticated
USING (
  auth.user_role() = 'admin'
  OR user_id = auth.uid()
);

-- Only admins can delete any profile
CREATE POLICY "Only admins delete profiles"
ON user_profiles FOR DELETE
TO authenticated
USING (auth.user_role() = 'admin');

Better alternative — JWT custom claims: Store the user's role in the JWT to avoid a database lookup on every RLS evaluation:

-- Access JWT claims in policies
CREATE POLICY "Admin access"
ON sensitive_table FOR ALL
TO authenticated
USING (
  (auth.jwt() -> 'app_metadata' ->> 'role') = 'admin'
);

Set custom claims via the Supabase Auth admin API on the server side. AI Customer Service Software support platforms use the JWT claims approach for agent role checks — tier-1, tier-2, and supervisor roles stored to app_metadata avoid a user_roles table lookup on every ticket row evaluated across high-volume support queue queries.

Pattern 5: Public Read, Authenticated Write

Common for blogs, product catalogs, public content:

CREATE TABLE blog_posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  author_id UUID REFERENCES auth.users(id),
  title TEXT NOT NULL,
  content TEXT,
  published BOOLEAN DEFAULT FALSE
);

ALTER TABLE blog_posts ENABLE ROW LEVEL SECURITY;

-- Anyone can read published posts
CREATE POLICY "Public read published posts"
ON blog_posts FOR SELECT
TO anon, authenticated
USING (published = TRUE);

-- Authors see their own unpublished posts too
CREATE POLICY "Authors see all their posts"
ON blog_posts FOR SELECT
TO authenticated
USING (author_id = auth.uid());

-- Only authors can write
CREATE POLICY "Authors manage own posts"
ON blog_posts FOR INSERT
TO authenticated
WITH CHECK (author_id = auth.uid());

CREATE POLICY "Authors update own posts"
ON blog_posts FOR UPDATE
TO authenticated
USING (author_id = auth.uid())
WITH CHECK (author_id = auth.uid());

Pattern 6: Soft Delete Visibility

ALTER TABLE items ADD COLUMN deleted_at TIMESTAMPTZ;

-- Regular users never see soft-deleted rows
CREATE POLICY "Hide soft-deleted rows"
ON items FOR SELECT
TO authenticated
USING (
  deleted_at IS NULL
  AND user_id = auth.uid()
);

-- Admins see everything including deleted
CREATE POLICY "Admins see all rows"
ON items FOR SELECT
TO authenticated
USING (
  auth.user_role() = 'admin'
);

Performance: RLS and Query Optimization

RLS adds overhead. Here is how to minimize it.

1. Use indexes on filtering columns:

-- If your RLS filters on user_id, index it
CREATE INDEX ON documents(owner_id);
CREATE INDEX ON org_members(user_id, org_id);

2. Avoid subqueries in USING when possible:

-- SLOW: subquery per row
USING (EXISTS (SELECT 1 FROM org_members WHERE user_id = auth.uid() AND org_id = table.org_id))

-- FASTER: use a function with SECURITY DEFINER
USING (is_org_member(org_id))
-- The function result can be cached within the transaction

3. Use JWT claims instead of table lookups for roles:

-- Avoids hitting user_roles table on every row evaluation
USING ((auth.jwt() -> 'app_metadata' ->> 'role') = 'admin')

4. Keep policies simple — avoid joins:

Complex joins in USING clauses execute for every row evaluated. If your policy requires multi-table joins, denormalize permissions into the main table or precompute them.

Web Application Development Services handles application-layer performance tuning that complements RLS optimization — caching frequently checked permission states client-side, batching requests to reduce the number of RLS evaluations per page load, and structuring queries to take advantage of the indexes described above.

Testing Your RLS Policies

Test as a specific user in SQL editor:

-- Set the JWT context to test as a specific user
SELECT set_config('request.jwt.claims', json_build_object(
  'sub', 'user-uuid-here',
  'role', 'authenticated'
)::text, true);

-- Now run queries as that user
SELECT * FROM documents;

-- Reset
SELECT set_config('request.jwt.claims', '', true);

Test the anon role:

SET ROLE anon;
SELECT * FROM blog_posts;
RESET ROLE;

Write automated tests with pgTAP:

BEGIN;
SELECT plan(3);

-- Test that anon can only see published posts
SET LOCAL role TO anon;
SELECT results_eq(
  'SELECT count(*) FROM blog_posts',
  ARRAY[3::bigint],
  'Anon sees 3 published posts'
);

ROLLBACK;

AI Incident Management Software deployments apply this same systematic testing discipline to security policy validation — treating RLS policy regressions as production incidents requiring root cause analysis, with the pgTAP suite running as a CI gate before any schema migration that touches policy-relevant tables reaches production.

Common Mistakes

Mistake	Consequence	Fix
Enabling RLS but writing no policies	Deny all — app breaks	Write at minimum a SELECT policy
Using auth.uid() in WITH CHECK but not USING	Users can still see others' rows	Always pair USING with WITH CHECK
Policies on tables but not on foreign key tables	Data leak via JOINs	Enable RLS on every table
Using service_role key in client-side code	RLS completely bypassed	Never expose service_role in frontend
No index on RLS filter columns	N+1 query performance	Index all columns used in USING

Review AgileSoftLabs case studies for multi-tenant SaaS and marketplace platforms where complex RLS architecture was implemented as the primary security layer — including the specific pattern combinations used for organizations with hierarchical permission structures.

Building a Multi-Tenant SaaS Platform with Supabase?

Row Level Security done correctly eliminates an entire category of application-layer security code — but done incorrectly, it creates silent data leaks that are far harder to detect than a broken API endpoint. The six patterns in this guide cover the access control structures that the overwhelming majority of production Supabase applications actually need.

AgileSoftLabs has implemented complex RLS patterns for multi-tenant applications, marketplaces, and enterprise platforms. Explore the full products and services portfolio or contact our team for architecture guidance on your Supabase security design.

Frequently Asked Questions

1. What is Supabase Row Level Security?

Supabase Row Level Security, or RLS, is a Postgres feature that controls access at the row level. Instead of giving access to an entire table, you define rules that decide which rows a user can read, insert, update, or delete. This makes the database itself enforce access, not just your frontend or API. It is especially useful when users should only see their own data.

2. Why should I use RLS in Supabase?

Use RLS because it protects data directly in the database. Even if your frontend has a bug or your API is misconfigured, unauthorized users still cannot access rows they should not see. This is especially useful for SaaS apps, dashboards, internal tools, and any app with user-specific data. It adds a strong security layer and helps prevent accidental leaks.

3. How do I enable RLS in Supabase?

First, open the table you want to protect and enable RLS on it. Once RLS is active, the table is locked down by default, so access must be explicitly allowed through policies. Then create rules for the actions you want to permit, such as reading, inserting, or updating specific rows. Best practice is to enable RLS before exposing the table in production.

4. What is the best way to start with RLS policies?

Start with a deny-all mindset. Do not assume any access unless you explicitly allow it through a policy. Begin with the smallest permission, usually read-only, then add insert, update, or delete only when your app needs them. This reduces mistakes and makes it easier to understand who can do what.

5. What are the most common real-world uses of Supabase RLS?

The most common use cases are per-user data, shared documents, and multi-tenant SaaS apps. For example, a to-do app should let users access only their own tasks. A team workspace may allow several users to view the same records. A SaaS platform may need to isolate rows by organization so each tenant only sees its own data.

6. How do I write a basic Supabase RLS policy?

A basic RLS policy usually checks whether the current signed-in user matches the owner of the row. In Supabase, this is commonly done with auth.uid(). For example, if a row contains a user_id column, you can allow access only when user_id = auth.uid(). This pattern is simple and works well for most user-owned data.

7. Which RLS policies should I create first?

Start with SELECT policies first because read access is usually the most common need and the easiest to verify. Once that works, add INSERT, UPDATE, and DELETE only if your app needs them. This step-by-step approach helps you avoid opening too much access too early and makes debugging easier.

8. How do authenticated and anonymous users work with RLS?

Supabase separates users into roles such as authenticated and anonymous. Anonymous users are visitors who are not logged in, while authenticated users have a valid session. You can use these roles to control whether a table is public or private. For example, you might allow anonymous users to read published content but require authentication for private data.

9. What are the most common mistakes with Supabase RLS?

The most common mistakes are forgetting to enable RLS, writing policies that are too broad, and not testing access from different user accounts. Another mistake is assuming frontend checks are enough, which they are not. If the database policy is wrong, the app can still leak data. It is also easy to forget to update or delete rules after testing only reads.

10. How should I test Supabase RLS safely?

Test your policies in the SQL editor and verify them using multiple user accounts or sessions. Check what happens for the owner of a row, for another logged-in user, and for an anonymous visitor if your app supports public access. Make sure each action behaves as intended before shipping. The goal is to confirm the rules are secure but still usable.