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;

Result:

 uname |  gname   | pread | pwrite |   fname   
-------+----------+-------+--------+-----------
 user1 | standard | t     | f      | file1.txt
 user2 | standard | t     | f      | file1.txt

Desired result:

 uname |  gname   | pread | pwrite |   fname   
-------+----------+-------+--------+-----------
 user1 | standard | t     | t      | file1.txt
 user1 | standard | f     | t      | file2.txt
 user2 | standard | t     | f      | file1.txt

Answers


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.


Need Your Help

Chart.js bar chart bars and labels do not align

jquery alignment chart.js labels

I am using Chart.js to display some data as bar chart. An example of this data is:

Windows thinks signed installer is malware after security update (KB3124605)

windows signing elevated-privileges windows-update

2 Years ago @Dejan Maksimovic asked a question about Internet Explorer shows valid certificate as “corrupt or invalid signature”.