I'm currently working on a multi-role permission system for one of my projects, my database (postgres) tables are setup like this:
- 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:
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.
Example output (unrelated to the fiddle content):
p.s. I would gladly donate some crypto for help if this is allowed.