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.

Answer 1

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