012_update_permissions_v1-6.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.6', E'Fix of validate_token', 'Tokens were invalidated after too late and expired token appeared as still valid',
													_component := 'keen_auth_permissions');

drop function if exists auth.set_token_as_used_by_token(_modified_by text, _user_id bigint, _token text, _token_type text, _ip_address text, _user_agent text,
																												_origin text);
create function auth.set_token_as_used_by_token(_modified_by text, _user_id bigint, _token text, _token_type text, _ip_address text, _user_agent text,
																								_origin text)
	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
	__token_uid text;
begin

	select uid
	from auth.token
	where token_type_code = _token_type
		and token = _token
	into __token_uid;

	return query
		select *
		from auth.set_token_as_used(_modified_by,
																_user_id,
																__token_uid,
																_token,
																_token_type,
																_ip_address,
																_user_agent,
																_origin
				 );
end;
$$;



create function 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 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
	__token_id         bigint;
	__token_uid        text;
	__token_state_code text;
	__token_user_id    bigint;
begin
	perform
		auth.has_permission(_user_id, 'tokens.validate_token');

	-- 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);

	select token_id, uid, token_state_code, user_id
	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
		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 __token_id, __token_uid, __token_state_code, __token_user_id;

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

	if
		__token_state_code <> 'valid' then
		perform error.raise_52278(__token_uid);
	end if;

	if
		_target_user_id is not null and _target_user_id <> __token_user_id then
		perform error.raise_52279(__token_uid);
	end if;

	perform
		add_journal_msg(_modified_by, _user_id
			, format('User: %s validated a token for user: %s'
											, _modified_by, _target_user_id)
			, 'token', __token_id
			, array ['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, __token_uid, _token,
																	_token_type, _ip_address, _user_agent,
																	_origin) used_token;
	else
		return query
			select token_id, uid, token_state_code, used_at, user_id, user_oid, token_data
			from auth.token
			where token_id = __token_id;
	end if;
end;
$$;

create or replace function auth.set_token_as_failed(_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
	__token_id  bigint;
	__token_uid text;
begin

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

	select token_id, uid
	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 __token_id, __token_uid;


	-- 	if helpers.is_empty_string(__token_uid) then
-- 		perform error.raise_52278(__token_uid);
-- 	end if;

	return query
		update auth.token
			set modified_by = _modified_by, modified = now(), token_state_code = 'validation_failed', 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(_modified_by, _user_id
			, format('Token (uid: %s) set as validation_failed by user: %s'
											, _token_uid, _modified_by)
			, 'token', __token_id
			, array ['ip_address', _ip_address, 'user_agent', _user_agent, 'origin', _origin]
			, _event_id := 50403
			, _tenant_id := 1);

end;
$$;

create or replace function auth.set_token_as_failed_by_token(_modified_by text,
																														 _user_id bigint,
																														 _token text,
																														 _token_type 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
	__token_uid text;
begin

	select uid
	from auth.token
	where token_type_code = _token_type
		and token = _token
	into __token_uid;

	return query
		select *
		from auth.set_token_as_failed(_modified_by,
																	_user_id,
																	__token_uid,
																	_token,
																	_token_type,
																	_ip_address,
																	_user_agent,
																	_origin
				 );
end;
$$;


create or replace function auth.ensure_user_info(_created_by text, _user_id bigint, _username text, _display_name text, _provider_code text default null::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;
	__username text;
begin

	__username := trim(lower(_username));

	select u.user_id
	from auth.user_info u
	where u.username = __username
	into __last_id;

	if
		__last_id is null then
		select user_id
		from unsecure.create_user_info(_created_by, _user_id, __username, lower(_email), _display_name,
																	 _provider_code)
		into __last_id;
	end if;

	return query
		select ui.user_id
				 , ui.code
				 , ui.uuid::text
				 , ui.username
				 , ui.email
				 , ui.display_name
		from auth.user_info ui
		where ui.user_id = __last_id;
end;
$$;

create or replace procedure internal.update_permissions_v1_6()
	language plpgsql
as
$$
	-- declare
-- 	__update_username text = 'update_permissions_v1_6';
begin
	insert into const.token_state (code)
	values ('validation_failed');
end;
$$;

call internal.update_permissions_v1_6();


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