Published 2017-11-21
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.
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:
js
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});
`);