authen
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
fn_role_revoke_permissions
Parameters
Name
Type
Mode
v_group_id
integer
IN
v_prop_id
integer
IN
v_codes
bpchar[]
IN
v_revoke_all
boolean
IN (DEFAULT false)
Definition
DECLARE v_is_standard boolean; v_app text; BEGIN SELECT (prop_id IS NULL), COALESCE(app_code, 'pms') INTO v_is_standard, v_app FROM public.user_group WHERE id = v_group_id; IF v_is_standard THEN INSERT INTO public.group_permission_override(group_id, prop_id, code, perm_id, action_type) SELECT v_group_id, v_prop_id, p.code, p.id, 'deny' FROM public.permission p WHERE (v_revoke_all AND p.app_code = v_app) OR p.code = ANY(v_codes) ON CONFLICT ON CONSTRAINT group_permission_override_pkey DO UPDATE SET perm_id = EXCLUDED.perm_id, action_type = 'deny', updated_at = now(); ELSE DELETE FROM public.group_permission gp WHERE gp.group_id = v_group_id AND (v_revoke_all OR gp.code = ANY(v_codes)); END IF; PERFORM public.sp_reset_user_permissions(ur.user_id, ur.prop_id) FROM public.user_roles ur WHERE ur.group_id = v_group_id AND ur.prop_id = v_prop_id AND ur.active; END