Contents

• Setting up Docker
• Basic database configuration
• Schema design
  • Tables
  • Authorization
  • JWT for authentication
  • Functions
  • Permissions
• Set up server
  • Integrate PostGraphQL middleware
• Interactive testing
  • Database level
  • GraphiQL level
    • Create a user
    • Observe authentication working
    • Try making an unauthenticated request when authentication is necessary
    • Try making an authenticated request when authentication is necessary
    • Observe authorization working
• Conclusion

For weekend projects, I generally want to get up and running as quick as possible. One constant for almost all web applications is authentication, authorization, and me not really wanting to deal with either. It always seems like a pain! While trying t02o still stay in the realm of learning new things, I figured I’d give PostGraphQL a shot. Sticking with technologies I’ve previously used, I’ll use PostGraphQL as middleware with Express, and run PostgreSQL in Docker.

Note: This is essentially a reimplementation of the wonderful PostGraphQL tutorial here.
All code is available here.

Setting up Docker

First things first, I’ll need an actual database server. My go to for this is Docker, as it’s easy to manage many instances, easy to scrap and start fresh, and easy to validate my provisioning works as expected.  With Docker installed, it’s a simple

docker run --restart=always -p 5432:5432 --name postgres -e POSTGRES_PASSWORD=password -d postgres:alpine

Basic database configuration

Before getting into tables and functions, I’ll need a database instance on the server. Doing this in psql is my go to.

 CREATE DATABASE auth;

Then connect to it

 \c auth

I’m going to be encrypting passwords, so I’ll add the pgcrypto extension. I’m also going to be dealing with email addresses, and for sanity’s sake I’m going to treat the entire address as case insensitive. I know that’s not technically accurate, but it’s a usability nightmare otherwise. To do so, I’ll enable the citext extension.

CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS "citext";

Both PostGraphql and PostgREST use schemas (or schemata if you’re that kind of person) to scope entities. There’s some good reading about it here. The simplest setup is to have one public schema, which will turn into the API, and one private schema, which will be kept “secret”.

CREATE SCHEMA auth_public; 
CREATE SCHEMA auth_private;

Following PostgREST some more, there are 3 main roles (when using row level security) – unauthenticated/anonymous, authenticated, and the role used by the actual framework itself (I’ve called it auth_postgraphql). The roles used by the framework should be able to access everything from both other roles.

CREATE ROLE auth_postgraphql LOGIN PASSWORD 'password'; 

CREATE ROLE auth_anonymous; 
GRANT auth_anonymous TO auth_postgraphql; 

CREATE ROLE auth_authenticated; 
GRANT auth_authenticated TO auth_postgraphql;

Schema design

Tables

Now the actual schema. For this seed project, I’m going to keep it about as minimal as possible while still allowing for authorization.

Users have firstnames, last names, and unique IDs, privately they also have an email address (this is their username) and a password.

Creating these two tables in their respective schemas:

CREATE TABLE auth_public.user ( 
  id              serial primary key, 
  first_name      text not null check (char_length(first_name) < 80), 
  last_name       text check (char_length(last_name) < 80), 
  created_at      timestamp default now() 
);

CREATE TABLE auth_private.user_account ( 
  user_id         integer primary key references auth_public.user(id) on delete cascade, 
  email           citext not null unique, 
  password_hash   text not null 
);

Authorization

PostGraphQL makes authorization pretty straightforward by delegating it to the database. PostgreSQL has Row-Level Security (as of 9.5), which means a naive implementation of authorization is to restrict users by only letting them modify their own rows (where their id matches the id of the row).

Enable RLS on the user table:

ALTER TABLE auth_public.user ENABLE ROW LEVEL SECURITY;

And set policies so users can interact with their own rows. Everyone (unauthenticated included) will be able to query the table, but only authenticated users will be able to update or delete entries, and only their own.

CREATE POLICY select_user ON auth_public.user FOR SELECT
  using(true);

CREATE POLICY update_user ON auth_public.user FOR UPDATE TO auth_authenticated 
  using (id = current_setting('jwt.claims.user_id')::integer); 

CREATE POLICY delete_user ON auth_public.user FOR DELETE TO auth_authenticated 
  using (id = current_setting('jwt.claims.user_id')::integer);

JWT for authentication

Before going any further, I have enough information to be able to create the type I’ll be using for my JWT. Keeping this simple, it will have role for authentication and user_id for authorization.

CREATE TYPE auth_public.jwt as ( 
  role    text, 
  user_id integer 
);

Functions

I’ll create 3 functions:

1. register a new user
2. authenticate that user with a provided email and password
3. show who the current user is

CREATE FUNCTION auth_public.register_user( 
  first_name  text, 
  last_name   text, 
  email       text, 
  password    text 
) RETURNS auth_public.user AS $$ 
DECLARE 
  new_user auth_public.user; 
BEGIN 
  INSERT INTO auth_public.user (first_name, last_name) values 
    (first_name, last_name) 
    returning * INTO new_user; 
    
  INSERT INTO auth_private.user_account (user_id, email, password_hash) values 
    (new_user.id, email, crypt(password, gen_salt('bf'))); 
    
  return new_user; 
END; 
$$ language plpgsql strict security definer;

CREATE FUNCTION auth_public.authenticate ( 
  email text, 
  password text 
) returns auth_public.jwt as $$ 
DECLARE 
  account auth_private.user_account; 
BEGIN 
  SELECT a.* INTO account 
  FROM auth_private.user_account as a 
  WHERE a.email = $1; 

  if account.password_hash = crypt(password, account.password_hash) then 
    return ('auth_authenticated', account.user_id)::auth_public.jwt; 
  else 
    return null; 
  end if; 
END; 
$$ language plpgsql strict security definer;

CREATE FUNCTION auth_public.current_user() RETURNS auth_public.user AS $$
SELECT *
FROM auth_public.user
WHERE id = current_setting('jwt.claims.user_id')::integer
$$ language sql stable;

Permissions

Everything I need for this seed project is defined now, so time to sort out the permissions for the various roles.

GRANT USAGE ON SCHEMA auth_public TO auth_anonymous, auth_authenticated; 
GRANT SELECT ON TABLE auth_public.user TO auth_anonymous, auth_authenticated; 
GRANT UPDATE, DELETE ON TABLE auth_public.user TO auth_authenticated; 
GRANT EXECUTE ON FUNCTION auth_public.authenticate(text, text) TO auth_anonymous, auth_authenticated; 
GRANT EXECUTE ON FUNCTION auth_public.register_user(text, text, text, text) TO auth_anonymous; 
GRANT EXECUTE ON FUNCTION auth_public.current_user() TO auth_anonymous, auth_authenticated;

Set up server

Create a regular ol’ Express server with dotenv to ensure our environment specific details don’t leak out.

$ yarn init
yarn init v0.24.6
question name (auth-server):
question version (1.0.0):
question description:
question entry point (index.js):
question repository url:
question author:
question license (MIT):
success Saved package.json
Done in 2.82s.
$ yarn add express dotenv
yarn add v0.24.6
info No lockfile found.
[1/4] Resolving packages...
[2/4] Fetching packages...
[3/4] Linking dependencies...
[4/4] Building fresh packages...
success Saved lockfile.
success Saved 43 new dependencies.
... (snip all the dependencies) ...
$ touch index.js .env

Now set up a barebones Express server, e.g.:

require('dotenv').config();
const express = require('express');

app.use(function (req, res, next) {
  var err = new Error('Not Found');
  err.status = 404;
  next(err);
});

app.use(function (err, req, res, next) {
  res.send('Error! ', err.message, ' ', (req.app.get('env') === 'development' ? err : {}));
});

app.listen(process.env.PORT);

Make sure you’ve added PORT to your .env file and set it appropriately – e.g. PORT=3000

Integrate PostGraphQL middleware

This part is arguably slightly more interesting, but still just using configuration to wire things together:

Interactive testing

Database level

Before getting any application level concerns involved, I like to test everything works on the database level. I’ll register a user (using the function), make sure it populates both the public and private table, intentionally fail authenticating against it, attempt to successfully athenticate against it, then finally clean up the user.

auth=# SELECT auth_public.register_user ('firstname', 'lastname', 'email', 'password');
                    register_user                    
-----------------------------------------------------
 (1,firstname,lastname,"2017-06-11 04:05:39.216743")
(1 row)

auth=# SELECT *
FROM auth_public.user
JOIN auth_private.user_account
  ON auth_public.user.id = auth_private.user_account.user_id
;
auth-# ;
 id | first_name | last_name |         created_at         | user_id | email |                        password_hash                         
----+------------+-----------+----------------------------+---------+-------+--------------------------------------------------------------
  1 | firstname  | lastname  | 2017-06-11 04:05:39.216743 |       1 | email | $2a$06$PZ9NUmYpgDjk8QJuDwah.OJSt/Quo53Qzkddc5ccOSYpuzYXdfYJO
(1 row)

auth=# SELECT auth_public.authenticate('email', 'wrong-password');
 authenticate 
--------------
 
(1 row)

auth=# SELECT auth_public.authenticate('email', 'password');
 authenticate 
--------------
 (user,1)
(1 row)

auth=# DELETE FROM auth_public.user;
DELETE 1

GraphiQL level

1. Navigate to GraphiQL the port you’ve configured (3000 by default)
   – e.g. http://localhost:3000/graphiql

Create a user

2. Register a user via GraphQL mutation

mutation {
  registerUser(input: {
    firstName: "Genghis"
    lastName: "Khan"
    email: "Genghis@khan.mn"
    password: "Genghis1162"
  }) {
    user {
      id
      firstName
      lastName
      createdAt
    }
  }
}

3. Observe the response

{
  "data": {
    "registerUser": {
      "user": {
        "id": 2,
        "firstName": "Genghis",
        "lastName": "Khan",
        "createdAt": "2017-06-11T06:17:39.084578"
      }
    }
  }
}

Observe authentication working

4. Try authenticating with a GraphQL mutation

mutation {
  authenticate(input: {
    email: "Genghis@khan.mn"
    password: "Genghis1162"
  }) {
    jwt 
  }
}

5. Observe the response

{
  "data": {
    "authenticate": {
      "jwt": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoiYXV0aF9hdXRoZW50aWNhdGVkIiwidXNlcl9pZCI6MiwiaWF0IjoxNDk3MTYyMTIyLCJleHAiOjE0OTcyNDg1MjIsImF1ZCI6InBvc3RncmFwaHFsIiwiaXNzIjoicG9zdGdyYXBocWwifQ.hLZ7p3vJs3UYW9IKB7u8tbXONUl_tZoWhiAAD1-OPQg"
    }
  }
}

Try making an unauthenticated request when authentication is necessary

6. currentUser is protected, so query that

query {
  currentUser{
    id
    firstName
    lastName
    createdAt
  }
}

7. Observe the not-particularly-friendly response

{
  "errors": [
    {
      "message": "unrecognized configuration parameter \"jwt.claims.user_id\"",
      "locations": [
        {
          "line": 2,
          "column": 3
        }
      ],
      "path": [
        "currentUser"
      ]
    }
  ],
  "data": {
    "currentUser": null
  }
}

Try making an authenticated request when authentication is necessary

8. You’ll need the ability to send your JWT to the server, which unfortunately isn’t possible with vanilla GraphiQL
   • If you’re in Chrome you can try [ModHeader](https://chrome.google.com/webstore/detail/modheader/idgpnmonknjnojddfkpgkljpfnnfcklj/related)
   • If you’re in Firefox you can try [Modify Headers](https://addons.mozilla.org/en-US/firefox/addon/modify-headers/)
   • If you’re in another browser, you can try Chrome or Firefox

9. Set an authorization header by copy/pasting the value out of the `jwt` field in the `authenticate` response in step 5.

Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoiYXV0aF9hdXRoZW50aWNhdGVkIiwidXNlcl9pZCI6MSwiaWF0IjoxNDk3MTYwNzA3LCJleHAiOjE0OTcyNDcxMDcsImF1ZCI6InBvc3RncmFwaHFsIiwiaXNzIjoicG9zdGdyYXBocWwifQ.aInZvEVhhDfi9yQDWRzvmSaE7Mk2PufbBrY3rxGlEt8

• Don’t forget the Bearer on the right side of the header, otherwise you’ll likely see Authorization header is not of the correct bearer scheme format.

10. Submit the query with the authorization header attached

query {
  currentUser{
    nodeId
    id
    firstName
    lastName
    createdAt
  }
}

11. Observe your now successful response

{
  "data": {
    "currentUser": {
      "nodeId": "WyJ1c2VycyIsMl0=",
      "id": 2,
      "firstName": "Genghis",
      "lastName": "Khan",
      "createdAt": "2017-06-11T06:17:39.084578"
    }
  }
}

Observe authorization working

12. With the authorization header set, try updating Genghis

mutation {
  updateUser(input: {
    nodeId: "WyJ1c2VycyIsMl0="
    userPatch: {
      lastName: "NotKhan"
    }
  }) {
    user {
      nodeId
      id
      firstName
      lastName
      createdAt
    }
  }
}

13. Observe that it works:

{
  "data": {
    "updateUser": {
      "user": {
        "nodeId": "WyJ1c2VycyIsMl0=",
        "id": 2,
        "firstName": "Ghengis",
        "lastName": "NotKhan",
        "createdAt": "2017-06-11T06:17:39.084578"
      }
    }
  }
}

14. Add a friend

mutation {
  registerUser(input: {
    firstName: "Serena"
    lastName: "Williams"
    email: "Serena@Williams.ca"
    password: "NotGhengis"
  }) {
    user {
      nodeId
      id
      firstName
      lastName
      createdAt
    }
  }
}

15. Keeping Genghis’ JWT, try modifying your friend

• Note this is Serena’s nodeId

mutation {
  updateUser(input: {
    nodeId: "WyJ1c2VycyIsM10="
    userPatch: {
      lastName: "KhanMaybe?"
    }
  }) {
    user {
      nodeId
      id
      firstName
      lastName
      createdAt
    }
  }
}

16. Get rejected

{
  "errors": [
    {
      "message": "No values were updated in collection 'users' using key 'id' because no values were found.",
      "locations": [
        {
          "line": 2,
          "column": 3
        }
      ],
      "path": [
        "updateUser"
      ]
    }
  ],
  "data": {
    "updateUser": null
  }
}

Conclusion

That’s it – the bulk of an application that has authentication and authorization already sorted. Missing a front end, but at least there’s GraphiQL?

Code is availabe here.