Query "all of" across many-to-many relation

You can do it with "fancy Postgres" features - much easier than "fancy MS SQL features" ;)

You can aggregate all the group IDs into an array and then compare that.

If with "all of" you mean those users that are assigned to exactly those groups, you can use something like this:

SELECT u.id
FROM users u
  JOIN user_group ug on ug.user_id = u.id
group by u.id
having array_agg(ug.group_id order by ug.group_id) = array[1,2,3];

Note that the = operator for arrays depends on the order [1,2,3] is a different array than [3,1,2] that's why array_agg() uses an order by and the values in the array are sorted as well.

If with "all of" you mean those users that are assigned to at least those groups (but could be assigned to more) then you can use a simple "contains" operator:

SELECT u.id
FROM users u
  JOIN user_group ug on ug.user_id = u.id
group by u.id
having array_agg(ug.group_id order) @> array[1,2,3];

The "contains" operator @ does not depend on the order of the elements.

---

If you need to return the complete row from the users table, you can do the aggregation in a derived table and join to that:

SELECT u.id
FROM users u
  JOIN (
    SELECT user_id
    FROM user_group 
    GROUP BY user_id    
    HAVING array_agg(group_id) @> array[1,2,3]
  ) ug on ug.user_id = u.id

---

The second query can also be done using standard SQL:

SELECT u.id
FROM users u
  JOIN (
    SELECT user_id
    FROM user_group 
    WHERE group_id in (1,2,3)
    GROUP BY user_id    
    HAVING count(distinct group_id) = 3
  ) ug on ug.user_id = u.id;

This solution has the disadvantages that you need to synchronize the values for the IN list and the count(..) = 3 expressions when you change the list of IDs