Please detail user authentication/authorization

[mariusa]

Hello,

You mention It doesn’t make sense to redefine your authentication in the API layer, instead just put your authorization logic in the database schema! With an advanced grants system and row level security, authorization in PostgreSQL is more than enough for your needs.

For an app with users where each has access to their own data, eg Wordpress, do you really mean we should create a database user for each new signup? (we won't) Please list another alternative in the docs.

Thanks

[benjie]

Thanks for bringing this confusion to our attention. You don't need to create a role for each user; you can use row level security combined with JWT claims to grant access. Have a look here for some details:

https://github.com/postgraphql/postgraphql/blob/master/docs/pg-jwt-spec.md

[deinspanjer]

I'm using PostgREST rather than postgraphql at the moment, but I thought I'd chime in here.

You can have a small set of postgres roles for different coarse permission levels (i.e. admin, power_user, read_only, etc.), but still have a users table and use the user ID to perform row level security checks to allow a user to only edit their specific data.

One of the common policy definitions I have allows any user with the admin role to edit certain records as well as allowing the owner of those records to also edit them. This just requires storing the user ID into the JWT token as well as the role. I recommend using the "sub" claim for that purpose.

[benjie]

@deinspanjer 👌

One question: is it a best practice to put user id in sub? I've seen it suggested to use for email/username in most places I've read so inferred it was meant to be a human readable identifier, but I'm no expert on JWT - would love to expand my knowledge here. Having both email and id seems redundant (and id has greater longevity) so it makes sense to me to use ID for sub.

[deinspanjer]

I chose to use the username as the ID and hence the sub claim for readability and ease of use at the expense of the slight uncleaness of having a string as a primary key.

The JWT RFC doesn't say much more than: sub should be unique, either globally, or with in the scope of the application context.

https://tools.ietf.org/html/rfc7519#section-4.1

[devuxer]

I'd like a bit of clarification on a related topic as well:

I am trying to set up JWT authentication in an express app, and I've read through the documentation and examples, but one thing I haven't seen mentioned is where the authenticate function actually gets called from.

At first, I thought authentication was something postgraphile would handle, but I'm beginning to think I need to create an "/authenticate" endpoint in express and call the function via pg and a little raw SQL, then return the result to the client (which would then need to store the token in memory, a cookie, or a query string).

1. Is that the intent?
2. If so, is there any way to call the authenticate function via GraphQL? It doesn't seem to naturally show up in the schema.

[benjie]

See: https://www.graphile.org/postgraphile/postgresql-schema-design/#logging-in

authenticate is a custom mutation; so you just call it via GraphQL from your client.

If it's not showing up in the schema, make sure you have marked it as "volatile" and that it's in the schema that you're exposing via --schema in PostGraphile

[devuxer]

Ahh, brilliant 👍

The only thing I'm confused about is why this function would be marked volatile (rather than stable) because it doesn't (seem to) have side effects.

[benjie]

Volatile makes it a mutation. It has a side effect in that it “creates” a JWT token, in future you may want to track it (store to db) without making a breaking change to your API.

[benjie]

The examples repo (very young, immature) contains a full example with React frontend and Koa server that implements GitHub social login and demonstrates using RLS/etc. I've got to find some time to finish building it at some point, but if you're desperate for an example to look over it might suffice for now:

https://github.com/graphile/examples

[benjie]

[semi-automated message] We try and keep the open issues to actual issues (bugs, etc); this seems like more of a discussion right now, so I'm closing it but please feel free to keep discussing it below 👍

[jedwards1211]

I'm working on an app (with a handwritten API) that allows admin users to grant individual app users or user groups permission to access specific devices. I'm just curious, how would I structure such a permissions system in Postgraphile/Postgres RBAC? Is there any way to set up such fine-grained permissions without making every one of my app users a database user?

I guess @deinspanjer is saying I can define row-level security policies using my own custom users table, instead of on database users?

The biggest question I'd have about making the jump to something like Postgraphile is how to perform arbitrary business logic, ideally in JS but in Postgres functions if need be. I wouldn't feel comfortable telling a client that I'll need to make a database user for every one of their app users.

[benjie]

You only need two database roles total: one to own the database, and an unprivileged one to perform user access. You do not need a database user per application user, that would be pretty unpleasant once you start getting into the hundreds of users!

Here’s an example of creating security policies for a simple forum:

https://github.com/graphile/examples/blob/master/db/700_forum.sql#L115

Here’s an example of how the roles for that DB are created (I create three roles but that’s not strictly necessary):

https://github.com/graphile/examples/blob/master/setup.sh#L97

I hope this helps, feel free to ask more questions or join us on the discord chat: http://discord.gg/graphile

[benjie]

You may find this video informative: https://youtu.be/XDOrhTXd4pE

[mariusa]

Hi,

Most examples are based on a single user/owner model (eg forum posts owned by one user).

How would it work for a system with multiple organizations ("org"), multiple staff ("staff"), and staff being assigned to org many-to-many? Example: "benjie" staff user is part of GitHub org (permissions: "item read-only") and BSystems org (permissions: admin, can read/write anything owned by this org).

Next, each org has data per org_id. Table "item" has items which belong to an org (item.org_id). Any staff part of the org (with appropriate permission) has the right to read or also write item data. So, rather than having RLS setup per post.user_id, it should check that current staff user is part of the org which owns item.org_id

The only discussion I've found with this setup is at https://www.reddit.com/r/graphql/comments/a84s22/graphile_vs_hasura/

(user select) => memberships->teams->memberships->user_id = X-Hasura-User-Id

and here https://dev.to/lineup-ninja/modelling-teams-and-user-security-with-hasura-204i but I don't know where the 'security rule' goes in graphile.

Thanks

[benjie]

This is a pretty standard setup; I've written out your example in SQL and demo'd RLS for only the items table (the rest is left as an exercise for the reader).

-- Tables roughly as described
create table users (
  id serial primary key,
  name text not null
);

create table organizations (
  id serial primary key,
  name text not null
);

create table staff (
  id serial primary key,
  organization_id int not null references organizations,
  user_id int not null references users,
  permissions text[] not null default ARRAY[]::text[],
  unique (organization_id, user_id)
);
create index on staff (user_id);

-- Who am I?
create function current_user_id() returns int as $$
  select nullif(current_settings('jwt.claims.user_id', true), '')::int;
$$ language sql stable;

-- What orgs do I have access to?
create function current_user_organizations_with_permission(permission_one_of text[])
returns setof int as $$
  select organization_id
  from staff
  where user_id = current_user_id()
  and permissions && permission_on_of -- && means "overlaps"
$$ language sql stable; -- you could also mark this as SECURITY DEFINER if you need to bypass RLS on the `staff` table.

-- Inserting the data
insert into users (id, name) values
  (1, 'Benjie');
insert into organizations (id, name) values
  (1, 'GitHub'),
  (2, 'BSystems');
insert into staff (organization_id, user_id, permissions) values
  (1, 1, ARRAY['item read-only']),
  (2, 1, ARRAY['admin']);

-- Now for the meat of the question
create table items (
  id serial primary key,
  organization_id int not null references organizations,
  data text
);
alter table items enable row level security;
create index on items (organization_id);

create policy select_org_member_with_permissions for select on items using (
  organization_id in (
    select current_user_organizations_with_permission(ARRAY['item read-only', 'admin'])
  )
);
grant select on items to graphql;

[mariusa]

Thanks so much, Benjie! I'll start setting this up

[benjie]

If you fancied writing up the approach in a blog post or a guide on our website that'd be a great way of sharing it.