This looks like a classic AND scenario - which will work with any database server that supports SQL. And, no need to use arrays or any "fancy MS SQL features" or, indeed, PostgreSQL ones or from any server!
What you want (in standard SQL) is (fiddle here):
SELECT u.id, u.name FROM the_user u
INNER JOIN user_group g1 ON g1.user_id = u.id
INNER JOIN user_group g2 ON g2.user_id = u.id
INNER JOIN user_group g3 ON g3.user_id = u.id
WHERE g1.group_id = 5
AND g2.group_id = 6
AND g3.group_id = 7;
Result:
id name
3 user3
This is the desired result - no arrays, no fancy stuff, just plain, honest SQL :-)
You can also use the INTERSECT set operator:
SELECT ug.user_id, u.name
FROM user_group ug
JOIN the_user u ON ug.user_id = u.id
WHERE ug.group_id = 5
INTERSECT
SELECT ug.user_id, u.name
FROM user_group ug
JOIN the_user u ON ug.user_id = u.id
WHERE ug.group_id = 6
INTERSECT
SELECT ug.user_id, u.name
FROM user_group ug
JOIN the_user u ON ug.user_id = u.id
WHERE ug.group_id = 7;
Result:
user_id name
3 user3
ditto! May be better performance wise?
========= DML and DDL =========
Tables (DDL):
CREATE TABLE the_user -- not "user" - can be an SQL keywowrd
(
id INT,
name VARCHAR (10)
);
CREATE TABLE groupe -- use the French - GROUP being a keyword!
(
id INT,
name VARCHAR (10)
);
CREATE TABLE user_group
(
user_id INT,
group_id INT
);
Data (DML):
INSERT INTO the_user VALUES (1, 'user1'), (2, 'user2'), (3, 'user3');
INSERT INTO groupe VALUES (5, 'group1'), (6, 'group2'), (7, 'group3');
INSERT INTO user_group VALUES (1, 6), (1, 7), (2, 5), (2, 7), (3, 5), (3, 6), (3, 7);