Implementing a Google Drive Style Hierarchical Authorization System in Hasura
Intro
Overview
- 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
Database Schema
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
- Folder ID
- Access Level
folder_id | user_id | access_type |
---|---|---|
1 | 1 | read |
2 | 1 | write |
3 | 1 | write |
3 | 2 | read |
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;
- For a
folder
:- Allow them to view a folder if:
- The
<current users id>
is present in<the flattened folder permissions view>
- The
- 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 ofwrite
- The
- Allow them to view a folder if:
{
"permissions": {
"user_id": {
"_eq": "X-Hasura-User-Id"
}
}
}
{
"permissions": {
"_and": [
"user_id": {
"_eq": "X-Hasura-User-Id"
},
"access_type": {
"_eq": "write"
}
]
}
}
Related reading