numerus/deploy/user_profile.sql

-- Deploy numerus:user_profile to pg
-- requires: schema_numerus
-- requires: user
-- requires: current_user_cookie
-- requires: current_user_email

begin;

set search_path to numerus, public;

create or replace view user_profile
with (security_barrier)
as
select user_id
     , email
     , name
     , role
     , lang_tag
     , left(cookie, 10) as csrf_token
from auth."user"
where email = current_user_email()
  and cookie = current_user_cookie()
  and cookie_expires_at > current_timestamp
  and length(cookie) > 30
union all
select 0
     , null::email
     , ''
     , 'guest'::name
     , 'und'
     , ''
where not exists (
	select 1
	from auth."user"
	where email = current_user_email()
	  and cookie = current_user_cookie()
	  and cookie_expires_at > current_timestamp
	  and length(cookie) > 30
);

grant select on table user_profile to guest;
grant select, update(email, name, lang_tag) on table user_profile to invoicer;
grant select, update(email, name, lang_tag) on table user_profile to admin;

create or replace function update_user_profile() returns trigger as
$$
begin
	update auth."user"
	set email = new.email
	  , name = new.name
	  , lang_tag = new.lang_tag
	where email = current_user_email()
	  and cookie = current_user_cookie()
	  and cookie_expires_at > current_timestamp
	  and length(cookie) > 30
	;

	perform set_config('request.user.email', new.email, false);

	return new;
end;
$$
language plpgsql
security definer
set search_path to auth, numerus, pg_temp;

create trigger update_user_profile
instead of update on user_profile
for each row execute procedure update_user_profile();

commit;