Authentication endpoint to generate JWT tokens

[pvdvreede]

Great project!

I am having trouble working out where I generate the JWT token. Your docs provide a really good description of how you can see the details of a passed in token, so I assume that you are meant to create a JWT token in another service and just have the same secret as you have in postgraphql. Is this correct?

If so, is it possible to add an endpoint to postgraphql to allow users to authenticate with a postgresql login role and password and have postgraphql generate and return a JWT token for use with graphql queries?

Thanks.

[calebmer]

We are still working out the best way to create tokens in PostGraphQL, if you have any ideas, we would love to here them! 😊

For now it's recommended to create a single HTTP endpoint where you can (temporarily) manually create tokens.

We'll probably have a solution soonish with the implemention of procedure execution, so stay tuned! (see my notes on #31)

[rahult]

This is an awesome project. Discovered it today and we already have a working example for implementation.

Is it possible to implement something like PostgREST

JWT tokens - http://postgrest.com/admin/security/
Users - http://postgrest.com/examples/users/

[calebmer]

@rahult Authentication currently works very similar to PostgREST (actually hoping they will rename where they mount claims to be compliant with this spec so we can be compatible in that regard).

I will also probably do authentication that way, it really seems like the best idea. That is blocked by the implementation of procedures though so stay tuned!

I'm going to leave this open until we have an authentication implementation.

[pvdvreede]

Thanks @calebmer! For what its worth, I do like that I can create my own authentication service to create JWT tokens for any special needs or authentication systems I might have, so it would be good to continue to be able to do that even once authentication is built in. It would just be nice to have the option of a built in authentication as well. So hopefully you can architect it to keep both options available.

Good luck getting the stored proc stuff sorted!

[calebmer]

Thanks! Yeah, opt in authentication is super important for us 😊

[meglio]

Now that procedure support has been released, chances are there will be an authentication point done any time soon as well?

[calebmer]

@meglio I’m going to look into adding this over the weekend.

[meglio]

This is awesome! Are you going to also handle replay attacks in any way?

[calebmer]

@meglio no, not in what I imagine for authentication. However, that doesn’t mean you can’t implement it on the PostgreSQL side 😉

What I imagine is we allow users to define tables, and/or custom types as tokens and then they could specify that type by the command line. So something like --token my_schema.my_token. Where that could be:

create type my_schema.my_token as (…);
-- or
create table my_schema.my_token (…);

If a table is specified we could bypass JWTs altogether and just return the primary key for the table. PostGraphQL could then also potentially define a function, postgraphql_current_token(), which would return the type you’ve specified. This would be nice as we could then have type safety for tokens. If the user didn’t specify a type by --token my_schema.my_token we would revert to the default behavior of course.

However, it looks like this feature requires more deep and fundamental changes in order to get right. It has also come to my attention that the library needs a good cleanup anyway. So it’s going to take a little longer to develop this feature than the weekend I initially anticipated. I think my time frame is around 2–3 weeks, we’ll see.

In the meantime, PostGraphQL already allows for authorization with whatever technique you devise, you’ll just need to put the authentication logic in you’re own microservice. Authentication will be coming to PostGraphQL though. Hopefully sooner rather than later 😉

[meglio]

May you please elaborate on this:

If a table is specified we could bypass JWTs altogether and just return the primary key for the table.

What would be the structure of such a table? Probably table user_session( userid bigint, session_token text) - is that what you mean?

Also, not sure what you mean with "bypass JWTs altogether".

[calebmer]

@meglio take the following example:

You have a user session table like so:

create table user_session (
  id serial primary key,
  user_id int references user(id)
);

…and on the command line interface you point to the table like so:

postgraphql … --token user_session

…and you had an authentication function like this:

create function authenticate() returns user_session …

Instead of creating a JWT, we could just return the primary key of the user_session table, which in this case is an integer. We’d sign and verify that similar to how JWTs are signed and verified (so someone couldn’t just put in a number and have it work). Then we could expose the data in the row identified by the primary key through environment variables like jwt.claims.…, or even better through a typed function that returns a typed user_session tuple.

Does that make sense?

[meglio]

Yep, thanks for making your explanation more material :)

So, I'd have to create an authenticate() function and expose it to anonymous users (e.g. role pool_user which connects to the database before SET LOCAL ROLE is run). It would write whatever data I need to the user_session table.

Next, client side I get the result and store it somewhere (local storage, cookies, ..).

In the network layer on my client I read the token and, if present, add an Authentication header.

Have I understood it correctly?

Also, where in this chain does SET LOCAL ROLE $R reside? - at which moment and from where will Postgresql fetch the $R and call the SET ROLE sql command?

[meglio]

After reading this article, I wonder why should JWT be used for sessions at all, and what other solutions are avialable to set roles for authorized users in GraphQL based on sessions.

[calebmer]

Yep you got it 😉

One small nit-pick though, the header would be Authorization and not Authentication. Authentication is the process of logging in and authorization is the process of confirming you’ve already logged in.

We would get the role from the table similar to how we get the role from the JWT. Imagine a table with a role column like so:

create table user_session (
  id serial primary key,
  user_id int references user(id),
  role text
);

We might even just detect references to the pg_user table 😉

---

As for JWTs, they are an interesting beast. Used incorrectly they can be a very dangerous session management system, but used correctly they have some pretty great advantages. Number one being we don’t need to query a database to validate our token. In many large systems setting up a Redis instance to store sessions may be efficient, but for most usecases it’s much easier to have a stateless JWT with a quick expire time.

I usually put an expiration of 10–30 minutes on my JWTs and also issue a refresh token with them. Then we get the best of both worlds. A JWT to make fast requests, and a refresh token that we can invalidate in the database. I recognize the article author’s concerns (concerns I’ve had when building JWT systems in the past), but I don’t agree with the conclusions.

I like JWTs for session management (when used wisely!), but this proposal would allow for either a JWT or a more managed solution.

[meglio]

Provided postgraphql is hosted on the same domain where the main application runs, what would be the best way for one to implement a custom authentication system (e.g. a classical cookies-based one), that would still SET ROLES based on session?

I can see /usr/bin/postgraphql is a compiled .js file.

Does it mean I'll have to create my own nodejs project, import postgraphql module, create my own server and handle session analysis there and call SET ROLE before issuing any SQL request? Is there a simpler way without developing/compiling/deploying my own nodejs project?

What I would like to try is basically a classical cookies-based authentication system, yet it should call SET ROLE for me - otherwise it's not clear how to use Postgraphql in a web project as is without making my own endpoint if not going with JWT.

What I have in mind is something like this:

1. Start postgraphql with --authentication=cookie
2. User requests authenticate() function, which I create and make available through PostgraphQL for anonymous users.
3. The authenticate() function returns a token to store in a cookie, e.g. abc
4. Postgraphql includes a Set-Cookie: some-name=abc header to the response, as well as 200 OK or whatever is valid for a successful GraphQL request
5. On any request that contains a cookie named some-name, Postgraphql executes authorize(abc), which will SET ROLE (or whatever is needed), and return some result for Set-Cookie to be set for the response. It might be the same string, a new string (rotation), or an empty string (to invalidate the cookie).
6. the some-name might be optionally customized, e.g. --cookie-name=some-name

Does it make any sense?

P.S. Returning just a cookie name from authenticate() is limited, as I won't be able to: Set-Cookie: sessionToken=abc123; Expires=Wed, 09 Jun 2021 10:18:14 GMT.

A more generalized way would be --authentication=header and then let's authenticate() return whatever headers I want to set. But then it's not clear from where should Postgraphql get values to pass to authorize().

[calebmer]

The flow would look more like this:

mutation Authenticate {
  authenticate(name: "admin", password: "password") {
    token
  }
}

Where then token you can do whatever you want with it. If you want token to be in a header, you can put it there. If you want token to be in local storage, you could save it there.

[andrioid]

If anyone has a working authentication example, I would love to see it added to the documentation. Very interesting thread btw.

[meglio]

Yep, that's what I mean, but just wanted to let it not be limited to JWT only.

[meglio]

If I understood correctly from the last news, you have postponed authentication till the new architecture is implemented, as described in the recent My vision for the future of PostGraphQL thread. Please confirm.

[calebmer]

@meglio correct. This is a feature that’s very high on my priority list, but I want to get it right.

I also want to remind that authentication can still be done outside of PostGraphQL today. It’s fairly simple to setup a Node.js server to manage authentication and use that token with PostGraphQL’s authorization features. If you want, it would also be fairly simple to hook into the generated mutation type allowing you to add your own mutation field.

[prevostc]

Is this included endpoint still needed ? I'm planning to use https://github.com/michelp/pgjwt to sign a jwt token using postgres and postgraphql only for ease of use (no external auth server) and it would be relevant to match the final API.

[calebmer]

@prevostc that’s pretty awesome, I did not know about that. I still want to support JWT generation in PostGraphQL for people who want it though, but that solves a lot of problems 😊

Here’s my thoughts on API, you pass into PostGraphQL --token-type my_schema.my_table or --token-type my_schema.my_composite_type. If you passed a table, the table must have a primary key. Then, any database function you write which either uses the token type (table or composite type) as an argument or return type, PostGraphQL will automatically swap that with a JWT type.

So for SQL that looks like:

create type my_token as (
  user_id uuid
);

create function authenticate (name text, password text) returns my_token …;

In PostGraphQL you’ll get a GraphQL schema that looks (roughly) like:

scalar JWT

type Mutation {
  authenticate(input: AuthenticateInput!): AuthenticatePayload!
}

type AuthenticateInput {
  name: String!
  password: String!
}

type AuthenticatePayload {
  myToken: JWT
}

Where JWT is just the token string. For compound types, the token will contain all of the fields in the compound type. For tables with a primary key, the token will only contain the information required to lookup the row for that primary key.

PostGraphQL might add an exp by default to your tokens. I haven’t decided yet. If we do it will probably be generous. Something like 5 days. Of course that’s configurable if your table or compound type has an exp attribute, that will be used instead.

Hope that helps @prevostc 😊

[prevostc]

@calebmer that look nice to me! I would like to start working on this and submit a WIP asap. Any idea on how an external oauth identity provider would fit this model ? (google/facebook/github/...)

[calebmer]

Authentication will be in PostGraphQL 2 which is almost done (you can track the development in the next branch). For now though https://github.com/michelp/pgjwt would work as a great “polyfill” if you will 😊

I’m not 100% sure how Google/Facebook/GitHub etc. auth would fit well into this model. You’d probably want a GraphQL mutation something to the effects of authenticateGoogle which takes a Google token and creates a token for PostGraphQL.

[calebmer]

PostGraphQL 2 beta has been released which has an authentication implementation (#145). Install the beta today and tell me what you think. I’ll be writing a documentation article on how to use the authentication feature and updating the example schema before the full release 👍

npm install -g postgraphql@next
postgraphql --secret cat-keyboard --token my_schema.my_token

(make sure you have a Postgres server listening on postgres://localhost:5432)

[meglio]

With authentication built-in, this is getting closer to a fully featured out-of-the-box no-server-code applications. Where can we read about v2 new features and changes?

[calebmer]

@meglio check out issue #145 and tell me what you think about the changes made 👍

[calebmer]

I’m going to close this as its in the PostGraphQL 2.0 beta 🎉

Start playing with the pre-release, the final release should be out really soon. Tell me what you think! 👍

npm install -g postgraphql@next
postgraphql

[calebmer]

If you’d like to read up on how authentication works in PostGraphQL 2, there is a detailed documentation tutorial going over the entire process.

The documentation article: https://github.com/calebmer/postgraphql/blob/787bbe4ee842e0d931989df88acbdb3a88e1c380/examples/forum/tutorial.md
The authentication section: https://github.com/calebmer/postgraphql/blob/787bbe4ee842e0d931989df88acbdb3a88e1c380/examples/forum/tutorial.md#authentication-and-authorization

[philostler]

@calebmer Been following your documentation and everything worked really well. Great documentation, thanks for the huge effort that must have gone into it