019_update_permissions_v1-13.sql

/*
 GROUP HEADERS GENERATED BY: https://patorjk.com/software/taag/#p=display&h=0&v=1&c=c&f=ANSI%20Shadow&t=STAGE%20FUNCS

 SUB GROUP HEADERS GENERATED BY: https://patorjk.com/software/taag/#p=display&h=1&v=1&c=c&f=Banner3&t=permissions

 */
set search_path = public, const, ext, stage, helpers, internal, unsecure;

select *
from public.check_version('1.12', _component := 'keen_auth_permissions', _throw_err := true);

select *
from public.start_version_update('1.13',
                                 'Fixes related to assigning the user all default groups',
                                 _component := 'keen_auth_permissions');

create or replace function unsecure.add_user_to_default_groups(_created_by text, _user_id bigint, _target_user_id bigint,
																										_tenant_id int default 1)
	returns table
					(
						__user_id          bigint,
						__user_group_id    int,
						__user_group_code  text,
						__user_group_title text
					)
	language plpgsql
as
$$
declare
	group_data RECORD;
begin

	if
		not exists(select from auth.user_info where user_id = _user_id) then
		perform error.raise_52103(_user_id);
	end if;

	drop table if exists tmp_default_groups;

	create
		temporary table tmp_default_groups as
	select aug.user_group_id
	from auth.active_user_groups aug
	where aug.tenant_id = _tenant_id
		and aug.is_default
		and user_group_id not in (select group_id
															from auth.user_group_member ugm
																		 inner join auth.user_group ug on ug.user_group_id = ugm.group_id
															where ugm.user_id = _target_user_id
																and ug.tenant_id = _tenant_id
																and ug.is_default);


	for group_data in
		select dg.*
		from tmp_default_groups dg
		loop
			perform unsecure.create_user_group_member(_created_by, _user_id, group_data.user_group_id,
																								_target_user_id,
																								_tenant_id) member;
		end loop;

	return query
		select user_id, user_group_id, group_code, group_title
		from auth.user_group_members ugms
		where ugms.tenant_id = _tenant_id
			and ugms.user_id = _target_user_id;

	drop table tmp_default_groups;
end;
$$;



create or replace function unsecure.recalculate_user_groups(
	_created_by text
, _target_user_id bigint
, _provider_code text)
	returns TABLE
	        (
		        __tenant_id       integer,
		        __user_group_id   integer,
		        __user_group_code text
	        )
	language plpgsql
as
$$
declare
	__not_really_used int;
	__provider_groups text[];
	__provider_roles  text[];
begin

	select provider_groups
			 , provider_roles
	from auth.user_identity
	where provider_code = _provider_code
		and user_id = _target_user_id
	into __provider_groups, __provider_roles;

	perform
	from auth.tenant t
		 , lateral unsecure.add_user_to_default_groups(_created_by, 1, _target_user_id,
		                                               t.tenant_id);
-- 	insert into auth.user_group_member (created_by, group_id, user_id, member_type_code)
-- 	select _created_by
-- 			 , ug.user_group_id
-- 			 , _target_user_id
-- 			 , 'adhoc'
-- 	from auth.user_group ug
-- 	where ug.is_default
-- 	on conflict (group_id, user_id) do nothing;

	-- cleanup membership of groups user is no longer part of
	with affected_deleted_group_tenants as (
		delete
			from auth.user_group_member
				where user_id = _target_user_id
					and mapping_id is not null
					and group_id not in (
						select distinct ugm.group_id
						from unnest(__provider_groups) g
							inner join auth.user_group_mapping ugm
							           on ugm.provider_code = _provider_code and ugm.mapped_object_id = lower(g)
						-- 							inner join auth.user_group u
						-- 							           on u.user_group_id = ugm.group_id
						union
						select distinct ugm.group_id
						from unnest(__provider_roles) r
							inner join auth.user_group_mapping ugm
							           on ugm.provider_code = _provider_code and ugm.mapped_role = lower(r)
						-- 							inner join auth.user_group u
						-- 							           on u.user_group_id = ugm.group_id
					)
				returning group_id)
		 , affected_group_tenants as (
		insert
			into auth.user_group_member (created_by, user_id, group_id, mapping_id, member_type_code)
				select distinct _created_by
				              , _target_user_id
				              , ugm.group_id
				              , ugm.ug_mapping_id
				              , 'adhoc'
				from unnest(__provider_groups) g
					inner join auth.user_group_mapping ugm
					           on ugm.provider_code = _provider_code and ugm.mapped_object_id = lower(g)
				where ugm.group_id not in (
					select group_id
					from auth.user_group_member
					where user_id = _target_user_id)
				returning group_id)
		 , affected_role_tenants as (
		insert
			into auth.user_group_member (created_by, user_id, group_id, mapping_id, member_type_code)
				select distinct _created_by
				              , _target_user_id
				              , ugm.group_id
				              , ugm.ug_mapping_id
				              , 'adhoc'
				from unnest(__provider_roles) r
					inner join auth.user_group_mapping ugm
					           on ugm.provider_code = _provider_code and ugm.mapped_role = lower(r)
				where ugm.group_id not in (
					select group_id
					from auth.user_group_member
					where user_id = _target_user_id)
				returning group_id)
		 , all_group_ids as (
		select group_id
		from affected_deleted_group_tenants
		union
		select group_id
		from affected_group_tenants
		union
		select group_id
		from affected_role_tenants)
		 , all_tenants as (
		select tenant_id
		from all_group_ids ids
			inner join auth.user_group ug
			           on ids.group_id = ug.user_group_id
		group by tenant_id)
	-- variable not really used, it's there just to avoid 'query has no destination for result data'
	select at.tenant_id
	from all_tenants at
		 , lateral unsecure.clear_permission_cache(_created_by, _target_user_id, at.tenant_id) r
	into __not_really_used;

	return query
		select distinct ug.tenant_id
		              , ug.user_group_id
		              , ug.code
		from auth.user_group_member ugm
			inner join auth.user_group ug on ug.user_group_id = ugm.group_id
		where ugm.user_id = _target_user_id;
end;
$$;


/***
 *    ██╗   ██╗██████╗ ██████╗  █████╗ ████████╗███████╗    ██████╗  █████╗ ████████╗ █████╗
 *    ██║   ██║██╔══██╗██╔══██╗██╔══██╗╚══██╔══╝██╔════╝    ██╔══██╗██╔══██╗╚══██╔══╝██╔══██╗
 *    ██║   ██║██████╔╝██║  ██║███████║   ██║   █████╗      ██║  ██║███████║   ██║   ███████║
 *    ██║   ██║██╔═══╝ ██║  ██║██╔══██║   ██║   ██╔══╝      ██║  ██║██╔══██║   ██║   ██╔══██║
 *    ╚██████╔╝██║     ██████╔╝██║  ██║   ██║   ███████╗    ██████╔╝██║  ██║   ██║   ██║  ██║
 *     ╚═════╝ ╚═╝     ╚═════╝ ╚═╝  ╚═╝   ╚═╝   ╚══════╝    ╚═════╝ ╚═╝  ╚═╝   ╚═╝   ╚═╝  ╚═╝
 *
 */


-- create
-- 	or replace function auth.update_permission_data_v1_13()
-- 	returns setof int
-- 	language plpgsql
-- as
-- $$
-- declare
-- 	__update_username text := 'auth_update_v1_13';
-- begin
-- end;
-- $$;


/***
 *    ██████╗  ██████╗ ███████╗████████╗     ██████╗██████╗ ███████╗ █████╗ ████████╗███████╗
 *    ██╔══██╗██╔═══██╗██╔════╝╚══██╔══╝    ██╔════╝██╔══██╗██╔════╝██╔══██╗╚══██╔══╝██╔════╝
 *    ██████╔╝██║   ██║███████╗   ██║       ██║     ██████╔╝█████╗  ███████║   ██║   █████╗
 *    ██╔═══╝ ██║   ██║╚════██║   ██║       ██║     ██╔══██╗██╔══╝  ██╔══██║   ██║   ██╔══╝
 *    ██║     ╚██████╔╝███████║   ██║       ╚██████╗██║  ██║███████╗██║  ██║   ██║   ███████╗
 *    ╚═╝      ╚═════╝ ╚══════╝   ╚═╝        ╚═════╝╚═╝  ╚═╝╚══════╝╚═╝  ╚═╝   ╚═╝   ╚══════╝
 *
 */

-- call auth.update_permission_data_v1_13();

select *
from public.stop_version_update('1.13', _component := 'keen_auth_permissions');