Securing Multi-Tenant Smart Lock Systems with Row-Level Security Policies

Introduction

In smart lock management systems, tenant isolation is not just a feature—it's a critical security requirement. A single misconfigured database query could potentially expose one property's access codes to another tenant, creating serious security vulnerabilities. This article explores how Row-Level Security (RLS) policies provide a robust foundation for multi-tenant security in access control systems.

The Challenge of Multi-Tenant Security

Smart lock systems handle highly sensitive data including access codes, guest information, and property details. In a multi-tenant environment, the database contains data from multiple properties and management companies, making proper isolation crucial.

Traditional application-level security can fail when:

• Developers forget to add tenant checks in queries
• Complex JOIN operations bypass tenant filters
• Administrative functions accidentally expose cross-tenant data
• Security reviews miss edge cases in data access patterns

Implementing Robust RLS Policies

Row-Level Security moves tenant isolation from the application layer to the database itself, creating a final line of defense against data leakage.

Basic Tenant Isolation Policy

-- Enable RLS on sensitive tables
ALTER TABLE access_codes ENABLE ROW LEVEL SECURITY;
ALTER TABLE guest_registrations ENABLE ROW LEVEL SECURITY;
ALTER TABLE property_configurations ENABLE ROW LEVEL SECURITY;

-- Create policy for tenant isolation
CREATE POLICY tenant_isolation_policy ON access_codes
  FOR ALL
  TO authenticated_users
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

Advanced Multi-Role Policies

Smart lock systems often require different access levels for property managers, staff, and system administrators:

-- Policy for property managers - full access to their properties
CREATE POLICY property_manager_policy ON access_codes
  FOR ALL
  TO property_managers
  USING (
    tenant_id = current_setting('app.current_tenant_id')::uuid
    AND property_id IN (
      SELECT property_id 
      FROM manager_properties 
      WHERE manager_id = current_setting('app.current_user_id')::uuid
    )
  );

-- Policy for staff - read-only access to assigned properties
CREATE POLICY staff_readonly_policy ON access_codes
  FOR SELECT
  TO property_staff
  USING (
    tenant_id = current_setting('app.current_tenant_id')::uuid
    AND property_id IN (
      SELECT property_id 
      FROM staff_assignments 
      WHERE staff_id = current_setting('app.current_user_id')::uuid
      AND is_active = true
    )
  );

Context Setting for Secure Sessions

RLS policies rely on session variables to determine the current tenant and user context. This must be set securely at the start of each database session:

export class SecureSessionManager {
  async initializeSession(userId: string, tenantId: string): Promise<void> {
    // Validate tenant membership before setting context
    const membership = await this.validateTenantMembership(userId, tenantId);
    if (!membership.isValid) {
      throw new SecurityError('Invalid tenant access');
    }

    // Set secure session variables
    await this.db.query(`
      SELECT 
        set_config('app.current_user_id', $1, true),
        set_config('app.current_tenant_id', $2, true),
        set_config('app.user_role', $3, true)
    `, [userId, tenantId, membership.role]);
  }

  private async validateTenantMembership(userId: string, tenantId: string) {
    const result = await this.db.query(`
      SELECT role, is_active
      FROM tenant_memberships
      WHERE user_id = $1 AND tenant_id = $2 AND is_active = true
    `, [userId, tenantId]);

    return {
      isValid: result.rows.length > 0 && result.rows[0].is_active,
      role: result.rows[0]?.role
    };
  }
}

Testing RLS Policy Effectiveness

Robust testing ensures RLS policies work as expected across different scenarios:

describe('RLS Policy Security Tests', () => {
  it('should prevent cross-tenant data access', async () => {
    // Set up test data for different tenants
    const tenantA = await createTestTenant();
    const tenantB = await createTestTenant();
    
    const accessCodeA = await createAccessCode(tenantA.id);
    const accessCodeB = await createAccessCode(tenantB.id);

    // Test tenant A isolation
    await withTenantContext(tenantA.id, async (db) => {
      const codes = await db.query('SELECT * FROM access_codes');
      
      expect(codes.rows).toHaveLength(1);
      expect(codes.rows[0].id).toBe(accessCodeA.id);
      expect(codes.rows.find(r => r.id === accessCodeB.id)).toBeUndefined();
    });
  });

  it('should enforce role-based access restrictions', async () => {
    const tenant = await createTestTenant();
    const property1 = await createProperty(tenant.id);
    const property2 = await createProperty(tenant.id);
    
    // Staff assigned only to property1
    const staffUser = await createStaffUser(tenant.id, [property1.id]);

    await withUserContext(staffUser.id, tenant.id, async (db) => {
      const accessibleCodes = await db.query(
        'SELECT * FROM access_codes WHERE property_id = ANY($1)',
        [[property1.id, property2.id]]
      );
      
      // Should only see codes for assigned property
      accessibleCodes.rows.forEach(code => {
        expect(code.property_id).toBe(property1.id);
      });
    });
  });
});

Performance Considerations

RLS policies can impact query performance, especially with complex tenant hierarchies. Optimize with proper indexing:

-- Composite indexes for efficient policy enforcement
CREATE INDEX idx_access_codes_tenant_property 
  ON access_codes (tenant_id, property_id);

CREATE INDEX idx_manager_properties_lookup
  ON manager_properties (manager_id, property_id)
  WHERE is_active = true;

-- Analyze query plans to ensure policies use indexes
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM access_codes 
WHERE property_id = '123e4567-e89b-12d3-a456-426614174000';

Database Cleanup and Security Maintenance

Regular database maintenance includes removing unused indexes and test tables that could pose security risks:

-- Drop unused indexes that could leak schema information
DROP INDEX IF EXISTS old_test_index;
DROP INDEX IF EXISTS unused_performance_index;

-- Remove test tables from production
DROP TABLE IF EXISTS test_access_codes CASCADE;
DROP TABLE IF EXISTS debug_tenant_data CASCADE;

-- Audit existing policies
SELECT schemaname, tablename, policyname, permissive, roles, cmd, qual
FROM pg_policies
WHERE schemaname = 'public'
ORDER BY tablename, policyname;

Summary

Row-Level Security policies provide essential defense-in-depth for multi-tenant smart lock systems. By implementing tenant isolation at the database level, systems become resilient against application-layer security bugs and provide strong guarantees about data access. Combined with proper session management, comprehensive testing, and regular security audits, RLS policies form the foundation of a trustworthy access control system.

The key to effective RLS implementation lies in treating it as a security layer that complements, rather than replaces, application-level access controls. This multi-layered approach ensures that even if application logic fails, the database itself prevents unauthorized data access.