107 lines
3.3 KiB
PL/PgSQL
107 lines
3.3 KiB
PL/PgSQL
-- ===========================================
|
|
-- MECANICAS DIESEL - Funciones RLS Multi-Tenant
|
|
-- ===========================================
|
|
-- Funciones para Row-Level Security
|
|
|
|
-- Función para obtener el tenant_id actual de la sesión
|
|
CREATE OR REPLACE FUNCTION get_current_tenant_id()
|
|
RETURNS UUID AS $$
|
|
BEGIN
|
|
RETURN NULLIF(current_setting('app.current_tenant_id', true), '')::UUID;
|
|
EXCEPTION
|
|
WHEN OTHERS THEN
|
|
RETURN NULL;
|
|
END;
|
|
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER;
|
|
|
|
COMMENT ON FUNCTION get_current_tenant_id() IS 'Obtiene el tenant_id de la sesión actual para RLS';
|
|
|
|
-- Función para establecer el tenant_id en la sesión
|
|
CREATE OR REPLACE FUNCTION set_current_tenant_id(p_tenant_id UUID)
|
|
RETURNS VOID AS $$
|
|
BEGIN
|
|
PERFORM set_config('app.current_tenant_id', p_tenant_id::TEXT, false);
|
|
END;
|
|
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
|
|
|
COMMENT ON FUNCTION set_current_tenant_id(UUID) IS 'Establece el tenant_id para la sesión actual';
|
|
|
|
-- Función para obtener el user_id actual
|
|
CREATE OR REPLACE FUNCTION get_current_user_id()
|
|
RETURNS UUID AS $$
|
|
BEGIN
|
|
RETURN NULLIF(current_setting('app.current_user_id', true), '')::UUID;
|
|
EXCEPTION
|
|
WHEN OTHERS THEN
|
|
RETURN NULL;
|
|
END;
|
|
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER;
|
|
|
|
-- Trigger function para updated_at automático
|
|
CREATE OR REPLACE FUNCTION trigger_set_updated_at()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
COMMENT ON FUNCTION trigger_set_updated_at() IS 'Actualiza automáticamente updated_at';
|
|
|
|
-- Trigger function para created_by automático
|
|
CREATE OR REPLACE FUNCTION trigger_set_created_by()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
IF NEW.created_by IS NULL THEN
|
|
NEW.created_by = get_current_user_id();
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Macro para crear políticas RLS completas
|
|
-- Uso: SELECT create_tenant_rls_policies('schema_name', 'table_name');
|
|
CREATE OR REPLACE FUNCTION create_tenant_rls_policies(
|
|
p_schema TEXT,
|
|
p_table TEXT,
|
|
p_tenant_column TEXT DEFAULT 'tenant_id'
|
|
)
|
|
RETURNS VOID AS $$
|
|
DECLARE
|
|
v_full_table TEXT;
|
|
BEGIN
|
|
v_full_table := p_schema || '.' || p_table;
|
|
|
|
-- Habilitar RLS
|
|
EXECUTE format('ALTER TABLE %I.%I ENABLE ROW LEVEL SECURITY', p_schema, p_table);
|
|
|
|
-- Política SELECT
|
|
EXECUTE format(
|
|
'CREATE POLICY %I ON %I.%I FOR SELECT USING (%I = get_current_tenant_id())',
|
|
p_table || '_select_policy', p_schema, p_table, p_tenant_column
|
|
);
|
|
|
|
-- Política INSERT
|
|
EXECUTE format(
|
|
'CREATE POLICY %I ON %I.%I FOR INSERT WITH CHECK (%I = get_current_tenant_id())',
|
|
p_table || '_insert_policy', p_schema, p_table, p_tenant_column
|
|
);
|
|
|
|
-- Política UPDATE
|
|
EXECUTE format(
|
|
'CREATE POLICY %I ON %I.%I FOR UPDATE USING (%I = get_current_tenant_id()) WITH CHECK (%I = get_current_tenant_id())',
|
|
p_table || '_update_policy', p_schema, p_table, p_tenant_column, p_tenant_column
|
|
);
|
|
|
|
-- Política DELETE
|
|
EXECUTE format(
|
|
'CREATE POLICY %I ON %I.%I FOR DELETE USING (%I = get_current_tenant_id())',
|
|
p_table || '_delete_policy', p_schema, p_table, p_tenant_column
|
|
);
|
|
|
|
RAISE NOTICE 'RLS policies created for %.%', p_schema, p_table;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
COMMENT ON FUNCTION create_tenant_rls_policies(TEXT, TEXT, TEXT) IS 'Crea políticas RLS completas (SELECT, INSERT, UPDATE, DELETE) para una tabla';
|