260 lines
13 KiB
SQL
260 lines
13 KiB
SQL
-- ============================================================================
|
|
-- ERP MECANICAS DIESEL - Consolidated RLS Policies
|
|
-- ============================================================================
|
|
-- File: 99-rls-policies.sql
|
|
-- Purpose: Row Level Security policies for tenant isolation
|
|
-- Generated: 2026-01-24
|
|
-- ============================================================================
|
|
-- This file consolidates all RLS policies for tables with tenant_id column.
|
|
-- The create_tenant_rls_policies() function in 02-rls-functions.sql handles
|
|
-- the actual policy creation. This file provides an explicit policy reference
|
|
-- for tables that may need manual policy management.
|
|
-- ============================================================================
|
|
|
|
-- ============================================================================
|
|
-- SCHEMA: workshop_core
|
|
-- ============================================================================
|
|
|
|
-- Table: users
|
|
ALTER TABLE workshop_core.users ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS tenant_isolation_users ON workshop_core.users;
|
|
CREATE POLICY tenant_isolation_users ON workshop_core.users
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
-- ============================================================================
|
|
-- SCHEMA: service_management
|
|
-- ============================================================================
|
|
|
|
-- Table: service_orders
|
|
ALTER TABLE service_management.service_orders ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS tenant_isolation_service_orders ON service_management.service_orders;
|
|
CREATE POLICY tenant_isolation_service_orders ON service_management.service_orders
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
-- Table: work_bays
|
|
ALTER TABLE service_management.work_bays ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS tenant_isolation_work_bays ON service_management.work_bays;
|
|
CREATE POLICY tenant_isolation_work_bays ON service_management.work_bays
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
-- Table: diagnostics
|
|
ALTER TABLE service_management.diagnostics ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS tenant_isolation_diagnostics ON service_management.diagnostics;
|
|
CREATE POLICY tenant_isolation_diagnostics ON service_management.diagnostics
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
-- Table: quotes
|
|
ALTER TABLE service_management.quotes ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS tenant_isolation_quotes ON service_management.quotes;
|
|
CREATE POLICY tenant_isolation_quotes ON service_management.quotes
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
-- Table: service_categories
|
|
ALTER TABLE service_management.service_categories ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS tenant_isolation_service_categories ON service_management.service_categories;
|
|
CREATE POLICY tenant_isolation_service_categories ON service_management.service_categories
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
-- Table: services
|
|
ALTER TABLE service_management.services ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS tenant_isolation_services ON service_management.services;
|
|
CREATE POLICY tenant_isolation_services ON service_management.services
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
-- Table: customers
|
|
ALTER TABLE service_management.customers ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS tenant_isolation_customers ON service_management.customers;
|
|
CREATE POLICY tenant_isolation_customers ON service_management.customers
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
-- Table: test_types (Note: tenant_id can be NULL for global types)
|
|
ALTER TABLE service_management.test_types ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS tenant_isolation_test_types ON service_management.test_types;
|
|
CREATE POLICY tenant_isolation_test_types ON service_management.test_types
|
|
USING (tenant_id IS NULL OR tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
-- Table: signature_audit
|
|
ALTER TABLE service_management.signature_audit ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS tenant_isolation_signature_audit ON service_management.signature_audit;
|
|
CREATE POLICY tenant_isolation_signature_audit ON service_management.signature_audit
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
-- ============================================================================
|
|
-- SCHEMA: parts_management
|
|
-- ============================================================================
|
|
|
|
-- Table: part_categories
|
|
ALTER TABLE parts_management.part_categories ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS tenant_isolation_part_categories ON parts_management.part_categories;
|
|
CREATE POLICY tenant_isolation_part_categories ON parts_management.part_categories
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
-- Table: suppliers
|
|
ALTER TABLE parts_management.suppliers ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS tenant_isolation_parts_suppliers ON parts_management.suppliers;
|
|
CREATE POLICY tenant_isolation_parts_suppliers ON parts_management.suppliers
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
-- Table: warehouse_locations
|
|
ALTER TABLE parts_management.warehouse_locations ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS tenant_isolation_warehouse_locations ON parts_management.warehouse_locations;
|
|
CREATE POLICY tenant_isolation_warehouse_locations ON parts_management.warehouse_locations
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
-- Table: parts
|
|
ALTER TABLE parts_management.parts ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS tenant_isolation_parts ON parts_management.parts;
|
|
CREATE POLICY tenant_isolation_parts ON parts_management.parts
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
-- Table: inventory_movements
|
|
ALTER TABLE parts_management.inventory_movements ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS tenant_isolation_inventory_movements ON parts_management.inventory_movements;
|
|
CREATE POLICY tenant_isolation_inventory_movements ON parts_management.inventory_movements
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
-- Table: inventory_adjustments
|
|
ALTER TABLE parts_management.inventory_adjustments ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS tenant_isolation_inventory_adjustments ON parts_management.inventory_adjustments;
|
|
CREATE POLICY tenant_isolation_inventory_adjustments ON parts_management.inventory_adjustments
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
-- Table: stock_alerts
|
|
ALTER TABLE parts_management.stock_alerts ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS tenant_isolation_stock_alerts ON parts_management.stock_alerts;
|
|
CREATE POLICY tenant_isolation_stock_alerts ON parts_management.stock_alerts
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
-- Table: physical_inventory
|
|
ALTER TABLE parts_management.physical_inventory ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS tenant_isolation_physical_inventory ON parts_management.physical_inventory;
|
|
CREATE POLICY tenant_isolation_physical_inventory ON parts_management.physical_inventory
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
-- Table: warranty_claims
|
|
ALTER TABLE parts_management.warranty_claims ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS tenant_isolation_warranty_claims ON parts_management.warranty_claims;
|
|
CREATE POLICY tenant_isolation_warranty_claims ON parts_management.warranty_claims
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
-- ============================================================================
|
|
-- SCHEMA: vehicle_management
|
|
-- ============================================================================
|
|
|
|
-- Table: fleets
|
|
ALTER TABLE vehicle_management.fleets ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS tenant_isolation_fleets ON vehicle_management.fleets;
|
|
CREATE POLICY tenant_isolation_fleets ON vehicle_management.fleets
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
-- Table: vehicles
|
|
ALTER TABLE vehicle_management.vehicles ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS tenant_isolation_vehicles ON vehicle_management.vehicles;
|
|
CREATE POLICY tenant_isolation_vehicles ON vehicle_management.vehicles
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
-- Table: maintenance_reminders
|
|
ALTER TABLE vehicle_management.maintenance_reminders ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS tenant_isolation_maintenance_reminders ON vehicle_management.maintenance_reminders;
|
|
CREATE POLICY tenant_isolation_maintenance_reminders ON vehicle_management.maintenance_reminders
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
-- ============================================================================
|
|
-- SCHEMA: notifications
|
|
-- ============================================================================
|
|
|
|
-- Table: messages
|
|
ALTER TABLE notifications.messages ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS tenant_isolation_messages ON notifications.messages;
|
|
CREATE POLICY tenant_isolation_messages ON notifications.messages
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
-- Table: followers
|
|
ALTER TABLE notifications.followers ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS tenant_isolation_followers ON notifications.followers;
|
|
CREATE POLICY tenant_isolation_followers ON notifications.followers
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
-- Table: activities
|
|
ALTER TABLE notifications.activities ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS tenant_isolation_activities ON notifications.activities;
|
|
CREATE POLICY tenant_isolation_activities ON notifications.activities
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
-- ============================================================================
|
|
-- SCHEMA: analytics
|
|
-- ============================================================================
|
|
|
|
-- Table: accounts
|
|
ALTER TABLE analytics.accounts ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS tenant_isolation_analytics_accounts ON analytics.accounts;
|
|
CREATE POLICY tenant_isolation_analytics_accounts ON analytics.accounts
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
-- Table: lines
|
|
ALTER TABLE analytics.lines ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS tenant_isolation_analytics_lines ON analytics.lines;
|
|
CREATE POLICY tenant_isolation_analytics_lines ON analytics.lines
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
-- ============================================================================
|
|
-- SCHEMA: purchasing
|
|
-- ============================================================================
|
|
|
|
-- Table: suppliers (distinct from parts_management.suppliers)
|
|
ALTER TABLE purchasing.suppliers ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS tenant_isolation_purchasing_suppliers ON purchasing.suppliers;
|
|
CREATE POLICY tenant_isolation_purchasing_suppliers ON purchasing.suppliers
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
-- Table: purchase_orders
|
|
ALTER TABLE purchasing.purchase_orders ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS tenant_isolation_purchase_orders ON purchasing.purchase_orders;
|
|
CREATE POLICY tenant_isolation_purchase_orders ON purchasing.purchase_orders
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
-- Table: purchase_receipts
|
|
ALTER TABLE purchasing.purchase_receipts ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS tenant_isolation_purchase_receipts ON purchasing.purchase_receipts;
|
|
CREATE POLICY tenant_isolation_purchase_receipts ON purchasing.purchase_receipts
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
-- ============================================================================
|
|
-- SUMMARY
|
|
-- ============================================================================
|
|
-- Total tables with RLS policies: 30
|
|
--
|
|
-- workshop_core: 1 table
|
|
-- - users
|
|
--
|
|
-- service_management: 10 tables
|
|
-- - service_orders, work_bays, diagnostics, quotes
|
|
-- - service_categories, services, customers, test_types
|
|
-- - signature_audit
|
|
--
|
|
-- parts_management: 9 tables
|
|
-- - part_categories, suppliers, warehouse_locations, parts
|
|
-- - inventory_movements, inventory_adjustments
|
|
-- - stock_alerts, physical_inventory, warranty_claims
|
|
--
|
|
-- vehicle_management: 3 tables
|
|
-- - fleets, vehicles, maintenance_reminders
|
|
--
|
|
-- notifications: 3 tables
|
|
-- - messages, followers, activities
|
|
--
|
|
-- analytics: 2 tables
|
|
-- - accounts, lines
|
|
--
|
|
-- purchasing: 3 tables
|
|
-- - suppliers, purchase_orders, purchase_receipts
|
|
-- ============================================================================
|
|
|
|
COMMENT ON SCHEMA workshop_core IS 'Workshop core schema with RLS enabled';
|
|
COMMENT ON SCHEMA service_management IS 'Service management schema with RLS enabled';
|
|
COMMENT ON SCHEMA parts_management IS 'Parts management schema with RLS enabled';
|
|
COMMENT ON SCHEMA vehicle_management IS 'Vehicle management schema with RLS enabled';
|
|
COMMENT ON SCHEMA notifications IS 'Notifications schema with RLS enabled';
|
|
COMMENT ON SCHEMA analytics IS 'Analytics schema with RLS enabled';
|
|
COMMENT ON SCHEMA purchasing IS 'Purchasing schema with RLS enabled';
|