title: How can I do access control via an SQL table?
body: <p>I'm trying to create an access control system. </p>
  
  <p>Here's a stripped down example of what the table I'm trying to control access to looks like:</p>
  
  <pre><code>things table:
  id   group_id   name
  1    1          thing 1
  2    1          thing 2
  3    1          thing 3
  4    1          thing 4
  5    2          thing 5
  </code></pre>
  
  <p>And the access control table looks like this:</p>
  
  <pre><code>access table:
  user_id  type   object_id  access
  1        group  1          50
  1        thing  1          10
  1        thing  2          100
  </code></pre>
  
  <p>Access can be granted either by specifying the id of the 'thing' directly, or granted for an entire group of things by specifying a group id. In the above example, user 1 has been granted an access level of 50 to group 1, which should apply unless there are any other rules granting more specific access to an individual thing.</p>
  
  <p>I need a query that returns a list of things (ids only is okay) along with the access level for a specific user. So using the example above I'd want something like this for user id 1:</p>
  
  <pre><code>desired result:
  thing_id   access
  1          10
  2          100
  3          50   (things 3 and 4 have no specific access rule,
  4          50   so this '50' is from the group rule)
  5               (thing 5 has no rules at all, so although I 
                  still want it in the output, there's no access
          level for it)
  </code></pre>
  
  <p>The closest I can come up with is this:</p>
  
  <pre><code>SELECT * 
  FROM things 
  LEFT JOIN access ON 
      user_id = 1
      AND (
          (access.type = 'group' AND access.object_id = things.group_id)
          OR (access.type = 'thing' AND access.object_id = things.id)
      )
  </code></pre>
  
  <p>But that returns multiple rows, when I only want one for each row in the 'things' table. I'm not sure how to get down to a single row for each 'thing', or how to prioritise 'thing' rules over 'group' rules.</p>
  
  <p>If it helps, the database I'm using is PostgreSQL.</p>
  
  <p>Please feel free to leave a comment if there's any information I've missed out. </p>
  
  <p>Thanks in advance!</p>