1

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.

3
  • Can you provide an example of the desired output given an input? Its not clear from your description or the data in the fiddle what the intended result is.
    – dwhitemv
    Commented Oct 16, 2022 at 1:20
  • Sure, I've updated my question with some example output.
    – cptproto
    Commented Oct 16, 2022 at 9:59
  • As a side note: many:many join tables such as user_role and role_permission do not need a id column. They should have a composite primary key made up of the two foreign key columns. Commented Oct 16, 2022 at 11:36

1 Answer 1

0

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

1
  • That seems to do the trick, thank you very much. Please drop me a wallet address, I would love to buy you a coffee.
    – cptproto
    Commented Oct 16, 2022 at 15:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.