Join tables and override values in postgresql
I have a database to manage users and their permissions. Every user is assigned to a group that has permissions assigned to it (read and write as booleans) but only for a specific file. And every user can have multiple special permissions that have higher priority and only give a specific user an additional right.
I'm looking for a query where I can specify a user and get back all files they have access to and which rights. So if a user user1 is in group standard which has a read permission to lets say file1.txt and they have two special permissions to additionally write the file file1.txt and another file named hello2.txt. Any another user in group standard should only be able to read file1.txt for example user2 which is also assigned to group standard.
Abstract form of my database:
CREATE TABLE files( fid INTEGER PRIMARY KEY, fname VARCHAR(200) UNIQUE ); CREATE TABLE permissions( pid INTEGER PRIMARY KEY, pread BOOLEAN DEFAULT FALSE, pwrite BOOLEAN DEFAULT FALSE, pfid INTEGER REFERENCES files(fid) ); CREATE TABLE groups( gid INTEGER PRIMARY KEY, gname VARCHAR(50) ); CREATE TABLE users ( uid INTEGER PRIMARY KEY, uname VARCHAR(10), ugroup INTEGER REFERENCES groups(gid) ); CREATE TABLE specialpermissions( sppid INTEGER REFERENCES permissions(pid), spuid INTEGER REFERENCES users(uid), PRIMARY KEY (sppid, spuid) ); CREATE TABLE grouppermissions( gpgid INTEGER REFERENCES groups(gid), gppid INTEGER REFERENCES permissions(pid), PRIMARY KEY (gpgid, gppid) ); --Create a group INSERT INTO groups (gid, gname) VALUES (1, 'standard'); --Crate users and assign them to the group standard INSERT INTO users (uid, uname, ugroup) VALUES (1, 'user1', 1), (2, 'user2' ,1); --Create tow files name file1.txt and file2.txt INSERT INTO files (fid, fname) VALUES (1, 'file1.txt'), (2, 'file2.txt'); --Create a permission to read file1, to write file1 and to write file2 INSERT INTO permissions (pid, pread, pwrite, pfid) VALUES (1, TRUE, FALSE, 1), (2, TRUE, TRUE, 1), (3, FALSE, TRUE, 2); --Assign the read permission for file1 to group standard INSERT INTO grouppermissions (gpgid, gppid) VALUES (1, 1); --Assign write permission for file1 and write permission to file2 INSERT INTO specialpermissions (sppid, spuid) VALUES (2, 1), (3, 1);
I managed to create a query to get the permissions that are assigned with the group but i don't know how I could override them if there is a special permission that grants additional rights to a user or how to join the two tables together while still having a simple result like this:
SELECT u.uname, g.gname, p.pread, p.pwrite, f.fname FROM users u INNER JOIN groups g ON g.gid = u.ugroup INNER JOIN grouppermissions gp ON gp.gpgid = g.gid INNER JOIN permissions p ON p.pid = gp.gppid INNER JOIN files f ON f.fid = p.pfid ORDER BY u.uname;
uname | gname | pread | pwrite | fname -------+----------+-------+--------+----------- user1 | standard | t | f | file1.txt user2 | standard | t | f | file1.txt
uname | gname | pread | pwrite | fname -------+----------+-------+--------+----------- user1 | standard | t | t | file1.txt user1 | standard | f | t | file2.txt user2 | standard | t | f | file1.txt
SELECT u.uname, ug.gname, exists(select * from permissions p left join grouppermissions gp on (gp.gppid = p.pid) left join groups g on (gp.gpgid = g.gid) left join specialpermissions sp on (sp.sppid = p.pid) where p.pread = true and p.pfid = f.fid and (u.ugroup = g.gid or u.uid = sp.spuid)) as pread, exists(select * from permissions p left join grouppermissions gp on (gp.gppid = p.pid) left join groups g on (gp.gpgid = g.gid) left join specialpermissions sp on (sp.sppid = p.pid) where p.pwrite = true and p.pfid = f.fid and (u.ugroup = g.gid or u.uid = sp.spuid)) as pwrite, f.fname FROM users u INNER JOIN files f ON 1=1 INNER JOIN groups ug ON (ug.gid = u.ugroup) ORDER BY u.uname;
Note, that I chose this type query because it would be easy to extend to a case where the relationship between user and group is many to many (by dropping the join in the main query and adding a final left join for group membership to the subqueries).
Additionally, this query does return in the case that the permission on the file for a user is 'f', 'f'. If you don't want this, you can filter the result of this query to remove it.