From 4519ac40237342e0216f5fe7af2029810edc3a6e Mon Sep 17 00:00:00 2001 From: Matthias Andreas Benkard Date: Tue, 3 Jul 2012 11:26:15 +0200 Subject: Lafargue: Migrate to a role-based access control model. --- migrations/0_init.sql | 148 ++++++++++++++++++++++++++++++++++++++++ migrations/1_add_roles.sql | 164 +++++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 312 insertions(+) create mode 100644 migrations/0_init.sql create mode 100644 migrations/1_add_roles.sql (limited to 'migrations') diff --git a/migrations/0_init.sql b/migrations/0_init.sql new file mode 100644 index 0000000..5c1d4c5 --- /dev/null +++ b/migrations/0_init.sql @@ -0,0 +1,148 @@ +--*- mode: sql; coding: utf-8 -*-- + +BEGIN TRANSACTION; + +CREATE TABLE users( + id SERIAL NOT NULL, + first_name VARCHAR, + middle_names VARCHAR, + last_name VARCHAR, + email VARCHAR, + website VARCHAR, + status VARCHAR, + PRIMARY KEY(id), + CHECK (status IN ('admin', 'approved', 'visitor', 'disabled')) +); + +CREATE TABLE openids( + "user" INTEGER NOT NULL, + openid VARCHAR NOT NULL, + PRIMARY KEY(openid), + FOREIGN KEY("user") REFERENCES users +); + +CREATE TABLE webids( + "user" INTEGER NOT NULL, + webid VARCHAR NOT NULL, + PRIMARY KEY(webid), + FOREIGN KEY("user") REFERENCES users +); + +CREATE TABLE rsa_keys( + modulus NUMERIC NOT NULL, + exponent NUMERIC NOT NULL, + PRIMARY KEY(modulus, exponent) +); + +CREATE TABLE user_rsa_keys( + "user" INTEGER NOT NULL, + modulus NUMERIC NOT NULL, + exponent NUMERIC NOT NULL, + PRIMARY KEY("user", modulus, exponent), + FOREIGN KEY("user") REFERENCES users, + FOREIGN KEY(modulus, exponent) REFERENCES rsa_keys +); + +CREATE TABLE user_email_addresses( + "user" INTEGER NOT NULL, + email VARCHAR NOT NULL, + PRIMARY KEY(email), + FOREIGN KEY("user") REFERENCES users +); + +CREATE TABLE user_nicknames( + "user" INTEGER NOT NULL, + nickname VARCHAR NOT NULL, + PRIMARY KEY(nickname), + FOREIGN KEY("user") REFERENCES users +); +CREATE INDEX user_nicknames_user ON user_nicknames ("user"); + +CREATE TABLE user_jids( + "user" INTEGER NOT NULL, + jid VARCHAR NOT NULL, + PRIMARY KEY("user", jid), + FOREIGN KEY("user") REFERENCES users +); +CREATE INDEX user_jids_user ON user_jids ("user"); + +CREATE TABLE page_keys( + "user" INTEGER NOT NULL, + page VARCHAR NOT NULL, + "key" DECIMAL NOT NULL, -- (~ NUMERIC DECIMAL) + PRIMARY KEY(page, "key"), + FOREIGN KEY("user") REFERENCES users +); + +CREATE TABLE wiki_pages( + id SERIAL NOT NULL, + PRIMARY KEY(id) +); + +CREATE TABLE wiki_page_revisions( + id SERIAL NOT NULL, + page INTEGER NOT NULL, + date TIMESTAMP WITH TIME ZONE DEFAULT now(), + title VARCHAR, + content VARCHAR, + author INTEGER, + format VARCHAR, + PRIMARY KEY(id), + FOREIGN KEY(page) REFERENCES wiki_pages, + FOREIGN KEY(author) REFERENCES users, + CHECK (format IN ('mulkwiki', 'html5', 'xhtml5', 'markdown', 'textile', 'muse', 'bbcode')) +); + + +CREATE TABLE bookmarks( + id SERIAL NOT NULL, + owner INTEGER, + date TIMESTAMP WITH TIME ZONE DEFAULT now(), + uri VARCHAR NOT NULL, + title VARCHAR, + description VARCHAR, + visibility VARCHAR, + PRIMARY KEY(id), + FOREIGN KEY(owner) REFERENCES users, + CHECK (visibility IN ('private', 'protected', 'public')) +); + +CREATE TABLE bookmark_tags( + bookmark INTEGER NOT NULL, + tag VARCHAR NOT NULL, + PRIMARY KEY(bookmark, tag), + FOREIGN KEY(bookmark) REFERENCES bookmarks +); + + +CREATE TABLE lazychat_messages( + id SERIAL NOT NULL, + author INTEGER, + date TIMESTAMP WITH TIME ZONE DEFAULT now(), + content VARCHAR, + visibility VARCHAR NOT NULL, + format VARCHAR NOT NULL, + PRIMARY KEY(id), + FOREIGN KEY(author) REFERENCES users, + CHECK (format IN ('markdown')), + CHECK (visibility IN ('personal', 'protected', 'public')) +); + +CREATE TABLE lazychat_targets( + message INTEGER NOT NULL, + target INTEGER NOT NULL, + PRIMARY KEY(message, target), + FOREIGN KEY(message) REFERENCES lazychat_messages, + FOREIGN KEY(target) REFERENCES users +); + +CREATE TABLE lazychat_references( + referrer INTEGER NOT NULL, + referee INTEGER NOT NULL, + PRIMARY KEY(referrer, referee), + FOREIGN KEY(referrer) REFERENCES lazychat_messages, + FOREIGN KEY(referee) REFERENCES lazychat_messages +); + +COMMIT; + diff --git a/migrations/1_add_roles.sql b/migrations/1_add_roles.sql new file mode 100644 index 0000000..f55d6bf --- /dev/null +++ b/migrations/1_add_roles.sql @@ -0,0 +1,164 @@ +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; -- cgit v1.2.3