Mercurial > ~dholland > hg > swallowtail > index.cgi
annotate database/init/users.sql @ 56:42d7888272a0 default tip
Implement fetch_classifications().
author | David A. Holland |
---|---|
date | Sun, 10 Apr 2022 19:37:18 -0400 |
parents | 40f64a96481f |
children |
rev | line source |
---|---|
8
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
1 -- Database user initialization. |
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
2 -- Needs to be done as database superuser. |
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:
30
diff
changeset
|
4 -- We create the following database-level users: |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
5 -- swallowtail_owner: owns the database tables |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
6 -- swallowtail_admin: used by administrator scripts |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
7 -- swallowtail_writer: has write access for ordinary operations |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
8 -- swallowtail_reader: has readonly access to everything |
30 | 9 -- swallowtail_public: cannot see confidential PRs |
55
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
10 -- swallowtail_uops: has access to the Swallowtail user table |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
11 -- |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
12 -- These are intended to be accessed by Unix-level users as follows: |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
13 -- swallowtail_owner: Swallowtail-level core administrators |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
14 -- swallowtail_admin: Swallowtail-level administrators |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
15 -- swallowtail_writer: all developers (used by edit-pr and browse-pr) |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
16 -- swallowtail_reader: all developers (used by query-pr) |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
17 -- swallowtail_public: the web server interface (used by query-pr) |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
18 -- swallowtail_uops: the logged-in web server interface |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
19 -- |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
20 -- The following principles underlie this: |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
21 -- |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
22 -- 1. All the database tables are created by and owned by |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
23 -- swallowtail_owner; for safety, nothing routine connects as this |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
24 -- database user. Administrators may connect by hand to fix the |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
25 -- database if it breaks, or to apply schema changes for Swallowtail |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
26 -- updates, or to adjust the configuration tables, or for other |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
27 -- similar purposes. |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
28 -- |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
29 -- 2. The tables behind administrative functions are only accessible |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
30 -- to the swallowtail_admin user. This is more to prevent accidents |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
31 -- than because developers with access to the other users are |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
32 -- distrusted. |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
33 -- |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
34 -- 3. All developers have full write access to all bugs via edit-pr, |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
35 -- which connects as the swallowtail_writer user. We don't make a |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
36 -- separate database user for every developer/Unix user because that's |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
37 -- pointless. We trust developers not to trash the database on |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
38 -- purpose. |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
39 -- |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
40 -- 4. The query-pr script connects as the swallowtail_reader user, |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
41 -- which has no write access to anything. This is to prevent accidents. |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
42 -- It is thus theoretically possible to grant some Unix users read but |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
43 -- not write access, but I hope we never have to do that. |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
44 -- |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
45 -- 5. When running via the web interface (and passed the --paranoid |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
46 -- option), query-pr connects as the swallowtail_public user. This |
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
47 -- prevents it (via restricted views) from seeing confidential PRs. |
30 | 48 -- |
8
68cc276ac118
SQL material from old tree, split up for accessibility.
David A. Holland
parents:
diff
changeset
|
49 |
55
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
50 create user swallowtail_owner; |
30 | 51 create user swallowtail_admin; |
52 create user swallowtail_writer; | |
55
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
53 create user swallowtail_reader; |
30 | 54 create user swallowtail_public; |
55
40f64a96481f
Adjust database user arrangements and grant schema privs.
David A. Holland
parents:
30
diff
changeset
|
55 create user swallowtail_uops; |