# HG changeset patch # User David A. Holland # Date 1649633789 14400 # Node ID 40f64a96481f95f4aff4616f485709101d783ad0 # Parent 36d91dfe017fd6567c168ce70a4c78dd3c49baae Adjust database user arrangements and grant schema privs. diff -r 36d91dfe017f -r 40f64a96481f database/init/users.sql --- 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; diff -r 36d91dfe017f -r 40f64a96481f database/schema.sql --- 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; diff -r 36d91dfe017f -r 40f64a96481f database/schema/admin.sql --- 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; diff -r 36d91dfe017f -r 40f64a96481f database/schema/bugs.sql --- 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; diff -r 36d91dfe017f -r 40f64a96481f database/schema/classify.sql --- 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; + diff -r 36d91dfe017f -r 40f64a96481f database/schema/config.sql --- 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; diff -r 36d91dfe017f -r 40f64a96481f database/schema/logs.sql --- 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; diff -r 36d91dfe017f -r 40f64a96481f database/schema/messages.sql --- 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; diff -r 36d91dfe017f -r 40f64a96481f database/schema/subscription.sql --- 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; diff -r 36d91dfe017f -r 40f64a96481f database/schema/users.sql --- 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; diff -r 36d91dfe017f -r 40f64a96481f database/schema/version.sql --- 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; diff -r 36d91dfe017f -r 40f64a96481f shelltools/query-pr/query.py --- 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))