summaryrefslogtreecommitdiff
path: root/migrations/1_add_roles.sql
blob: 802d9f8cf104630a227f2e0ea84a0f61d6fc7f13 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
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 new_user_put_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;