--- a/database/init/users.sql	Sun Apr 10 17:41:24 2022 -0400
+++ b/database/init/users.sql	Sun Apr 10 19:36:29 2022 -0400
@@ -1,14 +1,55 @@
 -- Database user initialization.
 -- Needs to be done as database superuser.
 --
--- The users:
---    swallowtail_admin: owns the database tables
---    swallowtail_writer: has write access
---    swallowtail: has readonly access to everything
+-- We create the following database-level users:
+--    swallowtail_owner: owns the database tables
+--    swallowtail_admin: used by administrator scripts
+--    swallowtail_writer: has write access for ordinary operations
+--    swallowtail_reader: has readonly access to everything
 --    swallowtail_public: cannot see confidential PRs
+--    swallowtail_uops: has access to the Swallowtail user table
+--
+-- These are intended to be accessed by Unix-level users as follows:
+--    swallowtail_owner: Swallowtail-level core administrators
+--    swallowtail_admin: Swallowtail-level administrators
+--    swallowtail_writer: all developers (used by edit-pr and browse-pr)
+--    swallowtail_reader: all developers (used by query-pr)
+--    swallowtail_public: the web server interface (used by query-pr)
+--    swallowtail_uops: the logged-in web server interface
+--
+-- The following principles underlie this:
+--
+-- 1. All the database tables are created by and owned by
+-- swallowtail_owner; for safety, nothing routine connects as this
+-- database user. Administrators may connect by hand to fix the
+-- database if it breaks, or to apply schema changes for Swallowtail
+-- updates, or to adjust the configuration tables, or for other
+-- similar purposes.
+--
+-- 2. The tables behind administrative functions are only accessible
+-- to the swallowtail_admin user. This is more to prevent accidents
+-- than because developers with access to the other users are
+-- distrusted.
+--
+-- 3. All developers have full write access to all bugs via edit-pr,
+-- which connects as the swallowtail_writer user. We don't make a
+-- separate database user for every developer/Unix user because that's
+-- pointless. We trust developers not to trash the database on
+-- purpose.
+--
+-- 4. The query-pr script connects as the swallowtail_reader user,
+-- which has no write access to anything. This is to prevent accidents.
+-- It is thus theoretically possible to grant some Unix users read but
+-- not write access, but I hope we never have to do that.
+--
+-- 5. When running via the web interface (and passed the --paranoid
+-- option), query-pr connects as the swallowtail_public user. This
+-- prevents it (via restricted views) from seeing confidential PRs.
 --
 
+create user swallowtail_owner;
 create user swallowtail_admin;
 create user swallowtail_writer;
-create user swallowtail;
+create user swallowtail_reader;
 create user swallowtail_public;
+create user swallowtail_uops;

--- a/database/schema.sql	Sun Apr 10 17:41:24 2022 -0400
+++ b/database/schema.sql	Sun Apr 10 19:36:29 2022 -0400
@@ -1,7 +1,7 @@
 -- Load the database schema.
--- Should be done as the swallowtail_admin user.
+-- Should be done as the swallowtail_owner user.
 
-begin;
+BEGIN;
 
 \ir schema/version.sql
 \ir schema/config.sql
@@ -18,7 +18,4 @@
 
 \ir schema/admin.sql
 
-commit;
-
-
-
+COMMIT;

--- a/database/schema/admin.sql	Sun Apr 10 17:41:24 2022 -0400
+++ b/database/schema/admin.sql	Sun Apr 10 19:36:29 2022 -0400
@@ -33,3 +33,8 @@
 	userid bigint		null references users
 )
 WITHOUT OIDS;
+
+-- adminmailtypes is fixed
+-- adminmailqueue is append-only except to admins
+GRANT SELECT, INSERT, UPDATE, DELETE ON adminmailqueue TO swallowtail_admin;
+GRANT INSERT ON attachments TO swallowtail_writer;

--- a/database/schema/bugs.sql	Sun Apr 10 17:41:24 2022 -0400
+++ b/database/schema/bugs.sql	Sun Apr 10 19:36:29 2022 -0400
@@ -74,3 +74,9 @@
 
 )
 WITHOUT OIDS;
+
+-- Only the admin can delete PRs.
+GRANT SELECT, INSERT, UPDATE, DELETE ON PRs TO swallowtail_admin;
+GRANT SELECT, INSERT, UPDATE ON PRs TO swallowtail_writer;
+GRANT SELECT ON PRs TO swallowtail_reader;
+GRANT SELECT ON PRs TO swallowtail_public;

--- a/database/schema/classify.sql	Sun Apr 10 17:41:24 2022 -0400
+++ b/database/schema/classify.sql	Sun Apr 10 19:36:29 2022 -0400
@@ -355,3 +355,94 @@
    ORDER BY schemeordering
 ;
 
+------------------------------------------------------------
+-- permissions
+
+-- Only administrators can add or rearrange schemes.
+GRANT SELECT, INSERT, UPDATE, DELETE ON hierclass_names TO swallowtail_admin;
+GRANT SELECT, INSERT, UPDATE, DELETE ON hierclass_values TO swallowtail_admin;
+GRANT SELECT, INSERT, UPDATE, DELETE ON flatclass_names TO swallowtail_admin;
+GRANT SELECT, INSERT, UPDATE, DELETE ON flatclass_values TO swallowtail_admin;
+GRANT SELECT, INSERT, UPDATE, DELETE ON textclass_names TO swallowtail_admin;
+GRANT SELECT, INSERT, UPDATE, DELETE ON tagclass_names TO swallowtail_admin;
+GRANT SELECT, INSERT, UPDATE, DELETE ON tagclass_values TO swallowtail_admin;
+
+GRANT SELECT ON hierclass_names TO swallowtail_writer;
+GRANT SELECT ON hierclass_values TO swallowtail_writer;
+GRANT SELECT ON flatclass_names TO swallowtail_writer;
+GRANT SELECT ON flatclass_values TO swallowtail_writer;
+GRANT SELECT ON textclass_names TO swallowtail_writer;
+GRANT SELECT ON tagclass_names TO swallowtail_writer;
+GRANT SELECT ON tagclass_values TO swallowtail_writer;
+
+GRANT SELECT ON hierclass_names TO swallowtail_reader;
+GRANT SELECT ON hierclass_values TO swallowtail_reader;
+GRANT SELECT ON flatclass_names TO swallowtail_reader;
+GRANT SELECT ON flatclass_values TO swallowtail_reader;
+GRANT SELECT ON textclass_names TO swallowtail_reader;
+GRANT SELECT ON tagclass_names TO swallowtail_reader;
+GRANT SELECT ON tagclass_values TO swallowtail_reader;
+
+GRANT SELECT ON hierclass_names TO swallowtail_public;
+GRANT SELECT ON hierclass_values TO swallowtail_public;
+GRANT SELECT ON flatclass_names TO swallowtail_public;
+GRANT SELECT ON flatclass_values TO swallowtail_public;
+GRANT SELECT ON textclass_names TO swallowtail_public;
+GRANT SELECT ON tagclass_names TO swallowtail_public;
+GRANT SELECT ON tagclass_values TO swallowtail_public;
+
+-- The data, however, is ordinarily accessible.
+GRANT SELECT, INSERT, UPDATE, DELETE ON hierclass_data TO swallowtail_admin;
+GRANT SELECT, INSERT, UPDATE, DELETE ON flatclass_data TO swallowtail_admin;
+GRANT SELECT, INSERT, UPDATE, DELETE ON textclass_data TO swallowtail_admin;
+GRANT SELECT, INSERT, UPDATE, DELETE ON tagclass_data TO swallowtail_admin;
+GRANT SELECT, INSERT, UPDATE, DELETE ON tagclass_data TO swallowtail_admin;
+
+GRANT SELECT, INSERT, UPDATE, DELETE ON hierclass_data TO swallowtail_writer;
+GRANT SELECT, INSERT, UPDATE, DELETE ON flatclass_data TO swallowtail_writer;
+GRANT SELECT, INSERT, UPDATE, DELETE ON textclass_data TO swallowtail_writer;
+GRANT SELECT, INSERT, UPDATE, DELETE ON tagclass_data TO swallowtail_writer;
+GRANT SELECT, INSERT, UPDATE, DELETE ON tagclass_data TO swallowtail_writer;
+
+GRANT SELECT ON hierclass_data TO swallowtail_reader, swallowtail_public;
+GRANT SELECT ON flatclass_data TO swallowtail_reader, swallowtail_public;
+GRANT SELECT ON textclass_data TO swallowtail_reader, swallowtail_public;
+GRANT SELECT ON tagclass_data TO swallowtail_reader, swallowtail_public;
+GRANT SELECT ON tagclass_data TO swallowtail_reader, swallowtail_public;
+
+-- The views are generally readable.
+GRANT SELECT ON tagclass_stringdata TO swallowtail_admin;
+GRANT SELECT ON tagclass_stringdata TO swallowtail_writer;
+GRANT SELECT ON tagclass_stringdata TO swallowtail_reader;
+GRANT SELECT ON tagclass_stringdata TO swallowtail_public;
+
+GRANT SELECT ON hierclass_data_ordered TO swallowtail_admin;
+GRANT SELECT ON hierclass_data_ordered TO swallowtail_writer;
+GRANT SELECT ON hierclass_data_ordered TO swallowtail_reader;
+GRANT SELECT ON hierclass_data_ordered TO swallowtail_public;
+
+GRANT SELECT ON flatclass_data_ordered TO swallowtail_admin;
+GRANT SELECT ON flatclass_data_ordered TO swallowtail_writer;
+GRANT SELECT ON flatclass_data_ordered TO swallowtail_reader;
+GRANT SELECT ON flatclass_data_ordered TO swallowtail_public;
+
+GRANT SELECT ON textclass_data_ordered TO swallowtail_admin;
+GRANT SELECT ON textclass_data_ordered TO swallowtail_writer;
+GRANT SELECT ON textclass_data_ordered TO swallowtail_reader;
+GRANT SELECT ON textclass_data_ordered TO swallowtail_public;
+
+GRANT SELECT ON tagclass_data_ordered TO swallowtail_admin;
+GRANT SELECT ON tagclass_data_ordered TO swallowtail_writer;
+GRANT SELECT ON tagclass_data_ordered TO swallowtail_reader;
+GRANT SELECT ON tagclass_data_ordered TO swallowtail_public;
+
+GRANT SELECT ON tagclass_stringdata_ordered TO swallowtail_admin;
+GRANT SELECT ON tagclass_stringdata_ordered TO swallowtail_writer;
+GRANT SELECT ON tagclass_stringdata_ordered TO swallowtail_reader;
+GRANT SELECT ON tagclass_stringdata_ordered TO swallowtail_public;
+
+GRANT SELECT ON classifications TO swallowtail_admin;
+GRANT SELECT ON classifications TO swallowtail_writer;
+GRANT SELECT ON classifications TO swallowtail_reader;
+GRANT SELECT ON classifications TO swallowtail_public;
+

--- a/database/schema/config.sql	Sun Apr 10 17:41:24 2022 -0400
+++ b/database/schema/config.sql	Sun Apr 10 19:36:29 2022 -0400
@@ -68,3 +68,9 @@
        nagsubmitter boolean	not null,
        timeout boolean		not null
 ) WITHOUT OIDS;
+
+-- These are readonly for all routine operations.
+GRANT SELECT ON states TO swallowtail_admin;
+GRANT SELECT ON states TO swallowtail_writer;
+GRANT SELECT ON states TO swallowtail_reader;
+GRANT SELECT ON states TO swallowtail_public;

--- a/database/schema/logs.sql	Sun Apr 10 17:41:24 2022 -0400
+++ b/database/schema/logs.sql	Sun Apr 10 19:36:29 2022 -0400
@@ -11,3 +11,11 @@
 	msgid bigint		null references messages (id)
 )
 WITHOUT OIDS;
+
+-- We don't ordinarily edit or delete log entries. If you really need
+-- to do that for some reason (e.g. mispasted passwords) log in as
+-- swallowtail_owner.
+GRANT SELECT, INSERT ON adminlog TO swallowtail_admin;
+GRANT SELECT, INSERT ON adminlog TO swallowtail_writer;
+GRANT SELECT ON adminlog TO swallowtail_reader;
+GRANT SELECT ON adminlog TO swallowtail_public;

--- a/database/schema/messages.sql	Sun Apr 10 17:41:24 2022 -0400
+++ b/database/schema/messages.sql	Sun Apr 10 19:36:29 2022 -0400
@@ -53,3 +53,16 @@
 -- uniqueness and to enable lookup.
 CREATE UNIQUE INDEX ON messages (pr, number_in_pr);
 CREATE UNIQUE INDEX ON attachments (msgid, number_in_msg);
+
+-- The raw mail queue is append-only except to admins.
+GRANT SELECT, INSERT, UPDATE, DELETE ON rawmail TO swallowtail_admin;
+GRANT INSERT ON rawmail TO swallowtail_writer;
+
+-- Messages and attachments are generically accessible, but we don't
+-- allow ordinary writers to modify them. (XXX: is that silly?)
+GRANT SELECT, INSERT, UPDATE, DELETE ON messages TO swallowtail_admin;
+GRANT SELECT, INSERT, UPDATE, DELETE ON attachments TO swallowtail_admin;
+GRANT SELECT, INSERT ON messages TO swallowtail_writer;
+GRANT SELECT, INSERT ON attachments TO swallowtail_writer;
+GRANT SELECT ON messages, attachments TO swallowtail_reader;
+GRANT SELECT ON messages, attachments TO swallowtail_public;

--- a/database/schema/subscription.sql	Sun Apr 10 17:41:24 2022 -0400
+++ b/database/schema/subscription.sql	Sun Apr 10 19:36:29 2022 -0400
@@ -42,3 +42,9 @@
 --    ;
 --
 -- should always return nothing.
+
+-- Generically accessible.
+GRANT SELECT, INSERT, UPDATE, DELETE ON subscriptions TO swallowtail_admin;
+GRANT SELECT, INSERT, UPDATE, DELETE ON subscriptions TO swallowtail_writer;
+GRANT SELECT ON subscriptions TO swallowtail_reader;
+GRANT SELECT ON subscriptions TO swallowtail_public;

--- a/database/schema/users.sql	Sun Apr 10 17:41:24 2022 -0400
+++ b/database/schema/users.sql	Sun Apr 10 19:36:29 2022 -0400
@@ -1,6 +1,8 @@
 --
 -- Users.
 --
+-- (That is, Swallowtail-level users.)
+--
 -- Rather than trying to have different kinds of users (as in the last
 -- rev of this stuff) I think we will just have user permissions.
 -- Otherwise we end up in various kinds of trouble if users change
@@ -73,3 +75,20 @@
 -- (oldresponsible OR responsible OR editpr OR admin)
 -- implies username not null.
 
+-- Admins need to be able to adjust user data freely.
+GRANT SELECT, INSERT, UPDATE, DELETE ON users TO swallowtail_admin;
+GRANT SELECT, INSERT, UPDATE, DELETE ON mailaddresses TO swallowtail_admin;
+GRANT SELECT ON usermail TO swallowtail_admin;
+
+-- Writers need to see the permission bits.
+GRANT SELECT ON users TO swallowtail_writer;
+
+-- Readers only need to be able to print.
+-- XXX: deny public access to emails?
+GRANT SELECT ON usermail TO swallowtail_reader;
+GRANT SELECT ON usermail TO swallowtail_public;
+
+-- The user operations interface needs to manage email addresses.
+GRANT SELECT ON users TO swallowtail_uops;
+GRANT UPDATE (realname) ON users TO swallowtail_uops;
+GRANT SELECT, INSERT, UPDATE, DELETE on mailaddresses to swallowtail_uops;

--- a/database/schema/version.sql	Sun Apr 10 17:41:24 2022 -0400
+++ b/database/schema/version.sql	Sun Apr 10 19:36:29 2022 -0400
@@ -11,3 +11,9 @@
        version int	not null
 )
 WITHOUT OIDS;
+
+-- This is readonly except for system updates.
+GRANT SELECT ON schemainfo TO swallowtail_admin;
+GRANT SELECT ON schemainfo TO swallowtail_writer;
+GRANT SELECT ON schemainfo TO swallowtail_reader;
+GRANT SELECT ON schemainfo TO swallowtail_public;

--- a/shelltools/query-pr/query.py	Sun Apr 10 17:41:24 2022 -0400
+++ b/shelltools/query-pr/query.py	Sun Apr 10 19:36:29 2022 -0400
@@ -239,11 +239,11 @@
 
 dblink = None
 
-def opendb():
+def opendb(paranoid):
         global dblink
 
         host = "localhost"
-        user = "swallowtail"
+        user = "swallowtail_public" if paranoid else "swallowtail_reader"
         database = "swallowtail"
         dblink = psycopg2.connect("host=%s user=%s dbname=%s" %
                                         (host, user, database))