camper/deploy/check_cookie.sql

-- Deploy camper:check_cookie to pg
-- requires: roles
-- requires: schema_public
-- requires: schema_auth
-- requires: user
-- requires: company_host
-- requires: company_user

begin;

set search_path to public, auth;

create or replace function check_cookie(input_cookie text, host text) returns name as
$$
declare
	cid text;
	user_email text;
	user_role name;
	user_cookie text;
begin
	select company_id::text, email::text, role, cookie
	into cid, user_email, user_role, user_cookie
	from "user"
	join company_user using (user_id)
	join public.company_host using (company_id)
	where email = split_part(input_cookie, '/', 2)
	  and cookie_expires_at > current_timestamp
	  and length(password) > 0
	  and cookie = split_part(input_cookie, '/', 1)
	  and company_host.host = check_cookie.host
	;
	if user_role is null then
		cid := '0';
		user_email := '';
		user_cookie := '';
		user_role := 'guest'::name;
	end if;
	perform set_config('request.user.email', user_email, false);
	perform set_config('request.user.cookie', user_cookie, false);
	perform set_config('request.company.id', cid, false);
	return user_role;
end;
$$
language plpgsql
security definer
stable
set search_path = auth, camper, pg_temp;

comment on function check_cookie(text, text) is
'Checks whether a given cookie is for a valid users, returning their role, and setting current_user_email and current_user_cookie';

revoke execute on function check_cookie(text, text) from public;
grant execute on function check_cookie(text, text) to authenticator;

commit;