From 4519ac40237342e0216f5fe7af2029810edc3a6e Mon Sep 17 00:00:00 2001
From: Matthias Andreas Benkard <code@mail.matthias.benkard.de>
Date: Tue, 3 Jul 2012 11:26:15 +0200
Subject: Lafargue: Migrate to a role-based access control model.

---
 schema.sql | 101 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++---
 1 file changed, 96 insertions(+), 5 deletions(-)

(limited to 'schema.sql')

diff --git a/schema.sql b/schema.sql
index 4170b3b..8934883 100644
--- a/schema.sql
+++ b/schema.sql
@@ -2,6 +2,11 @@
 
 BEGIN TRANSACTION;
 
+CREATE TABLE roles(
+  id SERIAL NOT NULL,
+  PRIMARY KEY(id)
+);
+
 CREATE TABLE users(
   id           SERIAL    NOT NULL,
   first_name   VARCHAR,
@@ -10,8 +15,10 @@ CREATE TABLE users(
   email        VARCHAR,
   website      VARCHAR,
   status       VARCHAR,
+  "role"       INTEGER   NOT NULL,
   PRIMARY KEY(id),
-  CHECK (status IN ('admin', 'approved', 'visitor', 'disabled'))
+  CHECK (status IN ('admin', 'approved', 'visitor', 'disabled')),
+  FOREIGN KEY("role") REFERENCES roles
 );
 
 CREATE TABLE openids(
@@ -66,6 +73,22 @@ CREATE TABLE user_jids(
 );
 CREATE INDEX user_jids_user ON user_jids ("user");
 
+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 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 TABLE page_keys(
   "user" INTEGER NOT NULL,
   page   VARCHAR NOT NULL,
@@ -120,12 +143,10 @@ CREATE TABLE lazychat_messages(
   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'))
+  CHECK (format IN ('markdown'))
 );
 
 CREATE TABLE lazychat_targets(
@@ -133,7 +154,7 @@ CREATE TABLE lazychat_targets(
   target  INTEGER NOT NULL,
   PRIMARY KEY(message, target),
   FOREIGN KEY(message) REFERENCES lazychat_messages,
-  FOREIGN KEY(target)  REFERENCES users
+  FOREIGN KEY(target)  REFERENCES roles
 );
 
 CREATE TABLE lazychat_references(
@@ -144,5 +165,75 @@ CREATE TABLE lazychat_references(
   FOREIGN KEY(referee)  REFERENCES lazychat_messages
 );
 
+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');
+
+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();
+
+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;
+
 ROLLBACK;
 --COMMIT;
-- 
cgit v1.2.3