018_update_permissions_v1-12.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 check_version('1.11', _component := 'keen_auth_permissions', _throw_err := true);

select *
from start_version_update('1.12',
													'Fixes related to situation when user identity has to be created, for example, for per resource authorization, but provider_uid is unknown at that point',
													_component := 'keen_auth_permissions');



-- ██████  ███████ ██████  ███    ███ ███████
-- ██   ██ ██      ██   ██ ████  ████ ██
-- ██████  █████   ██████  ██ ████ ██ ███████
-- ██      ██      ██   ██ ██  ██  ██      ██
-- ██      ███████ ██   ██ ██      ██ ███████

select auth.create_permission(_created_by := 'update_permissions_v1_12', _user_id := 1, _title := 'Create User',
															_parent_full_code := 'users', _is_assignable := true);


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


drop function unsecure.update_user_identity_oid;
-- _provider_code is now also taken into consideration, and both provider_uid and provider_oid are being updated, if different

create or replace function unsecure.update_user_identity_uid_oid(_updated_by text, _user_id bigint,
																																 _target_user_id bigint,
																																 _provider_code text, _provider_uid text,
																																 _provider_oid text) returns void
	language plpgsql
as
$$
declare
	__upn              text;
	__current_oid      text;
	__current_uid      text;
	__user_identity_id bigint;
begin

	select username
	from auth.user_info ui
	where ui.user_id = _user_id
	into __upn;

	select user_identity_id, uid, provider_oid
	from auth.user_identity uid
	where uid.user_id = _target_user_id
		and provider_code = _provider_code
	into __user_identity_id, __current_uid, __current_oid;

	if __current_uid <> _provider_uid then

		update auth.user_identity
		set uid = _provider_uid
		where user_identity_id = __user_identity_id;

		perform
			add_journal_msg_jsonb('system', _user_id
				, format('User: (upn: %s) updated user: (upn: %) identity uid for provider: (code: %)'
															, _updated_by, __upn, _provider_code)
				, 'user'
				, _target_user_id
				, _data_object_code := __upn
				, _payload := jsonb_build_object('provider_uid', _provider_uid)
				, _event_id := 50137
				, _tenant_id := 1);
	end if;

	if __current_oid <> _provider_oid then

		update auth.user_identity
		set provider_oid = _provider_oid
		where user_identity_id = __user_identity_id;

		perform
			add_journal_msg_jsonb('system', _user_id
				, format('User: (upn: %s) updated user: (upn: %) identity uid for provider: (code: %)'
															, _updated_by, __upn, _provider_code)
				, 'user'
				, _target_user_id
				, _data_object_code := __upn
				, _payload := jsonb_build_object('provider_oid', _provider_oid)
				, _event_id := 50137
				, _tenant_id := 1);
	end if;
end;
$$;



-- THE SEARCH FOR ALREADY EXISTING IDENTITY IS NOW DONE NOT ONLY BY _provider_uid,
-- WHICH MIGHT BE A TEMPORARY VALUE STORED IN auth.user_identity TABLE,
-- BUT ALSO BY provider_oid

create
	or replace function auth.ensure_user_from_provider(_created_by text, _user_id bigint, _provider_code text,
																										 _provider_uid text,
																										 _provider_oid text, _username text, _display_name text,
																										 _email text default null::text,
																										 _user_data jsonb default null::jsonb)
	returns TABLE
					(
						__user_id      bigint,
						__code         text,
						__uuid         text,
						__username     text,
						__email        text,
						__display_name text
					)
	language plpgsql
as
$$
declare
	__target_user_id     bigint;
	__can_login          bool;
	__is_user_active     bool;
	__is_identity_active bool;
	__username           text;
	__display_name       text;
	__email              text;
begin

	if
		lower(_provider_code) = 'email' then
		perform error.raise_52101(_username);
	end if;

	perform
		auth.validate_provider_is_active(_provider_code);

	select uid.user_id, u.is_active, uid.is_active, u.can_login, u.username, u.display_name, u.email
	from auth.user_identity uid
				 inner join auth.user_info u on uid.user_id = u.user_id
	where uid.provider_code = _provider_code
		and (uid.uid = _provider_uid or uid.provider_oid = _provider_oid)
	into __target_user_id, __is_user_active, __is_identity_active, __can_login, __username, __display_name, __email;

	if
		__target_user_id is null then
		-- create user because it does not exists
		select user_id
		from unsecure.create_user_info(_created_by, _user_id, lower(_username), lower(_email), _display_name,
																	 _provider_code)
		into __target_user_id;

		perform
			unsecure.create_user_identity(_created_by, _user_id, __target_user_id
				, _provider_code, _provider_uid, _provider_oid, _is_active := true);
	else
		-- update provider_oid
		perform unsecure.update_user_identity_uid_oid(_created_by, _user_id, __target_user_id
			, _provider_code, _provider_uid
			, _provider_oid);

		-- update basic user data coming from
		if
			(trim(lower(_username)) <> __username
				or _display_name <> __display_name
				or _email <> __email) then
			perform unsecure.update_user_info_basic_data(_created_by, _user_id, __target_user_id, _username, _display_name,
																									 _email);
		end if;

		if
			not __can_login then
			perform error.raise_52112(__target_user_id);
		end if;

		if
			not __is_user_active then
			perform error.raise_52105(__target_user_id);
		end if;

		if
			not __is_identity_active then
			perform error.raise_52110(__target_user_id, _provider_code);
		end if;
	end if;

	-- clean all previous uids for the same provider for given user
	delete
	from auth.user_identity
	where user_id = __target_user_id
		and provider_code = _provider_code
		and uid <> _provider_uid;

	perform
		unsecure.update_last_used_provider(__target_user_id, _provider_code);

	return query
		select ui.user_id
				 , ui.code
				 , ui.uuid::text
				 , ui.username
				 , ui.email
				 , ui.display_name
		from auth.user_identity uid
					 inner join auth.user_info ui on uid.user_id = ui.user_id
		where uid.provider_code = _provider_code
			and uid.uid = _provider_uid;
end;
$$;

create function auth.get_user_by_provider_oid(_user_id bigint, _provider_oid text)
	returns TABLE
					(
						__user_id      bigint,
						__code         text,
						__uuid         text,
						__username     text,
						__email        text,
						__display_name text
					)
	language plpgsql
as
$$
begin

	return query
		select ui.user_id
				 , code
				 , uuid::text
				 , username
				 , email
				 , display_name
		from auth.user_identity uid
			left join auth.user_info ui on ui.user_id = uid.user_id
		where uid.provider_oid = _provider_oid;
end;
$$;


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


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

select *
from stop_version_update('1.12', _component := 'keen_auth_permissions');