Security & Access Control

Row Level Security (RLS), access control patterns, and database security for Smart Shelf.

Security & Access Control

Comprehensive database security implementation using Row Level Security (RLS), access control patterns, and data protection strategies in Smart Shelf's PostgreSQL/Supabase architecture.

Security Architecture Overview

Smart Shelf implements a multi-layered database security approach:

  1. Row Level Security (RLS) - Fine-grained access control at the row level
  2. Role-Based Access Control (RBAC) - User roles and permissions
  3. Data Isolation - Warehouse and tenant-based data separation
  4. Audit Logging - Complete audit trail of data changes

Row Level Security (RLS) Policies

User-Based Access Control

User Profile Access

-- Enable RLS on user_profiles table
ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY;

-- Users can only access their own profile
CREATE POLICY "Users can view own profile" ON user_profiles
  FOR SELECT USING (auth.uid() = id);

-- Users can update their own profile (limited fields)
CREATE POLICY "Users can update own profile" ON user_profiles
  FOR UPDATE USING (auth.uid() = id)
  WITH CHECK (auth.uid() = id);

-- Only admins can create new user profiles
CREATE POLICY "Admins can create user profiles" ON user_profiles
  FOR INSERT WITH CHECK (
    EXISTS (
      SELECT 1 FROM user_profiles
      WHERE id = auth.uid() AND role = 'admin'
    )
  );

-- Only admins can delete user profiles
CREATE POLICY "Admins can delete user profiles" ON user_profiles
  FOR DELETE USING (
    EXISTS (
      SELECT 1 FROM user_profiles
      WHERE id = auth.uid() AND role = 'admin'
    )
  );

Role-Based Access Control

Admin Access Policies

-- Admins can manage all data
CREATE POLICY "Admins can manage all products" ON products
  FOR ALL USING (
    EXISTS (
      SELECT 1 FROM user_profiles
      WHERE id = auth.uid() AND role = 'admin'
    )
  );

CREATE POLICY "Admins can manage all inventory" ON inventory
  FOR ALL USING (
    EXISTS (
      SELECT 1 FROM user_profiles
      WHERE id = auth.uid() AND role = 'admin'
    )
  );

CREATE POLICY "Admins can view all stock movements" ON stock_movements
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM user_profiles
      WHERE id = auth.uid() AND role = 'admin'
    )
  );

Manager Access Policies

-- Managers can view and edit data in their assigned warehouse
CREATE POLICY "Managers can manage warehouse inventory" ON inventory
  FOR ALL USING (
    warehouse_id IN (
      SELECT warehouse_id FROM user_profiles
      WHERE id = auth.uid() AND role IN ('admin', 'manager')
    )
  );

-- Managers can view all products but only edit in their domain
CREATE POLICY "Managers can view all products" ON products
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM user_profiles
      WHERE id = auth.uid() AND role IN ('admin', 'manager', 'staff')
    )
  );

CREATE POLICY "Managers can edit products" ON products
  FOR UPDATE USING (
    EXISTS (
      SELECT 1 FROM user_profiles
      WHERE id = auth.uid() AND role IN ('admin', 'manager')
    )
  );

Staff Access Policies

-- Staff can view and update inventory in their warehouse
CREATE POLICY "Staff can view warehouse inventory" ON inventory
  FOR SELECT USING (
    warehouse_id = (
      SELECT warehouse_id FROM user_profiles
      WHERE id = auth.uid()
    )
  );

CREATE POLICY "Staff can update inventory quantities" ON inventory
  FOR UPDATE USING (
    warehouse_id = (
      SELECT warehouse_id FROM user_profiles
      WHERE id = auth.uid()
    )
  )
  WITH CHECK (
    warehouse_id = (
      SELECT warehouse_id FROM user_profiles
      WHERE id = auth.uid()
    )
  );

-- Staff can create stock movements in their warehouse
CREATE POLICY "Staff can create stock movements" ON stock_movements
  FOR INSERT WITH CHECK (
    warehouse_id = (
      SELECT warehouse_id FROM user_profiles
      WHERE id = auth.uid()
    )
    AND created_by = auth.uid()
  );

Viewer Access Policies

-- Viewers have read-only access to their warehouse data
CREATE POLICY "Viewers can view warehouse data" ON inventory
  FOR SELECT USING (
    warehouse_id = (
      SELECT warehouse_id FROM user_profiles
      WHERE id = auth.uid() AND role = 'viewer'
    )
  );

CREATE POLICY "Viewers can view stock movements" ON stock_movements
  FOR SELECT USING (
    warehouse_id = (
      SELECT warehouse_id FROM user_profiles
      WHERE id = auth.uid() AND role = 'viewer'
    )
  );

Data Isolation Policies

Warehouse-Based Isolation

-- Create function to get user's accessible warehouses
CREATE OR REPLACE FUNCTION get_user_warehouses(user_id UUID)
RETURNS UUID[] AS $$
BEGIN
  RETURN ARRAY(
    SELECT CASE 
      WHEN up.role = 'admin' THEN w.id
      ELSE up.warehouse_id
    END
    FROM user_profiles up
    CROSS JOIN warehouses w
    WHERE up.id = user_id
      AND (up.role = 'admin' OR w.id = up.warehouse_id)
      AND w.is_active = true
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Apply warehouse isolation to inventory
CREATE POLICY "Warehouse data isolation" ON inventory
  FOR ALL USING (
    warehouse_id = ANY(get_user_warehouses(auth.uid()))
  );

-- Apply warehouse isolation to stock movements
CREATE POLICY "Warehouse stock movement isolation" ON stock_movements
  FOR ALL USING (
    warehouse_id = ANY(get_user_warehouses(auth.uid()))
  );

Time-Based Access Control

-- Prevent editing of old stock movements (older than 24 hours)
CREATE POLICY "Prevent editing old stock movements" ON stock_movements
  FOR UPDATE USING (
    created_at > NOW() - INTERVAL '24 hours'
    AND created_by = auth.uid()
  );

-- Allow deletion only within 1 hour of creation
CREATE POLICY "Allow recent stock movement deletion" ON stock_movements
  FOR DELETE USING (
    created_at > NOW() - INTERVAL '1 hour'
    AND created_by = auth.uid()
    AND EXISTS (
      SELECT 1 FROM user_profiles
      WHERE id = auth.uid() AND role IN ('admin', 'manager')
    )
  );

Security Functions

User Role Validation

-- Function to check if user has required role
CREATE OR REPLACE FUNCTION user_has_role(required_role user_role)
RETURNS BOOLEAN AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 FROM user_profiles
    WHERE id = auth.uid()
      AND role = required_role
      AND is_active = true
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to check if user has permission level
CREATE OR REPLACE FUNCTION user_has_permission_level(required_level INTEGER)
RETURNS BOOLEAN AS $$
DECLARE
  user_level INTEGER;
BEGIN
  SELECT 
    CASE role
      WHEN 'admin' THEN 4
      WHEN 'manager' THEN 3
      WHEN 'staff' THEN 2
      WHEN 'viewer' THEN 1
      ELSE 0
    END INTO user_level
  FROM user_profiles
  WHERE id = auth.uid() AND is_active = true;
  
  RETURN COALESCE(user_level, 0) >= required_level;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Secure Data Access

-- Function to get filtered inventory based on user permissions
CREATE OR REPLACE FUNCTION get_user_inventory(
  p_warehouse_id UUID DEFAULT NULL,
  p_low_stock_only BOOLEAN DEFAULT FALSE
)
RETURNS TABLE (
  product_id UUID,
  product_name VARCHAR,
  sku VARCHAR,
  warehouse_id UUID,
  warehouse_name VARCHAR,
  quantity_on_hand INTEGER,
  quantity_available INTEGER,
  stock_status TEXT
) 
SECURITY DEFINER
AS $$
BEGIN
  -- Check if user has access
  IF NOT EXISTS (
    SELECT 1 FROM user_profiles
    WHERE id = auth.uid() AND is_active = true
  ) THEN
    RAISE EXCEPTION 'Access denied';
  END IF;

  RETURN QUERY
  SELECT 
    i.product_id,
    p.name,
    p.sku,
    i.warehouse_id,
    w.name,
    i.quantity_on_hand,
    i.quantity_available,
    CASE 
      WHEN i.quantity_available <= 0 THEN 'OUT_OF_STOCK'
      WHEN i.quantity_available <= p.min_stock_level THEN 'LOW_STOCK'
      ELSE 'IN_STOCK'
    END
  FROM inventory i
  JOIN products p ON i.product_id = p.id
  JOIN warehouses w ON i.warehouse_id = w.id
  WHERE 
    -- Apply warehouse filter if provided
    (p_warehouse_id IS NULL OR i.warehouse_id = p_warehouse_id)
    -- Apply low stock filter if requested
    AND (NOT p_low_stock_only OR i.quantity_available <= p.min_stock_level)
    -- Apply RLS policies
    AND i.warehouse_id = ANY(get_user_warehouses(auth.uid()))
    -- Only active records
    AND p.is_active = true
    AND w.is_active = true;
END;
$$ LANGUAGE plpgsql;

Audit and Logging

Sensitive Operation Logging

-- Enhanced audit log for sensitive operations
CREATE TABLE security_audit_log (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES user_profiles(id),
  operation VARCHAR(50) NOT NULL,
  resource_type VARCHAR(50) NOT NULL,
  resource_id UUID,
  details JSONB,
  ip_address INET,
  user_agent TEXT,
  success BOOLEAN DEFAULT true,
  error_message TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Function to log security events
CREATE OR REPLACE FUNCTION log_security_event(
  p_operation VARCHAR,
  p_resource_type VARCHAR,
  p_resource_id UUID DEFAULT NULL,
  p_details JSONB DEFAULT NULL,
  p_success BOOLEAN DEFAULT TRUE,
  p_error_message TEXT DEFAULT NULL
)
RETURNS VOID AS $$
BEGIN
  INSERT INTO security_audit_log (
    user_id,
    operation,
    resource_type,
    resource_id,
    details,
    ip_address,
    user_agent,
    success,
    error_message
  ) VALUES (
    auth.uid(),
    p_operation,
    p_resource_type,
    p_resource_id,
    p_details,
    COALESCE(current_setting('request.headers', true)::jsonb->>'x-forwarded-for', '0.0.0.0')::inet,
    current_setting('request.headers', true)::jsonb->>'user-agent',
    p_success,
    p_error_message
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Automatic Security Logging Triggers

-- Trigger for sensitive operations
CREATE OR REPLACE FUNCTION security_audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
  -- Log all changes to user_profiles
  IF TG_TABLE_NAME = 'user_profiles' THEN
    PERFORM log_security_event(
      TG_OP,
      'user_profile',
      COALESCE(NEW.id, OLD.id),
      jsonb_build_object(
        'old_role', OLD.role,
        'new_role', NEW.role,
        'old_warehouse', OLD.warehouse_id,
        'new_warehouse', NEW.warehouse_id
      )
    );
  END IF;

  -- Log large stock adjustments
  IF TG_TABLE_NAME = 'inventory_adjustments' AND TG_OP = 'INSERT' THEN
    IF ABS(NEW.adjustment_quantity) >= 100 THEN
      PERFORM log_security_event(
        'large_stock_adjustment',
        'inventory',
        NEW.product_id,
        jsonb_build_object(
          'adjustment_quantity', NEW.adjustment_quantity,
          'reason', NEW.reason,
          'warehouse_id', NEW.warehouse_id
        )
      );
    END IF;
  END IF;

  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

-- Apply security audit triggers
CREATE TRIGGER user_profiles_security_audit
  AFTER INSERT OR UPDATE OR DELETE ON user_profiles
  FOR EACH ROW EXECUTE FUNCTION security_audit_trigger();

CREATE TRIGGER inventory_adjustments_security_audit
  AFTER INSERT ON inventory_adjustments
  FOR EACH ROW EXECUTE FUNCTION security_audit_trigger();

Data Encryption

Sensitive Data Protection

-- Function to encrypt sensitive data
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Create function for encryption
CREATE OR REPLACE FUNCTION encrypt_sensitive_data(data TEXT)
RETURNS TEXT AS $$
BEGIN
  RETURN encode(
    encrypt(
      data::bytea,
      current_setting('app.encryption_key')::bytea,
      'aes'
    ),
    'base64'
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Create function for decryption
CREATE OR REPLACE FUNCTION decrypt_sensitive_data(encrypted_data TEXT)
RETURNS TEXT AS $$
BEGIN
  RETURN convert_from(
    decrypt(
      decode(encrypted_data, 'base64'),
      current_setting('app.encryption_key')::bytea,
      'aes'
    ),
    'UTF8'
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Personal Data Protection (GDPR Compliance)

-- Table for tracking personal data
CREATE TABLE personal_data_processing (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES user_profiles(id),
  processing_purpose VARCHAR(100) NOT NULL,
  legal_basis VARCHAR(50) NOT NULL,
  data_categories TEXT[],
  retention_period INTERVAL,
  consent_given BOOLEAN DEFAULT FALSE,
  consent_date TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Function to anonymize user data
CREATE OR REPLACE FUNCTION anonymize_user_data(p_user_id UUID)
RETURNS VOID AS $$
BEGIN
  -- Log the anonymization
  PERFORM log_security_event(
    'data_anonymization',
    'user_profile',
    p_user_id,
    jsonb_build_object('reason', 'GDPR_request')
  );

  -- Anonymize user profile
  UPDATE user_profiles 
  SET 
    full_name = 'Anonymized User',
    avatar_url = NULL,
    is_active = FALSE
  WHERE id = p_user_id;

  -- Remove from processing records
  DELETE FROM personal_data_processing WHERE user_id = p_user_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Security Monitoring

Failed Access Attempts

-- Table for tracking failed access attempts
CREATE TABLE failed_access_attempts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES user_profiles(id),
  email VARCHAR(255),
  ip_address INET,
  attempted_resource VARCHAR(255),
  failure_reason VARCHAR(100),
  user_agent TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Function to log failed access
CREATE OR REPLACE FUNCTION log_failed_access(
  p_email VARCHAR DEFAULT NULL,
  p_resource VARCHAR DEFAULT NULL,
  p_reason VARCHAR DEFAULT 'unauthorized_access'
)
RETURNS VOID AS $$
BEGIN
  INSERT INTO failed_access_attempts (
    user_id,
    email,
    ip_address,
    attempted_resource,
    failure_reason,
    user_agent
  ) VALUES (
    auth.uid(),
    p_email,
    COALESCE(current_setting('request.headers', true)::jsonb->>'x-forwarded-for', '0.0.0.0')::inet,
    p_resource,
    p_reason,
    current_setting('request.headers', true)::jsonb->>'user-agent'
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

This comprehensive security implementation ensures data protection, access control, and compliance with privacy regulations while maintaining operational efficiency.