Row-Level Security (RLS) Policies

Complete guide to Row-Level Security policies that enforce tenant isolation.

What is RLS?

Row-Level Security (RLS) is a PostgreSQL feature that automatically filters rows based on policies. In Lager Guru, RLS ensures users can only access data belonging to their tenant.

How RLS Works

1. Policy Definition: SQL policies define who can access what data
2. Automatic Enforcement: PostgreSQL applies policies to all queries
3. Transparent to Application: Application code doesn't need explicit filtering
4. Bypass Options: Service role can bypass RLS for background jobs

Policy Structure

CREATE POLICY policy_name ON table_name
  FOR operation  -- SELECT, INSERT, UPDATE, DELETE, or ALL
  TO role        -- authenticated, anon, service_role
  USING (condition)  -- For SELECT, UPDATE, DELETE
  WITH CHECK (condition);  -- For INSERT, UPDATE

Core Tables Policies

tenants Table

Super Admin: Full Access

CREATE POLICY tenants_super_admin_insert ON public.tenants
  FOR INSERT
  TO authenticated
  WITH CHECK (public.is_admin());

CREATE POLICY tenants_super_admin_update ON public.tenants
  FOR UPDATE
  TO authenticated
  USING (public.is_admin())
  WITH CHECK (public.is_admin());

CREATE POLICY tenants_super_admin_delete ON public.tenants
  FOR DELETE
  TO authenticated
  USING (public.is_admin());

Allowed Operations:

• Super admins can create, read, update, and delete any tenant

Tenant Admin: Read Own Tenant

CREATE POLICY tenants_tenant_admin_select ON public.tenants
  FOR SELECT
  TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM public.tenant_users
      WHERE tenant_users.tenant_id = tenants.id
      AND tenant_users.user_id = auth.uid()
      AND tenant_users.role = 'tenant_admin'
    )
  );

Allowed Operations:

• Tenant admins can read their own tenant's information

Tenant Users: Read Own Tenant

CREATE POLICY tenants_tenant_user_select ON public.tenants
  FOR SELECT
  TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM public.tenant_users
      WHERE tenant_users.tenant_id = tenants.id
      AND tenant_users.user_id = auth.uid()
    )
  );

Allowed Operations:

• Regular tenant users can read their tenant's information (read-only)

tenant_users Table

Super Admin: Full Access

CREATE POLICY tenant_users_super_admin_all ON public.tenant_users
  FOR ALL
  TO authenticated
  USING (public.is_admin())
  WITH CHECK (public.is_admin());

Allowed Operations:

• Super admins can manage all tenant-user assignments

Tenant Admin: Manage Own Tenant's Users

CREATE POLICY tenant_users_tenant_admin_all ON public.tenant_users
  FOR ALL
  TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM public.tenant_users tu
      WHERE tu.tenant_id = tenant_users.tenant_id
      AND tu.user_id = auth.uid()
      AND tu.role = 'tenant_admin'
    )
  )
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM public.tenant_users tu
      WHERE tu.tenant_id = tenant_users.tenant_id
      AND tu.user_id = auth.uid()
      AND tu.role = 'tenant_admin'
    )
  );

Allowed Operations:

• Tenant admins can create, read, update, and delete users in their tenant

Users: Read Own Record

CREATE POLICY tenant_users_select_own ON public.tenant_users
  FOR SELECT
  TO authenticated
  USING (user_id = auth.uid());

Allowed Operations:

• Users can read their own tenant assignment

Module Table Policies

Pattern: Tenant Isolation

Most module tables follow this pattern:

-- Users can only see their tenant's data
CREATE POLICY <table>_tenant_isolation ON <table>
  FOR SELECT
  TO authenticated
  USING (
    tenant_id IN (
      SELECT tenant_id FROM public.tenant_users
      WHERE user_id = auth.uid()
    )
  );

-- Users can only insert data for their tenant
CREATE POLICY <table>_tenant_insert ON <table>
  FOR INSERT
  TO authenticated
  WITH CHECK (
    tenant_id IN (
      SELECT tenant_id FROM public.tenant_users
      WHERE user_id = auth.uid()
    )
  );

-- Users can only update their tenant's data
CREATE POLICY <table>_tenant_update ON <table>
  FOR UPDATE
  TO authenticated
  USING (
    tenant_id IN (
      SELECT tenant_id FROM public.tenant_users
      WHERE user_id = auth.uid()
    )
  )
  WITH CHECK (
    tenant_id IN (
      SELECT tenant_id FROM public.tenant_users
      WHERE user_id = auth.uid()
    )
  );

-- Users can only delete their tenant's data
CREATE POLICY <table>_tenant_delete ON <table>
  FOR DELETE
  TO authenticated
  USING (
    tenant_id IN (
      SELECT tenant_id FROM public.tenant_users
      WHERE user_id = auth.uid()
    )
  );

Example: shipments Table

-- Super admin: Full access
CREATE POLICY shipments_admin_all ON public.shipments
  FOR ALL
  TO authenticated
  USING (public.is_admin())
  WITH CHECK (public.is_admin());

-- Tenant users: Access only their tenant's shipments
CREATE POLICY shipments_tenant_isolation ON public.shipments
  FOR SELECT
  TO authenticated
  USING (
    tenant_id IN (
      SELECT tenant_id FROM public.tenant_users
      WHERE user_id = auth.uid()
    )
  );

Service Role Policies

Service role can bypass RLS for background operations:

CREATE POLICY <table>_service_role_all ON <table>
  FOR ALL
  TO service_role
  USING (true)
  WITH CHECK (true);

When to Use:

• Seeding data
• Background jobs
• Data migrations
• Bulk operations

Security Note: Service role should NEVER be used in frontend code or user-facing APIs.

Policy Evaluation Order

PostgreSQL evaluates policies in this order:

1. Service Role: If using service role, bypasses all other policies
2. Super Admin: If user is super admin, uses admin policies
3. Tenant Policies: If user is tenant user, uses tenant-scoped policies
4. Default Deny: If no policy matches, access is denied

Common Policy Patterns

1. Tenant Isolation (Most Common)

USING (
  tenant_id IN (
    SELECT tenant_id FROM public.tenant_users
    WHERE user_id = auth.uid()
  )
)

2. Tenant Admin Only

USING (
  EXISTS (
    SELECT 1 FROM public.tenant_users
    WHERE tenant_id = <table>.tenant_id
    AND user_id = auth.uid()
    AND role = 'tenant_admin'
  )
)

3. Super Admin + Tenant

USING (
  public.is_admin()
  OR
  tenant_id IN (
    SELECT tenant_id FROM public.tenant_users
    WHERE user_id = auth.uid()
  )
)

4. Owner + Tenant Admin

USING (
  created_by = auth.uid()
  OR
  EXISTS (
    SELECT 1 FROM public.tenant_users
    WHERE tenant_id = <table>.tenant_id
    AND user_id = auth.uid()
    AND role = 'tenant_admin'
  )
)

Performance Considerations

Efficient Policies

Use helper functions for better performance:

-- Good: Uses helper function (cached)
USING (public.is_admin())

-- Bad: Direct subquery (evaluated every time)
USING (
  EXISTS (
    SELECT 1 FROM public.user_roles
    WHERE user_id = auth.uid()
    AND role = 'admin'
  )
)

Index Usage

Ensure tenant_id columns are indexed:

CREATE INDEX idx_<table>_tenant_id ON <table>(tenant_id);

Policy Optimization

Avoid complex subqueries in policies. Use helper functions:

-- Optimized helper function
CREATE OR REPLACE FUNCTION public.is_admin()
RETURNS boolean 
LANGUAGE sql 
STABLE 
SET search_path = public
AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.user_roles ur
    WHERE ur.user_id = (SELECT auth.uid()) AND ur.role = 'admin'
  );
$$;

Testing RLS Policies

Test as Regular User

-- Switch to regular user context
SET ROLE authenticated;
SET request.jwt.claim.sub = '<user-id>';

-- Test query
SELECT * FROM shipments;
-- Should only return user's tenant's shipments

Test as Super Admin

-- Ensure user has admin role
INSERT INTO user_roles (user_id, role) VALUES ('<user-id>', 'admin');

-- Test query
SELECT * FROM tenants;
-- Should return all tenants

Test Service Role

-- Use service role key
SET ROLE service_role;

-- Test query
SELECT * FROM tenants;
-- Should return all tenants (bypasses RLS)

Troubleshooting

Policy Not Working

1. Check RLS is enabled:

   SELECT tablename, rowsecurity FROM pg_tables WHERE schemaname = 'public';

2. Check policy exists:

   SELECT * FROM pg_policies WHERE tablename = '<table>';

3. Check user's tenant assignment:

   SELECT * FROM tenant_users WHERE user_id = auth.uid();

4. Check user's role:

   SELECT * FROM user_roles WHERE user_id = auth.uid();

Common Issues

Issue: User can't see any data

• Solution: Ensure user is assigned to a tenant in tenant_users

Issue: User can see all tenants' data

• Solution: Check RLS policies are enabled and correct

Issue: Service role queries fail

• Solution: Ensure service role policies exist

Related Documentation

• Database Structure - Table schemas
• Service Role - When to bypass RLS
• Auth Flow - How authentication works