Mercurial > ~dholland > hg > swallowtail > index.cgi

annotate database/schema/users.sql @ 55:40f64a96481f

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Adjust database user arrangements and grant schema privs.
author David A. Holland
date Sun, 10 Apr 2022 19:36:29 -0400
parents 12a4c9f88510
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
8
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
1 --
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
2 -- Users.
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
3 --
55
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 31
diff changeset
4 -- (That is, Swallowtail-level users.)
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 31
diff changeset
5 --
8
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
6 -- Rather than trying to have different kinds of users (as in the last
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
7 -- rev of this stuff) I think we will just have user permissions.
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
8 -- Otherwise we end up in various kinds of trouble if users change
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
9 -- type. This means we cannot use foreign key constraints to e.g.
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
10 -- ensure that non-developers are not responsible for bugs; however,
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
11 -- I guess we can cope.
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
12 --
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
13 -- The permissions are:
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
14 -- mailto we can mail to the user's e-mail address on file
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
15 -- oldresponsible allowed to be responsible for closed PRs
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
16 -- responsible allowed to be responsible for PRs
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
17 -- editpr can do edit-pr things
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
18 -- admin can do destructive things
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
19 --
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
20 -- The following types of users should have permissions as follows:
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
21 -- peon none or mailto
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
22 -- submitter mailto
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
23 -- role mailto|oldresponsible|responsible
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
24 -- developer mailto|oldresponsible|responsible|editpr
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
25 -- administrator mailto|oldresponsible|responsible|editpr|admin
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
26 -- retired devel mailto|oldresponsible
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
27 -- deceased devel oldresponsible
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
28 --
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
29 -- However, this may vary; e.g. users whose mail address bounces might
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
30 -- have their mailto permission revoked.
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
31 --
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
32 -- The "webpassword" is for use logging in over unsecured HTTP to do
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
33 -- things submitters can do. It is assumed that developers (and
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
34 -- administrators, who should all be developers) authenticate some
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
35 -- other way.
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
36 --
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
37
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
38 CREATE SEQUENCE next_user;
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
39
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
40 CREATE TABLE users (
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
41 id bigint primary key default nextval('next_user'),
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
42 username text ,
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
43 realname text ,
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
44
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
45 -- permissions --
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
46 mailto boolean not null,
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
47 oldresponsible boolean not null,
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
48 responsible boolean not null,
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
49 editpr boolean not null,
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
50 admin boolean not null
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
51
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
52 -- responsible implies oldresponsible
29
David A. Holland
parents: 12
diff changeset
53 check (oldresponsible OR NOT responsible)
8
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
54 )
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
55 WITHOUT OIDS;
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
56
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
57 CREATE TABLE mailaddresses (
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
58 id bigint not null references users (id),
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
59 selected boolean not null,
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
60 email text not null,
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
61 organization text ,
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
62 webpassword text ,
12
d4c3bd255653 "timestamp", not "date". from asau
David A. Holland
parents: 8
diff changeset
63 lastheardfrom timestamp
8
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
64 )
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
65 WITHOUT OIDS;
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
66
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
67 CREATE VIEW usermail AS
31
12a4c9f88510 accidentally left "organization" out of usermail
David A. Holland
parents: 29
diff changeset
68 SELECT users.id AS id, username, realname, email, organization,
12a4c9f88510 accidentally left "organization" out of usermail
David A. Holland
parents: 29
diff changeset
69 mailto, oldresponsible, responsible, editpr, admin
8
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
70 FROM users, mailaddresses
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
71 WHERE users.id = mailaddresses.id AND mailaddresses.selected
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
72 ;
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
73
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
74 -- Intended constraint:
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
75 -- (oldresponsible OR responsible OR editpr OR admin)
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
76 -- implies username not null.
68cc276ac118 SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff changeset
77
55
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 31
diff changeset
78 -- Admins need to be able to adjust user data freely.
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 31
diff changeset
79 GRANT SELECT, INSERT, UPDATE, DELETE ON users TO swallowtail_admin;
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 31
diff changeset
80 GRANT SELECT, INSERT, UPDATE, DELETE ON mailaddresses TO swallowtail_admin;
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 31
diff changeset
81 GRANT SELECT ON usermail TO swallowtail_admin;
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 31
diff changeset
82
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 31
diff changeset
83 -- Writers need to see the permission bits.
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 31
diff changeset
84 GRANT SELECT ON users TO swallowtail_writer;
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 31
diff changeset
85
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 31
diff changeset
86 -- Readers only need to be able to print.
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 31
diff changeset
87 -- XXX: deny public access to emails?
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 31
diff changeset
88 GRANT SELECT ON usermail TO swallowtail_reader;
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 31
diff changeset
89 GRANT SELECT ON usermail TO swallowtail_public;
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 31
diff changeset
90
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 31
diff changeset
91 -- The user operations interface needs to manage email addresses.
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 31
diff changeset
92 GRANT SELECT ON users TO swallowtail_uops;
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 31
diff changeset
93 GRANT UPDATE (realname) ON users TO swallowtail_uops;
40f64a96481f Adjust database user arrangements and grant schema privs.
David A. Holland
parents: 31
diff changeset
94 GRANT SELECT, INSERT, UPDATE, DELETE on mailaddresses to swallowtail_uops;