Advanced Supabase RLS Patterns: Secure Your Database Like a Pro

Why RLS Matters

Row Level Security is Supabase's most powerful security feature. When configured correctly, RLS enforces access control at the database level — meaning even if your application has a bug, users cannot access data they shouldn't see. This article covers patterns beyond the basic "users see their own data" scenario.

The Fundamentals

-- Always enable RLS first
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

-- Without policies, no rows are accessible (default deny)
-- Add policies to explicitly allow access

Pattern 1: User Owns Their Data

-- Users can CRUD their own rows
CREATE POLICY "own_data_select" ON profiles FOR SELECT
  USING (auth.uid() = user_id);

CREATE POLICY "own_data_insert" ON profiles FOR INSERT
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "own_data_update" ON profiles FOR UPDATE
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "own_data_delete" ON profiles FOR DELETE
  USING (auth.uid() = user_id);

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

-- Users belong to organizations, data belongs to organizations
CREATE TABLE organization_members (
  org_id UUID REFERENCES organizations(id),
  user_id UUID REFERENCES auth.users(id),
  role TEXT CHECK (role IN ('owner', 'admin', 'member')),
  PRIMARY KEY (org_id, user_id)
);

-- Documents visible to org members
CREATE POLICY "org_member_access" ON documents FOR SELECT
  USING (
    org_id IN (
      SELECT org_id FROM organization_members
      WHERE user_id = auth.uid()
    )
  );

-- Only admins and owners can delete
CREATE POLICY "org_admin_delete" ON documents FOR DELETE
  USING (
    org_id IN (
      SELECT org_id FROM organization_members
      WHERE user_id = auth.uid() AND role IN ('owner', 'admin')
    )
  );

Pattern 3: Team-Based Access with Roles

-- Helper function to check user role
CREATE OR REPLACE FUNCTION get_user_role(p_org_id UUID)
RETURNS TEXT AS $$
  SELECT role FROM organization_members
  WHERE user_id = auth.uid() AND org_id = p_org_id
$$ LANGUAGE sql SECURITY DEFINER;

-- Use the function in policies for cleaner syntax
CREATE POLICY "admin_only_billing" ON billing_records FOR SELECT
  USING (get_user_role(org_id) IN ('owner', 'admin'));

Pattern 4: Public Content with Private Annotations

-- Posts table: public posts visible to all, drafts only to owner
CREATE POLICY "public_posts_select" ON posts FOR SELECT
  USING (
    published = true
    OR author_id = auth.uid()
  );

-- Only authors can modify their posts
CREATE POLICY "author_modify" ON posts FOR UPDATE
  USING (author_id = auth.uid());

Pattern 5: Hierarchical Permissions

-- Comments on posts: visible if the post is visible
CREATE POLICY "comments_select" ON comments FOR SELECT
  USING (
    post_id IN (
      SELECT id FROM posts WHERE published = true
      UNION
      SELECT id FROM posts WHERE author_id = auth.uid()
    )
  );

Pattern 6: Admin Override

-- Store admin status in a table
CREATE TABLE admins (user_id UUID PRIMARY KEY REFERENCES auth.users(id));

-- Admin helper function
CREATE OR REPLACE FUNCTION is_admin()
RETURNS BOOLEAN AS $$
  SELECT EXISTS (SELECT 1 FROM admins WHERE user_id = auth.uid())
$$ LANGUAGE sql SECURITY DEFINER;

-- Admins see everything
CREATE POLICY "admin_all_access" ON documents FOR ALL
  USING (is_admin());

-- Regular users see their own
CREATE POLICY "user_own_access" ON documents FOR SELECT
  USING (user_id = auth.uid());

Performance: Avoid Slow Subqueries in RLS

RLS policies run on every query. A slow policy can bring your entire app to a crawl. Always index columns used in RLS policies:

-- These indexes make RLS lookups fast
CREATE INDEX idx_org_members_user_id ON organization_members(user_id);
CREATE INDEX idx_documents_org_id ON documents(org_id);
CREATE INDEX idx_posts_author_published ON posts(author_id, published);

Testing Your RLS Policies

-- Test as a specific user
SET LOCAL role TO authenticated;
SET LOCAL request.jwt.claims TO '{"sub": "user-uuid-here"}';

SELECT * FROM documents; -- Should only return rows this user can see