In Postgraphile, use permissions on many-to-many relationships

Question

How would one go about defining extended permissions and authorization on a many-to-many relationship using Postgraphile?

Imagine I have this many-to-many relationship (graciously borrowed from official docs and adjusted with simplified permissions):

create table post (
  id serial primary key,
  headline text,
  body text,
  summary text
);
create table author (
  id serial primary key,
  name text
);

create type post_permission as enum (
  'owner', -- assume owner can change `headline`, `summary` and `body` of a post
  'editor' -- assume editor can modify `summary` of a post
);
create table post_author (
  post_id integer references post,
  author_id integer references author,
  permission post_permission,
  primary key (post_id, author_id)
);

I can make a Row Level Security Policy like:

create policy update_post on post for update to app_user using (
  EXISTS(
    SELECT 1
    FROM post_author as pa
    WHERE pa.post_id = post.id 
      AND pa.author_id = app_hidden.current_user_id()
      AND pa.permission = 'owner'
  )
);

-- Assume `app_hidden.current_user_id()` returns a logged in user id

But as I am a recent MySQL convert to PostgreSQL I am trying to see if I can make the policy check pa.permission above in relation to the attempted change and only allow permission = owner to update all fields of a post, whereas a user with permission = editor can just update summary.

I am aware that this is often handled in the app layer and not database, but figured I would see whats possible first.

Thanks!

See also related topic here.

Your first postgres schema looks fine. What's the problem with it? What GraphQL schema do you want to achieve that you can't? — Bergi, Feb 12 '21 at 10:28
@Bergi I am investigating Mutation policies with RLS, I've updated my question with more detail and some additional code context. — Cameron, Feb 14 '21 at 07:36
I'll have to disappoint you there: row level security policies can only restrict updates to the whole row, not to individual columns. Table permissions could do that, but only based on the postgres role not dynamically depending on data - so that would only work if you had two disjoint user groups of authors and editors. — Bergi, Feb 14 '21 at 12:22
Thanks! I've added an answer with something that is hopefully useful for the next person — Cameron, Feb 16 '21 at 20:14

score 1 · Accepted Answer · answered Feb 16 '21 at 20:13

Based on investigation and trial-and-error, this seems to be something that is best solved with a custom function for updating posts.

An owner can use this function via GraphQL/Postgraphile:

create function updatePost(
  headline text,
  body text,
  summary text
) returns post as $$
-- implement this function to check that the user found via 
-- app_hidden.current_user_id() exists in join table
-- with an `owner` permission
 -- then modify post
$$ language plpgsql strict security definer;

An editor can use this function via GraphQL/Postgraphile:

create function updatePostMeta(
  summary text
) returns post as $$
-- implement this function to check that the user found via 
-- app_hidden.current_user_id() exists in join table 
-- with an `editor` or `owner` permission
-- then modify post
$$ language plpgsql strict security definer;

Additionally, using RLS, one would want to prevent anyone from changing a post directly via GraphQL/Postgraphile, so we'd only let users SELECT from post

In Postgraphile, use permissions on many-to-many relationships

1 Answers1

Linked