1

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

Tom
  • 2,688
  • 3
  • 29
  • 53
  • Could you be more specific about what's not working? Is it allowing you to update other people's data? (That would imply you're connecting to the database as a superuser or database owner, perhaps, which bypasses RLS.) Is it not allowing you to view any data at all? (That would imply that the jwt.claims.person_id is incorrect; *or* that you're not setting the role to `app_user` via the JWT, or that the JWT role path setting is correct.) Perhaps you could share your signed JWT and the `postgraphile` command line you're using? – Benjie Jul 09 '18 at 12:34

1 Answers1

2

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.

Tom
  • 2,688
  • 3
  • 29
  • 53