Implementing a Google Drive Style Hierarchical Authorisation System in Hasura

Table of Contents

Intro

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:

Or the tutorial on our “Learn” resources at:

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

Overview

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
);

How to accomplish this

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:

| folder\_id | user\_id | access\_type |
| — — | — — | — — |
| 1 | 1 | read |
| 2 | 1 | write |
| 3 | 1 | write |
| 3 | 2 | read |

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:

  • 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.

Originally published at https://hasura.io on October 6, 2021.

⚡️ Instant realtime GraphQL APIs! Connect Hasura to your database & data sources (GraphQL, REST & 3rd party API) and get a unified data access layer instantly.

⚡️ Instant realtime GraphQL APIs! Connect Hasura to your database & data sources (GraphQL, REST & 3rd party API) and get a unified data access layer instantly.