BEGIN TRANSACTION; CREATE TABLE roles( id SERIAL NOT NULL, name VARCHAR, PRIMARY KEY(id) ); ALTER TABLE lazychat_targets DROP CONSTRAINT lazychat_targets_target_fkey; ALTER TABLE lazychat_targets ADD CONSTRAINT lazychat_targets_target_fkey FOREIGN KEY(target) REFERENCES roles; CREATE TABLE role_tags( "role" INTEGER NOT NULL, tag VARCHAR NOT NULL, PRIMARY KEY("role", tag), CHECK (tag IN ('admin', 'everyone', 'world')) ); ALTER TABLE users ADD COLUMN "role" INTEGER REFERENCES roles; CREATE TABLE user_roles( "user" INTEGER NOT NULL, "role" INTEGER NOT NULL, PRIMARY KEY("user", "role"), FOREIGN KEY("user") REFERENCES users, FOREIGN KEY("role") REFERENCES roles ); -- Create singleton roles for existing users. CREATE FUNCTION create_singleton_roles() RETURNS VOID AS $$ DECLARE "user" INTEGER; user_name VARCHAR; BEGIN FOR "user", user_name IN SELECT id, concat(first_name, ' ', last_name) FROM users LOOP DECLARE new_role INTEGER; BEGIN INSERT INTO roles("name") VALUES (user_name) RETURNING id INTO STRICT new_role; UPDATE users SET "role" = new_role WHERE id = "user"; INSERT INTO user_roles VALUES ("user", new_role); END; END LOOP; END; $$ LANGUAGE plpgsql; SELECT create_singleton_roles(); DROP FUNCTION create_singleton_roles(); ALTER TABLE users ALTER COLUMN "role" SET NOT NULL; CREATE TABLE role_subroles( "superrole" INTEGER NOT NULL, "subrole" INTEGER NOT NULL, PRIMARY KEY("superrole", "subrole"), FOREIGN KEY("superrole") REFERENCES roles, FOREIGN KEY("subrole") REFERENCES roles ); CREATE VIEW effective_role_subroles AS WITH RECURSIVE t(superrole, subrole) AS ( SELECT id, id FROM roles UNION SELECT t.superrole, rs.subrole FROM t t INNER JOIN role_subroles rs ON (rs.superrole = t.subrole) ) SELECT * FROM t; CREATE VIEW effective_user_roles AS (SELECT ur."user", er.subrole AS role FROM user_roles ur INNER JOIN effective_role_subroles er ON (er.superrole = ur.role)) UNION (SELECT u.id AS "user", rt.role FROM users u, role_tags rt WHERE rt.tag IN ('everyone', 'world')) UNION (SELECT NULL, rt.role FROM role_tags rt WHERE rt.tag = 'world'); WITH r(id) AS ( INSERT INTO roles(name) VALUES ('Admininistrators') RETURNING id ) INSERT INTO role_tags SELECT r.id, 'admin' FROM r; WITH world(id) AS ( INSERT INTO roles(name) VALUES ('World') RETURNING id ), t AS ( INSERT INTO lazychat_targets SELECT m.id, world.id FROM lazychat_messages m, world WHERE m.visibility = 'public'; ) INSERT INTO role_tags SELECT id, 'world' FROM world; WITH r(id) AS ( INSERT INTO roles(name) VALUES ('Logged-In Users') RETURNING id ), t AS ( INSERT INTO user_roles SELECT users.id, r.id FROM users, r ) INSERT INTO role_tags SELECT r.id, 'everyone' FROM r; WITH inner_circle(id) AS ( INSERT INTO roles(name) VALUES ('Inner Circle') RETURNING id ), t AS ( INSERT INTO lazychat_targets SELECT m.id, inner_circle.id FROM lazychat_messages m, inner_circle WHERE m.visibility = 'protected' ) INSERT INTO user_roles SELECT users.id, inner_circle.id FROM users, inner_circle; ALTER TABLE lazychat_messages DROP COLUMN visibility; CREATE VIEW user_visible_lazychat_messages AS SELECT eur.user, t.message FROM effective_user_roles eur, lazychat_targets t WHERE t.target = eur.role; CREATE FUNCTION new_user_put_in_universal_role() RETURNS TRIGGER AS $$ DECLARE universal_role INTEGER; BEGIN SELECT "role" FROM role_tags WHERE tag = 'everyone' INTO universal_role; INSERT INTO user_roles VALUES (NEW.id, universal_role); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER new_user_put_in_universal_role AFTER INSERT ON users FOR EACH ROW EXECUTE PROCEDURE put_new_user_in_universal_role(); CREATE FUNCTION new_user_put_user_in_user_role() RETURNS TRIGGER AS $$ BEGIN INSERT INTO user_roles VALUES (NEW."role", user_role); END; $$ LANGUAGE plpgsql; CREATE TRIGGER new_user_put_user_in_user_role AFTER INSERT ON users FOR EACH ROW EXECUTE PROCEDURE new_user_put_user_in_user_role(); CREATE FUNCTION new_user_create_user_role() RETURNS TRIGGER AS $$ DECLARE new_role INTEGER; BEGIN INSERT INTO roles("name") VALUES (concat(NEW.first_name, ' ', NEW.last_name)) RETURNING id INTO STRICT new_role; UPDATE users SET "role" = new_role WHERE id = NEW.id; NEW.role := new_role; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER new_user_create_user_role BEFORE INSERT ON users FOR EACH ROW EXECUTE PROCEDURE new_user_create_user_role(); COMMIT;