015_update_permissions_v1-9.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.9',
													E'Tenant/Token/Journal related changes',
													'- new user_identity column provider_oid ' ||
													'- fixed journal messages ' ||
													'- fixed token related journal messages',
													_component := 'keen_auth_permissions');

/***
 *    ########  #######  ##    ## ######## ##    ##    ##     ## ######## ######## ##     ##  #######  ########   ######
 *       ##    ##     ## ##   ##  ##       ###   ##    ###   ### ##          ##    ##     ## ##     ## ##     ## ##    ##
 *       ##    ##     ## ##  ##   ##       ####  ##    #### #### ##          ##    ##     ## ##     ## ##     ## ##
 *       ##    ##     ## #####    ######   ## ## ##    ## ### ## ######      ##    ######### ##     ## ##     ##  ######
 *       ##    ##     ## ##  ##   ##       ##  ####    ##     ## ##          ##    ##     ## ##     ## ##     ##       ##
 *       ##    ##     ## ##   ##  ##       ##   ###    ##     ## ##          ##    ##     ## ##     ## ##     ## ##    ##
 *       ##     #######  ##    ## ######## ##    ##    ##     ## ########    ##    ##     ##  #######  ########   ######
 */

create or replace function auth.create_token(_created_by text, _user_id bigint,
																						 _target_user_id bigint,
																						 _target_user_oid text,
																						 _user_event_id int,
																						 _token_type_code text,
																						 _token_channel_code text,
																						 _token text,
																						 _expires_at timestamptz default null,
																						 _token_data jsonb default null)
	returns table
					(
						___token_id   bigint,
						___token_uid  text,
						___expires_at timestamptz
					)
	language plpgsql
as
$$
declare
	__default_expiration_in_seconds int;
-- 	__last_id                       bigint;
-- 	__token_uid                     text;
-- 	__token_expires_at              timestamptz;
	__last_item                     auth.token;
	__target_username               text;
begin
	perform
		auth.has_permission(_user_id, 'tokens.create_token');

	if
		_expires_at is null then

		select default_expiration_in_seconds
		from const.token_type
		where code = _token_type_code
		into __default_expiration_in_seconds;

		_expires_at := now() + '1 second'::interval * __default_expiration_in_seconds;
	end if;

	if
		_target_user_id is not null then
		-- invalidate all previous tokens of the same type for the same user that are still valid
		update auth.token
		set modified         = now()
			, modified_by      = _created_by
			, token_state_code = 'invalid'
		where user_id = _target_user_id
			and token_type_code = _token_type_code
			and token_state_code = 'valid';
	end if;

	if
		exists(select
					 from auth.token
					 where token = _token
						 and token_state_code = 'valid'
						 and token_type_code = _token_type_code) then
		perform error.raise_52276();
	end if;

	insert into auth.token ( created_by, user_id, user_oid, user_event_id, token_type_code, token_channel_code, token
												 , expires_at, token_data)
	values ( _created_by
				 , _target_user_id
				 , _target_user_oid
				 , _user_event_id
				 , _token_type_code
				 , _token_channel_code
				 , _token
				 , _expires_at
				 , _token_data)
	returning *
		into __last_item;

	select username
	from auth.user_info ui
	where ui.user_id = __last_item.user_id
	into __target_username;

	perform
		add_journal_msg_jsonb(_created_by, _user_id
			, format('Token: (type: %s, uid: %s) for user: (upn: %s) created by: %s'
														, __last_item.token_type_code, __last_item.uid, __target_username, _created_by)
			, 'token', __last_item.token_id
			, jsonb_build_object('user_id', __last_item.user_id, 'username', __target_username)
			, 50401
			, _tenant_id := 1);

	return query
		select __last_item.token_id, __last_item.uid, __last_item.expires_at;

	perform unsecure.expire_tokens(_created_by);
end;
$$;

drop function auth.validate_token(_modified_by text, _user_id bigint, _target_user_id bigint,
																	_token_uid text, _token text,
																	_token_type text, _ip_address text, _user_agent text, _origin text,
																	_set_as_used boolean);

-- auth.validate_token now creates better journal messages and is more effective
create or replace function auth.validate_token(_modified_by text, _user_id bigint, _target_user_id bigint,
																							 _token_uid text, _token text,
																							 _token_type_code text, _ip_address text, _user_agent text, _origin text,
																							 _set_as_used boolean default false)
	returns TABLE
					(
						___token_id         bigint,
						___token_uid        text,
						___token_state_code text,
						___used_at          timestamp with time zone,
						___user_id          bigint,
						___user_oid         text,
						___token_data       jsonb
					)
	language plpgsql
as
$$
declare
	__target_username text;
	__last_item       auth.token;
begin
	perform
		auth.has_permission(_user_id, 'tokens.validate_token');

	select *
	from auth.token
	where ((_target_user_id is not null and token.user_id = _target_user_id) or true)
		and token_type_code = _token_type_code
		and (helpers.is_not_empty_string(_token_uid) or helpers.is_not_empty_string(_token))
		and (helpers.is_empty_string(_token_uid) or uid = _token_uid)
		and (helpers.is_empty_string(_token) or token = _token)
	into __last_item;

	if
		__last_item.token_id is null then
		perform error.raise_52277();
	end if;

	if
		__last_item.token_state_code <> 'valid' then
		perform error.raise_52278(__last_item.uid);
	end if;

	if
		_target_user_id is not null and _target_user_id <> __last_item.user_id then
		perform error.raise_52279(__last_item.uid);
	end if;

	select username
	from auth.user_info ui
	where ui.user_id = __last_item.user_id
	into __target_username;

	perform
		add_journal_msg_jsonb(_modified_by, _user_id
			, format('Token: (type: %s, uid: %s) for user: (upn: %s) validated by: %s'
														, __last_item.token_type_code, __last_item.uid, __target_username, _modified_by)
			, 'token', __last_item.token_id
			, jsonb_build_object('user_id', __last_item.user_id, 'username', __target_username
														, 'ip_address', _ip_address
														, 'user_agent', _user_agent
														, 'origin', _origin)
			, 50402
			, _tenant_id := 1);

	if
		_set_as_used then
		return query
			select used_token.__token_id
					 , used_token.__token_uid
					 , used_token.__token_state_code
					 , used_token.__used_at
					 , used_token.__user_id
					 , used_token.__user_oid
					 , used_token.__token_data
			from auth.set_token_as_used(_modified_by, _user_id, __last_item.uid, _token,
																	_token_type_code, _ip_address, _user_agent,
																	_origin) used_token;
	else
		return query
			select __last_item.token_id,
						 __last_item.uid,
						 __last_item.token_state_code,
						 __last_item.used_at,
						 __last_item.user_id,
						 __last_item.user_oid,
						 __last_item.token_data;
	end if;

	-- invalidate old tokens, this way we don't need a job to do that, every user will work for us this way
	perform unsecure.expire_tokens(_modified_by);
end;
$$;

create or replace function auth.set_token_as_used(_modified_by text,
																									_user_id bigint,
																									_token_uid text,
																									_token text,
																									_token_type_code text,
																									_ip_address text,
																									_user_agent text,
																									_origin text
)
	returns table
					(
						__token_id         bigint,
						__token_uid        text,
						__token_state_code text,
						__used_at          timestamptz,
						__user_id          bigint,
						__user_oid         text,
						__token_data       jsonb
					)
	language plpgsql
as
$$
declare
	__last_item       auth.token;
	__target_username text;
begin

	perform
		auth.has_permission(_user_id, 'tokens.set_as_used');

	select *
	from auth.token
	where (helpers.is_not_empty_string(_token_uid) or helpers.is_not_empty_string(_token))
		and uid = _token_uid
		and token = _token
		and token_type_code = _token_type_code
		and token_state_code = 'valid'
	into __last_item;

	select username
	from auth.user_info ui
	where ui.user_id = __last_item.user_id
	into __target_username;

	return query
		update auth.token
			set modified_by = _modified_by, modified = now(), token_state_code = 'used', used_at = now(), ip_address = _ip_address, user_agent = _user_agent, origin = _origin
			where
				(helpers.is_empty_string(_token_uid) or _token_uid = uid)
					and token = _token
			returning token_id
				, uid
				, token_state_code
				, used_at
				, user_id
				, user_oid
				, token_data;

	perform
		add_journal_msg_jsonb(_modified_by, _user_id
			, format('Token: (type: %s, uid: %s) for user: (upn: %s) set as used'
														, __last_item.token_type_code, __last_item.uid, __target_username)
			, 'token', __last_item.token_id
			, jsonb_build_object('user_id', __last_item.user_id, 'username', __target_username
														, 'ip_address', _ip_address
														, 'user_agent', _user_agent
														, 'origin', _origin)
			, _event_id := 50403
			, _tenant_id := 1);
end;
$$;



/***
 *    ##     ##  ######  ######## ########     ######## ######## ##    ##    ###    ##    ## ########  ######
 *    ##     ## ##    ## ##       ##     ##       ##    ##       ###   ##   ## ##   ###   ##    ##    ##    ##
 *    ##     ## ##       ##       ##     ##       ##    ##       ####  ##  ##   ##  ####  ##    ##    ##
 *    ##     ##  ######  ######   ########        ##    ######   ## ## ## ##     ## ## ## ##    ##     ######
 *    ##     ##       ## ##       ##   ##         ##    ##       ##  #### ######### ##  ####    ##          ##
 *    ##     ## ##    ## ##       ##    ##        ##    ##       ##   ### ##     ## ##   ###    ##    ##    ##
 *     #######   ######  ######## ##     ##       ##    ######## ##    ## ##     ## ##    ##    ##     ######
 */

-- add last_selected_tenant_id to auth.user_info, so we can select proper tenant on next login

alter table auth.user_info
	add column last_selected_tenant_id integer references auth.tenant (tenant_id) on delete set null;

create or replace function auth.update_user_last_selected_tenant(_user_id bigint, _target_user_id bigint, _selected_tenant_id integer)
	returns table
					(
						__used_id   bigint,
						__tenant_id integer
					)
	language plpgsql
as
$$
declare
	__necessary_permission_code text := 'users.update_last_selected_tenant';
	__user_upn                  text;
	__target_user_upn           text;
begin

	if _user_id <> _target_user_id and not auth.has_permission(_user_id, __necessary_permission_code) then
		perform auth.throw_no_permission(_user_id, __necessary_permission_code);
	end if;

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

	select username
	from auth.user_info
	where user_id = _target_user_id
	into __target_user_upn;

	return query
		update auth.user_info
			set last_selected_tenant_id = _selected_tenant_id
			where user_id = _user_id
			returning user_id, last_selected_tenant_id;


	if _user_id <> _target_user_id and _user_id <> 1 then
		perform
			add_journal_msg_jsonb('system', _user_id
				, format('User''s (upn: %s) last selected tenant updated by: %s'
															, __target_user_upn, __user_upn)
				, 'user'
				, _target_user_id
				, _data_object_code := __target_user_upn
				, _payload := jsonb_build_object('tenant_id', _selected_tenant_id)
				, _event_id := 50138
				, _tenant_id := 1);
	end if;
end;
$$;

create or replace function auth.get_user_available_tenants(_user_id bigint, _target_user_id bigint,
																													 _area text default 'areas.admin')
	returns table
					(
						_tenant_id    integer,
						_tenant_code  text,
						_tenant_title text
					)
	language plpgsql
as
$$
declare
	__necessary_permission_code text := 'users.get_available_tenants';
	__permission_id             int;
begin

	if _user_id <> _target_user_id and not auth.has_permission(_user_id, __necessary_permission_code) then
		perform auth.throw_no_permission(_user_id, __necessary_permission_code);
	end if;

	select p.permission_id
	from permission p
	where p.code = _area
	into __permission_id;

	return query
		with member_of_tenants as (select pa.tenant_id
															 from perm_set_perm psp
																			inner join auth.permission_assignment pa
																								 on psp.perm_set_id = pa.perm_set_id or pa.permission_id = __permission_id
															 where psp.permission_id = __permission_id
																 and (pa.user_id = _target_user_id or
																			pa.group_id in
																			(select group_id
																			 from auth.user_group_member ugm
																			 where ugm.user_id = _target_user_id)))
		select mt.tenant_id, t.code, t.title
		from member_of_tenants mt
					 inner join auth.tenant t on mt.tenant_id = t.tenant_id
		order by t.title;
end;
$$;

/***
 *       ###    ########  ########     ########  ########   #######  ##     ## #### ########  ######## ########      #######  #### ########
 *      ## ##   ##     ## ##     ##    ##     ## ##     ## ##     ## ##     ##  ##  ##     ## ##       ##     ##    ##     ##  ##  ##     ##
 *     ##   ##  ##     ## ##     ##    ##     ## ##     ## ##     ## ##     ##  ##  ##     ## ##       ##     ##    ##     ##  ##  ##     ##
 *    ##     ## ##     ## ##     ##    ########  ########  ##     ## ##     ##  ##  ##     ## ######   ########     ##     ##  ##  ##     ##
 *    ######### ##     ## ##     ##    ##        ##   ##   ##     ##  ##   ##   ##  ##     ## ##       ##   ##      ##     ##  ##  ##     ##
 *    ##     ## ##     ## ##     ##    ##        ##    ##  ##     ##   ## ##    ##  ##     ## ##       ##    ##     ##     ##  ##  ##     ##
 *    ##     ## ########  ########     ##        ##     ##  #######     ###    #### ########  ######## ##     ##     #######  #### ########
 */

-- add provider_oid to auth.user_identity so we can have it at one place
-- provider_oid is meant for real ID of user in authentication authority database.
--
-- For example, in Azure Active Directory, it's field "id"
-- In Active Directory, it's "objectGUID"
--
-- Value of this column is supposed to be updated with sign in of user, since we added later into the game, it will contain temporary value "to_be_updated" + user_id, so we can set it as unique

alter table auth.user_identity
	add column provider_oid text;

update auth.user_identity
set provider_oid = 'to_be_updated_' || user_id
where true;

alter table auth.user_identity
	alter column provider_oid set not null,
	add constraint uq_user_identity_provider_oid unique (provider_oid);


-- added _provider_oid parameter and fixed journal message payload
drop function unsecure.create_user_identity(_created_by text, _user_id bigint, _target_user_id bigint,
																						_provider_code text,
																						_provider_uid text, _password_hash text,
																						_user_data text, _password_salt text,
																						_is_active boolean);
create or replace function unsecure.create_user_identity(_created_by text, _user_id bigint, _target_user_id bigint,
																							_provider_code text,
																							_provider_uid text,
																							_provider_oid text,
																							_password_hash text default null::text,
																							_user_data text default null::text,
																							_password_salt text default null::text,
																							_is_active boolean default false)
	returns TABLE
					(
						__user_id       bigint,
						__provider_code text,
						__provider_uid  text
					)
	rows 1
	language plpgsql
as
$$
declare
	__user_info auth.user_info;
begin

	select *
	from auth.user_info
	where user_id = _target_user_id
	into __user_info;

	return query insert into auth.user_identity (created_by, modified_by, user_id, provider_code, uid, provider_oid,
																							 user_data, password_hash, password_salt, is_active)
		values ( _created_by, _created_by, _target_user_id, _provider_code, _provider_uid, _provider_oid, _user_data::jsonb
					 , _password_hash
					 , _password_salt, _is_active)
		returning user_id, provider_code, uid;

	perform
		add_journal_msg_jsonb('system', _user_id
			, format('User identity for user: (upn: %s) created by user: %s'
														, __user_info.username, _target_user_id)
			, 'user'
			, _target_user_id
			, _data_object_code := __user_info.username
			, _payload := jsonb_build_object('provider_code', _provider_code, 'provider_uid', _provider_uid, 'provider_oid',
																			 _provider_oid, 'is_active', _is_active)
			, _event_id := 50134
			, _tenant_id := 1);
end;
$$;

create or replace function unsecure.update_user_identity_oid(_updated_by text, _user_id bigint, _target_user_id bigint,
																														 _provider_oid text)
	returns void
	language plpgsql
as
$$
declare
	__current_oid text;
	__user_info   auth.user_info;
begin

	select provider_oid
	from auth.user_identity uid
	where uid.user_id = _target_user_id
	into __current_oid;

	select *
	from auth.user_info
	where user_id = _target_user_id
	into __user_info;

	if __current_oid <> _provider_oid then

		update auth.user_identity
		set provider_oid = _provider_oid
		where user_id = _target_user_id;

		perform
			add_journal_msg_jsonb(_updated_by, _user_id
				, format('User identity for user: (upn: %s) updated by user: %s'
															, __user_info.username, _updated_by)
				, 'user', _target_user_id
				, jsonb_build_object('provider_oid', _provider_oid)
				, _event_id := 50137
				, _tenant_id := 1);
	end if;
end;
$$;


drop 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,
																						 _user_data jsonb);
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
	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_oid(_created_by, _user_id, __target_user_id, _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;
$$;


/***
 *    ######## #### ##     ##          ##  #######  ##     ## ########  ##    ##    ###    ##
 *    ##        ##   ##   ##           ## ##     ## ##     ## ##     ## ###   ##   ## ##   ##
 *    ##        ##    ## ##            ## ##     ## ##     ## ##     ## ####  ##  ##   ##  ##
 *    ######    ##     ###             ## ##     ## ##     ## ########  ## ## ## ##     ## ##
 *    ##        ##    ## ##      ##    ## ##     ## ##     ## ##   ##   ##  #### ######### ##
 *    ##        ##   ##   ##     ##    ## ##     ## ##     ## ##    ##  ##   ### ##     ## ##
 *    ##       #### ##     ##     ######   #######   #######  ##     ## ##    ## ##     ## ########
 */

alter table public.journal
	add column nrm_search_data text generated always as (helpers.normalize_text(message)) stored not null;

create index if not exists ix_journal_message
	on public.journal using gin (nrm_search_data ext.gin_trgm_ops);


create or replace function public.search_journal_msgs(_user_id bigint, _search_text text,
																											_from timestamp with time zone default null::timestamp with time zone,
																											_to timestamp with time zone default null::timestamp with time zone,
																											_target_user_id integer default 1,
																											_event_id integer default null::integer,
																											_data_group text default null::text,
																											_data_object_id bigint default null::bigint,
																											_data_object_code text default null::text,
																											_payload_criteria jsonb default null::jsonb,
																											_page integer default 1, _page_size integer default 10,
																											_tenant_id integer default 1)
	returns TABLE
					(
						__created          timestamp with time zone,
						__created_by       text,
						__journal_id       bigint,
						__event_id         integer,
						__data_group       text,
						__data_object_id   bigint,
						__data_object_code text,
						__user_id          bigint,
						__msg              text,
						__total_items      bigint
					)
	stable
	language plpgsql
as
$$
declare
	__can_read_global_journal bool;
begin

	__can_read_global_journal = auth.has_permission(_user_id, 'journal.read_global_journal');

	if (_tenant_id = 1) then
		if not __can_read_global_journal then
			perform auth.throw_no_permission(_user_id, 'journal.read_global_journal');
		end if;
	else
		perform
			auth.has_permission(_user_id, 'journal.read_journal', _tenant_id);
	end if;

	_search_text := helpers.normalize_text(_search_text);

	return query
		with filtered_rows
					 as (select journal_id
										, count(1) over () as total_items
							 from journal
							 where (helpers.is_empty_string(_search_text) or journal.nrm_search_data like '%' || _search_text || '%')
								 and ((_tenant_id = 1 and __can_read_global_journal) or tenant_id = _tenant_id)
								 and (_target_user_id is null or user_id = _target_user_id)
								 and (_event_id is null or event_id = _event_id)
								 and (_data_group is null or data_group = _data_group)
								 and (_data_object_id is null or data_object_id = _data_object_id)
								 and (_data_object_code is null or data_object_code = _data_object_code)
								 and (_payload_criteria is null or data_payload @> _payload_criteria)
								 and created between coalesce(_from, now() - interval '100 years') and coalesce(_to, now() + interval '100 years')
							 order by created desc
							 offset ((_page - 1) * _page_size) limit _page_size)
		select created
				 , created_by
				 , fr.journal_id
				 , event_id
				 , data_group
				 , data_object_id
				 , data_object_code
				 , user_id
				 , message
				 , fr.total_items
		from filtered_rows fr
					 inner join journal j on fr.journal_id = j.journal_id
		order by created desc;
end;
$$;


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

create
	or replace function auth.update_permission_data_v1_9()
	returns setof int
	language plpgsql
as
$$
declare
	__update_username text := 'auth_update_v1_9';
begin

	perform unsecure.create_permission(__update_username, 1, 'Update last selected tenant', 'users');
	perform unsecure.create_permission(__update_username, 1, 'Get available tenants', 'users');
	-- 	perform unsecure.create_permission_as_system('Read user group memberships', 'users');

end;
$$;


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

select *
from auth.update_permission_data_v1_9();

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