I'm trying to set up a GraphQL server with PostGraphile (formerly known as PostGraphQL) and I cannot get the row permissions working, meaning that I keep getting permission denied messages, despite it should work. Here is my schema:
create function app.current_user() returns app.profile as $$
select *
from app.profile
where id = current_setting('jwt.claims.person_id')::integer
$$ language sql stable;
comment on function app.current_user() is 'Gets the person who was identified by our JWT.';
grant select (name,about,created_at,updated_at) on table app.profile to app_user;
grant update (name,about) on table app.profile to app_user;
alter table app.profile enable row level security;
create policy select_profile on app.profile for select to app_user
using (id = current_setting('jwt.claims.person_id')::integer);
create policy update_profile on app.profile for update to app_user
using (id = current_setting('jwt.claims.person_id')::integer);
as far as I can see in debug output, everything works as it should. I can authenticate, get a JWT token, it complains with a proper error message when the JWT token is invalid, so it's not any of that.
what am I doing wrong, or how can I debug more closely what is happening?
I'm using PostGraphile v4 and PostgreSQL 10.4
Found the issue myself. It turns out that you must give permissions on the id, even if you don't select it but only use it in the WHERE part.
So in the above example, modify it to:
grant select (id,name,about,created_at,updated_at) on table app.profile to app_user;
then it should work.