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

annotate database/schema/users.sql @ 56:42d7888272a0 default tip

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Implement fetch_classifications().
author David A. Holland
date Sun, 10 Apr 2022 19:37:18 -0400
parents 40f64a96481f
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;