Session-based authentication?

[singingwolfboy]

I'm submitting a ...

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

PostGraphile has documentation around security which positions JWT as the only option for handling authentication. There is also a handy JWT guide, which also implies that JWT is the only way to do authentication.

However, there are also several widely-circulated blog posts that claim that JWT is not suitable for session-based authentication. Here are the ones I've seen:

• Stop using JWT for sessions
• Stop using JWT for sessions, part 2: Why your solution doesn't work
• No Way, JOSE! Javascript Object Signing and Encryption is a Bad Standard That Everyone Should Avoid

Considering that session-based authentication is a common requirement for web applications, and PostGraphile is positioning itself to work with web applications, this seems like a strange omission from the documentation. So in light of all of this, I have a few questions:

• Is it possible to do session-based authentication with PostGraphile?
• If it is possible, can we add some documentation to the project that explains how to do so? (I'm happy to help write that documentation, once I get an adequate explanation of the situation.)
• If it is not possible, can we clearly document that? (Perhaps with a summary of why it is not possible.)

[benjie]

I do not use JWT with my PostGraphile projects at all, and never have, so yes we definitely support sessions 👍 Agreed that the docs on this are lacking.

Using sessions with PostGraphile is very easy; it all hinges on the pgSettings function. This is detailed here:

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

There’s a few examples here and here:

https://github.com/graphile/examples/blob/master/server-koa2/middleware/installPostGraphile.js

https://github.com/graphile/bootstrap-react-apollo/blob/master/server/middleware/installPostGraphile.js

Help putting this more obviously in the docs would be welcome! We’re currently working on a new bootstrap we plan to rewrite the docs around, many parts of the docs are out of date and better approaches have been found that we intend to document.

[TheJoeSchr]

Hi there,

I'm new to postgraphile and first of all let me say: I love the concept! It's like a fresh air, just using db/graphql/nuxt.js is enough for most of my usecases without some other framework meddling inbetween when all I need is just a way to CRUD my data.

But like you said, for a newcomer, parsing the postgraphile documentation to fit everything together is not easy. So I thought I would give you feedback on what helped me most and what I struggled with and maybe it well help the next newbie.

Quick Start Guide is really good and helpful. Also I'm using docker to setup all my services and setting up postgraphile docker was a breeze (just for testing if it works, I will use it as a library)

But after that, I kind of fell in a hole. How do I now stick every service together. In my experience building a login is kind of the "real" hello world, so I wanted to do this. But the different guides/article with authentication were to complicated or to advanced or to specific (based on other services like auth0 or github or jwt, which is not ideal for session based).

Gladly I finally found Postgresql Schema Design Guide or even better a seemingly more up-to-date variant here by searching and skimming all issues with "authentication".

I believe the name "Postgresql Schema Design" is to humble for this document. It should be called something more significant and eyecatching e.g. "Quick Start 2 / Tutorial" or "Best Practices Example". It even took me 5-10min to find it again today, because the name is so generic. And it should come next after Quick Start Guide on the right side bar.

Also 3 other questions: 1. password_hash

select ('forum_example_person', person_id)::forum_example.jwt_token
    from forum_example_private.person_account
    where 
      person_account.email = $1 
      and person_account.password_hash = crypt($2, person_account.password_hash);```

sometimes in the tutorial password_hash gets passed instead of gen_salt(). does it recover the salt from the existing password_hash automatically or was this just abbreviated for shortness sake? Anyway a line or two regarding this would be great. Most of the time, the tutorial is really on point in this regard, eg. > (we will add an updated_at column later) was referencing something I was actually thinking at this moment. So it shines out with crypt/gen_salt as this was used 2-3 times with an existing password without explaining it.

2. session handling / authentication

Using sessions with PostGraphile is very easy; it all hinges on the pgSettings function. This is detailed here:

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

There’s a few examples here and here:

https://github.com/graphile/examples/blob/master/server-koa2/middleware/installPostGraphile.js

https://github.com/graphile/bootstrap-react-apollo/blob/master/server/middleware/installPostGraphile.js

could you please be a little bit more specific which parts are dealing with authentication/sessions are important, this is quite a wall of text for somebody new.

3. setup

using the setup.sh script from example, could I just put them inside a e.g. 000_setup.sql inside db/init/ folder which gets run by initdb from postgres-docker. or would that mess up the the authorization level of the tables, since that gets run by the postgres superuser?

Just my 2 cent as somebody who stumbled in and tries to get up to speed.

Thank you for all your hard work!

ps.: sorry if this is not the right place to post this, but I thought I would rather write it down as long as the experience is fresh, because as the past has shown, once it starts to click, it's hard to get back to beginner's mind

[TheJoeSchr]

One more; I just digged into bootstrap-react-apollo. I like the setup with db-migrate a lot and it seems easier to make it work with a docker-service. Maybe I can just modify the psql command in scripts/setup to use docker-compose and then run yarn setup.

But one side question, I really like the db setup which splits into the 000-900 sql files. Why did you not use it in bootstrap-react-apollo? And is bootstrap-react-apollo more state of the art then graphile/example?

[benjie]

Hey @JoeSchr; this is excellent feedback.

does it recover the salt from the existing password_hash automatically

Yes.

Using sessions with PostGraphile is very easy

Ugh, should never use "very easy" in documentation 😳

or would that mess up the the authorization level of the tables, since that gets run by the postgres superuser?

setup.sh is designed to be ran as the database superuser: it creates databases and users.

sorry if this is not the right place to post this, but I thought I would rather write it down as long as the experience is fresh, because as the past has shown, once it starts to click, it's hard to get back to beginner's mind

💯 Really appreciate the input

I really like the db setup which splits into the 000-900 sql files. Why did you not use it in bootstrap-react-apollo?

graphile/examples is intended to get something up and running and show you how bits fit together. Intention is to have different servers (PostGraphile CLI, HTTP, Express, Koa, Fastify, ...) and clients (React, Vue, Angular, React Native, ...) and basically be a kitchen-sink of "how does X work with Y". It does not have a migration system so much as a "database reset" system.

graphile/bootstrap-react-apollo is intended to be the starting point for building an app with PostGraphile, React and Apollo. As such it does have a migration system that you can build on (as you add to your app), though I do not like it and will be replacing it with graphile-migrate in due course.

And is bootstrap-react-apollo more state of the art then graphile/example?

Yes, but that's accidental. They're both in need of some TLC.

---

I'm leaving this issue open so I can address the feedback; it may take a while - I've a lot to do.

[angel-cs]

Sadly I leave postgraphile because it seems impossible to find a minimal explanation anywhere about how to implement express-session with it, and judging by this thread not even the people in charge know how to do it.

[singingwolfboy]

For reference, the Graphile Starter project has a file that is specifically for implementing sessions using express-session. It will store session information in Redis if you have a Redis server configured, or directly in PostgreSQL if not.

[angel-cs]

@singingwolfboy thanks a lot

[benjie]

Let me know if you need any further explanations @angel-cs; I've been using sessions with PostGraphile for 3 years across a multitude of projects, so I'm very familiar with it. As @singingwolfboy states, Graphile Starter is a great demonstration of a fully working system using sessions via Passport.js and regular username/password auth.

The basics are that you set up your express sessions as usual, and then you use the pgSettings function in PostGraphile to do do something like:

app.use(postgraphile(DATABASE_URL, SCHEMA_NAME, {
  pgSettings: (req) => ({
    'my_app.user_id': req.session?.user_id,
  }),
}));

Then in Postgres you can use a function to retrieve this, such as:

create function current_user_id() returns int as $$
  select nullif(current_setting('my_app.user_id', true), '')::int;
$$ language sql stable;

[benjie]

Closing this since we have supported session-based authentication for a long time; examples include Express sessions with Passport.js and regular username/password in Graphile Starter, Koa sessions with OAuth login in graphile/examples, and Express sessions using Passport.js in graphile/bootstrap-react-apollo. It's also documented in the library usage docs.

[angel-cs]

It's as pgSetting is not returning nothing. I can access that param from postgres only if I set it with alter db and setting a static value in this way (without that I obtain an 'unrecognized param' error when I call the function), but it's not updated with any value that I set into pgSettings. I've tried with setting a simple object, instead of a function, as pgSettings output, I've tried with string values only... nothing works. nothing seems to be reaching the db from pgSettings. any idea about why? Thanks.

[benjie]

@angel-cs Please could you share your code? The whole postgraphile call including all options please.

Also see if you can register and log in with Graphile Starter, if you can then you at least have a working example.

[angel-cs]

hi bejie, this is the code, I'm accessing req from LoginPlugin without problem, but that thing of pgSettings is never available from postgres (with any value I set it with):

app.use(
  postgraphile(
    process.env.DATABASE_URL,
    "public",
    {
      watchPg: true,
      graphiql: true,
      enhanceGraphiql: true,
      // retryOnInitFail: true,
      appendPlugins: [PgSimplifyInflectorPlugin, LoginPlugin],
      showErrorStack: true,
      extendedErrors: ["hint", "detail", "errcode"],
      enhanceGraphiql: true,

      allowExplain: req => {
        return true;
      },
      pgSettings: req => {
        return { "sample_app.user_id": `${req.session?.user}` };
      },
      async additionalGraphQLContextFromRequest(req, res) {
        return { req };
      }
    }
  )
);

[benjie]

Do you mean req.session?.user?.id?

[angel-cs]

Yes, I'm configuring some session props (like user or isLoggedIn) in LoginPlugin (where I have the register, login and logout mutations) through context.req (req returned by additionalGraphQLContextFromRequest); I mean I have the session auth rudimentarily working in that way (although I guess it's not the most correct way to do it). But what I want is to learn and implement the postgres security system and the first thing I need if I'm not wrong is access to that current_setting value ("sample_app.user_id") to be able to identify the user from the db. But pgSettings doesn't seem to be returning it.

[benjie]

I meant your code example above omitted the .id part so it was probably setting the value to [object Object] which isn’t the int you’d expect. I was wondering if this was the cause of your issues.

[angel-cs]

'user' in this case is just the name I've given to a session prop whose value is the user id in the user table. I'm receiving it in req.session (as I'm also receiving the value 'true' for the prop req.session.isLoggedIn when that's the case). But the problem here is: even when I set that value of "sample_app.user_id" to any static string in pgSettings it's not being configured in the db and the function current_user_id() doesn't have access to it.

however if I set that value with sql 'alter database sample_app_db set "sample_app.user_id" to 'any-string-here'; (as you suggest in another thread to test the problem of 'unrecognized param...') the value is correctly configured and accessed from that function.

[benjie]

Please can you share what pg_dump says your current_user_id() function is?

This definitely works, so we need to track down where it’s going wrong in your code.

Have you tried using the Starter as mentioned above in order to check you don’t have something funky going on in Postgres?