In postgres, how can I execute queries that only return results where two many-to-many relationships overlap?

Question

Sorry for the confusing title. The situation really isn't that complicated but I'm having trouble verbalizing it well! Imagine a model with users, roles, and resources. A user can belong to many roles, and a resource can belong to many roles as well. If I query for a specific resource / userId (or, more importantly, a generic query for resources), I only want that resource to return if the user is in ALL the same roles as that resource. I am able to get results if there are ANY matches, but I can't figure out the sql to do exactly what I want.

For example, a users table with a couple of users in it:

     Table "public.users"
  Column  |  Type  | Modifiers 
----------+--------+-----------
 id       | bigint | 
 username | text   | 

postgres=# select * from users;
 id | username 
----+----------
  1 | foo
  2 | bar
(2 rows)

And a couple of roles:

postgres=# \d roles;
      Table "public.roles"
  Column   |  Type  | Modifiers 
-----------+--------+-----------
 id        | bigint | 
 role_name | text   | 

postgres=# select * from roles;
 id | role_name 
----+-----------
  1 | roleOne
  2 | roleTwo
(2 rows)

User one is in both roles:

postgres=# select * from user_role_join where user_id = 1;
 user_id | role_id 
---------+---------
       1 |       1
       1 |       2
(2 rows)

While user two is in only the first role:

postgres=# select * from user_role_join where user_id = 2;
 user_id | role_id 
---------+---------
       2 |       1
(1 row)

Lastly, I have a resource which is assigned to two roles..

postgres=# \d resource_role_join
Table "public.resource_role_join"
   Column    |  Type  | Modifiers 
-------------+--------+-----------
 resource_id | bigint | 
 role_id     | bigint | 

postgres=# 

postgres=# SELECT resource.name, resource.id, resource_role_join.role_id FROM resource INNER JOIN resource_role_join ON resource.id = resource_role_join.resource_id;
  name   | id | role_id 
---------+----+---------
 special |  1 |       1
 special |  1 |       2
(2 rows)

postgres=# 

This query, of course, will return my resource:

SELECT DISTINCT resource.id, resource.name
FROM resource
INNER JOIN resource_role_join ON resource.id = resource_role_join.resource_id
INNER JOIN user_role_join ON resource_role_join.role_id = user_role_join.role_id
WHERE user_role_join.user_id = 2 AND resource_id = 1;

But I'm trying to figure out how to structure my query so that when I use a user_id that is not in ALL roles that the resource belongs to, the resource will not come back in query results:

SELECT DISTINCT resource.id, resource.name
FROM resource
INNER JOIN resource_role_join ON resource.id = resource_role_join.resource_id
INNER JOIN user_role_join ON resource_role_join.role_id = user_role_join.role_id
WHERE user_role_join.user_id = 2 AND resource_id = 1;

I tried adding the users table itself to the join:

SELECT DISTINCT resource.id, resource.name
FROM resource
INNER JOIN resource_role_join ON resource.id = resource_role_join.resource_id
INNER JOIN user_role_join ON resource_role_join.role_id = user_role_join.role_id
INNER JOIN users ON users.id = user_role_join.user_id
WHERE users.id = 2 AND resource_id = 1;

I'm starting to suspect I'll need some kind of subquery. Any help is greatly appreciated! I understand I could also check role membership after the results come back, but I'd like to be able to limit it through the query for when the user is querying for many resources at once.

Answer 1

Using not exists to also cover the special case when a resource has no role (= it is public). Then we left outer join everything and see if we get a role of the resource for which the user doesn't have one.

SELECT DISTINCT
   r.id,
   r.name
FROM
   resource r
WHERE
   NOT EXISTS(
      SELECT
         *
      FROM
         resource_role_join rrj
         LEFT OUTER JOIN
            user_role_join urj
         ON
            urj.role_id = rrj.role_id AND
            urj.user_id = :USER_ID
      WHERE
         r.id = rrj.resource_id AND
         urj.role_id IS NULL
   )
;