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