3

I have two tables, roles and permissions, both of which use ltree for maintaining a tree structure. I also have a pivot table, roles_permissions, which serves to connect the two tables. How can I join them to each other – and bring in all the relations – in a reasonably efficient way?

In this system, the ancestry determines the capabilities of a role or permission. A role below building_access will inherit the rights and permissions of its parent. I can quite easily query roles or permissions and determine the entire line of ancestry for a given record.

What I am trying to do is query a role and determine the complete tree of permissions for that role. I am also trying to query all roles and determine the same list of permissions, for each and every role, aggregating it into a string.

SELECT r.name AS "role", CONCAT('["', string_agg(p.name, '", "' ORDER BY p.id), '"]') AS "permissions" FROM roles r JOIN roles_permissions rp ON rp.role = r.id JOIN permissions p ON p.id = rp.permission WHERE r.path @> 'company_employee.warehouse_employee' GROUP BY r.id; Actual Results: ---------------------+------------------------------------------------- role | permissions ---------------------+------------------------------------------------- company employee | ["building access", "break room access"] warehouse employee | ["warehouse access", "warehouse stock access"] Intended Results: ---------------------+------------------------------------------------- role | permissions ---------------------+------------------------------------------------- company employee | ["building access", "break room access"] warehouse employee | ["building access", "break room access", "warehouse access", "warehouse stock access"]

This query functions to give me the direct permissions for every role, but it doesn't get the ancestors of those permissions to establish the actual total permissions for the role.

My sample database is as displayed below:

CREATE EXTENSION ltree; CREATE EXTENSION tablefunc; CREATE TABLE roles ( id int PRIMARY KEY, name text NOT NULL, path ltree ); CREATE INDEX roles_path_idx ON roles USING gist (path); INSERT INTO roles (id, name, path) VALUES (1, 'company employee', 'company_employee'), (2, 'warehouse employee', 'company_employee.warehouse_employee'), (3, 'warehouse manager', 'company_employee.warehouse_employee.warehouse_manager'); CREATE TABLE permissions ( id int PRIMARY KEY, name text NOT NULL, path ltree ); CREATE INDEX permissions_path_idx ON permissions USING gist (path); INSERT INTO permissions (id, name, path) VALUES (1, 'building access', 'building_access'), (2, 'break room access', 'building_access.break_room_access'), (3, 'warehouse access', 'building_access.warehouse_access'), (4, 'warehouse stock access', 'building_access.warehouse_access.warehouse_stock_access'), (5, 'warehouse security access', 'building_access.warehouse_access.warehouse_security_access'); CREATE TABLE roles_permissions ( role int REFERENCES roles, permission int REFERENCES permissions, PRIMARY KEY (role, permission) ); INSERT INTO roles_permissions (role, permission) VALUES (1, 1), (1, 2), (2, 3), (2, 4), (3, 5);
Improve this question
2
  • Why warehouse employee role have break room access permission? Commented Dec 29, 2018 at 22:13
  • draw special attention to array_agg which McNets use rather than the string_agg idea. It's far better. Also, never double quote identifiers. Commented Dec 29, 2018 at 23:18

2 Answers 2

Reset to default
3

Essentially, what you're saying is that

  • a role of x.y.z, has all of the permissions granted to parent x.y, and grandparent x
  • a permission of x.y.z, has all of the permissions granted to parent x.y, and grandparent x

So essentially all of the things on the left, should have all the permissions of the combined group on the right,

SELECT r1.name, array_agg(r2.name) AS role_list FROM roles AS r1 INNER JOIN roles AS r2 ON r2.path @> r1.path GROUP BY r1.name; name | role_list --------------------+--------------------------------------------------------------- company employee | {"company employee"} warehouse manager | {"company employee","warehouse employee","warehouse manager"} warehouse employee | {"company employee","warehouse employee"} (3 rows)

From that point 

SELECT r1.name, array_agg(p.name) AS perm_list FROM roles AS r1 INNER JOIN roles AS r2 ON r2.path @> r1.path INNER JOIN roles_permissions AS rp ON rp.role = r2.id INNER JOIN permissions AS p ON rp.permission = p.id GROUP BY r1.name; name | perm_list --------------------+----------------------------------------------------------------------------------------------------------------- company employee | {"building access","break room access"} warehouse manager | {"building access","break room access","warehouse access","warehouse stock access","warehouse security access"} warehouse employee | {"building access","break room access","warehouse access","warehouse stock access"} (3 rows)

You can just pop back in the WHERE clause...

SELECT r1.name, array_agg(p.name) AS perm_list FROM roles AS r1 INNER JOIN roles AS r2 ON r2.path @> r1.path INNER JOIN roles_permissions AS rp ON rp.role = r2.id INNER JOIN permissions AS p ON rp.permission = p.id WHERE r1.path @> 'company_employee.warehouse_employee' GROUP BY r1.name;

Note in this scheme we just use role_permissions to map to the permissions and they don't work themselves hierarchical. That's not present in the test-data anyway. If you look at your mapping of (1,1) that's a weird one. If you have break-room access, do you also need to know explicitly that have you have company access? If not, then you need self-joins on both sides.

My diagnosis here is that your schema is most probably needlessly complex, and you should pick either hierarchical permissions, or M2M mapping. Having both is borderline insane.

Improve this answer
3

Inner join returns all ancestors of an specific path, I've used array_agg(distinc to avoid duplicates.

 select array_agg(distinct p2.name) permissions from permissions p1 join permissions p2 on p2.path @> p1.path

Then I've used it for each role that match the path company_employee.warehouse_employee

select r.name, (select array_agg(distinct p2.name) permissions from permissions p1 join permissions p2 on p2.path @> p1.path join roles_permissions rp on rp.permission = p1.id where rp.role in (select distinct r2.id from roles r1 join roles r2 on r2.path @> r1.path where r1.id = r.id)) permissions from roles r where r.path @> 'company_employee.warehouse_employee'; 
| "company employee" | "{"building access","break room access"}" | |----------------------|-------------------------------------------------------------------| | "warehouse employee" | "{"building access","break room access","warehouse access","warehouse stock access"}" |

rextester here

As Evan Carroll has pointed out

  • If a role X.Y.Z inherits permissions from X.Y and hence from X
  • And a permission A.B.C inherits from A.B and from A

Your bridge table should avoid to assign those rows twice.

Given next data:

ID | Roles ID | Perm ---+----- ---+------ 1 | X 1 | A 2 | X.Y 2 | A.B 3 | X.Y.Z 3 | A.C 4 | A.C.D 5 | A.C.E

Your bridge table joins (1,1) and (1,2):

X => A <-- Redundant X => A.B ~> A

In this case (1,1) is redundant because A permission is given by inheritance on A.B

And same for (2,3), (2,4):

 Inherits from role X.Y => A.C ~> X ~> A.B ~> A <-- Redundant X.Y => A.C.D ~> A.C ~> A ~> X ~> A.B ~> A
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.