Securing Access Control with RLS Policies and State Management

Introduction

In security-critical systems like smart lock management, implementing robust access control mechanisms is paramount. This article explores how to design secure Row Level Security (RLS) policies, manage check-in state transitions safely, and prevent common security pitfalls like infinite recursion in authorization logic.

Row Level Security Policy Design

RLS policies act as the first line of defense in database security, ensuring users can only access data they're authorized to see. However, poorly designed policies can create security vulnerabilities or system instability.

Preventing Infinite Recursion in Authorization

One critical issue we encountered was infinite recursion in platform admin policies:

-- Problematic policy that causes infinite recursion
CREATE POLICY "platform_admins_select" ON platform_admins
FOR SELECT USING (
  EXISTS (
    SELECT 1 FROM platform_admins pa 
    WHERE pa.user_id = auth.uid()
  )
);

This policy creates a circular dependency where checking if a user is a platform admin requires querying the same table with the same policy. The solution is to use a base condition that doesn't recurse:

-- Safe policy using direct user validation
CREATE POLICY "platform_admins_select" ON platform_admins
FOR SELECT USING (
  auth.uid() IS NOT NULL 
  AND auth.jwt() ->> 'role' = 'platform_admin'
);

Hierarchical Access Control

For facility management, we implement hierarchical access control that respects the chain of authority:

-- Policy for facility managers accessing check-in events
CREATE POLICY "checkin_events_facility_manager" ON checkin_events
FOR SELECT USING (
  facility_id IN (
    SELECT f.id FROM facilities f
    JOIN facility_managers fm ON f.id = fm.facility_id
    WHERE fm.user_id = auth.uid()
      AND fm.is_active = true
  )
);

State Management for Check-in Processes

Check-in processes in smart lock systems require careful state management to ensure security and prevent unauthorized access.

Separating Guest Policies from System Configuration

A key security principle is separating user policies from system configuration. Guest policies should not be mixed with check-in configurations:

// Secure approach - separate concerns
interface CheckinConfiguration {
  facilityId: string;
  requiredDocuments: DocumentType[];
  validationRules: ValidationRule[];
  auditSettings: AuditSettings;
}
interface GuestPolicy {
  guestId: string;
  accessLevel: AccessLevel;
  timeRestrictions: TimeWindow[];
  allowedAreas: string[];
}
// Keep these separate to prevent privilege escalation
class CheckinService {
  async validateCheckin(config: CheckinConfiguration, guest: GuestPolicy) {
    // Validate configuration independently of guest policy
    const configValidation = await this.validateConfig(config);
    if (!configValidation.isValid) {
      throw new SecurityError('Invalid configuration');
    }
    // Then apply guest-specific rules
    return this.applyGuestPolicy(guest, configValidation);
  }
}

Event-Driven State Transitions

For reliable check-in state management, we include all relevant events in policy queries:

// Comprehensive event inclusion for state validation
interface CheckinEventQuery {
  facilityManagerId: string;
  includeEvents: {
    checkinEvents: boolean;
    validationEvents: boolean;
    auditEvents: boolean;
  };
}
class CheckinStateManager {
  async getManagerCheckins(query: CheckinEventQuery) {
    // Include all event types for complete state picture
    const events = await this.db.query(`
      SELECT c.*, ce.event_type, ce.timestamp
      FROM checkins c
      LEFT JOIN checkin_events ce ON c.id = ce.checkin_id
      WHERE c.facility_id IN (
        SELECT facility_id FROM facility_managers 
        WHERE user_id = $1 AND is_active = true
      )
      ORDER BY ce.timestamp DESC
    `, [query.facilityManagerId]);
    return this.buildStateFromEvents(events);
  }
}

Deployment Security and Environment Management

Secure deployment practices are crucial for maintaining system integrity across environments.

Environment Variable Validation

Proper environment loading and validation prevents configuration-related security issues:

// Secure environment configuration
interface SecureConfig {
  apiKey: string;
  databaseUrl: string;
  encryptionKey: string;
  auditLogLevel: 'error' | 'warn' | 'info' | 'debug';
}
function validateEnvironment(): SecureConfig {
  const requiredVars = ['API_KEY', 'DATABASE_URL', 'ENCRYPTION_KEY'];
  const missing = requiredVars.filter(key => !process.env[key]);
  
  if (missing.length > 0) {
    throw new Error(`Missing required environment variables: ${missing.join(', ')}`);
  }
  
  return {
    apiKey: process.env.API_KEY!,
    databaseUrl: process.env.DATABASE_URL!,
    encryptionKey: process.env.ENCRYPTION_KEY!,
    auditLogLevel: (process.env.AUDIT_LOG_LEVEL as any) || 'info'
  };
}

Build-Time Security Checks

Integrating security validation into the build process catches issues early:

// Vite config with security-first environment loading
export default defineConfig({
  plugins: [
    {
      name: 'security-validation',
      buildStart() {
        try {
          validateEnvironment();
        } catch (error) {
          this.error(`Security validation failed: ${error.message}`);
        }
      }
    }
  ],
  build: {
    rollupOptions: {
      external: ['crypto'] // Ensure crypto module availability
    }
  }
});

Feature Flag Security Architecture

Feature flags in security systems require special consideration to prevent unauthorized feature access:

// Secure feature flag implementation
interface FeatureFlag {
  id: string;
  name: string;
  enabled: boolean;
  userGroups: string[];
  securityLevel: 'low' | 'medium' | 'high';
}
class SecureFeatureManager {
  async isFeatureEnabled(flagName: string, userId: string): Promise<boolean> {
    const flag = await this.getFlag(flagName);
    if (!flag) return false;
    
    // Security-level based validation
    if (flag.securityLevel === 'high') {
      const hasPermission = await this.validateHighSecurityAccess(userId);
      if (!hasPermission) {
        await this.auditLog('unauthorized_feature_access', { userId, flagName });
        return false;
      }
    }
    
    return flag.enabled && this.userInGroups(userId, flag.userGroups);
  }
  
  private async ensureFeatureSystemExists(): Promise<void> {
    // Idempotent feature system initialization
    const exists = await this.db.query('SELECT 1 FROM feature_flags LIMIT 1');
    if (exists.length === 0) {
      await this.seedDefaultFlags();
    }
  }
}

Summary

Secure access control systems require careful attention to policy design, state management, and deployment practices. Key principles include:

1. Avoid recursive policies that can cause infinite loops in authorization
2. Separate concerns between system configuration and user policies
3. Include comprehensive event data for accurate state reconstruction
4. Validate environments at build time to catch configuration issues early
5. Implement security-aware feature flags with proper audit logging

By following these patterns, smart lock management systems can maintain robust security while providing reliable access control functionality.