Flattened view for multi-role permission system

Question

I'm currently working on a multi-role permission system for one of my projects, my database (postgres) tables are setup like this:

user

• id
• username
• email
• …

user_role

• id
• user_id
• role_id

role

• id
• name

permission

• id
• name

role_permission

• id
• role_id
• permission_id
• allowed (nullable bool)

For my backend I need a view table now which contains a "flattened" permission set for each user with the following columns:

• user_id
• permission_name
• permission_allowed

now the part I struggle badly with is that I need the following rules be applied:

• role id 1 should act as starting point, the "default" permissions
• then all allowed = true results of each assigned role to the user (except role id 1) should overwrite the permissions of the results
• then all allowed = false results of each assigned role to the user (except role id 1) should overwrite the permissions of the results

without having duplicate permission names. Basically how a firewall works (deny > allow).

I've already tinkered with UNION's and bool_and/bool_or aggregations, but I don't think that this is the correct solution, as I want the allowed column of each query result sequentially overridden.

Any ideas are welcome.

Fiddle: https://www.db-fiddle.com/f/dCLRKXr7HwpZgKBSN99jXn/0

Example output (unrelated to the fiddle content):

user_id	permission_name	permission_allowed
1	can_login	false
1	can_change_email	true
1	can_change_password	true
1	can_post	true
2	can_login	true
2	can_change_email	true
2	can_change_password	false
2	can_post	true
3	can_login	true
3	can_change_email	false
3	can_change_password	true
3	can_post	true

p.s. I would gladly donate some crypto for help if this is allowed.

Answer 1

The following script should get you all the user permissions using the desire logic

SELECT
  u.id,
  p.name,
  COALESCE(BOOL_AND(CASE WHEN rp.role_id > 1 THEN rp.allowed END),
           BOOL_AND(CASE WHEN rp.role_id = 1 THEN rp.allowed END)) AS permission_allowed
FROM "user" u
LEFT JOIN user_role ur ON ur.user_id = u.id
LEFT JOIN role_permission rp ON (rp.role_id = ur.role_id OR rp.role_id = 1)
LEFT JOIN "permission" p on p.id = rp.permission_id
GROUP BY
  u.id,
  p.id,
  p.name;

• We simply join all users with their roles (as well as the default role), and with the permissions for those roles.
• Then we group by user ID and permission ID, and take the BOOL_AND of allowed (ie false if available otherwise true) for each permission, using the default role if any other is not available.

DB Fiddle