A take on role-based access control with Nodejs and Postgres

RBAC stands for “Role-based access control” and is a great solution for dealing with authorization within a single system or application. It can even be considered as best practice in those situations. I find it perfect for B2B SAAS as it can provide customers a way of configuring who gets access to what within their organization. This example is for a multi-tenant SAAS.

The RBAC entities

• Subject: the user; a person or API user.
• Role: title or job, e.g. Administrator, Manager etc.
• Permission: the action you want to provide authorization for. E.g. Publish article, edit billing settings.
• Permission assignments: an entity representing the assignment of a permission to a role
• Subject assignments: an entity representing the assignment of a role to a subject (user)

The RBAC Database Model

To translate the RBAC entities for our application the following tables will be created:

Create the tables

First: activate the UUID extension.

create extension "uuid-ossp";

Create the organizations table. Since this is a business to business SAAS, the organizations are the customers.

CREATE TABLE IF NOT EXISTS organizations
(
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    name character varying(320) NOT NULL,
    phone character varying(32) NOT NULL,
    email character varying(320) NOT NULL,
    country character varying(2) NOT NULL,
    address character varying(255) NOT NULL,
    city character varying(255) NOT NULL,
    postal_code character varying(12) NOT NULL,
	updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT pk_organizations PRIMARY KEY (id)
);

The users are the “subjects” in RBAC terminology.

CREATE TABLE IF NOT EXISTS users
(
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    email character varying(320) NOT NULL,
    password character varying(255) NOT NULL,
    first_name character varying(80) NOT NULL,
    last_name character varying(100) NOT NULL,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT pk_users PRIMARY KEY (id),
    CONSTRAINT uq_users_email UNIQUE (email)
);

The roles table.

CREATE TABLE IF NOT EXISTS roles
(
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    slug character varying(60) NOT NULL,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT pk_roles PRIMARY KEY (id),
    CONSTRAINT uq_roles_slug UNIQUE (slug)
);

The permissions table contains pre-defined permissions for all actions within the application.

CREATE TABLE IF NOT EXISTS permissions
(
	id uuid NOT NULL DEFAULT uuid_generate_v4(),
    slug character varying(60) NOT NULL,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT pk_permissions PRIMARY KEY (id),
    CONSTRAINT uq_permissions_slug UNIQUE (slug)
);

This table connects permissions to roles.

CREATE TABLE IF NOT EXISTS roles_permissions
(
    role_id uuid NOT NULL,
    permission_id uuid NOT NULL,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT pk_role_permissions PRIMARY KEY (role_id, permission_id),
    CONSTRAINT fk_roles_permissions_permission FOREIGN KEY (permission_id)
        REFERENCES permissions (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE,
    CONSTRAINT fk_roles_permissions_role FOREIGN KEY (role_id)
        REFERENCES roles (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE
);

This table connects roles to users. Note that it also contains a reference to a organization; to track which organization the user has the role within.

CREATE TABLE IF NOT EXISTS users_roles
(
    user_id uuid NOT NULL,
    role_id uuid NOT NULL,
    organization_id uuid NOT NULL,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT pk_users_roles PRIMARY KEY (user_id, role_id, organization_id),
    CONSTRAINT fk_users_roles_role FOREIGN KEY (role_id)
        REFERENCES roles (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE,
    CONSTRAINT fk_users_roles_user FOREIGN KEY (user_id)
        REFERENCES users (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE,
    CONSTRAINT fk_users_roles_organization FOREIGN KEY (organization_id)
        REFERENCES organizations (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE
);

A useful view for figuring out which permissions a user has within an organization.

CREATE VIEW vw_user_organization_permissions AS
	SELECT A.user_id, A.organization_id, C.slug as permission_slug
	FROM users_roles AS A
	LEFT JOIN roles_permissions AS B ON A.role_id = B.role_id
	LEFT JOIN permissions AS C on B.permission_id = C.id;

Reusable Expressjs authorization middleware to check permissions

To create highly reuseable middleware functions I’m using a middleware factory function. It takes the permission slug, and returns a function that checks if the user has the required permission.

In order to use this “permissions”-module you must inject a postgres connection pool.

Note that in production code, you wouldn’t run SQL statements inside the middleware. That should be abstracted away to a data layer. I like to use a three-tiered architecture to keep my business logic separate from both HTTP/Express-code and SQL/data-related code.

usePermissionsMiddleware.js

module.exports = function usePermissionsMiddleware(pgConnectionPool) {
  function hasPermission(permissionSlug) {
    return async (request, response, next) => {

    /* 
    It's expected that other middleware has set the user and organization property
    to the request object
    */
    const { user, organization } = request;

    const sql = `
      SELECT 1 FROM vw_user_organization_permissions
       WHERE permission_slug = $1
         AND organization_id = $2
         AND user_id = $3
    `;

    const values = [permissionSlug, organization.id, user.id];

    const result = await pgConnectionPool.query(sql, values);

    if (!!result.rows[0]) {
      next();
    } else {
      next(new Error(`User does not have permission ${permissionSlug} within ${organization.name}`));
    }
  }

  return {
    hasPermission
  }

}

Using our new permission module

Lets say we have a permission with the slug PUBLISH_ARTICLE and we want to check for that permission in our Expressjs endpoint for publishing articles.

index.js

const express = require('express');
const { Pool } = require('pg');
const usePermissionsMiddleware = require('./usePermissionsMiddleware.js');

(async () => {

  const app = express();

  const pool = await new Pool({
    user: process.env.POSTGRES_USER,
    password: process.env.POSTGRES_PASSWORD,
    host: process.env.POSTGRES_HOST,
    database: process.env.POSTGRES_DATABASE,
    port: process.env.POSTGRES_PORT
  });

  await pool.connect();

  const { hasPermission } = usePermissionsMiddleware(pool);

  // hasPermission returns a middleware function that will be called by Express
  app.post('/publish-article', hasPermission('PUBLISH_ARTICLE'), (req, res) => {
    // This will only be reached if the user has the permission PUBLISH_ARTICLE through its roles
    res.send({status: 'ok'}),
  });

  app.listen(3000);

});

This gives us a clean, reuseable middleware function for specifying and checking the required permissions for performing an action within our API.

Let me know if anything is unclear!