Prepare database rights

For: cluster operators.

Prepare the restricted role and login rule management uses for an edge's allowed statements.

Database rights

How management runs edges' statements

Edges have no connection to management's database. Management runs each statement an edge sends on a separate pool of connections that log in as a role of their own, oz0_edge_<database> (oz0_edge_oz0 for a database named oz0). The role may touch only the tables edges need, and of the accounts only the columns without the password hash, so the database itself refuses anything else. A statement cannot switch to another role: the role is a member of none. Management makes the role when it starts and sets its password, which it derives from the master key.

What else the role may do, and why:

• Retention locks: retained-history copies and reads use a dedicated read-only view to coordinate with holds and erasures. Management validates the view and its narrow lock grant at startup; its owner and definition must match the installed schema.
• Sequences: it reads them and takes their next values, which the registry's triggers do as an edge writes a node or a key, but it cannot set one, so the registry's version only moves forward and only as the registry changes.
• No objects of its own: it cannot create a schema, a table, or a temporary table, so every name an edge's statement reads is one of the schema's own tables. PostgreSQL gives every role temporary tables through PUBLIC (and, before version 15, tables in the public schema), so management takes those rights from PUBLIC in its database at each start, and refuses to start the internal API when it cannot (see below).
• Its own settings: every session of the role starts with a 30-second limit on each statement, the schema's tables before any temporary one in its search path, and standard strings. Each connection is reset before management uses it for the next statement, so nothing one edge's statement leaves in its session (settings, temporary tables, locks) reaches another's.
• A share of the pool: one server runs at most 4 of the pool's 16 connections outside transactions, and at most 6 transactions, and a transaction ends after 5 minutes, so no one server certificate holds every connection. A statement that finds no place within 3 seconds is answered as if management were busy, and the edge answers from its read cache.
• Only what management knows: before the database sees a statement, management refuses one that is not a single SELECT, WITH, INSERT, UPDATE or DELETE, that makes a table (SELECT ... INTO), calls a function the platform's own statements do not, names a table outside the allowlist, or reads PostgreSQL's catalogs or another schema.
  It finds where each literal, comment and name begins and ends as PostgreSQL's own parser does, so nothing PostgreSQL runs can pass for text inside a literal, and it refuses a statement that it could read otherwise than PostgreSQL: one with a character outside ASCII anywhere but inside a literal or a comment, or with a $ that is neither a parameter nor a dollar quote. A refused statement is logged once on management with its reason.

What the database must allow

Three things, which a server that runs all three roles does not need:

• management's database user needs CREATEROLE, to make and keep the edges' role;
• pg_hba.conf must let oz0_edge_<database> log in from management's hosts using scram-sha-256, as it lets management's own user. Put that permission before any line that would refuse it, and restrict it to the platform's database.
  PostgreSQL gives every role access to connect to, and make temporary tables in, every database through PUBLIC. If the server holds other databases, such as Temporal's, name the database in the line below instead of all. Alternatively, revoke those rights there with REVOKE CONNECT, TEMPORARY ON DATABASE temporal FROM PUBLIC;, after granting CONNECT to the roles that use it. The same holds for the edges' payload role;
• management's database user must own the migration-created tenant table and lifecycle-lock view, so it can validate the lock capability. It should also own the database and its public schema, as it does when oz0 server init creates the database, so it can take temporary tables (and, before PostgreSQL 15, tables in the public schema) from PUBLIC.
  Before version 15 the public schema belongs to the superuser who made the cluster: give it to management's user once, ALTER SCHEMA public OWNER TO oz0;. Other roles in that database, such as the edges' payload role, lose the same rights; none of the platform's needs them.

ALTER ROLE oz0 CREATEROLE;

# TYPE  DATABASE  USER          ADDRESS        METHOD
host    oz0       oz0_edge_oz0  10.0.1.4/32    scram-sha-256

Then SELECT pg_reload_conf();. Check both before the first start, from a management host:

oz0 server init --db-file /etc/oz0/db-url --master-key-file /etc/oz0/master.key --exist-ok --roles-apart

--roles-apart makes the edges' role as management does and logs in as it. Management checks the same at every start and refuses to start without them, with what to change:

the database does not meet the split layout's requirements: management's database user oz0 needs CREATEROLE to make
the edges' role oz0_edge_oz0; as a superuser: ALTER ROLE "oz0" CREATEROLE; ...

the database does not meet the split layout's requirements: the edges' role oz0_edge_oz0 cannot log in to oz0 (...):
pg_hba.conf must let it in with a password from management's hosts, before any line that would refuse it, such as:
    host oz0 oz0_edge_oz0 10.0.1.4/32 scram-sha-256

the database does not meet the split layout's requirements: the edges' role oz0_edge_oz0 may still create tables in the
public schema, which another user owns (...); as a superuser, in database oz0: ALTER SCHEMA public OWNER TO <management's
database user>; ...

The address in the suggested line is the one PostgreSQL sees for management's own connection. Management refuses to take over a role of that name that it did not make and that can log in. Without CREATEROLE, a server that runs all three roles works as before, and edges apart from management cannot reach it.