PostGraphQL Dev Notes - Part 1

Warning: some of this stuff will look pretty n00b. I’m not the strongest PostgreSQL user, which is the whole point of diving in deep with PostGraphQL. So far, this “database as app server” approach is working for this simple CRUD app I’m building, and I’ll be writing up more notes like this as I go, both to help solidify it in my mind and to help out other n00bs like myself with this different approach.

Dependent Table Permissions

I have a thing that contain other things (a has_many/belongs_to relationship in Rails) and I want to enforce, in PostgreSQL, ownership of both using the JWT approach in the PostGraphQL when working with the child thing. Within a node-pg-migrate migration file, I did this and it enforced the parent/child relationship of everything. This was probably overkill on the DELETE policy, but eh, it’s more code reuse and less to write for this very basic app:

  var rowLevelCheck = `
    (select true from myschema.parent_table as a
    where user_id = current_setting('jwt.claims.user_id')::integer
    and parent_id = a.id
    and user_id = a.user_id)
  `;

  pgm.sql(`
    create policy insert_child_table on myschema.child_table
    for insert to myapp_user with check (${rowLevelCheck});
  `);

  pgm.sql(`
    create policy update_child_table on myschema.child_table
    for update to myapp_user using (${rowLevelCheck});
  `);

  pgm.sql(`
    create policy delete_child_table on myschema.child_table
    for delete to myapp_user using (${rowLevelCheck});
  `);