authen.public

Parameters

Name	Type	Mode
user_id	integer	IN
prop_id	integer	IN (DEFAULT NULL)

Definition

DECLARE
    granted_count int;
BEGIN
    DELETE FROM public.user_permission p
    WHERE p.user_id = sp_reset_user_permissions.user_id
      AND (sp_reset_user_permissions.prop_id IS NULL OR p.prop_id = sp_reset_user_permissions.prop_id);

INSERT INTO public.user_permission(user_id, prop_id, code, perm_id)
    WITH target_props AS (
        SELECT up.prop_id
        FROM public.user_property up
        WHERE up.user_id = sp_reset_user_permissions.user_id
          AND (sp_reset_user_permissions.prop_id IS NULL OR up.prop_id = sp_reset_user_permissions.prop_id)
    ),
    role_source AS (
        SELECT ur.user_id, ur.prop_id, ur.group_id
        FROM public.user_roles ur
        WHERE ur.user_id = sp_reset_user_permissions.user_id
          AND ur.active
          AND (sp_reset_user_permissions.prop_id IS NULL OR ur.prop_id = sp_reset_user_permissions.prop_id)
        UNION
        SELECT up.user_id, up.prop_id, u.user_group_id
        FROM public.user_property up
        JOIN public.users u ON u.id = up.user_id
        WHERE up.user_id = sp_reset_user_permissions.user_id
          AND u.user_group_id IS NOT NULL
          AND (sp_reset_user_permissions.prop_id IS NULL OR up.prop_id = sp_reset_user_permissions.prop_id)
          AND NOT EXISTS (
              SELECT 1
              FROM public.user_roles ur
              WHERE ur.user_id = up.user_id
                AND ur.prop_id = up.prop_id
                AND ur.active
          )
    ),
    role_allow AS (
        SELECT DISTINCT rs.user_id, rs.prop_id, p.code, p.id AS perm_id
        FROM role_source rs
        CROSS JOIN LATERAL public.fn_role_effective_perm_ids(rs.group_id, rs.prop_id) ep
        JOIN public.permission p ON p.id = ep
    ),
    override_allow AS (
        SELECT DISTINCT uo.user_id, uo.prop_id, uo.code, uo.perm_id
        FROM public.user_permission_override uo
        JOIN target_props tp ON tp.prop_id = uo.prop_id
        WHERE uo.user_id = sp_reset_user_permissions.user_id
          AND uo.action_type = 'allow'
    ),
    override_deny AS (
        SELECT DISTINCT uo.prop_id, uo.perm_id
        FROM public.user_permission_override uo
        JOIN target_props tp ON tp.prop_id = uo.prop_id
        WHERE uo.user_id = sp_reset_user_permissions.user_id
          AND uo.action_type = 'deny'
    ),
    allowed AS (
        SELECT * FROM role_allow
        UNION
        SELECT * FROM override_allow
    )
    SELECT DISTINCT allowed.user_id, allowed.prop_id, allowed.code, allowed.perm_id
    FROM allowed
    WHERE NOT EXISTS (
        SELECT 1
        FROM override_deny deny
        WHERE deny.prop_id = allowed.prop_id
          AND deny.perm_id = allowed.perm_id
    )
    ON CONFLICT ON CONSTRAINT user_permission_pkey DO UPDATE
    SET perm_id = EXCLUDED.perm_id;

GET DIAGNOSTICS granted_count = ROW_COUNT;
    RETURN granted_count;
END