69 lines
3.2 KiB
SQL
69 lines
3.2 KiB
SQL
-- 019_requisitions_rbac.sql
|
|
-- Manual one-shot: the `requisitions` permission module (8 tags), a
|
|
-- `requisitions_management` bundle holding them, and the bundle attached to
|
|
-- the staff roles that fill Employee Requisition forms (Annexure A). Mirrors
|
|
-- 007's idempotent pattern; applied automatically at startup by
|
|
-- alembic_setup.run_manual_sql() and recorded in manual_migrations.
|
|
--
|
|
-- The all_access bundle is a fixed id list seeded before this module existed,
|
|
-- so system_administrator gets requisitions access through THIS bundle, not
|
|
-- that one. Users must log in again after this applies — permissions are
|
|
-- resolved from the DB per request, but the frontend caches the list from
|
|
-- /users/me.
|
|
|
|
-- =============================================================================
|
|
-- 1. The 8 requisitions.* permission tags
|
|
-- =============================================================================
|
|
INSERT INTO app.permission_tags
|
|
(tag_name, module, action, description, created_at, updated_at, is_active, is_deleted)
|
|
VALUES
|
|
('requisitions.view', 'requisitions', 'view', NULL, NOW(), NOW(), true, false),
|
|
('requisitions.create', 'requisitions', 'create', NULL, NOW(), NOW(), true, false),
|
|
('requisitions.edit', 'requisitions', 'edit', NULL, NOW(), NOW(), true, false),
|
|
('requisitions.delete', 'requisitions', 'delete', NULL, NOW(), NOW(), true, false),
|
|
('requisitions.approve', 'requisitions', 'approve', NULL, NOW(), NOW(), true, false),
|
|
('requisitions.export', 'requisitions', 'export', NULL, NOW(), NOW(), true, false),
|
|
('requisitions.manage', 'requisitions', 'manage', NULL, NOW(), NOW(), true, false),
|
|
('requisitions.configure', 'requisitions', 'configure', NULL, NOW(), NOW(), true, false)
|
|
ON CONFLICT (tag_name) DO NOTHING;
|
|
|
|
-- =============================================================================
|
|
-- 2. Bundle holding all eight requisitions tags
|
|
-- =============================================================================
|
|
INSERT INTO app.permissions (name, description, permission_tags, is_system, created_at, updated_at, is_active, is_deleted)
|
|
SELECT
|
|
'requisitions_management',
|
|
'Employee requisition forms: view, create, edit and manage requisitions',
|
|
(
|
|
SELECT COALESCE(jsonb_agg(id ORDER BY id), '[]'::jsonb)
|
|
FROM app.permission_tags
|
|
WHERE is_deleted = false
|
|
AND module = 'requisitions'
|
|
),
|
|
true,
|
|
NOW(),
|
|
NOW(),
|
|
true,
|
|
false
|
|
WHERE NOT EXISTS (
|
|
SELECT 1 FROM app.permissions WHERE name = 'requisitions_management'
|
|
);
|
|
|
|
-- =============================================================================
|
|
-- 3. Attach the bundle to the staff roles (idempotent; same role list as 007)
|
|
-- =============================================================================
|
|
UPDATE app.roles r
|
|
SET permissions = COALESCE(r.permissions, '[]'::jsonb) || jsonb_build_array(p.id),
|
|
updated_at = NOW()
|
|
FROM app.permissions p
|
|
WHERE p.name = 'requisitions_management'
|
|
AND r.role_name IN (
|
|
'system_administrator',
|
|
'hr_administrator',
|
|
'recruiter',
|
|
'hiring_manager',
|
|
'department_head',
|
|
'ceo'
|
|
)
|
|
AND NOT (COALESCE(r.permissions, '[]'::jsonb) @> jsonb_build_array(p.id));
|