Implementing a Google Drive Style Hierarchical Authorization System in Hasura

Table of Contents

• Intro
• Overview
  • Database Schema
• How to accomplish this

Hasura is an open-source platform that provides a realtime GraphQL and REST APIs on top of data sources. These data sources can be any mixture of databases, REST API's, or even other GraphQL API's. Hasura generates a unified, federated schema and data-graph, and enables defining virtual relationships between data that lives in different places. This allows to transparently query across data sources as though they were one entity.

Authorization and access control is critical when dealing with data-access. Hasura has a role and permissions system to handle this that is capable of most any scenario.
Some scenarios require a bit of engineering between the database and Hasura's permission system though, and can be tricky to get right.

An auth model which has come up several times now is one based around hierarchical roles (IE manager, should have permissions of both manager and employee) AND dynamic access controls (IE user with id = 3 can READ/WRITE thing with id = 5)

This recipe gives an example of implementing such an auth pattern -- similar to the one you'd find in an environment like Google Drive.

Note: For further reference, you can check out our other post which contains a similar guide at:

• https://hasura.io/blog/authorization-rules-for-multi-tenant-system-google-cloud/

Or the tutorial on our "Learn" resources at:

• https://hasura.io/learn/graphql/hasura-auth-slack/introduction/

We're going to be publishing more posts on AuthZ patterns over this month. If you have any requests, message me on Discord / Twitter: @GavinRayDev

The scenario represented in the application is:

• An architecture project for a house
• Collaborators are broken up into "teams"
• Teams have been assigned parts of the project (and access to those folders)
  • Users may be assigned to one or more teams
  • Individual users may have folder permissions overriden/specified
• Being assigned access to a folder grants access to all child folders
  • Unless an overriding rule is present

To visualize, you might imagine something like this:

Database Schema

The schema for the database is composed of seven tables, and a few views used to calculate permissions:

CREATE TABLE user (
  id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE folder (
  id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name text NOT NULL,
  parent_folder_id int REFERENCES folder (id)
);

CREATE TABLE team (
  id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE user_team (
  user_id int NOT NULL REFERENCES user (id),
  team_id int NOT NULL REFERENCES team (id),
  PRIMARY KEY (user_id, team_id)
);

CREATE TABLE permission_folder_user (
  folder_id int REFERENCES folder (id),
  user_id int NOT NULL REFERENCES user (id),
  access_type text NOT NULL
);

CREATE TABLE permission_folder_team (
  folder_id int REFERENCES folder (id),
  team_id int NOT NULL REFERENCES team (id),
  access_type text NOT NULL
);

CREATE TABLE user_folder_blocklist (
  folder_id int REFERENCES folder (id),
  user_id int NOT NULL REFERENCES user (id),
  reason text NOT NULL
);

The end goal is to wind up with a flat table, containing the:

• Folder ID
• Access Level

For every user and folder they are allowed to access.
Something like:

Modeling this directly with Hasura's permission clauses isn't possible because the depth of the relationships is unknown ahead of time -- the parent-child tree could be arbitrarily deep.

Because of this, we need to use SQL to dynamically generate the "flattened" view of the relationships and folder <-> user <-> access level records.

While not the focus of this walkthrough, an implementation of that can be found below:

CREATE
OR REPLACE VIEW "acl"."permission_folder_user_and_team" AS
SELECT
  DISTINCT ON (q.folder_id, q.user_id)
  q.folder_id,
  q.user_id,
  q.access_type
FROM
  (
    SELECT
      pfu.folder_id,
      pfu.user_id,
      pfu.access_type,
      0 AS specificty
    FROM
      permission_folder_user pfu
    UNION ALL
    SELECT
      pft.folder_id,
      ut.user_id,
      pft.access_type,
      1 AS specificity
    FROM
      (
        permission_folder_team pft
        LEFT JOIN user_team ut ON ((pft.team_id = ut.team_id))
      )
  ) q
ORDER BY
  q.folder_id,
  q.user_id,
  q.specificty,
  q.access_type DESC;

CREATE
OR REPLACE VIEW "acl"."folder_ancestor" AS WITH RECURSIVE flattened_folder(
  folder_id,
  folder_name,
  ancestor_id,
  ancestor_name,
  depth
) AS (
  SELECT
    folder.id AS folder_id,
    folder.name AS folder_name,
    folder.id AS ancestor_id,
    folder.name AS ancestor_name,
    0
  FROM
    folder
  UNION ALL
  SELECT
    ff.folder_id,
    ff.folder_name,
    f.parent_folder AS ancestor_id,
    f2.name AS ancestor_name,
    (ff.depth + 1)
  FROM
    flattened_folder ff,
    folder f,
    folder f2
  WHERE
    (
      (ff.ancestor_id = f.id)
      AND (f.parent_folder IS NOT NULL)
      AND (f2.id = f.parent_folder)
    )
)
SELECT
  flattened_folder.folder_id,
  flattened_folder.folder_name,
  flattened_folder.ancestor_id,
  flattened_folder.ancestor_name,
  flattened_folder.depth
FROM
  flattened_folder
ORDER BY
  flattened_folder.folder_id,
  flattened_folder.depth;

CREATE
OR REPLACE VIEW "acl"."flattened_folder_permission" AS
SELECT
  DISTINCT ON (q.folder_id, q.user_id)
  q.folder_id,
  q.user_id,
  q.access_type
FROM
  (
    SELECT
      ff.folder_id,
      ff.folder_name,
      ff.ancestor_id,
      ff.ancestor_name,
      ff.depth,
      pfu.user_id,
      pfu.access_type
    FROM
      (
        acl.folder_ancestor ff
        JOIN acl.permission_folder_user_and_team pfu ON ((ff.ancestor_id = pfu.folder_id))
      )
  ) q
ORDER BY
  q.folder_id,
  q.user_id,
  q.depth;

Creating the view mentioned above should give us the table we need, with data that looks something like this:

Now what we want to say is:

• For a folder:
  • Allow them to view a folder if:
    • The <current users id> is present in <the flattened folder permissions view>
  • Allow them to update the folder if
    • The <current users id> is present in <the flattened folder permissions view>
    • The <current user id> record in the <the flattened folder permissions view> has an access type of write

The way we phrase the first permission in Hasura is like:

{
    "permissions": {
        "user_id": {
            "_eq": "X-Hasura-User-Id"
        }
    }
}

And to configure the permissions for restricted update capabilities, is like:

{
    "permissions": {
        "_and": [
            "user_id": {
                "_eq": "X-Hasura-User-Id"
            },
            "access_type": {
                "_eq": "write"
            }
        ]
    }
}

After adding these two rules, congratulations!

We've now successfully implemented a hierarchical, role-based ACL system with individual permission overrides and permission inheritance!

If you have any questions or want to share your thoughts on the topic, reach out to us on Twitter / Discord / GitHub Discussions.