Securing Multi-Tenant Systems with RLS and Authentication

Introduction

In security-critical systems like smart lock management platforms, robust access control is paramount. When multiple facilities, users, and roles need secure data isolation, PostgreSQL's Row-Level Security (RLS) combined with proper authentication patterns provides a powerful defense mechanism. This article explores advanced RLS implementation patterns and authentication hardening techniques derived from production smart lock systems.

Row-Level Security: Beyond Basic Policies

Safe Policy Management with Conditional Drops

One challenge in RLS implementation is handling policy updates in production environments. Attempting to create duplicate policies can cause deployment failures:

-- Unsafe: Will fail if policy exists
CREATE POLICY reservations_tenant_isolation ON reservations
FOR ALL TO authenticated
USING (facility_id = auth.get_current_facility_id());

A robust approach uses conditional policy management:

-- Safe policy recreation pattern
DO $$
BEGIN
  -- Drop existing policy if it exists
  DROP POLICY IF EXISTS reservations_tenant_isolation ON reservations;
  
  -- Create new policy
  CREATE POLICY reservations_tenant_isolation ON reservations
  FOR ALL TO authenticated
  USING (facility_id = auth.get_current_facility_id());
END $$;

Multi-Table Policy Coordination

Complex applications require coordinated policies across multiple tables. Consider a reservation system with plans, groups, and user roles:

-- Coordinated RLS policies for related tables
DO $$
BEGIN
  -- Plan access control
  DROP POLICY IF EXISTS plans_facility_access ON plans;
  CREATE POLICY plans_facility_access ON plans
  FOR ALL TO authenticated
  USING (facility_id = auth.get_current_facility_id());
  
  -- Plan groups with hierarchical access
  DROP POLICY IF EXISTS plan_groups_access ON plan_groups;
  CREATE POLICY plan_groups_access ON plan_groups
  FOR ALL TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM plans p 
      WHERE p.group_id = plan_groups.id 
      AND p.facility_id = auth.get_current_facility_id()
    )
  );
END $$;

Authentication Security Hardening

Eliminating Information Disclosure in Error Responses

Error responses can inadvertently leak sensitive information. Stack traces are particularly dangerous in production:

// Vulnerable: Exposes internal structure
interface ErrorResponse {
  message: string;
  stack?: string; // Dangerous in production
  code: string;
}

// Secure: Sanitized error response
interface SecureErrorResponse {
  message: string;
  code: string;
  timestamp: string;
}

function sanitizeError(error: Error): SecureErrorResponse {
  return {
    message: error.message,
    code: error.name,
    timestamp: new Date().toISOString()
    // Stack trace intentionally omitted
  };
}

Role-Based Security Flag Pattern

Instead of inferring permissions from data relationships, use explicit security flags:

// Vulnerable: Implicit permission checking
function hasAdminAccess(user: User): boolean {
  return user.facilityId === null; // Fragile assumption
}

// Secure: Explicit permission flags
interface SecureUser {
  id: string;
  facilityId: string | null;
  isPlatformAdmin: boolean; // Explicit security flag
  roles: UserRole[];
}

function hasAdminAccess(user: SecureUser): boolean {
  return user.isPlatformAdmin; // Clear, auditable
}

Advanced RLS Patterns for Complex Hierarchies

Nested Resource Access Control

For systems with nested resources (facilities → reservations → access tokens), implement layered RLS:

-- Multi-level access control for guest tokens
CREATE POLICY guest_tokens_secure_access ON guest_keyvox_tokens
FOR ALL TO authenticated
USING (
  -- Direct facility ownership
  facility_id = auth.get_current_facility_id()
  OR
  -- Indirect access through reservation
  EXISTS (
    SELECT 1 FROM reservations r
    WHERE r.id = guest_keyvox_tokens.reservation_id
    AND r.facility_id = auth.get_current_facility_id()
  )
);

Audit-Safe Role Management

Role assignments require additional security considerations:

-- Restrictive role assignment policy
CREATE POLICY role_members_insert_restriction ON role_members
FOR INSERT TO authenticated
WITH CHECK (
  -- Only facility admins can assign roles
  auth.has_facility_permission('manage_roles')
  AND
  -- Cannot elevate beyond current user's level
  auth.get_role_level(role_id) <= auth.get_user_role_level()
  AND
  -- Must be within same facility
  EXISTS (
    SELECT 1 FROM users u
    WHERE u.id = user_id
    AND u.facility_id = auth.get_current_facility_id()
  )
);

Database Migration Security

Safe Schema Evolution

Database migrations in production require careful ordering and rollback safety:

-- Migration with rollback safety
BEGIN;

-- Step 1: Create new structures
CREATE TABLE IF NOT EXISTS new_permissions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  role_id uuid REFERENCES roles(id),
  resource varchar(50) NOT NULL,
  action varchar(50) NOT NULL,
  UNIQUE(role_id, resource, action)
);

-- Step 2: Migrate data with validation
INSERT INTO new_permissions (role_id, resource, action)
SELECT role_id, resource, action
FROM old_permissions
WHERE role_id IS NOT NULL -- Data validation
ON CONFLICT (role_id, resource, action) DO NOTHING;

-- Step 3: Apply RLS policies
ALTER TABLE new_permissions ENABLE ROW LEVEL SECURITY;

CREATE POLICY new_permissions_access ON new_permissions
FOR ALL TO authenticated
USING (auth.has_role_access(role_id));

COMMIT;

Production Deployment Strategies

Feature Flag Integration with Security

Feature flags must respect existing security boundaries:

interface SecurityAwareFeatureFlag {
  key: string;
  enabled: boolean;
  requiredRole?: string;
  facilityRestricted?: boolean;
}

class SecureFeatureManager {
  async isEnabled(
    flag: string, 
    user: SecureUser
  ): Promise<boolean> {
    const feature = await this.getFeature(flag);
    
    if (!feature.enabled) return false;
    
    // Respect role requirements
    if (feature.requiredRole && 
        !user.roles.some(r => r.name === feature.requiredRole)) {
      return false;
    }
    
    // Facility-level restrictions
    if (feature.facilityRestricted && 
        !this.hasFeatureAccess(user.facilityId, flag)) {
      return false;
    }
    
    return true;
  }
}

Monitoring and Compliance

RLS Policy Validation

Implement automated testing for RLS policies:

describe('RLS Policy Security Tests', () => {
  it('should isolate facility data', async () => {
    const facility1User = await createTestUser({ facilityId: 'facility-1' });
    const facility2User = await createTestUser({ facilityId: 'facility-2' });
    
    // Create test data
    await createReservation({ facilityId: 'facility-1' });
    await createReservation({ facilityId: 'facility-2' });
    
    // Test isolation
    const facility1Data = await getReservations(facility1User);
    const facility2Data = await getReservations(facility2User);
    
    expect(facility1Data).toHaveLength(1);
    expect(facility2Data).toHaveLength(1);
    expect(facility1Data[0].facilityId).toBe('facility-1');
    expect(facility2Data[0].facilityId).toBe('facility-2');
  });
});

Summary

Securing multi-tenant systems requires a layered approach combining database-level RLS policies, application-level authentication hardening, and robust deployment practices. Key principles include:

• Use conditional policy management for safe deployments
• Implement explicit security flags rather than implicit checks
• Design coordinated RLS policies across related tables
• Sanitize error responses to prevent information disclosure
• Test security boundaries with automated RLS validation
• Apply feature flags within existing security contexts

These patterns provide the foundation for building trustworthy access control systems that scale with business requirements while maintaining security integrity.