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;
|
