sql - 在 postgresql 中加入表并覆盖值

Question

我有一个数据库来管理用户及其权限。每个用户都被分配到一个组，该组具有分配给它的权限(作为 bool 值读取和写入)，但仅限于特定文件。并且每个用户都可以拥有多个具有更高优先级的特殊权限，并且只授予特定用户额外的权限。

我正在寻找一个查询，我可以在其中指定用户并取回他们有权访问的所有文件以及哪些权限。因此，如果用户 user1 在组 standard 中，该组具有读取权限让我们说 file1.txt 并且他们有两个特殊权限可以另外写入文件 file1.txt 和另一个名为 hello2.txt 的文件。 standard 组中的任何其他用户应该只能读取 file1.txt，例如 user2，它也被分配给组标准。

我的数据库的抽象形式:

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);

我设法创建了一个查询来获取分配给该组的权限，但我不知道如果有授予用户额外权限的特殊权限或如何连接两个表，我该如何覆盖它们在一起，同时仍然有一个像这样的简单结果:

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

Answer 1

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;

请注意，我选择这种类型的查询是因为它很容易扩展到用户和组之间的关系是多对多的情况(通过删除主查询中的连接并为组成员添加最后的左连接到子查询)。

此外，如果用户对文件的权限为“f”，“f”，则此查询确实会返回。如果您不想要它，您可以过滤此查询的结果以将其删除。