mirror of
https://origin.cursor.com/mrdevmx/panels.git
synced 2026-10-09 18:23:22 +00:00
a9fbca232d
The migrate job failed at changeset 005h during Coolify deploy. Liquibase was splitting the DO $$ block on inner semicolons (splitStatements:true), which breaks PL/pgSQL. Move the unique-index DO block to 005h2 with splitStatements:false. Add 005g2 to create missing operativo roles per tenant, backfill any remaining role_id values, demote duplicate tenant_admin users to operativo, and remove orphan users without tenant_id before SET NOT NULL. Co-authored-by: alberto.martinez <alberto.martinez@mrdev.mx>
1016 lines
42 KiB
PL/PgSQL
1016 lines
42 KiB
PL/PgSQL
--liquibase formatted sql
|
|
-- PANELS · iam · IAM v2: permisos CRUD, roles por tenant, usuarios ampliados y RPC
|
|
|
|
--changeset panel:iam-005a-permissions-metadata endDelimiter:; splitStatements:true
|
|
--preconditions onFail:MARK_RAN
|
|
--precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM information_schema.columns WHERE table_schema='iam' AND table_name='permissions' AND column_name='module'
|
|
ALTER TABLE iam.permissions ADD COLUMN module TEXT;
|
|
ALTER TABLE iam.permissions ADD COLUMN verb TEXT;
|
|
ALTER TABLE iam.permissions ADD COLUMN perm_group TEXT;
|
|
|
|
--changeset panel:iam-005b-permissions-v2-seed endDelimiter:; splitStatements:true
|
|
INSERT INTO iam.permissions (code, label, module, verb, perm_group) VALUES
|
|
('workers.view', 'Consultar padrón', 'workers', 'view', NULL),
|
|
('workers.create', 'Alta de personal', 'workers', 'create', NULL),
|
|
('workers.update', 'Editar personal', 'workers', 'update', NULL),
|
|
('workers.delete', 'Baja de personal', 'workers', 'delete', NULL),
|
|
('kanban.view', 'Tablero kanban', 'kanban', 'view', NULL),
|
|
('projects.view', 'Consultar obras', 'projects', 'view', NULL),
|
|
('projects.create', 'Alta de obras', 'projects', 'create', NULL),
|
|
('projects.update', 'Editar obras', 'projects', 'update', NULL),
|
|
('companies.view', 'Consultar empresas', 'companies', 'view', NULL),
|
|
('companies.create', 'Alta de empresas', 'companies', 'create', NULL),
|
|
('companies.update', 'Editar empresas', 'companies', 'update', NULL),
|
|
('budget.view', 'Ver presupuesto', 'budget', 'view', NULL),
|
|
('budget.create', 'Alta partidas presupuesto', 'budget', 'create', NULL),
|
|
('budget.update', 'Editar presupuesto', 'budget', 'update', NULL),
|
|
('budget.delete', 'Eliminar partidas', 'budget', 'delete', NULL),
|
|
('work_program.view', 'Ver programa de obra', 'work_program', 'view', NULL),
|
|
('work_program.create', 'Importar/generar programa', 'work_program', 'create', NULL),
|
|
('work_program.update', 'Capturar avances', 'work_program', 'update', NULL),
|
|
('cost_control.view', 'Control de costos', 'cost_control', 'view', NULL),
|
|
('expenses.view', 'Ver gastos', 'expenses', 'view', NULL),
|
|
('expenses.create', 'Capturar gastos', 'expenses', 'create', NULL),
|
|
('expenses.update', 'Editar gastos', 'expenses', 'update', NULL),
|
|
('expenses.delete', 'Eliminar gastos', 'expenses', 'delete', NULL),
|
|
('warehouse.view', 'Ver almacén', 'warehouse', 'view', NULL),
|
|
('warehouse.create', 'Entradas almacén', 'warehouse', 'create', NULL),
|
|
('warehouse.update', 'Salidas/transferencias', 'warehouse', 'update', NULL),
|
|
('payroll.view', 'Ver nómina', 'payroll', 'view', NULL),
|
|
('payroll.create', 'Alta nómina/préstamos', 'payroll', 'create', NULL),
|
|
('payroll.update', 'Armar/pagar nómina', 'payroll', 'update', NULL),
|
|
('payroll.delete', 'Eliminar líneas nómina', 'payroll', 'delete', NULL),
|
|
('documents.view', 'Ver documentos/gafetes', 'documents', 'view', NULL),
|
|
('documents.create', 'Subir documentos/gafetes', 'documents', 'create', NULL),
|
|
('project_docs.tecnico.view', 'Planos y docs técnicos', 'project_docs', 'view', 'tecnico'),
|
|
('project_docs.tecnico.create', 'Subir docs técnicos', 'project_docs', 'create', 'tecnico'),
|
|
('project_docs.contrato.view', 'Docs contrato', 'project_docs', 'view', 'contrato'),
|
|
('project_docs.contrato.create', 'Subir docs contrato', 'project_docs', 'create', 'contrato'),
|
|
('project_docs.permisos.view', 'Docs permisos', 'project_docs', 'view', 'permisos'),
|
|
('project_docs.permisos.create', 'Subir docs permisos', 'project_docs', 'create', 'permisos'),
|
|
('project_docs.ambiental.view', 'Docs ambientales', 'project_docs', 'view', 'ambiental'),
|
|
('project_docs.ambiental.create', 'Subir docs ambientales', 'project_docs', 'create', 'ambiental'),
|
|
('project_docs.imss.view', 'Docs IMSS', 'project_docs', 'view', 'imss'),
|
|
('project_docs.imss.create', 'Subir docs IMSS', 'project_docs', 'create', 'imss'),
|
|
('project_docs.sst.view', 'Docs SST', 'project_docs', 'view', 'sst'),
|
|
('project_docs.sst.create', 'Subir docs SST', 'project_docs', 'create', 'sst'),
|
|
('project_docs.otro.view', 'Otros docs obra', 'project_docs', 'view', 'otro'),
|
|
('project_docs.otro.create', 'Subir otros docs', 'project_docs', 'create', 'otro'),
|
|
('users.view', 'Listar usuarios IAM', 'users', 'view', NULL),
|
|
('users.create', 'Alta usuarios IAM', 'users', 'create', NULL),
|
|
('users.update', 'Editar usuarios IAM', 'users', 'update', NULL),
|
|
('users.delete', 'Baja usuarios IAM', 'users', 'delete', NULL),
|
|
('settings.view', 'Ver configuración', 'settings', 'view', NULL),
|
|
('settings.update', 'Guardar configuración', 'settings', 'update', NULL),
|
|
('reports.view', 'Reportes e inicio', 'reports', 'view', NULL)
|
|
ON CONFLICT (code) DO UPDATE SET
|
|
label = EXCLUDED.label,
|
|
module = EXCLUDED.module,
|
|
verb = EXCLUDED.verb,
|
|
perm_group = EXCLUDED.perm_group;
|
|
|
|
--changeset panel:iam-005c-legacy-perm-map endDelimiter:; splitStatements:true
|
|
CREATE TABLE IF NOT EXISTS iam._legacy_perm_map (
|
|
legacy_code TEXT NOT NULL,
|
|
v2_code TEXT NOT NULL REFERENCES iam.permissions(code) ON DELETE CASCADE,
|
|
PRIMARY KEY (legacy_code, v2_code)
|
|
);
|
|
|
|
INSERT INTO iam._legacy_perm_map (legacy_code, v2_code) VALUES
|
|
('manage_workers', 'workers.view'),
|
|
('manage_workers', 'workers.create'),
|
|
('manage_workers', 'workers.update'),
|
|
('manage_workers', 'workers.delete'),
|
|
('manage_workers', 'kanban.view'),
|
|
('manage_projects', 'projects.view'),
|
|
('manage_projects', 'projects.create'),
|
|
('manage_projects', 'projects.update'),
|
|
('manage_companies', 'companies.view'),
|
|
('manage_companies', 'companies.create'),
|
|
('manage_companies', 'companies.update'),
|
|
('manage_budget', 'budget.view'),
|
|
('manage_budget', 'budget.create'),
|
|
('manage_budget', 'budget.update'),
|
|
('manage_budget', 'budget.delete'),
|
|
('manage_budget', 'work_program.view'),
|
|
('manage_budget', 'work_program.create'),
|
|
('manage_budget', 'work_program.update'),
|
|
('manage_expenses', 'expenses.view'),
|
|
('manage_expenses', 'expenses.create'),
|
|
('manage_expenses', 'expenses.update'),
|
|
('manage_expenses', 'expenses.delete'),
|
|
('manage_expenses', 'cost_control.view'),
|
|
('view_expenses', 'expenses.view'),
|
|
('view_expenses', 'cost_control.view'),
|
|
('manage_payroll', 'payroll.view'),
|
|
('manage_payroll', 'payroll.create'),
|
|
('manage_payroll', 'payroll.update'),
|
|
('manage_payroll', 'payroll.delete'),
|
|
('manage_documents', 'documents.view'),
|
|
('manage_documents', 'documents.create'),
|
|
('manage_documents', 'project_docs.tecnico.view'),
|
|
('manage_documents', 'project_docs.tecnico.create'),
|
|
('manage_documents', 'project_docs.contrato.view'),
|
|
('manage_documents', 'project_docs.contrato.create'),
|
|
('manage_documents', 'project_docs.permisos.view'),
|
|
('manage_documents', 'project_docs.permisos.create'),
|
|
('manage_users', 'users.view'),
|
|
('manage_users', 'users.create'),
|
|
('manage_users', 'users.update'),
|
|
('manage_users', 'users.delete'),
|
|
('manage_settings', 'settings.view'),
|
|
('manage_settings', 'settings.update'),
|
|
('view_reports', 'reports.view'),
|
|
('view_warehouse', 'warehouse.view'),
|
|
('manage_warehouse', 'warehouse.view'),
|
|
('manage_warehouse', 'warehouse.create'),
|
|
('manage_warehouse', 'warehouse.update'),
|
|
('close_warehouse', 'warehouse.update'),
|
|
('manage_cost_settings', 'settings.update'),
|
|
('manage_cost_settings', 'cost_control.view')
|
|
ON CONFLICT DO NOTHING;
|
|
|
|
--changeset panel:iam-005d-roles-v2 endDelimiter:; splitStatements:true
|
|
--preconditions onFail:MARK_RAN
|
|
--precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM information_schema.columns WHERE table_schema='iam' AND table_name='roles' AND column_name='id'
|
|
CREATE TABLE iam.roles_v2 (
|
|
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
tenant_id INTEGER,
|
|
code TEXT NOT NULL,
|
|
label TEXT NOT NULL,
|
|
is_system BOOLEAN NOT NULL DEFAULT false,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
INSERT INTO iam.roles_v2 (tenant_id, code, label, is_system)
|
|
SELECT NULL, code, label, true
|
|
FROM iam.roles
|
|
WHERE code = 'tenant_admin';
|
|
|
|
INSERT INTO iam.roles_v2 (tenant_id, code, label, is_system)
|
|
SELECT DISTINCT u.tenant_id, 'operativo', 'Operativo', false
|
|
FROM iam.users u
|
|
WHERE u.role_code = 'user' AND u.tenant_id IS NOT NULL
|
|
AND NOT EXISTS (
|
|
SELECT 1 FROM iam.roles_v2 r
|
|
WHERE r.tenant_id = u.tenant_id AND r.code = 'operativo'
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS uq_iam_roles_v2_tenant_code
|
|
ON iam.roles_v2 (tenant_id, code)
|
|
WHERE tenant_id IS NOT NULL;
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS uq_iam_roles_v2_system_code
|
|
ON iam.roles_v2 (code)
|
|
WHERE tenant_id IS NULL AND is_system;
|
|
|
|
--changeset panel:iam-005e-role-permissions-v2 endDelimiter:; splitStatements:true
|
|
--preconditions onFail:MARK_RAN
|
|
--precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM information_schema.columns WHERE table_schema='iam' AND table_name='role_permissions' AND column_name='role_id'
|
|
CREATE TABLE iam.role_permissions_v2 (
|
|
role_id BIGINT NOT NULL REFERENCES iam.roles_v2(id) ON DELETE CASCADE,
|
|
permission_code TEXT NOT NULL REFERENCES iam.permissions(code) ON DELETE CASCADE,
|
|
PRIMARY KEY (role_id, permission_code)
|
|
);
|
|
|
|
INSERT INTO iam.role_permissions_v2 (role_id, permission_code)
|
|
SELECT DISTINCT sr.id, lm.v2_code
|
|
FROM iam.role_permissions rp
|
|
JOIN iam._legacy_perm_map lm ON lm.legacy_code = rp.permission_code
|
|
JOIN iam.roles_v2 sr ON sr.code = 'tenant_admin' AND sr.is_system AND sr.tenant_id IS NULL
|
|
WHERE rp.role_code = 'tenant_admin'
|
|
ON CONFLICT DO NOTHING;
|
|
|
|
INSERT INTO iam.role_permissions_v2 (role_id, permission_code)
|
|
SELECT sr.id, p.code
|
|
FROM iam.roles_v2 sr
|
|
CROSS JOIN iam.permissions p
|
|
WHERE sr.code = 'tenant_admin' AND sr.is_system AND sr.tenant_id IS NULL
|
|
AND p.module IS NOT NULL
|
|
ON CONFLICT DO NOTHING;
|
|
|
|
INSERT INTO iam.role_permissions_v2 (role_id, permission_code)
|
|
SELECT DISTINCT tr.id, lm.v2_code
|
|
FROM iam.role_permissions rp
|
|
JOIN iam._legacy_perm_map lm ON lm.legacy_code = rp.permission_code
|
|
JOIN iam.roles_v2 tr ON tr.code = 'operativo' AND tr.tenant_id IS NOT NULL
|
|
WHERE rp.role_code = 'user'
|
|
ON CONFLICT DO NOTHING;
|
|
|
|
--changeset panel:iam-005f-users-v2-columns endDelimiter:; splitStatements:true
|
|
--preconditions onFail:MARK_RAN
|
|
--precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM information_schema.columns WHERE table_schema='iam' AND table_name='users' AND column_name='role_id'
|
|
ALTER TABLE iam.users ADD COLUMN role_id BIGINT;
|
|
ALTER TABLE iam.users ADD COLUMN status TEXT NOT NULL DEFAULT 'activo';
|
|
ALTER TABLE iam.users ADD COLUMN scope_all_projects BOOLEAN NOT NULL DEFAULT false;
|
|
ALTER TABLE iam.users ADD COLUMN warehouse_central BOOLEAN NOT NULL DEFAULT false;
|
|
ALTER TABLE iam.users ADD COLUMN warehouse_projects BOOLEAN NOT NULL DEFAULT true;
|
|
|
|
ALTER TABLE iam.users ADD CONSTRAINT users_status_check
|
|
CHECK (status IN ('activo', 'baja'));
|
|
|
|
--changeset panel:iam-005g-users-backfill-role-id endDelimiter:; splitStatements:true
|
|
UPDATE iam.users u
|
|
SET role_id = sr.id
|
|
FROM iam.roles_v2 sr
|
|
WHERE u.role_code = 'tenant_admin'
|
|
AND sr.code = 'tenant_admin' AND sr.is_system AND sr.tenant_id IS NULL
|
|
AND u.role_id IS NULL;
|
|
|
|
UPDATE iam.users u
|
|
SET role_id = sr.id
|
|
FROM iam.roles_v2 sr
|
|
WHERE u.role_code = 'user'
|
|
AND sr.code = 'operativo' AND sr.tenant_id = u.tenant_id
|
|
AND u.role_id IS NULL;
|
|
|
|
UPDATE iam.users u
|
|
SET role_id = sr.id
|
|
FROM iam.roles_v2 sr
|
|
WHERE u.role_id IS NULL
|
|
AND sr.code = 'operativo' AND sr.tenant_id = u.tenant_id;
|
|
|
|
--changeset panel:iam-005g2-ensure-role-backfill endDelimiter:; splitStatements:true
|
|
--preconditions onFail:MARK_RAN
|
|
--precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM information_schema.columns WHERE table_schema='iam' AND table_name='roles' AND column_name='id'
|
|
INSERT INTO iam.roles_v2 (tenant_id, code, label, is_system)
|
|
SELECT DISTINCT u.tenant_id, 'operativo', 'Operativo', false
|
|
FROM iam.users u
|
|
WHERE u.tenant_id IS NOT NULL
|
|
AND NOT EXISTS (
|
|
SELECT 1 FROM iam.roles_v2 r
|
|
WHERE r.tenant_id = u.tenant_id AND r.code = 'operativo'
|
|
);
|
|
|
|
UPDATE iam.users u
|
|
SET role_id = sr.id
|
|
FROM iam.roles_v2 sr
|
|
WHERE u.role_code = 'tenant_admin'
|
|
AND sr.code = 'tenant_admin' AND sr.is_system AND sr.tenant_id IS NULL
|
|
AND u.role_id IS NULL;
|
|
|
|
UPDATE iam.users u
|
|
SET role_id = sr.id
|
|
FROM iam.roles_v2 sr
|
|
WHERE u.role_id IS NULL
|
|
AND u.tenant_id IS NOT NULL
|
|
AND sr.code = 'operativo' AND sr.tenant_id = u.tenant_id;
|
|
|
|
UPDATE iam.users u
|
|
SET role_id = op.id
|
|
FROM iam.roles_v2 sr, iam.roles_v2 op
|
|
WHERE u.role_id = sr.id
|
|
AND sr.code = 'tenant_admin' AND sr.is_system AND sr.tenant_id IS NULL
|
|
AND u.tenant_id IS NOT NULL
|
|
AND op.code = 'operativo' AND op.tenant_id = u.tenant_id
|
|
AND u.id <> (
|
|
SELECT MIN(u2.id)
|
|
FROM iam.users u2
|
|
JOIN iam.roles_v2 sr2 ON sr2.id = u2.role_id
|
|
WHERE u2.tenant_id = u.tenant_id
|
|
AND sr2.code = 'tenant_admin' AND sr2.is_system AND sr2.tenant_id IS NULL
|
|
);
|
|
|
|
DELETE FROM iam.users WHERE role_id IS NULL AND tenant_id IS NULL;
|
|
|
|
--changeset panel:iam-005h-swap-roles endDelimiter:; splitStatements:true
|
|
--preconditions onFail:MARK_RAN
|
|
--precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM information_schema.columns WHERE table_schema='iam' AND table_name='roles' AND column_name='id'
|
|
ALTER TABLE iam.users DROP CONSTRAINT IF EXISTS users_role_code_fkey;
|
|
ALTER TABLE iam.role_permissions DROP CONSTRAINT IF EXISTS role_permissions_role_code_fkey;
|
|
ALTER TABLE iam.role_permissions DROP CONSTRAINT IF EXISTS role_permissions_permission_code_fkey;
|
|
|
|
DROP TABLE iam.role_permissions;
|
|
|
|
ALTER TABLE iam.users DROP COLUMN IF EXISTS role_code;
|
|
|
|
ALTER TABLE iam.role_permissions_v2 RENAME TO role_permissions;
|
|
ALTER TABLE iam.roles DROP CONSTRAINT roles_pkey;
|
|
DROP TABLE iam.roles;
|
|
ALTER TABLE iam.roles_v2 RENAME TO roles;
|
|
|
|
ALTER TABLE iam.users
|
|
ADD CONSTRAINT users_role_id_fkey FOREIGN KEY (role_id) REFERENCES iam.roles(id);
|
|
|
|
ALTER TABLE iam.users ALTER COLUMN role_id SET NOT NULL;
|
|
|
|
--changeset panel:iam-005h2-unique-tenant-admin splitStatements:false
|
|
DO $$
|
|
DECLARE
|
|
v_admin_role_id bigint;
|
|
BEGIN
|
|
SELECT id INTO v_admin_role_id
|
|
FROM iam.roles
|
|
WHERE code = 'tenant_admin' AND is_system AND tenant_id IS NULL
|
|
LIMIT 1;
|
|
IF v_admin_role_id IS NOT NULL THEN
|
|
EXECUTE format(
|
|
'CREATE UNIQUE INDEX IF NOT EXISTS uq_iam_users_one_tenant_admin_per_tenant ON iam.users (tenant_id) WHERE role_id = %s',
|
|
v_admin_role_id
|
|
);
|
|
END IF;
|
|
END $$;
|
|
|
|
--changeset panel:iam-005i-user-projects endDelimiter:; splitStatements:true
|
|
--preconditions onFail:MARK_RAN
|
|
--precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='iam' AND table_name='user_projects'
|
|
CREATE TABLE iam.user_projects (
|
|
user_id BIGINT NOT NULL REFERENCES iam.users(id) ON DELETE CASCADE,
|
|
project_id INTEGER NOT NULL,
|
|
PRIMARY KEY (user_id, project_id)
|
|
);
|
|
|
|
CREATE INDEX idx_iam_user_projects_user ON iam.user_projects(user_id);
|
|
|
|
--changeset panel:iam-005j-rls endDelimiter:; splitStatements:true
|
|
ALTER TABLE iam.roles ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS tenant_and_system_roles ON iam.roles;
|
|
CREATE POLICY tenant_and_system_roles ON iam.roles
|
|
USING (
|
|
(tenant_id IS NOT NULL AND tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::integer)
|
|
OR (tenant_id IS NULL AND is_system)
|
|
)
|
|
WITH CHECK (
|
|
tenant_id IS NOT NULL
|
|
AND tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::integer
|
|
AND NOT is_system
|
|
);
|
|
|
|
ALTER TABLE iam.role_permissions ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS tenant_role_permissions ON iam.role_permissions;
|
|
CREATE POLICY tenant_role_permissions ON iam.role_permissions
|
|
USING (
|
|
EXISTS (
|
|
SELECT 1 FROM iam.roles r
|
|
WHERE r.id = role_permissions.role_id
|
|
AND (
|
|
(r.tenant_id IS NOT NULL AND r.tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::integer)
|
|
OR (r.tenant_id IS NULL AND r.is_system)
|
|
)
|
|
)
|
|
)
|
|
WITH CHECK (
|
|
EXISTS (
|
|
SELECT 1 FROM iam.roles r
|
|
WHERE r.id = role_permissions.role_id
|
|
AND r.tenant_id IS NOT NULL
|
|
AND r.tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::integer
|
|
AND NOT r.is_system
|
|
)
|
|
);
|
|
|
|
ALTER TABLE iam.user_projects ENABLE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS tenant_user_projects ON iam.user_projects;
|
|
CREATE POLICY tenant_user_projects ON iam.user_projects
|
|
USING (
|
|
EXISTS (
|
|
SELECT 1 FROM iam.users u
|
|
WHERE u.id = user_projects.user_id
|
|
AND u.tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::integer
|
|
)
|
|
)
|
|
WITH CHECK (
|
|
EXISTS (
|
|
SELECT 1 FROM iam.users u
|
|
WHERE u.id = user_projects.user_id
|
|
AND u.tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::integer
|
|
)
|
|
);
|
|
|
|
--changeset panel:iam-005k-fn-permission-list splitStatements:false
|
|
CREATE OR REPLACE FUNCTION iam.fn_permission_list(payload jsonb)
|
|
RETURNS jsonb
|
|
LANGUAGE plpgsql
|
|
SECURITY INVOKER
|
|
SET search_path = iam
|
|
AS $$
|
|
DECLARE
|
|
v_rows jsonb;
|
|
BEGIN
|
|
SELECT COALESCE(jsonb_agg(
|
|
jsonb_build_object(
|
|
'code', p.code,
|
|
'label', p.label,
|
|
'module', p.module,
|
|
'verb', p.verb,
|
|
'group', p.perm_group
|
|
) ORDER BY p.module, p.code
|
|
), '[]'::jsonb) INTO v_rows
|
|
FROM permissions p
|
|
WHERE p.module IS NOT NULL;
|
|
RETURN core.rpc_ok(
|
|
jsonb_build_object('permissions', v_rows),
|
|
format('Catálogo de permisos v2: %s', jsonb_array_length(v_rows)),
|
|
jsonb_build_object('fn', 'fn_permission_list')
|
|
);
|
|
EXCEPTION WHEN OTHERS THEN
|
|
RETURN core.rpc_from_exception('fn_permission_list', SQLSTATE, SQLERRM);
|
|
END;
|
|
$$;
|
|
|
|
--changeset panel:iam-005l-fn-role-list splitStatements:false
|
|
CREATE OR REPLACE FUNCTION iam.fn_role_list(payload jsonb)
|
|
RETURNS jsonb
|
|
LANGUAGE plpgsql
|
|
SECURITY INVOKER
|
|
SET search_path = iam
|
|
AS $$
|
|
DECLARE
|
|
v_tid integer := COALESCE(
|
|
NULLIF(payload->>'tenant_id', '')::integer,
|
|
NULLIF(current_setting('app.tenant_id', true), '')::integer
|
|
);
|
|
v_rows jsonb;
|
|
BEGIN
|
|
SELECT COALESCE(jsonb_agg(
|
|
jsonb_build_object(
|
|
'id', r.id,
|
|
'code', r.code,
|
|
'label', r.label,
|
|
'tenant_id', r.tenant_id,
|
|
'is_system', r.is_system,
|
|
'is_editable', NOT r.is_system,
|
|
'created_at', r.created_at
|
|
) ORDER BY r.is_system DESC, r.label
|
|
), '[]'::jsonb) INTO v_rows
|
|
FROM roles r
|
|
WHERE (r.tenant_id = v_tid) OR (r.tenant_id IS NULL AND r.is_system);
|
|
RETURN core.rpc_ok(
|
|
jsonb_build_object('roles', v_rows),
|
|
format('Roles del tenant %s: %s', v_tid, jsonb_array_length(v_rows)),
|
|
jsonb_build_object('fn', 'fn_role_list', 'tenant_id', v_tid)
|
|
);
|
|
EXCEPTION WHEN OTHERS THEN
|
|
RETURN core.rpc_from_exception('fn_role_list', SQLSTATE, SQLERRM);
|
|
END;
|
|
$$;
|
|
|
|
--changeset panel:iam-005m-fn-role-create splitStatements:false
|
|
CREATE OR REPLACE FUNCTION iam.fn_role_create(payload jsonb)
|
|
RETURNS jsonb
|
|
LANGUAGE plpgsql
|
|
SECURITY INVOKER
|
|
SET search_path = iam
|
|
AS $$
|
|
DECLARE
|
|
v_tid integer := COALESCE(
|
|
NULLIF(payload->>'tenant_id', '')::integer,
|
|
NULLIF(current_setting('app.tenant_id', true), '')::integer
|
|
);
|
|
v_code text := lower(nullif(btrim(payload->>'code'), ''));
|
|
v_label text := nullif(btrim(payload->>'label'), '');
|
|
v_new_id bigint;
|
|
v_role jsonb;
|
|
BEGIN
|
|
IF v_tid IS NULL THEN
|
|
RETURN core.rpc_err('VALIDATION', 'fn_role_create: tenant_id es obligatorio',
|
|
jsonb_build_object('fn', 'fn_role_create'));
|
|
END IF;
|
|
IF v_code IS NULL OR v_code !~ '^[a-z][a-z0-9_]{1,63}$' THEN
|
|
RETURN core.rpc_err('VALIDATION', 'fn_role_create: code inválido (minúsculas, números, _, 2-64 chars)',
|
|
jsonb_build_object('fn', 'fn_role_create', 'field', 'code'));
|
|
END IF;
|
|
IF v_label IS NULL THEN
|
|
RETURN core.rpc_err('VALIDATION', 'fn_role_create: label es obligatorio',
|
|
jsonb_build_object('fn', 'fn_role_create', 'field', 'label'));
|
|
END IF;
|
|
IF v_code IN ('tenant_admin', 'platform_admin', 'user', 'operativo') THEN
|
|
RETURN core.rpc_err('VALIDATION', format('fn_role_create: el código %s está reservado', v_code),
|
|
jsonb_build_object('fn', 'fn_role_create', 'code', v_code));
|
|
END IF;
|
|
INSERT INTO roles (tenant_id, code, label, is_system)
|
|
VALUES (v_tid, v_code, v_label, false)
|
|
RETURNING id INTO v_new_id;
|
|
SELECT to_jsonb(r) INTO v_role FROM roles r WHERE r.id = v_new_id;
|
|
RETURN core.rpc_created(
|
|
jsonb_build_object('role', v_role),
|
|
format('Rol %s creado (id=%s)', v_code, v_new_id),
|
|
jsonb_build_object('fn', 'fn_role_create', 'role_id', v_new_id, 'tenant_id', v_tid)
|
|
);
|
|
EXCEPTION
|
|
WHEN unique_violation THEN
|
|
RETURN core.rpc_err('CONFLICT', format('fn_role_create: ya existe el rol %s en el tenant', v_code),
|
|
jsonb_build_object('fn', 'fn_role_create', 'code', v_code, 'tenant_id', v_tid));
|
|
WHEN OTHERS THEN
|
|
RETURN core.rpc_from_exception('fn_role_create', SQLSTATE, SQLERRM);
|
|
END;
|
|
$$;
|
|
|
|
--changeset panel:iam-005n-fn-role-update splitStatements:false
|
|
CREATE OR REPLACE FUNCTION iam.fn_role_update(payload jsonb)
|
|
RETURNS jsonb
|
|
LANGUAGE plpgsql
|
|
SECURITY INVOKER
|
|
SET search_path = iam
|
|
AS $$
|
|
DECLARE
|
|
v_tid integer := COALESCE(
|
|
NULLIF(payload->>'tenant_id', '')::integer,
|
|
NULLIF(current_setting('app.tenant_id', true), '')::integer
|
|
);
|
|
v_role_id bigint := NULLIF(payload->>'role_id', '')::bigint;
|
|
v_label text := nullif(btrim(payload->>'label'), '');
|
|
v_role record;
|
|
v_updated jsonb;
|
|
BEGIN
|
|
IF v_role_id IS NULL THEN
|
|
RETURN core.rpc_err('VALIDATION', 'fn_role_update: role_id es obligatorio',
|
|
jsonb_build_object('fn', 'fn_role_update', 'field', 'role_id'));
|
|
END IF;
|
|
SELECT * INTO v_role FROM roles WHERE id = v_role_id;
|
|
IF NOT FOUND THEN
|
|
RETURN core.rpc_err('NOT_FOUND', format('fn_role_update: rol id=%s no encontrado', v_role_id),
|
|
jsonb_build_object('fn', 'fn_role_update', 'role_id', v_role_id));
|
|
END IF;
|
|
IF v_role.is_system OR v_role.tenant_id IS NULL THEN
|
|
RETURN core.rpc_err('VALIDATION', 'fn_role_update: no se puede modificar un rol del sistema',
|
|
jsonb_build_object('fn', 'fn_role_update', 'role_id', v_role_id));
|
|
END IF;
|
|
IF v_role.tenant_id <> v_tid THEN
|
|
RETURN core.rpc_err('VALIDATION', format('fn_role_update: rol id=%s no pertenece al tenant %s', v_role_id, v_tid),
|
|
jsonb_build_object('fn', 'fn_role_update', 'role_id', v_role_id, 'tenant_id', v_tid));
|
|
END IF;
|
|
IF v_label IS NULL THEN
|
|
RETURN core.rpc_err('VALIDATION', 'fn_role_update: label es obligatorio',
|
|
jsonb_build_object('fn', 'fn_role_update', 'field', 'label'));
|
|
END IF;
|
|
UPDATE roles SET label = v_label WHERE id = v_role_id;
|
|
SELECT to_jsonb(r) INTO v_updated FROM roles r WHERE r.id = v_role_id;
|
|
RETURN core.rpc_ok(
|
|
jsonb_build_object('role', v_updated),
|
|
format('Rol %s actualizado (id=%s)', v_updated->>'code', v_role_id),
|
|
jsonb_build_object('fn', 'fn_role_update', 'role_id', v_role_id)
|
|
);
|
|
EXCEPTION WHEN OTHERS THEN
|
|
RETURN core.rpc_from_exception('fn_role_update', SQLSTATE, SQLERRM);
|
|
END;
|
|
$$;
|
|
|
|
--changeset panel:iam-005o-fn-role-delete splitStatements:false
|
|
CREATE OR REPLACE FUNCTION iam.fn_role_delete(payload jsonb)
|
|
RETURNS jsonb
|
|
LANGUAGE plpgsql
|
|
SECURITY INVOKER
|
|
SET search_path = iam
|
|
AS $$
|
|
DECLARE
|
|
v_tid integer := COALESCE(
|
|
NULLIF(payload->>'tenant_id', '')::integer,
|
|
NULLIF(current_setting('app.tenant_id', true), '')::integer
|
|
);
|
|
v_role_id bigint := NULLIF(payload->>'role_id', '')::bigint;
|
|
v_role record;
|
|
v_assigned integer;
|
|
BEGIN
|
|
IF v_role_id IS NULL THEN
|
|
RETURN core.rpc_err('VALIDATION', 'fn_role_delete: role_id es obligatorio',
|
|
jsonb_build_object('fn', 'fn_role_delete', 'field', 'role_id'));
|
|
END IF;
|
|
SELECT * INTO v_role FROM roles WHERE id = v_role_id;
|
|
IF NOT FOUND THEN
|
|
RETURN core.rpc_err('NOT_FOUND', format('fn_role_delete: rol id=%s no encontrado', v_role_id),
|
|
jsonb_build_object('fn', 'fn_role_delete', 'role_id', v_role_id));
|
|
END IF;
|
|
IF v_role.is_system OR v_role.tenant_id IS NULL THEN
|
|
RETURN core.rpc_err('VALIDATION', 'fn_role_delete: no se puede eliminar un rol del sistema',
|
|
jsonb_build_object('fn', 'fn_role_delete', 'role_id', v_role_id));
|
|
END IF;
|
|
IF v_role.tenant_id <> v_tid THEN
|
|
RETURN core.rpc_err('VALIDATION', format('fn_role_delete: rol id=%s no pertenece al tenant %s', v_role_id, v_tid),
|
|
jsonb_build_object('fn', 'fn_role_delete', 'role_id', v_role_id, 'tenant_id', v_tid));
|
|
END IF;
|
|
SELECT COUNT(*)::integer INTO v_assigned FROM users WHERE role_id = v_role_id;
|
|
IF v_assigned > 0 THEN
|
|
RETURN core.rpc_err('CONFLICT',
|
|
format('fn_role_delete: rol %s tiene %s usuario(s) asignado(s)', v_role.code, v_assigned),
|
|
jsonb_build_object('fn', 'fn_role_delete', 'role_id', v_role_id, 'assigned_users', v_assigned));
|
|
END IF;
|
|
DELETE FROM roles WHERE id = v_role_id;
|
|
RETURN core.rpc_ok(
|
|
jsonb_build_object('role_id', v_role_id, 'code', v_role.code),
|
|
format('Rol %s eliminado (id=%s)', v_role.code, v_role_id),
|
|
jsonb_build_object('fn', 'fn_role_delete', 'role_id', v_role_id)
|
|
);
|
|
EXCEPTION WHEN OTHERS THEN
|
|
RETURN core.rpc_from_exception('fn_role_delete', SQLSTATE, SQLERRM);
|
|
END;
|
|
$$;
|
|
|
|
--changeset panel:iam-005p-fn-role-permissions-get splitStatements:false
|
|
CREATE OR REPLACE FUNCTION iam.fn_role_permissions_get(payload jsonb)
|
|
RETURNS jsonb
|
|
LANGUAGE plpgsql
|
|
SECURITY INVOKER
|
|
SET search_path = iam
|
|
AS $$
|
|
DECLARE
|
|
v_role_id bigint := NULLIF(payload->>'role_id', '')::bigint;
|
|
v_role record;
|
|
v_rows jsonb;
|
|
BEGIN
|
|
IF v_role_id IS NULL THEN
|
|
RETURN core.rpc_err('VALIDATION', 'fn_role_permissions_get: role_id es obligatorio',
|
|
jsonb_build_object('fn', 'fn_role_permissions_get', 'field', 'role_id'));
|
|
END IF;
|
|
SELECT * INTO v_role FROM roles WHERE id = v_role_id;
|
|
IF NOT FOUND THEN
|
|
RETURN core.rpc_err('NOT_FOUND', format('fn_role_permissions_get: rol id=%s no encontrado', v_role_id),
|
|
jsonb_build_object('fn', 'fn_role_permissions_get', 'role_id', v_role_id));
|
|
END IF;
|
|
SELECT COALESCE(jsonb_agg(rp.permission_code ORDER BY rp.permission_code), '[]'::jsonb) INTO v_rows
|
|
FROM role_permissions rp WHERE rp.role_id = v_role_id;
|
|
RETURN core.rpc_ok(
|
|
jsonb_build_object(
|
|
'role_id', v_role_id,
|
|
'role_code', v_role.code,
|
|
'is_editable', NOT v_role.is_system,
|
|
'permissions', v_rows
|
|
),
|
|
format('Permisos del rol %s: %s', v_role.code, jsonb_array_length(v_rows)),
|
|
jsonb_build_object('fn', 'fn_role_permissions_get', 'role_id', v_role_id)
|
|
);
|
|
EXCEPTION WHEN OTHERS THEN
|
|
RETURN core.rpc_from_exception('fn_role_permissions_get', SQLSTATE, SQLERRM);
|
|
END;
|
|
$$;
|
|
|
|
--changeset panel:iam-005q-fn-role-permissions-save splitStatements:false
|
|
CREATE OR REPLACE FUNCTION iam.fn_role_permissions_save(payload jsonb)
|
|
RETURNS jsonb
|
|
LANGUAGE plpgsql
|
|
SECURITY INVOKER
|
|
SET search_path = iam
|
|
AS $$
|
|
DECLARE
|
|
v_role_id bigint := NULLIF(payload->>'role_id', '')::bigint;
|
|
v_perms jsonb := COALESCE(payload->'permissions', '[]'::jsonb);
|
|
v_role record;
|
|
v_perm text;
|
|
BEGIN
|
|
IF v_role_id IS NULL THEN
|
|
RETURN core.rpc_err('VALIDATION', 'fn_role_permissions_save: role_id es obligatorio',
|
|
jsonb_build_object('fn', 'fn_role_permissions_save', 'field', 'role_id'));
|
|
END IF;
|
|
SELECT * INTO v_role FROM roles WHERE id = v_role_id;
|
|
IF NOT FOUND THEN
|
|
RETURN core.rpc_err('NOT_FOUND', format('fn_role_permissions_save: rol id=%s no encontrado', v_role_id),
|
|
jsonb_build_object('fn', 'fn_role_permissions_save', 'role_id', v_role_id));
|
|
END IF;
|
|
IF v_role.is_system AND v_role.code = 'tenant_admin' THEN
|
|
RETURN core.rpc_err('VALIDATION', 'fn_role_permissions_save: no se puede modificar tenant_admin',
|
|
jsonb_build_object('fn', 'fn_role_permissions_save', 'role_id', v_role_id));
|
|
END IF;
|
|
IF v_role.is_system OR v_role.tenant_id IS NULL THEN
|
|
RETURN core.rpc_err('VALIDATION', 'fn_role_permissions_save: no se puede modificar un rol del sistema',
|
|
jsonb_build_object('fn', 'fn_role_permissions_save', 'role_id', v_role_id));
|
|
END IF;
|
|
DELETE FROM role_permissions WHERE role_id = v_role_id;
|
|
FOR v_perm IN SELECT jsonb_array_elements_text(v_perms)
|
|
LOOP
|
|
INSERT INTO role_permissions (role_id, permission_code)
|
|
VALUES (v_role_id, v_perm)
|
|
ON CONFLICT DO NOTHING;
|
|
END LOOP;
|
|
RETURN core.rpc_ok(
|
|
jsonb_build_object('role_id', v_role_id, 'role_code', v_role.code, 'permissions', v_perms),
|
|
format('Permisos del rol %s actualizados (%s permiso(s))', v_role.code, jsonb_array_length(v_perms)),
|
|
jsonb_build_object('fn', 'fn_role_permissions_save', 'role_id', v_role_id)
|
|
);
|
|
EXCEPTION WHEN OTHERS THEN
|
|
RETURN core.rpc_from_exception('fn_role_permissions_save', SQLSTATE, SQLERRM);
|
|
END;
|
|
$$;
|
|
|
|
--changeset panel:iam-005r-fn-user-list splitStatements:false
|
|
CREATE OR REPLACE FUNCTION iam.fn_user_list(payload jsonb)
|
|
RETURNS jsonb
|
|
LANGUAGE plpgsql
|
|
SECURITY INVOKER
|
|
SET search_path = iam
|
|
AS $$
|
|
DECLARE
|
|
v_tid integer := COALESCE(
|
|
NULLIF(payload->>'tenant_id', '')::integer,
|
|
NULLIF(current_setting('app.tenant_id', true), '')::integer
|
|
);
|
|
v_rows jsonb;
|
|
BEGIN
|
|
SELECT COALESCE(jsonb_agg(row_data ORDER BY row_data->>'display_name'), '[]'::jsonb) INTO v_rows
|
|
FROM (
|
|
SELECT jsonb_build_object(
|
|
'id', u.id,
|
|
'username', u.username,
|
|
'display_name', u.display_name,
|
|
'email', u.email,
|
|
'company_id', u.company_id,
|
|
'tenant_id', u.tenant_id,
|
|
'role_id', u.role_id,
|
|
'role_code', r.code,
|
|
'role_label', r.label,
|
|
'status', u.status,
|
|
'scope_all_projects', u.scope_all_projects,
|
|
'warehouse_central', u.warehouse_central,
|
|
'warehouse_projects', u.warehouse_projects,
|
|
'project_ids', COALESCE((
|
|
SELECT jsonb_agg(up.project_id ORDER BY up.project_id)
|
|
FROM user_projects up WHERE up.user_id = u.id
|
|
), '[]'::jsonb),
|
|
'is_owner', (r.code = 'tenant_admin' AND r.is_system AND r.tenant_id IS NULL),
|
|
'must_change_password', u.must_change_password,
|
|
'created_at', u.created_at
|
|
) AS row_data
|
|
FROM users u
|
|
JOIN roles r ON r.id = u.role_id
|
|
WHERE u.tenant_id = v_tid
|
|
) sub;
|
|
RETURN core.rpc_ok(
|
|
jsonb_build_object('users', v_rows),
|
|
format('Usuarios del tenant %s: %s cuenta(s)', v_tid, jsonb_array_length(v_rows)),
|
|
jsonb_build_object('fn', 'fn_user_list', 'tenant_id', v_tid)
|
|
);
|
|
EXCEPTION WHEN OTHERS THEN
|
|
RETURN core.rpc_from_exception('fn_user_list', SQLSTATE, SQLERRM);
|
|
END;
|
|
$$;
|
|
|
|
--changeset panel:iam-005s-fn-user-create splitStatements:false
|
|
CREATE OR REPLACE FUNCTION iam.fn_user_create(payload jsonb)
|
|
RETURNS jsonb
|
|
LANGUAGE plpgsql
|
|
SECURITY INVOKER
|
|
SET search_path = iam
|
|
AS $$
|
|
DECLARE
|
|
v_tid integer := COALESCE(
|
|
NULLIF(payload->>'tenant_id', '')::integer,
|
|
NULLIF(current_setting('app.tenant_id', true), '')::integer
|
|
);
|
|
v_username text := lower(nullif(btrim(payload->>'username'), ''));
|
|
v_password_hash text := nullif(btrim(payload->>'password_hash'), '');
|
|
v_display_name text := nullif(btrim(payload->>'display_name'), '');
|
|
v_email text := COALESCE(nullif(btrim(payload->>'email'), ''), '');
|
|
v_role_id bigint := NULLIF(payload->>'role_id', '')::bigint;
|
|
v_company_id integer := NULLIF(payload->>'company_id', '')::integer;
|
|
v_status text := COALESCE(nullif(btrim(payload->>'status'), ''), 'activo');
|
|
v_scope_all boolean := COALESCE((payload->>'scope_all_projects')::boolean, false);
|
|
v_wh_central boolean := COALESCE((payload->>'warehouse_central')::boolean, false);
|
|
v_wh_projects boolean := COALESCE((payload->>'warehouse_projects')::boolean, true);
|
|
v_role record;
|
|
v_new_id bigint;
|
|
v_user jsonb;
|
|
BEGIN
|
|
IF v_tid IS NULL THEN
|
|
RETURN core.rpc_err('VALIDATION', 'fn_user_create: tenant_id es obligatorio',
|
|
jsonb_build_object('fn', 'fn_user_create'));
|
|
END IF;
|
|
IF v_username IS NULL OR v_username !~ '^[a-z0-9._-]{3,64}$' THEN
|
|
RETURN core.rpc_err('VALIDATION', 'fn_user_create: username inválido',
|
|
jsonb_build_object('fn', 'fn_user_create', 'field', 'username'));
|
|
END IF;
|
|
IF v_password_hash IS NULL THEN
|
|
RETURN core.rpc_err('VALIDATION', 'fn_user_create: password_hash es obligatorio',
|
|
jsonb_build_object('fn', 'fn_user_create', 'field', 'password_hash'));
|
|
END IF;
|
|
IF v_display_name IS NULL THEN
|
|
RETURN core.rpc_err('VALIDATION', 'fn_user_create: display_name es obligatorio',
|
|
jsonb_build_object('fn', 'fn_user_create', 'field', 'display_name'));
|
|
END IF;
|
|
IF v_role_id IS NULL THEN
|
|
RETURN core.rpc_err('VALIDATION', 'fn_user_create: role_id es obligatorio',
|
|
jsonb_build_object('fn', 'fn_user_create', 'field', 'role_id'));
|
|
END IF;
|
|
IF v_status NOT IN ('activo', 'baja') THEN
|
|
RETURN core.rpc_err('VALIDATION', 'fn_user_create: status debe ser activo o baja',
|
|
jsonb_build_object('fn', 'fn_user_create', 'field', 'status'));
|
|
END IF;
|
|
SELECT * INTO v_role FROM roles WHERE id = v_role_id;
|
|
IF NOT FOUND THEN
|
|
RETURN core.rpc_err('NOT_FOUND', format('fn_user_create: rol id=%s no encontrado', v_role_id),
|
|
jsonb_build_object('fn', 'fn_user_create', 'role_id', v_role_id));
|
|
END IF;
|
|
IF v_role.code = 'tenant_admin' AND v_role.is_system THEN
|
|
RETURN core.rpc_err('VALIDATION', 'fn_user_create: use bootstrap para crear tenant_admin',
|
|
jsonb_build_object('fn', 'fn_user_create', 'role_id', v_role_id));
|
|
END IF;
|
|
IF v_role.tenant_id IS NOT NULL AND v_role.tenant_id <> v_tid THEN
|
|
RETURN core.rpc_err('VALIDATION', format('fn_user_create: rol id=%s no pertenece al tenant %s', v_role_id, v_tid),
|
|
jsonb_build_object('fn', 'fn_user_create', 'role_id', v_role_id));
|
|
END IF;
|
|
INSERT INTO users (
|
|
username, password_hash, display_name, email, company_id, tenant_id, role_id,
|
|
status, scope_all_projects, warehouse_central, warehouse_projects, must_change_password
|
|
) VALUES (
|
|
v_username, v_password_hash, v_display_name, v_email, v_company_id, v_tid, v_role_id,
|
|
v_status, v_scope_all, v_wh_central, v_wh_projects, COALESCE((payload->>'must_change_password')::boolean, true)
|
|
)
|
|
RETURNING id INTO v_new_id;
|
|
SELECT to_jsonb(u) INTO v_user FROM users u WHERE u.id = v_new_id;
|
|
RETURN core.rpc_created(
|
|
jsonb_build_object('user', v_user),
|
|
format('Usuario %s creado (id=%s)', v_username, v_new_id),
|
|
jsonb_build_object('fn', 'fn_user_create', 'user_id', v_new_id, 'tenant_id', v_tid)
|
|
);
|
|
EXCEPTION
|
|
WHEN unique_violation THEN
|
|
RETURN core.rpc_err('CONFLICT', format('fn_user_create: ya existe el username %s', v_username),
|
|
jsonb_build_object('fn', 'fn_user_create', 'username', v_username));
|
|
WHEN OTHERS THEN
|
|
RETURN core.rpc_from_exception('fn_user_create', SQLSTATE, SQLERRM);
|
|
END;
|
|
$$;
|
|
|
|
--changeset panel:iam-005t-fn-user-update splitStatements:false
|
|
CREATE OR REPLACE FUNCTION iam.fn_user_update(payload jsonb)
|
|
RETURNS jsonb
|
|
LANGUAGE plpgsql
|
|
SECURITY INVOKER
|
|
SET search_path = iam
|
|
AS $$
|
|
DECLARE
|
|
v_tid integer := COALESCE(
|
|
NULLIF(payload->>'tenant_id', '')::integer,
|
|
NULLIF(current_setting('app.tenant_id', true), '')::integer
|
|
);
|
|
v_user_id bigint := NULLIF(payload->>'id', '')::bigint;
|
|
v_user record;
|
|
v_role record;
|
|
v_role_id bigint := NULLIF(payload->>'role_id', '')::bigint;
|
|
v_updated jsonb;
|
|
BEGIN
|
|
IF v_user_id IS NULL THEN
|
|
RETURN core.rpc_err('VALIDATION', 'fn_user_update: id es obligatorio',
|
|
jsonb_build_object('fn', 'fn_user_update', 'field', 'id'));
|
|
END IF;
|
|
SELECT u.*, r.code AS role_code, r.is_system AS role_is_system, r.tenant_id AS role_tenant_id
|
|
INTO v_user
|
|
FROM users u
|
|
JOIN roles r ON r.id = u.role_id
|
|
WHERE u.id = v_user_id;
|
|
IF NOT FOUND THEN
|
|
RETURN core.rpc_err('NOT_FOUND', format('fn_user_update: usuario id=%s no encontrado', v_user_id),
|
|
jsonb_build_object('fn', 'fn_user_update', 'user_id', v_user_id));
|
|
END IF;
|
|
IF v_user.tenant_id <> v_tid THEN
|
|
RETURN core.rpc_err('VALIDATION', format('fn_user_update: usuario id=%s no pertenece al tenant %s', v_user_id, v_tid),
|
|
jsonb_build_object('fn', 'fn_user_update', 'user_id', v_user_id));
|
|
END IF;
|
|
IF v_user.role_code = 'tenant_admin' AND v_user.role_is_system AND v_user.role_tenant_id IS NULL THEN
|
|
RETURN core.rpc_err('VALIDATION', 'fn_user_update: no se puede modificar el propietario del tenant',
|
|
jsonb_build_object('fn', 'fn_user_update', 'user_id', v_user_id));
|
|
END IF;
|
|
IF v_role_id IS NOT NULL THEN
|
|
SELECT * INTO v_role FROM roles WHERE id = v_role_id;
|
|
IF NOT FOUND THEN
|
|
RETURN core.rpc_err('NOT_FOUND', format('fn_user_update: rol id=%s no encontrado', v_role_id),
|
|
jsonb_build_object('fn', 'fn_user_update', 'role_id', v_role_id));
|
|
END IF;
|
|
IF v_role.code = 'tenant_admin' AND v_role.is_system THEN
|
|
RETURN core.rpc_err('VALIDATION', 'fn_user_update: no se puede asignar rol tenant_admin',
|
|
jsonb_build_object('fn', 'fn_user_update', 'role_id', v_role_id));
|
|
END IF;
|
|
IF v_role.tenant_id IS NOT NULL AND v_role.tenant_id <> v_tid THEN
|
|
RETURN core.rpc_err('VALIDATION', format('fn_user_update: rol id=%s no pertenece al tenant %s', v_role_id, v_tid),
|
|
jsonb_build_object('fn', 'fn_user_update', 'role_id', v_role_id));
|
|
END IF;
|
|
END IF;
|
|
UPDATE users SET
|
|
display_name = COALESCE(nullif(btrim(payload->>'display_name'), ''), display_name),
|
|
email = COALESCE(nullif(btrim(payload->>'email'), ''), email),
|
|
company_id = COALESCE(NULLIF(payload->>'company_id', '')::integer, company_id),
|
|
role_id = COALESCE(v_role_id, role_id),
|
|
status = COALESCE(nullif(btrim(payload->>'status'), ''), status),
|
|
scope_all_projects = COALESCE((payload->>'scope_all_projects')::boolean, scope_all_projects),
|
|
warehouse_central = COALESCE((payload->>'warehouse_central')::boolean, warehouse_central),
|
|
warehouse_projects = COALESCE((payload->>'warehouse_projects')::boolean, warehouse_projects)
|
|
WHERE id = v_user_id;
|
|
SELECT to_jsonb(u) INTO v_updated FROM users u WHERE u.id = v_user_id;
|
|
RETURN core.rpc_ok(
|
|
jsonb_build_object('user', v_updated),
|
|
format('Usuario id=%s actualizado', v_user_id),
|
|
jsonb_build_object('fn', 'fn_user_update', 'user_id', v_user_id)
|
|
);
|
|
EXCEPTION WHEN OTHERS THEN
|
|
RETURN core.rpc_from_exception('fn_user_update', SQLSTATE, SQLERRM);
|
|
END;
|
|
$$;
|
|
|
|
--changeset panel:iam-005u-fn-user-reset-access splitStatements:false
|
|
CREATE OR REPLACE FUNCTION iam.fn_user_reset_access(payload jsonb)
|
|
RETURNS jsonb
|
|
LANGUAGE plpgsql
|
|
SECURITY INVOKER
|
|
SET search_path = iam
|
|
AS $$
|
|
DECLARE
|
|
v_tid integer := COALESCE(
|
|
NULLIF(payload->>'tenant_id', '')::integer,
|
|
NULLIF(current_setting('app.tenant_id', true), '')::integer
|
|
);
|
|
v_user_id bigint := NULLIF(payload->>'user_id', '')::bigint;
|
|
v_password_hash text := nullif(btrim(payload->>'password_hash'), '');
|
|
v_user record;
|
|
BEGIN
|
|
IF v_user_id IS NULL THEN
|
|
RETURN core.rpc_err('VALIDATION', 'fn_user_reset_access: user_id es obligatorio',
|
|
jsonb_build_object('fn', 'fn_user_reset_access', 'field', 'user_id'));
|
|
END IF;
|
|
IF v_password_hash IS NULL THEN
|
|
RETURN core.rpc_err('VALIDATION', 'fn_user_reset_access: password_hash es obligatorio',
|
|
jsonb_build_object('fn', 'fn_user_reset_access', 'field', 'password_hash'));
|
|
END IF;
|
|
SELECT u.*, r.code AS role_code, r.is_system AS role_is_system, r.tenant_id AS role_tenant_id
|
|
INTO v_user
|
|
FROM users u
|
|
JOIN roles r ON r.id = u.role_id
|
|
WHERE u.id = v_user_id;
|
|
IF NOT FOUND THEN
|
|
RETURN core.rpc_err('NOT_FOUND', format('fn_user_reset_access: usuario id=%s no encontrado', v_user_id),
|
|
jsonb_build_object('fn', 'fn_user_reset_access', 'user_id', v_user_id));
|
|
END IF;
|
|
IF v_user.tenant_id <> v_tid THEN
|
|
RETURN core.rpc_err('VALIDATION', format('fn_user_reset_access: usuario id=%s no pertenece al tenant %s', v_user_id, v_tid),
|
|
jsonb_build_object('fn', 'fn_user_reset_access', 'user_id', v_user_id));
|
|
END IF;
|
|
UPDATE users
|
|
SET password_hash = v_password_hash,
|
|
must_change_password = COALESCE((payload->>'must_change_password')::boolean, true)
|
|
WHERE id = v_user_id;
|
|
RETURN core.rpc_ok(
|
|
jsonb_build_object('user_id', v_user_id),
|
|
format('Acceso restablecido para usuario id=%s', v_user_id),
|
|
jsonb_build_object('fn', 'fn_user_reset_access', 'user_id', v_user_id)
|
|
);
|
|
EXCEPTION WHEN OTHERS THEN
|
|
RETURN core.rpc_from_exception('fn_user_reset_access', SQLSTATE, SQLERRM);
|
|
END;
|
|
$$;
|
|
|
|
--changeset panel:iam-005v-fn-user-projects-save splitStatements:false
|
|
CREATE OR REPLACE FUNCTION iam.fn_user_projects_save(payload jsonb)
|
|
RETURNS jsonb
|
|
LANGUAGE plpgsql
|
|
SECURITY INVOKER
|
|
SET search_path = iam
|
|
AS $$
|
|
DECLARE
|
|
v_tid integer := COALESCE(
|
|
NULLIF(payload->>'tenant_id', '')::integer,
|
|
NULLIF(current_setting('app.tenant_id', true), '')::integer
|
|
);
|
|
v_user_id bigint := NULLIF(payload->>'user_id', '')::bigint;
|
|
v_project_ids jsonb := COALESCE(payload->'project_ids', '[]'::jsonb);
|
|
v_user record;
|
|
v_pid integer;
|
|
BEGIN
|
|
IF v_user_id IS NULL THEN
|
|
RETURN core.rpc_err('VALIDATION', 'fn_user_projects_save: user_id es obligatorio',
|
|
jsonb_build_object('fn', 'fn_user_projects_save', 'field', 'user_id'));
|
|
END IF;
|
|
SELECT u.*, r.code AS role_code, r.is_system AS role_is_system, r.tenant_id AS role_tenant_id
|
|
INTO v_user
|
|
FROM users u
|
|
JOIN roles r ON r.id = u.role_id
|
|
WHERE u.id = v_user_id;
|
|
IF NOT FOUND THEN
|
|
RETURN core.rpc_err('NOT_FOUND', format('fn_user_projects_save: usuario id=%s no encontrado', v_user_id),
|
|
jsonb_build_object('fn', 'fn_user_projects_save', 'user_id', v_user_id));
|
|
END IF;
|
|
IF v_user.tenant_id <> v_tid THEN
|
|
RETURN core.rpc_err('VALIDATION', format('fn_user_projects_save: usuario id=%s no pertenece al tenant %s', v_user_id, v_tid),
|
|
jsonb_build_object('fn', 'fn_user_projects_save', 'user_id', v_user_id));
|
|
END IF;
|
|
IF v_user.role_code = 'tenant_admin' AND v_user.role_is_system AND v_user.role_tenant_id IS NULL THEN
|
|
RETURN core.rpc_err('VALIDATION', 'fn_user_projects_save: no se puede modificar proyectos del propietario',
|
|
jsonb_build_object('fn', 'fn_user_projects_save', 'user_id', v_user_id));
|
|
END IF;
|
|
DELETE FROM user_projects WHERE user_id = v_user_id;
|
|
FOR v_pid IN
|
|
SELECT DISTINCT (elem)::integer
|
|
FROM jsonb_array_elements_text(v_project_ids) AS elem
|
|
WHERE btrim(elem) ~ '^\d+$'
|
|
LOOP
|
|
INSERT INTO user_projects (user_id, project_id) VALUES (v_user_id, v_pid);
|
|
END LOOP;
|
|
RETURN core.rpc_ok(
|
|
jsonb_build_object('user_id', v_user_id, 'project_ids', v_project_ids),
|
|
format('Proyectos del usuario id=%s actualizados (%s)', v_user_id, jsonb_array_length(v_project_ids)),
|
|
jsonb_build_object('fn', 'fn_user_projects_save', 'user_id', v_user_id)
|
|
);
|
|
EXCEPTION WHEN OTHERS THEN
|
|
RETURN core.rpc_from_exception('fn_user_projects_save', SQLSTATE, SQLERRM);
|
|
END;
|
|
$$;
|
|
|
|
--changeset panel:iam-005w-iam-v2-rpc-grants endDelimiter:; splitStatements:true
|
|
GRANT EXECUTE ON FUNCTION iam.fn_permission_list(jsonb) TO panels_iam_app;
|
|
GRANT EXECUTE ON FUNCTION iam.fn_role_list(jsonb) TO panels_iam_app;
|
|
GRANT EXECUTE ON FUNCTION iam.fn_role_create(jsonb) TO panels_iam_app;
|
|
GRANT EXECUTE ON FUNCTION iam.fn_role_update(jsonb) TO panels_iam_app;
|
|
GRANT EXECUTE ON FUNCTION iam.fn_role_delete(jsonb) TO panels_iam_app;
|
|
GRANT EXECUTE ON FUNCTION iam.fn_role_permissions_get(jsonb) TO panels_iam_app;
|
|
GRANT EXECUTE ON FUNCTION iam.fn_role_permissions_save(jsonb) TO panels_iam_app;
|
|
GRANT EXECUTE ON FUNCTION iam.fn_user_list(jsonb) TO panels_iam_app;
|
|
GRANT EXECUTE ON FUNCTION iam.fn_user_create(jsonb) TO panels_iam_app;
|
|
GRANT EXECUTE ON FUNCTION iam.fn_user_update(jsonb) TO panels_iam_app;
|
|
GRANT EXECUTE ON FUNCTION iam.fn_user_reset_access(jsonb) TO panels_iam_app;
|
|
GRANT EXECUTE ON FUNCTION iam.fn_user_projects_save(jsonb) TO panels_iam_app;
|