Row Level Security (RLS)

Implementation of row-level security policies for data access control in the Smart Shelf database.

Row Level Security (RLS)

Row Level Security (RLS) is implemented throughout the Smart Shelf database to ensure users can only access data they're authorized to see and modify.

Overview

RLS policies are applied to sensitive tables to enforce business rules and security requirements at the database level. This provides an additional layer of security beyond application-level access controls.

Core Security Principles

1. User Context

All database operations include user context to determine access rights:

-- Set current user context
SET app.current_user_id = 'user-uuid-here';
SET app.current_user_role = 'manager';
SET app.current_warehouse_id = 'warehouse-uuid-here';

2. Role-Based Access

Different user roles have different levels of access:

  • Admin: Full system access
  • Manager: Department/warehouse-specific access
  • Employee: Limited operational access
  • Viewer: Read-only access

3. Data Isolation

Multi-tenant architecture ensures data isolation between different organizations or business units.

RLS Policy Examples

Users Table

Protects user information with role-based access:

-- Enable RLS
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Admin can see all users
CREATE POLICY admin_all_users ON users
  FOR ALL USING (
    EXISTS (
      SELECT 1 FROM users u 
      WHERE u.id::text = current_setting('app.current_user_id', true)
      AND u.role = 'admin'
    )
  );

-- Users can see their own record
CREATE POLICY user_own_record ON users
  FOR ALL USING (
    id::text = current_setting('app.current_user_id', true)
  );

-- Managers can see users in their department
CREATE POLICY manager_department_users ON users
  FOR SELECT USING (
    department_id IN (
      SELECT department_id FROM users
      WHERE id::text = current_setting('app.current_user_id', true)
      AND role IN ('admin', 'manager')
    )
  );

Products Table

Controls product visibility based on user access:

-- Enable RLS
ALTER TABLE products ENABLE ROW LEVEL SECURITY;

-- All authenticated users can view products
CREATE POLICY view_products ON products
  FOR SELECT USING (
    current_setting('app.current_user_id', true) IS NOT NULL
  );

-- Only managers and admins can modify products
CREATE POLICY modify_products ON products
  FOR ALL USING (
    EXISTS (
      SELECT 1 FROM users u 
      WHERE u.id::text = current_setting('app.current_user_id', true)
      AND u.role IN ('admin', 'manager')
    )
  );

-- Department-specific product access
CREATE POLICY department_products ON products
  FOR ALL USING (
    department_id IN (
      SELECT department_id FROM users
      WHERE id::text = current_setting('app.current_user_id', true)
    ) OR
    EXISTS (
      SELECT 1 FROM users u 
      WHERE u.id::text = current_setting('app.current_user_id', true)
      AND u.role = 'admin'
    )
  );

Inventory Table

Restricts inventory access to specific warehouses:

-- Enable RLS
ALTER TABLE inventory ENABLE ROW LEVEL SECURITY;

-- Warehouse-specific inventory access
CREATE POLICY warehouse_inventory ON inventory
  FOR ALL USING (
    warehouse_id IN (
      SELECT warehouse_id FROM user_warehouse_access uwa
      WHERE uwa.user_id::text = current_setting('app.current_user_id', true)
    ) OR
    EXISTS (
      SELECT 1 FROM users u 
      WHERE u.id::text = current_setting('app.current_user_id', true)
      AND u.role = 'admin'
    )
  );

-- Read-only access for viewers
CREATE POLICY viewer_inventory ON inventory
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM users u 
      WHERE u.id::text = current_setting('app.current_user_id', true)
      AND u.role = 'viewer'
    )
  );

Sales Orders Table

Protects customer order information:

-- Enable RLS
ALTER TABLE sales_orders ENABLE ROW LEVEL SECURITY;

-- Users can only see orders they created or are assigned to
CREATE POLICY user_sales_orders ON sales_orders
  FOR ALL USING (
    created_by::text = current_setting('app.current_user_id', true) OR
    assigned_to::text = current_setting('app.current_user_id', true) OR
    EXISTS (
      SELECT 1 FROM users u 
      WHERE u.id::text = current_setting('app.current_user_id', true)
      AND u.role IN ('admin', 'manager')
    )
  );

-- Department-based access
CREATE POLICY department_sales_orders ON sales_orders
  FOR SELECT USING (
    created_by IN (
      SELECT id FROM users
      WHERE department_id IN (
        SELECT department_id FROM users
        WHERE id::text = current_setting('app.current_user_id', true)
      )
    )
  );

Purchase Orders Table

Controls supplier and purchasing information:

-- Enable RLS
ALTER TABLE purchase_orders ENABLE ROW LEVEL SECURITY;

-- Only purchasing team and managers can access
CREATE POLICY purchasing_access ON purchase_orders
  FOR ALL USING (
    EXISTS (
      SELECT 1 FROM users u 
      WHERE u.id::text = current_setting('app.current_user_id', true)
      AND (u.role IN ('admin', 'manager') OR u.department = 'purchasing')
    )
  );

-- Users can see their own created orders
CREATE POLICY user_purchase_orders ON purchase_orders
  FOR SELECT USING (
    created_by::text = current_setting('app.current_user_id', true)
  );

Audit and Compliance

Audit Log Access

Protects sensitive audit information:

-- Enable RLS on audit logs
ALTER TABLE audit_logs ENABLE ROW LEVEL SECURITY;

-- Only admins and compliance officers can access audit logs
CREATE POLICY audit_access ON audit_logs
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM users u 
      WHERE u.id::text = current_setting('app.current_user_id', true)
      AND (u.role = 'admin' OR u.department = 'compliance')
    )
  );

-- Users can see audit logs for their own actions
CREATE POLICY user_audit_logs ON audit_logs
  FOR SELECT USING (
    user_id::text = current_setting('app.current_user_id', true)
  );

Dynamic Policies

Time-Based Access

Some policies include time-based restrictions:

-- Only allow modifications during business hours
CREATE POLICY business_hours_modification ON inventory
  FOR UPDATE USING (
    EXTRACT(hour FROM CURRENT_TIME) BETWEEN 6 AND 22 OR
    EXISTS (
      SELECT 1 FROM users u 
      WHERE u.id::text = current_setting('app.current_user_id', true)
      AND u.role = 'admin'
    )
  );

Location-Based Access

IP or location-based restrictions:

-- Restrict sensitive operations to office networks
CREATE POLICY office_network_access ON financial_records
  FOR ALL USING (
    inet_client_addr() << '192.168.1.0/24'::inet OR
    EXISTS (
      SELECT 1 FROM users u 
      WHERE u.id::text = current_setting('app.current_user_id', true)
      AND u.role = 'admin'
    )
  );

Performance Considerations

Policy Optimization

  • Use indexes on columns referenced in RLS policies
  • Keep policy conditions simple and efficient
  • Test policy performance with large datasets

Monitoring

Regular monitoring of RLS policy performance:

-- Monitor slow queries affected by RLS
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
WHERE query LIKE '%policy%'
ORDER BY total_time DESC;

Testing RLS Policies

Test Framework

Comprehensive testing approach for RLS policies:

-- Test user context switching
BEGIN;
SET app.current_user_id = 'test-user-id';
SET app.current_user_role = 'employee';

-- Verify expected access
SELECT COUNT(*) FROM products; -- Should return limited results

-- Test unauthorized access
SELECT COUNT(*) FROM users; -- Should return minimal results

ROLLBACK;

Automated Testing

Automated tests verify policy effectiveness:

  • Unit tests for each policy
  • Integration tests for complex scenarios
  • Performance tests under load
  • Security penetration testing

Best Practices

  1. Principle of Least Privilege: Grant minimum necessary access
  2. Defense in Depth: Combine RLS with application-level security
  3. Regular Audits: Review and update policies regularly
  4. Performance Testing: Monitor impact on query performance
  5. Documentation: Maintain clear policy documentation
  6. Error Handling: Provide meaningful error messages for access denials