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

annotate database/init/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 -- 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
90ec9e3b0a6f add more users
David A. Holland
parents: 28
diff changeset
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
90ec9e3b0a6f add more users
David A. Holland
parents: 28
diff changeset
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
90ec9e3b0a6f add more users
David A. Holland
parents: 28
diff changeset
51 create user swallowtail_admin;
90ec9e3b0a6f add more users
David A. Holland
parents: 28
diff changeset
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
90ec9e3b0a6f add more users
David A. Holland
parents: 28
diff changeset
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;