011_update_permissions_v1-5.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

 */

select *
from start_version_update('1.5',
													E'Search enhancements for user info and data\nupdate of username, display name and email based on data coming from authentication provider',
													'', _component := 'keen_auth_permissions');


create schema if not exists triggers;

alter table auth.user_data
	add nrm_search_data text;

create index if not exists ix_trgm_user_data_search
	on auth.user_data using gin (nrm_search_data ext.gin_trgm_ops);

create or replace function triggers.calculate_user_data_search_values(_user_data auth.user_data)
	returns text
	language plpgsql as
$$
begin

	return null;
end ;
$$;

create or replace function triggers.calculate_user_data() returns trigger
	language plpgsql
as
$$
begin
	if tg_op = 'INSERT' or tg_op = 'UPDATE' then

		new.nrm_search_data = triggers.calculate_user_data_search_values(new);

		return new;
	end if;
end ;
$$;

create trigger trg_auth_calculate_user_data
	before insert or update
	on auth.user_data
	for each row
execute procedure triggers.calculate_user_data();

alter table auth.user_info
	add column nrm_search_data text;

create index if not exists ix_trgm_user_info_search
	on auth.user_info using gin (nrm_search_data ext.gin_trgm_ops);

create or replace function triggers.calculate_user_info_search_values(_user_info auth.user_info)
	returns text
	language plpgsql as
$$
begin

	return concat_ws(' ', helpers.normalize_text(_user_info.username)
		, helpers.normalize_text(_user_info.display_name)
		, helpers.normalize_text(_user_info.email)
		);
end;
$$;

create or replace function triggers.calculate_user_info() returns trigger
	language plpgsql
as
$$
begin
	if tg_op = 'INSERT' or tg_op = 'UPDATE' then

		new.nrm_search_data = triggers.calculate_user_info_search_values(new);

		return new;
	end if;
end ;
$$;

create trigger trg_auth_calculate_user_info
	before insert or update
	on auth.user_info
	for each row
execute procedure triggers.calculate_user_info();


-- update username, display name and email on login based on data coming from provider

create or replace function unsecure.update_user_info_basic_data(_updated_by text, _user_id bigint,
																																_target_user_id bigint,
																																_username text, _display_name text,
																																_email text DEFAULT NULL::text)
	returns table
					(
						__user_info_id bigint
					)
	language plpgsql
as
$$
begin
	update auth.user_info
	set modified          = now(),
			modified_by       = _updated_by,
			username          = trim(lower(_username)),
			original_username = _username,
			display_name      = _display_name,
			email             = _email
	where user_id = _target_user_id;

	perform auth.create_user_event(_updated_by, _user_id, 'update_user_info', _target_user_id);

	perform
		add_journal_msg(_updated_by, _user_id
			, format('User basic data (upn: %s) updated by user: %s'
											, _username, _updated_by)
			, 'user_info', _target_user_id
			, _payload := array ['username', _username, 'display_name', _display_name,
			'email', _email]
			, _event_id := 50102
			, _tenant_id := 1);
end;
$$;

create or replace function auth.ensure_user_from_provider(_created_by text, _user_id bigint, _provider_code text,
																													_provider_uid 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
	__last_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
	into __last_id, __is_user_active, __is_identity_active, __can_login, __username, __display_name, __email;

	if __last_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 __last_id;

		perform
			unsecure.create_user_identity(_created_by, _user_id, __last_id
				, _provider_code, _provider_uid, _is_active := true);
	else
		-- 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, __last_id, _username, _display_name, _email);
		end if;

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

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

		if
			not __is_identity_active then
			perform error.raise_52110(__last_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 = __last_id
		and provider_code = _provider_code
		and uid <> _provider_uid;

	perform unsecure.update_last_used_provider(__last_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
-- 	or replace function auth.update_permission_data_v1_5()
-- 	returns setof int
-- 	language plpgsql
-- as
-- $$
-- declare
-- 	__update_username text := 'auth_update_v1_5';
-- begin
--
--
-- end;
-- $$;

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