authen.public

Parameters

Name	Type	Mode
p_code	text	IN
p_name	text	IN
p_category	text	IN (DEFAULT NULL)
p_copy_from	text	IN (DEFAULT NULL)
p_parent	text	IN (DEFAULT NULL)
p_app_code	text	IN (DEFAULT 'pms')

Definition

DECLARE
    v_parent_id bigint;
    v_source_id bigint;
    v_perm_id   bigint;
BEGIN
    IF p_code IS NULL OR btrim(p_code) = '' THEN
        RAISE EXCEPTION 'sp_create_permission: p_code is required';
    END IF;

IF p_parent IS NOT NULL THEN
        SELECT id
        INTO v_parent_id
        FROM public.permission
        WHERE code = p_parent
          AND app_code = p_app_code;

IF v_parent_id IS NULL THEN
            RAISE EXCEPTION 'sp_create_permission: parent permission % not found for app %',
                p_parent, p_app_code;
        END IF;
    END IF;

IF p_copy_from IS NOT NULL THEN
        SELECT id
        INTO v_source_id
        FROM public.permission
        WHERE code = p_copy_from
          AND app_code = p_app_code;

IF v_source_id IS NULL THEN
            RAISE EXCEPTION 'sp_create_permission: source permission % not found for app %',
                p_copy_from, p_app_code;
        END IF;
    END IF;

INSERT INTO public.permission (code, name, category, parent, app_code, parent_id)
    VALUES (p_code, p_name, p_category, p_parent, p_app_code, v_parent_id)
    ON CONFLICT ON CONSTRAINT permission_code_key DO UPDATE
    SET name      = EXCLUDED.name,
        category  = EXCLUDED.category,
        parent    = EXCLUDED.parent,
        app_code  = EXCLUDED.app_code,
        parent_id = EXCLUDED.parent_id
    RETURNING id INTO v_perm_id;

IF v_source_id IS NULL OR v_source_id = v_perm_id THEN
        RETURN v_perm_id;
    END IF;

-- Role baseline grants.
    INSERT INTO public.group_permission (group_id, code, perm_id)
    SELECT src.group_id, p_code, v_perm_id
    FROM public.group_permission src
    WHERE src.perm_id = v_source_id
    ON CONFLICT ON CONSTRAINT group_permission_pkey DO UPDATE
    SET code = EXCLUDED.code;

-- Materialized effective user grants, per property.
    INSERT INTO public.user_permission (user_id, prop_id, code, perm_id)
    SELECT src.user_id, src.prop_id, p_code, v_perm_id
    FROM public.user_permission src
    WHERE src.perm_id = v_source_id
    ON CONFLICT ON CONSTRAINT user_permission_pkey DO UPDATE
    SET code = EXCLUDED.code;

-- Property-specific role overrides (allow/deny).
    INSERT INTO public.group_permission_override
        (group_id, prop_id, code, perm_id, action_type, created_at, updated_at)
    SELECT src.group_id, src.prop_id, p_code, v_perm_id,
           src.action_type, src.created_at, src.updated_at
    FROM public.group_permission_override src
    WHERE src.perm_id = v_source_id
    ON CONFLICT ON CONSTRAINT group_permission_override_pkey DO UPDATE
    SET code        = EXCLUDED.code,
        action_type = EXCLUDED.action_type,
        created_at  = EXCLUDED.created_at,
        updated_at  = EXCLUDED.updated_at;

-- Explicit per-user overrides (allow/deny).
    INSERT INTO public.user_permission_override
        (user_id, prop_id, code, perm_id, action_type, created_at, updated_at)
    SELECT src.user_id, src.prop_id, p_code, v_perm_id,
           src.action_type, src.created_at, src.updated_at
    FROM public.user_permission_override src
    WHERE src.perm_id = v_source_id
    ON CONFLICT ON CONSTRAINT user_permission_override_pkey DO UPDATE
    SET code        = EXCLUDED.code,
        action_type = EXCLUDED.action_type,
        created_at  = EXCLUDED.created_at,
        updated_at  = EXCLUDED.updated_at;

RETURN v_perm_id;
END

sp_create_permission

Description

Parameters

Definition