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).
