This specification was authored by Caleb Meredith for use in the PostGraphQL project. The language of the specification is meant to be generally applicable and adoptable by any who might want to use it.

NOTE: what follows is a specification of how you can use JWTs to authenticate a user with PostgreSQL such that PostgreSQL may perform your authorization checks. This is not the only way of achieving this goal with PostGraphile, for example you can do as the PostGraphile maintainer does and use standard session-based authentication (i.e. with cookies) via the relevant Express/Koa/Fastify middleware and PostGraphile's pgSettings function allowing you to leverage powerful pre-built authentication stacks such as passport.js which has OAuth integration with most major "social login" providers.

PostgreSQL JSON Web Token Serialization Specification

This specification aims to define a standard way to serialize JSON Web Tokens (JWT, RFC 7519) to a PostgreSQL database for developers who want to move authorization logic into their PostgreSQL schema.

Terminology from the JSON Web Token specification will be used.

After a JSON Web Token has been verified and decoded, the resulting claims will be serialized to the PostgreSQL database in two ways:

1. Using the role claim, the corresponding role will be set in the database using SET ROLE:

   set local role $role;

   Where $role is the claim value for the role claim. It is not an error if the role claim is not set.

2. All remaining claims will be set using the SET command under the jwt.claims namespace. Using:

   set local jwt.claims.$claim_name to $claim_value;

   Will be run for every claim including registered claims like iss, sub, and the claim specified 1 (role). $claim_name is the name of the claim and $claim_value is the associated value.

Example

A JSON Web Token with the following claims:

{
  "sub": "postgraphql",
  "role": "user",
  "user_id": 2
}

Would result in the following SQL being run:

set local role user;
set local jwt.claims.sub to 'postgraphql';
set local jwt.claims.role to 'user';
set local jwt.claims.user_id to 2;

A Note on local

Using local for SET and SET ROLE is not required, however it is recommended. This is so that every transaction block (beginning with BEGIN and ending with COMMIT or ROLLBACK) will have its own local parameters. See the following demonstration:

begin;
set local jwt.claims.user_id to 2;

-- Has access to `jwt.claims.user_id`
commit;

-- Does not have access to `jwt.claims.user_id`

Retrieving Claims in PostgreSQL

In order to retrieve a claim set by the serialization of a JSON Web Token as defined in this spec, either the current_setting function or the SHOW command may be used like so:

select current_setting('jwt.claims.user_id');
-- Or…
show jwt.claims.user_id;