# Enforcing Row Level Security in Supabase: A Deep Dive into LockIn's Multi-Tenant Architecture

Introduction

When building LockIn, a secure API key management platform for hackathons and small teams, we faced the critical challenge of implementing bulletproof data isolation in a multi-tenant environment. Supabase's Row Level Security (RLS) became our primary defense mechanism, but implementing it correctly proved to be more complex than initially anticipated.

This blog post details our journey implementing RLS in Supabase, the challenges we encountered, and the solutions we developed to create a secure, scalable multi-tenant system.

Repo link :https://github.com/Blackie360/lockin

Live Demo:https://lockin-wine.vercel.app/

The Challenge: Multi-Tenant Data Isolation

The Problem

LockIn needed to support multiple organizations, each with their own:

• Projects and environments
• Team members with different roles (owner, admin, member)
• Encrypted secrets and API keys
• Invitation system

Critical Requirement: Complete data isolation - users should never be able to access data from organizations they don't belong to, even if they try to bypass application-level security.

Why RLS Was Essential

While application-level security is important, RLS provides defense-in-depth by enforcing security policies directly at the database level. This ensures that even if there's a bug in our application code or if someone gains direct database access, the data remains protected.

Supabase RLS Implementation Strategy

1. Architecture Overview

Our RLS implementation follows a hierarchical security model:

Organization (Top Level)
├── Members (Role-based access)
├── Projects (Org-scoped)
├── Environments (Project-scoped)
├── Secrets (Environment-scoped)
└── Audit Logs (Org-scoped)

2. Helper Functions for Role-Based Access

Before implementing policies, we created utility functions to check user permissions:

-- Core function to get current authenticated user ID
CREATE OR REPLACE FUNCTION public.current_user_id()
RETURNS TEXT AS $$
  SELECT COALESCE(
    current_setting('request.jwt.claims', true)::json->>'sub',
    current_setting('app.current_user_id', true)
  );
$$ LANGUAGE SQL STABLE;

-- Check if user is a member of an organization
CREATE OR REPLACE FUNCTION public.is_organization_member(org_id TEXT, user_id TEXT)
RETURNS BOOLEAN AS $$
  SELECT EXISTS(
    SELECT 1
    FROM public.member
    WHERE organization_id = org_id 
    AND user_id = is_organization_member.user_id
  );
$$ LANGUAGE SQL SECURITY DEFINER;

-- Check if user has admin or owner privileges
CREATE OR REPLACE FUNCTION public.is_organization_admin(org_id TEXT, user_id TEXT)
RETURNS BOOLEAN AS $$
  SELECT EXISTS(
    SELECT 1
    FROM public.member
    WHERE organization_id = org_id 
    AND user_id = is_organization_admin.user_id
    AND role IN ('admin', 'owner')
  );
$$ LANGUAGE SQL SECURITY DEFINER;

-- Check if user is specifically an owner
CREATE OR REPLACE FUNCTION public.is_organization_owner(org_id TEXT, user_id TEXT)
RETURNS BOOLEAN AS $$
  SELECT EXISTS(
    SELECT 1
    FROM public.member
    WHERE organization_id = org_id 
    AND user_id = is_organization_owner.user_id
    AND role = 'owner'
  );
$$ LANGUAGE SQL SECURITY DEFINER;

Key Insight: Using SECURITY DEFINER ensures these functions run with elevated privileges, allowing them to check membership even when called from restricted contexts.

3. Organization-Level Security Policies

Organizations are the top-level tenant boundary. Our policies ensure complete isolation:

-- Enable RLS on organization table
ALTER TABLE public.organization ENABLE ROW LEVEL SECURITY;

-- Users can only view organizations they're members of
CREATE POLICY "Users can view their organizations"
  ON public.organization
  FOR SELECT
  TO authenticated
  USING (
    id IN (
      SELECT organization_id
      FROM public.member
      WHERE user_id = current_user_id()
    )
  );

-- Only admins can update organization settings
CREATE POLICY "Admins can update organization"
  ON public.organization
  FOR UPDATE
  TO authenticated
  USING (is_organization_admin(id, current_user_id()))
  WITH CHECK (is_organization_admin(id, current_user_id()));

-- Only owners can delete organizations
CREATE POLICY "Owners can delete organization"
  ON public.organization
  FOR DELETE
  TO authenticated
  USING (is_organization_owner(id, current_user_id()));

4. Member Management Security

Member management required careful consideration of role hierarchy and self-protection:

ALTER TABLE public.member ENABLE ROW LEVEL SECURITY;

-- Users can view members of organizations they belong to
CREATE POLICY "Users can view organization members"
  ON public.member
  FOR SELECT
  TO authenticated
  USING (
    organization_id IN (
      SELECT organization_id
      FROM public.member
      WHERE user_id = current_user_id()
    )
  );

-- Admins can add new members
CREATE POLICY "Admins can add members"
  ON public.member
  FOR INSERT
  TO authenticated
  WITH CHECK (is_organization_admin(organization_id, current_user_id()));

-- Admins can update member roles (but not their own)
CREATE POLICY "Admins can update member roles"
  ON public.member
  FOR UPDATE
  TO authenticated
  USING (
    is_organization_admin(organization_id, current_user_id())
    AND user_id != current_user_id()  -- Self-protection
  )
  WITH CHECK (
    is_organization_admin(organization_id, current_user_id())
    AND user_id != current_user_id()
  );

-- Users can leave organizations (delete their own membership)
CREATE POLICY "Users can leave organization"
  ON public.member
  FOR DELETE
  TO authenticated
  USING (user_id = current_user_id());

5. Project and Secret-Level Security

Projects and secrets inherit organization-level permissions:

-- Projects are organization-scoped
ALTER TABLE public.project ENABLE ROW LEVEL SECURITY;

CREATE POLICY "org members can select project" ON public.project
  FOR SELECT TO authenticated
  USING (
    org_id IN (
      SELECT organization_id 
      FROM public.member 
      WHERE user_id = public.current_user_id()
    )
  );

-- Only admins can manage projects
CREATE POLICY "admins can mutate project" ON public.project
  FOR ALL TO authenticated
  USING (public.is_organization_admin(org_id, public.current_user_id()))
  WITH CHECK (public.is_organization_admin(org_id, public.current_user_id()));

-- Environment variables follow project permissions
ALTER TABLE public.environment ENABLE ROW LEVEL SECURITY;

CREATE POLICY "org members can select environment" ON public.environment
  FOR SELECT TO authenticated
  USING (
    project_id IN (
      SELECT p.id FROM public.project p
      WHERE p.org_id IN (
        SELECT organization_id FROM public.member 
        WHERE user_id = public.current_user_id()
      )
    )
  );

6. Invitation System Security

The invitation system required special handling to allow non-members to accept invites:

ALTER TABLE public.invitation ENABLE ROW LEVEL SECURITY;

-- Users can view invitations sent to their email
CREATE POLICY "Users can view their own invitations"
  ON public.invitation
  FOR SELECT
  TO authenticated
  USING (
    email IN (
      SELECT email
      FROM public.user
      WHERE id = current_user_id()
    )
  );

-- Admins can create and manage invitations
CREATE POLICY "Admins can create invitations"
  ON public.invitation
  FOR INSERT
  TO authenticated
  WITH CHECK (
    is_organization_admin(organization_id, current_user_id())
    AND inviter_id = current_user_id()
  );

Major Challenges and Solutions

Challenge 1: Authentication Context in RLS

Problem: RLS policies need to identify the current user, but Supabase's auth.uid() doesn't work with Better Auth.

Solution: We implemented a custom current_user_id() function that works with Better Auth's JWT structure:

CREATE OR REPLACE FUNCTION public.current_user_id()
RETURNS TEXT AS $$
  SELECT COALESCE(
    current_setting('request.jwt.claims', true)::json->>'sub',
    current_setting('app.current_user_id', true)
  );
$$ LANGUAGE SQL STABLE;

Implementation in Application:

// Set user context for RLS
export async function withUserContext<T>(
  userId: string, 
  operation: () => Promise<T>
): Promise<T> {
  return await db.execute(
    sql`SET LOCAL app.current_user_id = ${userId}`
  ).then(() => operation());
}

Challenge 2: Performance with Complex Subqueries

Problem: RLS policies with multiple subqueries were causing performance issues.

Initial Policy (Slow):

CREATE POLICY "slow_policy" ON public.secret
  FOR SELECT TO authenticated
  USING (
    project_id IN (
      SELECT id FROM public.project 
      WHERE org_id IN (
        SELECT organization_id FROM public.member 
        WHERE user_id = current_user_id()
      )
    )
  );

Solution: We optimized with strategic indexing and simplified policies:

-- Added composite indexes
CREATE INDEX idx_member_user_org ON public.member(user_id, organization_id);
CREATE INDEX idx_project_org ON public.project(org_id);
CREATE INDEX idx_secret_project ON public.secret(project_id);

-- Simplified policy with better performance
CREATE POLICY "optimized_policy" ON public.secret
  FOR SELECT TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM public.member m
      JOIN public.project p ON p.org_id = m.organization_id
      WHERE m.user_id = current_user_id()
      AND p.id = project_id
    )
  );

Challenge 3: Testing RLS Policies

Problem: Testing RLS policies is notoriously difficult because they depend on authentication context.

Solution: We created a comprehensive testing strategy:

// Test helper for RLS policies
export async function testRLSPolicy<T>(
  userId: string,
  testQuery: () => Promise<T>
): Promise<T> {
  // Set user context
  await db.execute(sql`SET LOCAL app.current_user_id = ${userId}`);

  // Run test query
  const result = await testQuery();

  // Reset context
  await db.execute(sql`RESET app.current_user_id`);

  return result;
}

// Usage in tests
test('RLS prevents cross-org access', async () => {
  // User 1 should not see User 2's organization data
  await expect(
    testRLSPolicy('user-1', () => 
      db.query.organization.findFirst({
        where: eq(organization.id, 'user-2-org')
      })
    )
  ).resolves.toBeUndefined();
});

Challenge 4: Better Auth Integration

Problem: Better Auth doesn't natively integrate with Supabase's RLS system.

Solution: We implemented a middleware layer to bridge the gap:

// Middleware to set RLS context
export async function withRLSContext<T>(
  session: Session,
  operation: () => Promise<T>
): Promise<T> {
  const userId = session.user.id;

  try {
    // Set user context for RLS
    await db.execute(sql`SET LOCAL app.current_user_id = ${userId}`);

    // Execute the operation
    return await operation();
  } finally {
    // Always reset context
    await db.execute(sql`RESET app.current_user_id`);
  }
}

// Usage in API routes
export async function GET(request: Request) {
  const session = await auth.api.getSession({ headers: await headers() });
  if (!session) return NextResponse.json({ error: "Unauthorized" }, { status: 401 });

  return await withRLSContext(session, async () => {
    const organizations = await db.query.organization.findMany();
    return NextResponse.json(organizations);
  });
}

Challenge 5: Migration Complexity

Problem: Enabling RLS on existing tables can break existing functionality.

Solution: We implemented a careful migration strategy:

-- Step 1: Enable RLS without policies (allows all access temporarily)
ALTER TABLE public.organization ENABLE ROW LEVEL SECURITY;

-- Step 2: Create policies in a separate transaction
BEGIN;
CREATE POLICY "test_policy" ON public.organization FOR SELECT TO authenticated USING (true);
COMMIT;

-- Step 3: Test thoroughly, then replace with restrictive policies
BEGIN;
DROP POLICY "test_policy" ON public.organization;
CREATE POLICY "restrictive_policy" ON public.organization 
  FOR SELECT TO authenticated 
  USING (/* actual restriction logic */);
COMMIT;

Performance Optimizations

1. Strategic Indexing

-- Composite indexes for common RLS query patterns
CREATE INDEX idx_member_user_org ON public.member(user_id, organization_id);
CREATE INDEX idx_project_org ON public.project(org_id);
CREATE INDEX idx_environment_project ON public.environment(project_id);
CREATE INDEX idx_secret_environment ON public.secret(environment_id);

-- Partial indexes for active records
CREATE INDEX idx_active_members ON public.member(user_id) 
  WHERE status = 'active';

2. Query Optimization

// Optimized query using joins instead of subqueries
export async function getOrganizationWithProjects(orgId: string, userId: string) {
  return await db
    .select({
      organization: organization,
      projects: project
    })
    .from(organization)
    .leftJoin(project, eq(project.orgId, organization.id))
    .where(
      and(
        eq(organization.id, orgId),
        // RLS will automatically filter based on user membership
        sql`true` // Placeholder - RLS handles the actual filtering
      )
    );
}

Monitoring and Debugging

1. RLS Policy Testing

-- Query to test RLS policies
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM public.organization 
WHERE id = 'test-org-id';

-- Check if RLS is working
SELECT schemaname, tablename, rowsecurity 
FROM pg_tables 
WHERE schemaname = 'public' AND rowsecurity = true;

2. Performance Monitoring

// Log slow RLS queries
export function logSlowQueries(query: string, duration: number) {
  if (duration > 1000) { // Log queries over 1 second
    console.warn(`Slow RLS query detected: ${query} took ${duration}ms`);
  }
}

Lessons Learned

What Worked Well

1. Defense in Depth: RLS provided an additional security layer that caught potential issues
2. Performance: After optimization, RLS overhead was minimal (< 5ms per query)
3. Testing: Comprehensive testing strategy caught several edge cases
4. Documentation: Well-documented policies made maintenance easier

What Was Challenging

1. Complexity: RLS policies can become complex and hard to debug
2. Performance: Initial implementation had significant performance overhead
3. Testing: Testing RLS policies requires careful setup of authentication context
4. Migration: Enabling RLS on existing tables requires careful planning

Best Practices Developed

1. Start Simple: Begin with basic policies and add complexity gradually
2. Test Thoroughly: Create comprehensive test suites for RLS policies
3. Monitor Performance: Always profile RLS policies for performance impact
4. Document Everything: RLS policies should be well-documented for future maintenance
5. Use Helper Functions: Abstract complex logic into reusable SQL functions

Conclusion

Implementing Row Level Security in Supabase for LockIn was a challenging but ultimately rewarding experience. While the initial setup was complex, the security benefits and peace of mind it provides are invaluable.

The key takeaways for anyone implementing RLS in Supabase:

1. Plan Your Security Model: Design your RLS policies around your application's security requirements
2. Optimize for Performance: RLS can impact performance, so optimize early and often
3. Test Comprehensively: RLS bugs can be security vulnerabilities
4. Monitor and Debug: Have tools in place to monitor RLS performance and debug issues
5. Document Everything: RLS policies are complex and need good documentation

RLS in Supabase provides a powerful tool for implementing multi-tenant security, but it requires careful planning, implementation, and testing to be effective. For LockIn, it was the foundation that enabled us to build a secure, scalable platform for API key management.

---

LockIn is now live and protecting API keys for hackathon teams worldwide. The RLS implementation ensures that even in the event of application bugs or security breaches, user data remains isolated and protected at the database level.