Subquery returning multiple columns - or a close approximation

Question

I have an interesting problem, however I don't know quite how to articulate it better than saying I have a subquery that needs to return multiple columns. PostgreSQL throws an error when I attempt to do this, so while my SQL looks somewhat logically sound to me - obviously there is a better way to do this. I'm attempting to merge user permissions into one table (hoping to throw this in to a view or even a "materialized view" of sorts). Here are my tables:

CREATE TABLE users (
  user_id integer NOT NULL,
  username character varying(32) NOT NULL,
  passwd character varying(32) NOT NULL,
  dept_id integer NOT NULL,
  last_activity timestamp with time zone NOT NULL DEFAULT now(),
  CONSTRAINT "pk-users-user_id" PRIMARY KEY (user_id)
);

CREATE TABLE groups (
  group_id integer NOT NULL,
  group_name character varying(32) NOT NULL,
  add_posts integer NOT NULL DEFAULT 0,
  remove_posts integer NOT NULL DEFAULT 0,
  modify_users integer NOT NULL DEFAULT 0,
  add_users integer NOT NULL DEFAULT 0,
  delete_users integer NOT NULL DEFAULT 0,
  CONSTRAINT "pk-groups-group_id" PRIMARY KEY (group_id)
);

CREATE TABLE user_groups (
  user_id integer NOT NULL,
  group_id integer NOT NULL,
  CONSTRAINT "fk-user_groups-group_id" FOREIGN KEY (group_id)
      REFERENCES groups (group_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "fk-user_groups-user_id" FOREIGN KEY (user_id)
      REFERENCES users (user_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE user_rights (
  user_id integer NOT NULL,
  add_posts integer NOT NULL DEFAULT 0,
  remove_posts integer NOT NULL DEFAULT 0,
  modify_users integer NOT NULL DEFAULT 0,
  add_users integer NOT NULL DEFAULT 0,
  delete_users integer NOT NULL DEFAULT 0,
  CONSTRAINT "fk-user_rights-user_id" FOREIGN KEY (user_id)
      REFERENCES users (user_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
);

and some data to populate them:

INSERT INTO users(user_id, username, passwd, dept_id) VALUES (1, 'nicole','123456',12);
INSERT INTO users(user_id, username, passwd, dept_id) VALUES (2, 'john','324634',11);
INSERT INTO users(user_id, username, passwd, dept_id) VALUES (3, 'susan','61236',14);
INSERT INTO users(user_id, username, passwd, dept_id) VALUES (4, 'mary','1213612',2);
INSERT INTO user_rights(user_id, add_posts, remove_posts, modify_users, add_users, delete_users) VALUES (1,0,0,1,1,1);
INSERT INTO user_rights(user_id, add_posts, remove_posts, modify_users, add_users, delete_users) VALUES (2,1,1,1,1,1);
INSERT INTO user_rights(user_id, add_posts, remove_posts, modify_users, add_users, delete_users) VALUES (3,0,0,0,0,0);
INSERT INTO user_rights(user_id, add_posts, remove_posts, modify_users, add_users, delete_users) VALUES (4,0,0,0,0,0);
INSERT INTO groups(group_id, group_name, add_posts, remove_posts, modify_users, add_users, delete_users) VALUES (1,'Poster',1,1,0,0,0);
INSERT INTO groups(group_id, group_name, add_posts, remove_posts, modify_users, add_users, delete_users) VALUES (2,'User Mgr',0,0,1,1,1);
INSERT INTO groups(group_id, group_name, add_posts, remove_posts, modify_users, add_users, delete_users) VALUES (3,'Admin',1,1,1,1,1);
INSERT INTO user_groups(user_id, group_id) VALUES (1,1);
INSERT INTO user_groups(user_id, group_id) VALUES (2,2);
INSERT INTO user_groups(user_id, group_id) VALUES (3,2);
INSERT INTO user_groups(user_id, group_id) VALUES (4,3);
INSERT INTO user_groups(user_id, group_id) VALUES (1,2);

What I'm trying to do is create a query that can calculate the effective permissions a user might have. Users are stored in the (you guessed it) 'users' table. Groups in 'groups', whatever groups a user might be assigned to are in 'user_groups'. Finally, each user can have individual permissions that should override the group permissions - those are stored in 'user_rights'.

I can pull a query of all this information using (and yes, I know this is ugly):

select
  max(add_posts) as add_posts,
  max(remove_posts) as remove_posts,
  max(modify_users) as modify_users,
  max(add_users) as add_users,
  max(delete_users) as delete_users
from
(
select 
  max(add_posts) as add_posts,
  max(remove_posts) as remove_posts,
  max(modify_users) as modify_users,
  max(add_users) as add_users,
  max(delete_users) as delete_users
from
  groups
where
  group_id in (select group_id from user_groups where user_id = 3)
union all
select  
  max(add_posts) as add_posts,
  max(remove_posts) as remove_posts,
  max(modify_users) as modify_users,
  max(add_users) as add_users,
  max(delete_users) as delete_users
from 
  user_rights
where
  user_id = 3
) as combined_user_groups

Which given the above data will give me the effective permissions for any user I specify in the WHERE clauses. What I want to do is create a materialized view that is only updated when the user or group data changes, but is otherwise static. This I know how to do with no problem - the problem I'm encountering is generating this view. My idea is using the above query - but having it run for each user in the 'users' table and creating a 'user_id' column. So my 'effective_permissions' table would look like this:

user_id, add_posts, remove_posts, modify_users, add_users, delete_users
1        1          1             1             1          1
2        1          1             1             1          1
3        0          0             1             1          1

..and so on. I just can't figure out how to add user_id to this result and show multiple rows. I hope I have provided enough information for someone to understand what it is I am trying to do. I realize that ultimately this method can become rather costly performance-wise once the tables group in size - and this solution seems to be the best one I can come up with to mitigate that problem.

The examples provided should work if you want to re-create the sample data for testing purposes (I just rebuilt it on my local pg server real quick, though it's much simpler than the real tables the same concepts apply).

Answer 1

select
  user_id
  max(add_posts) as add_posts,
  max(remove_posts) as remove_posts,
  max(modify_users) as modify_users,
  max(add_users) as add_users,
  max(delete_users) as delete_users
from
(
select 
  ug.user_id
  max(g.add_posts) as add_posts,
  max(g.remove_posts) as remove_posts,
  max(g.modify_users) as modify_users,
  max(g.add_users) as add_users,
  max(g.delete_users) as delete_users
from
  groups g
inner join
  users_groups ug
on g.group_id = ug.group_id
group by    
  ug.user_id
union
select
  user_id  
  max(add_posts) as add_posts,
  max(remove_posts) as remove_posts,
  max(modify_users) as modify_users,
  max(add_users) as add_users,
  max(delete_users) as delete_users
from 
  user_rights
group by
  user_id
) as combined_user_groups
group by
  user_id