Postgraphile: jwt authentication and authorization

[danyalalizahid]

I'm submitting a ...

• [ ] bug report
• [ ] feature request
• [ x] question

PostGraphile version:

Minimal SQL file that can be loaded into a clean database:

Steps to reproduce:

Current behavior:
I am looking for a solution to add authentication in my graphql implementation through postgraphile. But currently how my system works is all post request are implemented through an express app and i use jwt authentication there. I am looking for a solution if i can implement authentication and authorization when making graph ql request for each and different table entity as well as mutation.
Expected behavior:
implement authorization and authentication on graphql

[benjie]

Hey; we might be able to help you better over chat: http://discord.gg/graphile

You can read about how security / JWTs work in these articles:

https://www.graphile.org/postgraphile/security/

https://www.graphile.org/postgraphile/postgresql-schema-design/#authentication-and-authorization

Custom authentication can be done via pgSettings:

https://www.graphile.org/postgraphile/usage-library/#pgsettings-function

[coolaj86]

Problem: Authorization (with a z)

I want to scope all queries such that they only return data that belongs to the current user.

Scenario

Imagine that a user belongs to a team which has access to files.

I want to be able to query "files" and not get back a list of _all_ files, but scope the files to the current user. That means that when I query files I'm implicitly querying teams that a user belongs to as well as the files that belong to those teams.

Expectation

I don't know what to expect.

Maybe I'm just thinking about this backwards because I'm an old-hat REST guy. Circumstances as they are, however, I'm supposed to deliver magic because too many hipsters are up on stages selling front-end devs on some sort of fairytale that graphql solves all known problems in the universe (but whenever anyone asks about authorization for any of the frameworks everyone just gets all hand-wavy about it).

Hopes

Rant aside, I'd assume there's some way to allow querying of a table that's scoped to the user. It may even be obvious, but I keep landing on the same 2 or 3 pages talking about roles or how to parse the jwt (not a problem), but I imagine that what I'm looking for would be more like this (very crude psuedo-code):

postgraphile.preManipulate(func (query) {
  if query.files {
    query = { teams: { files: query.files }, userId: jwt.claim.sub }; 

    return { query: query, postManipulate: func (results) {
      return pluck('teams.files', results); 
    }};
  }
});

[coolaj86]

While reading through https://www.graphile.org/postgraphile/postgresql-schema-design/#authentication-and-authorization I had a glimmer of hope, but then I came across https://stackoverflow.com/questions/10179121/sql-sub-queries-in-check-constraint

TL;DR: It looks like the example only applies to authz in direct parent-child relationships and not many-to-many relationships and the SO post suggests that checks are not intended to be able to check such relationships.

It really sounds like the solution would be to verify the token in middleware and then somehow manipulate the incoming query to be structured in the right way before it gets to the database.

Thoughts?

P.S. I'm also watching your video right now. I'm not as big on SQL as you, but I am a believer in the SQL benefits you're talking about so far (which is somewhat implicit in using postgres and landing on postgraphile).

[coolaj86]

Okay, finished watching the video and it looks like the slide you have right here https://youtu.be/XDOrhTXd4pE?t=359 is perhaps what someone like myself would want to create, but modified so that it can somehow be scoped to the local variable representing the user.

Something like

CREATE VIEW my_files AS (
  SELECT *
  FROM files
  WHERE files.id IN (
    SELECT DISTINCT f.id
    FROM files AS f
    JOIN files_teams AS ft ON f.id = ft.file_id
    JOIN teams AS t ON t.id = ft.team_id
    JOIN teams_users AS tu ON tu.id = (SELECT current_setting('jwt.claims.sub', true))
  )
);

I'm assuming that postgres' query planner would eliminate the redundant sub-queries that would occur as a result of the resolver constructing queries across multiple views...

but then there's the problem that UPDATE operations can't occur on such a view.

I dunno... seems kinda convoluted and I'm back to thinking maybe some way of modifying the incoming graphql query is the right way to go.

Thoughts?

[chadfurman]

@coolaj86 hey, hop into the discord I'm there right now.

Generally, I solve this with RLS / RBAC policies w/ current_setting jwt profile_id matched against respective columns in the tables.

I don't use views.

I'm curious to know if we can devise a better solution then what I'm currently using. Perhaps if we discuss it in some detail in real-time it might help?

Edit: @ lotus https://discordapp.com/channels/489127045289476126/489127045826215962

[benjie]

Hi @coolaj86, I think where you’re going wrong is that you’re expecting to do authorization in JS-land. While this is possible, PostGraphile encourages you to do it in the database using a technology known as row level security (RLS). This tech allows you to implicitly add “where” clauses to any request to a particular table. This means we don’t handle authorization at the application level, we do authentication and then pass this authentication info to Postgres for it to use during authorization. It’s a very clean, reliable, and above all secure approach; but it’s only been possible since 2015!

Here’s a cheatsheet we recently made that explains a little more about RLS: https://learn.graphile.org/docs/PostgreSQL_Row_Level_Security_Infosheet.pdf

You may find this article of interest: https://medium.com/@cazzer/designing-the-most-performant-row-level-security-strategy-in-postgres-a06084f31945

Inside a RLS policy you may include any valid SQL fragment, so simple expressions are allowed, but also subqueries, function calls, etc; so you should be able to achieve everything you need to.

[coolaj86]

@benjie Thanks. I'm going through it now. I'm glad that you've stayed up-to-date on so many of the new features of postgres.

30-year-old technologies that just won't stop improving and stay put... I guess it's a good thing.

@chadfurman That link didn't work for me. I'm solderjs#9759.

[benjie]

PostgreSQL’s constant advancement is truly amazing. How are you getting on @coolaj86?

[coolaj86]

Not so well.

I go into postgres:

psql "postgres://postgres:postgres@localhost:5432/postgres"
SELECT version();
> PostgreSQL 10.6

I create a table and a policy that should prevent all access to that table:

-- load the old uuid extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- create a table for test
CREATE TABLE teams (
  id TEXT PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT
);

-- enable RLS
ALTER TABLE teams ENABLE ROW LEVEL SECURITY;

-- create a policy that denies everyone everything
CREATE POLICY show_teams ON teams USING (false);

-- force the policy, even on the table owner
ALTER TABLE teams FORCE ROW LEVEL SECURITY;

But then I'm able to insert data into the table, and select the data from it:

INSERT INTO teams (name) VALUES ("bad guy"), ("worse guy");
> INSERT 0 2

SELECT COUNT(*) FROM teams;
> 2

I had a much more complex policy the first go around, but when that didn't work and I started working backwards I ended up finding that even defaulting to disallow didn't work.

I'm not finding much documentation on postgres policies.

[srp]

It looks like FORCE ROWLEVEL SECURITY only works for the table owner, but not for a superuser. https://stackoverflow.com/a/44437041/348612

By switching roles to a non-superuser I'm able to get your thing working:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE ROLE owner;
GRANT CREATE, USAGE ON SCHEMA "public" TO "owner";

SET ROLE owner;

CREATE TABLE teams (
  id TEXT PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT
);
ALTER TABLE teams ENABLE ROW LEVEL SECURITY;
ALTER TABLE teams FORCE ROW LEVEL SECURITY;
CREATE POLICY show_teams ON teams USING (false);

INSERT INTO teams (name) VALUES ('bad guy'), ('worse guy');
SELECT count(*) FROM teams;

RESET ROLE;
INSERT INTO teams (name) VALUES ('bad guy'), ('worse guy');
SELECT count(*) FROM teams;
SET ROLE owner;
SELECT count(*) FROM teams;

[benjie]

That’s correct, you need to run PostGraphile as an unprivileged role, and grant to that.

If RLS applied to superuser then you couldn’t back up your database as not all rows would come through.

[benjie]

(I do not recommend using FORCE ROW LEVEL SECURITY. You can read about roles here: https://www.graphile.org/postgraphile/postgresql-schema-design/#postgres-roles)

[benjie]

[semi-automated message] Thanks for your question; hopefully we're well on the way to helping you solve your issue. This doesn't currently seem to be a bug in the library so I'm going to close the issue, but please feel free to keep requesting help below and if it does turn out to be a bug we can definitely re-open it 👍

[Prasanthmv]

@benjie can we integrate the jwt token from GitLab for authenticate postgraphile?
I would like to achieve the following

1. Generate JWT using GitLab (https://docs.gitlab.com/ee/administration/auth/jwt.html)
2. Using that call each graphql queries.

Can we do that ? Can you please help to find configuration examples for that.

[benjie]

This was discussed on Discord:

Yes, you can do that. PostGraphile's JWT support is "flat", if GitLab's JWTs are non-flat (not a simple key-value string-string map) then you'll need to use pgSettings and the jsonwebtoken module (and disable PostGraphile's built in JWT support)

[...]

async pgSettings(req) {
  const token = getTokenFrom(req.headers.authorization);
  const claims = await jwt.verify(token, mySecret, {...});
  return {
    'jwt.claims.user_id': claims.something.whatever,
  };
}