I have an interesting problem, but I don’t know how to better formulate it than to say that I have a subquery that should return several columns. PostgreSQL throws an error when I try to do this, so although my SQL looks a bit logical to me - obviously there is a better way to do this. I am trying to combine user permissions into one table (hoping to throw this into a view or even a “materialized view”). 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 fill them out:
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 that the user can have. Users are stored in the user table (you guessed it). Groups in 'groups', any groups to which a user can be assigned are in 'user_groups'. Finally, each user can have individual permissions that must override group permissions - they are stored in user_rights.
I can pull out a request for 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 of the above data will give me effective permissions for any user specified in WHERE clauses. I want to create a materialized view that is updated only when user or group data changes, but otherwise static. I know this how to do it without problems - the problem that I encounter gives rise to this view. My idea is to use the above query, but at the same time it is launched for each user in the "users" table and the column "user_id" is created. So my table "effective_permissions" will 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 a few lines. I hope I have provided enough information so that someone understands what I'm trying to do. I understand that in the end, this method can become quite expensive in terms of performance after a group of tables in size - and this solution seems to be the best I can come up with to mitigate this problem.
The above examples should work if you want to recreate sample data for testing purposes (I just rebuilt it on my local pg server very quickly, although it is much simpler than real tables).