title: Join tables and override values in postgresql
body: <p>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.</p>
  
  <p>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 <code>user1</code> is in group <code>standard</code> which has a read permission to lets say <code>file1.txt</code> and they have two special permissions to additionally write the file <code>file1.txt</code> and another file named <code>hello2.txt</code>. Any another user in group <code>standard</code> should only be able to read <code>file1.txt</code> for example <code>user2</code> which is also assigned to group standard.</p>
  
  <p>Abstract form of my database:</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code> uname |  gname   | pread | pwrite |   fname   
  -------+----------+-------+--------+-----------
   user1 | standard | t     | f      | file1.txt
   user2 | standard | t     | f      | file1.txt
  </code></pre>
  
  <p>Desired result:</p>
  
  <pre><code> uname |  gname   | pread | pwrite |   fname   
  -------+----------+-------+--------+-----------
   user1 | standard | t     | t      | file1.txt
   user1 | standard | f     | t      | file2.txt
   user2 | standard | t     | f      | file1.txt
  </code></pre>