CODEWITHSUNDEEP
sqlpostgresql
mmjmanders
mmjmanders

Reputation: 1553

Selecting relations from multiple tables in SQL

I'm really stuck with a complex query in Postgresql. The use case is that the system has users, groups, and policies. A user can belong to multiple groups and a policy can belong to either a group or a user. This is modelled as follows:

CREATE TABLE "user" (
    id BIGSERIAL NOT NULL,
    username CHARACTER VARYING(64) NOT NULL UNIQUE,
    hashed_password CHARACTER VARYING(64) NOT NULL,
  ldap BOOLEAN DEFAULT FALSE,
    PRIMARY KEY (id)
);

CREATE TABLE "group" (
  id BIGSERIAL NOT NULL,
  name CHARACTER VARYING(64) NOT NULL UNIQUE,
  PRIMARY KEY (id)
);

CREATE TABLE "user_group" (
  user_id BIGINT NOT NULL,
  group_id BIGINT NOT NULL,
  PRIMARY KEY (user_id, group_id),
  FOREIGN KEY (user_id) REFERENCES "user"(id),
  FOREIGN KEY (group_id) REFERENCES "group"(id)
);

CREATE TABLE "policy" (
  id BIGSERIAL NOT NULL,
  user_id BIGINT,
  group_id BIGINT,
  effect VARCHAR(5),
  action TEXT,
  resource TEXT,
  PRIMARY KEY (id),
  FOREIGN KEY (user_id) REFERENCES "user"(id),
  FOREIGN KEY (group_id) REFERENCES "group"(id)
);

Now I need to check the user's policies based his user id combined with the policies based on the user's group and I want to join them with the user information.

I came up with this but this isn't showing while the user with ID=1 should have policies because he is in the group with ID=1.

The data which is now in the policies is this this:

id=1
user_id=<null>
group_id=1
...

The query used is this:

SELECT
  p.effect,
  p.action,
  p.resource,
  u.*
FROM policy AS p
LEFT JOIN "user" AS u ON p.user_id = u.id
LEFT JOIN "group" AS g ON p.group_id = g.id
LEFT JOIN user_group AS ug ON u.id = ug.user_id
WHERE p.user_id = ug.user_id OR p.group_id = ug.group_id AND ug.user_id = 1;

Upvotes: 0

Views: 85

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

You want policy records. So select from policy and use EXISTS or IN in your WHERE clause. You want all policies for user 1. This is where the policy's user ID is 1 or the policy's group ID is connected to this user.

There are only two tables needed: the policy table and the bridge table that links users to groups.

select *
from policy
where user_id = 1 
   or group_id in (select group_id from user_group where user_id = 1);

UPDATE: You have altered your request. You want to show user information, too, now. That's an additional join.

select *
from policy p
cross join (select * from "user" where id = 1) u
where p.user_id = 1 
   or p.group_id in (select group_id from user_group where user_id = 1);

And if you like it better, you can replace the two = 1 in the main query's WHERE clause with = u.id. I do :-)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

I think your join conditions are wrong. I think this is what you want:

SELECT p.effect, p.action, p.resource
FROM "user" u JOIN
     policy  p
     ON p.user_id = u.id JOIN
     user_group ug
     ON u.id = ug.user_id JOIN
     "group" g
     ON ug.group_id = g.id AND p.group_id = ug.group_id
WHERE ug.user_id = 1;

It is a little confusing on how the group in policy should related to the group in user_groups.

Upvotes: 0

Related Questions