Row Level Security (RLS) in Supabase: Data-Level Security

Row Level Security (RLS) is one of PostgreSQL's most powerful features and a fundamental pillar of Supabase's security architecture. With RLS, you can control exactly which rows each user can view, insert, update, or delete, directly at the database level.

What Is RLS and Why Does It Matter?

In a traditional application, authorization logic lives in the backend (middleware, controllers). With RLS, access rules are defined directly in PostgreSQL:

Without RLS: Client → API → Backend (checks permissions) → Database
With RLS:    Client → Supabase API → Database (checks permissions automatically)

Why is this better?

• Security by default: If you forget to protect an API endpoint, RLS still protects the data
• Impossible to bypass: Policies apply to ALL queries, including those from SQL functions
• Centralized: Access rules are in one place
• Performance: PostgreSQL optimizes policies as part of the query plan

Enabling RLS

RLS must be explicitly enabled on each table:

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- IMPORTANT: Without policies, NOBODY can access the data
-- (except service_role which bypasses RLS)

Policy Anatomy

RLS policies have this structure:

CREATE POLICY "descriptive_name"
  ON table_name
  FOR operation -- SELECT, INSERT, UPDATE, DELETE, ALL
  TO role       -- anon, authenticated, or a custom role
  USING (condition_for_existing_rows)
  WITH CHECK (condition_for_new_rows);

• USING: Evaluated against existing rows (SELECT, UPDATE, DELETE)
• WITH CHECK: Evaluated against new or modified rows (INSERT, UPDATE)

Basic CRUD Policies

SELECT: Who Can See What?

-- Anyone can see published posts
CREATE POLICY "Published posts visible to all"
  ON posts
  FOR SELECT
  TO anon, authenticated
  USING (status = 'Publicado');

-- Authenticated users see their own drafts
CREATE POLICY "Users see their own drafts"
  ON posts
  FOR SELECT
  TO authenticated
  USING (author_id = auth.uid() AND status = 'Borrador');

INSERT: Who Can Create?

CREATE POLICY "Authenticated users create posts"
  ON posts
  FOR INSERT
  TO authenticated
  WITH CHECK (author_id = auth.uid());

UPDATE: Who Can Modify?

CREATE POLICY "Users edit their own posts"
  ON posts
  FOR UPDATE
  TO authenticated
  USING (author_id = auth.uid())
  WITH CHECK (author_id = auth.uid());

DELETE: Who Can Remove?

CREATE POLICY "Users delete their own posts"
  ON posts
  FOR DELETE
  TO authenticated
  USING (author_id = auth.uid());

auth.uid() - The Key Function

auth.uid() returns the UUID of the currently authenticated user:

SELECT auth.uid();  -- 'd0e8c77e-4f2a-4b5c-9e3d-...'

-- Access other JWT data
SELECT auth.jwt() ->> 'email';
SELECT auth.jwt() -> 'app_metadata' ->> 'role';

Role-Based Policies

For systems with roles (admin, editor, user):

CREATE TABLE user_roles (
  id SERIAL PRIMARY KEY,
  user_id UUID REFERENCES auth.users(id),
  role TEXT NOT NULL CHECK (role IN ('admin', 'editor', 'user'))
);

-- Admins can see everything
CREATE POLICY "Admins see everything"
  ON posts
  FOR SELECT
  TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM user_roles
      WHERE user_roles.user_id = auth.uid()
      AND user_roles.role = 'admin'
    )
  );

Using app_metadata for Roles

A more efficient approach stores the role in user metadata:

-- Assign role via SQL (service_role only)
UPDATE auth.users
SET raw_app_meta_data = raw_app_meta_data || '{"role": "admin"}'::jsonb
WHERE id = 'user-uuid-here';

-- Policy using app_metadata
CREATE POLICY "Admin sees all"
  ON posts
  FOR ALL
  TO authenticated
  USING (
    (auth.jwt() -> 'app_metadata' ->> 'role') = 'admin'
  );

Policies with JOINs

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

Testing Policies

-- Simulate a specific user
SET request.jwt.claim.sub = 'user-uuid-here';
SET request.jwt.claim.role = 'authenticated';

SELECT * FROM posts;

RESET request.jwt.claim.sub;
RESET request.jwt.claim.role;

Common Mistakes

1. Forgetting to enable RLS

Always enable RLS on tables with sensitive data.

2. Overly permissive policies

-- BAD: allows inserting with any author_id
WITH CHECK (true);

-- GOOD: forces author_id to be current user
WITH CHECK (author_id = auth.uid());

3. Missing WITH CHECK on UPDATE

-- GOOD: checks both existing and modified rows
USING (author_id = auth.uid())
WITH CHECK (author_id = auth.uid());

4. Recursive policies

Use SECURITY DEFINER functions to avoid recursion:

CREATE OR REPLACE FUNCTION public.is_admin()
RETURNS boolean AS $$
  SELECT EXISTS (
    SELECT 1 FROM user_roles
    WHERE user_id = auth.uid() AND role = 'admin'
  );
$$ LANGUAGE sql SECURITY DEFINER;

Performance Considerations

1. Index columns referenced in policies

2. Avoid expensive JOINs in policies on large tables

3. Use EXPLAIN ANALYZE to verify execution plans

4. Consider SECURITY DEFINER functions for complex repeated logic

Service Role: Bypassing RLS

The service_role key bypasses all RLS policies. Use it ONLY on the server:

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

Conclusion

RLS is the first line of defense for data in Supabase. Properly configured, it makes unauthorized data access practically impossible, even if there are bugs in your application code. Start with simple auth.uid() policies and increase complexity as needed.