The console connects to its database as a superuser, so the privilege revokes bind nothing #23

New Issue

2026-09-21T07:18:08Z

cgalo5758 commented

2026-09-21 07:18:08 +00:00

What happens

The migrations that enclose the entitlement ledger revoke direct writes on those tables from the writer role, so that only the enclosed functions can change them, and the console's login is granted that role. The login the console actually connects with is the database container's bootstrap user, which the database creates as a superuser, and a superuser bypasses every privilege check. The revokes are therefore inert wherever the console runs, in the test stack and in a deployment created the same way.

What should happen

The console connects as an ordinary role, so a stray write to an enclosed table is refused by the database rather than by convention, and the enclosure means what the schema says it means.

Where

The migration that creates the roles and the console's login; the test stack's compose file, where the login is the bootstrap user; the deployment documentation.

Steps

Connect as the console's configured login and write a row directly into one of the enclosed tables. The write succeeds. Set the role to the writer role first and the same write is refused, which is what the existing test does.

Why it matters

The enclosure is the last defence under the ledger and the reason the enclosed functions are the only documented way in. Today it holds only inside a test that sets the role by hand. The fix is a non-superuser login for the console in both stacks, the bootstrap superuser kept for migrations and operations, and a boot-time refusal to start as a superuser outside development mode.

Migrated from status/issues.md at b7a0e15

## What happens The migrations that enclose the entitlement ledger revoke direct writes on those tables from the writer role, so that only the enclosed functions can change them, and the console's login is granted that role. The login the console actually connects with is the database container's bootstrap user, which the database creates as a superuser, and a superuser bypasses every privilege check. The revokes are therefore inert wherever the console runs, in the test stack and in a deployment created the same way. ## What should happen The console connects as an ordinary role, so a stray write to an enclosed table is refused by the database rather than by convention, and the enclosure means what the schema says it means. ## Where The migration that creates the roles and the console's login; the test stack's compose file, where the login is the bootstrap user; the deployment documentation. ## Steps Connect as the console's configured login and write a row directly into one of the enclosed tables. The write succeeds. Set the role to the writer role first and the same write is refused, which is what the existing test does. ## Why it matters The enclosure is the last defence under the ledger and the reason the enclosed functions are the only documented way in. Today it holds only inside a test that sets the role by hand. The fix is a non-superuser login for the console in both stacks, the bootstrap superuser kept for migrations and operations, and a boot-time refusal to start as a superuser outside development mode. Migrated from status/issues.md at b7a0e15

cgalo5758 added this to the Public launch milestone 2026-09-21 07:18:08 +00:00

cgalo5758 added the

kind

bug

area/ops security labels 2026-09-21 07:18:08 +00:00

cgalo5758 referenced this issue

2026-10-23 07:09:49 +00:00

The `env` setting only picks the log format, and the image sets an `ENV` the console never reads #251

cgalo5758 referenced this issue

2026-10-23 07:13:25 +00:00

Objects in each schema belong to the user that runs migrations, not to the schema's owner role #252

cgalo5758 referenced this issue from a commit

2026-10-23 09:56:57 +00:00

Grant every writer to one runtime role, and leave writers only the four writes

cgalo5758 referenced this issue from a commit

2026-10-23 10:06:44 +00:00

Read the database's -file key in every command that opens the database

cgalo5758 referenced this issue from a commit

2026-10-23 10:53:09 +00:00

Create webhook partitions and take the plan-act obligation lock through core_owner

cgalo5758 referenced this issue from a commit

2026-10-23 12:01:29 +00:00

Run the console on a runtime user and migrate on a second connection string

cgalo5758 referenced this issue from a commit

2026-10-23 13:43:48 +00:00

Refuse to start on a runtime user that can get around the grants

cgalo5758 referenced this issue from a commit

2026-10-23 13:44:00 +00:00

Document the two database users and how an existing deployment moves to them

cgalo5758 commented

2026-10-23 13:44:21 +00:00

Fixed in 028e2ef4, 40ce2d3d, 3cdf7bbc, f9c9b322, dd4a0e2e and e722e436.

The console now connects as two users. db-dsn names a runtime user that serves every request: it is not a superuser, owns nothing, and holds its privileges through console_runtime, a group role every module's writer is granted to. The new db-migrate-dsn names the user that runs migrations; at start the console migrates on that connection, grants console_runtime to the runtime user (inherited, without SET), and closes it before serving. The revokes that enclose the entitlement ledger now bind the console itself.

The console refuses to start, with no exemption, on a configuration that would reopen the bypass: one user for both strings, a runtime user that is a superuser or can become one, owns the database or any object in it or can act as their owner, can create objects in the database or its schemas, holds any role but console_runtime, or holds a privilege an enclosure withholds. Each refusal names the statement that fixes it. The checks that read nothing the migrations create run before any migration, and every check query runs with search_path pinned to pg_catalog, pg_temp and qualified names, so nothing the runtime user could plant runs in the migration user's session. With db-migrate-dsn left out, the console runs no migration and refuses while the schema is behind, so a host can run migrate up as a separate step and keep the migration password out of the running process.

Four more fixes came with it, each listed in the change:

The writer roles held ALL on their tables, which included TRIGGER; a user holding a writer could put a trigger on an ordinary table that a SECURITY DEFINER function writes, and the trigger then ran as the function's owner and wrote the enclosed tables directly. The writers now hold only SELECT, INSERT, UPDATE and DELETE, and a catalog test fails on anything more.
The plan-act obligation move took its row lock with FOR UPDATE on an enclosed table, which needs UPDATE; it now takes the same lock through a function owned by core_owner. Monthly webhook partitions are created the same way, since creating a partition needs the parent table's owner.
The core baseline's initial webhook partitions are computed in UTC, as every later partition is.
migrate, config and seed-demo read the -file form of the database setting, as start did.

A deployment changes its configuration to upgrade: the next release refuses a superuser db-dsn. docs/production-deployment.md has the steps for a deployment that has connected as one login so far; the bootstrap superuser of the official image cannot lose SUPERUSER, so it stays as the migration user.

Fixed in 028e2ef4, 40ce2d3d, 3cdf7bbc, f9c9b322, dd4a0e2e and e722e436. The console now connects as two users. `db-dsn` names a runtime user that serves every request: it is not a superuser, owns nothing, and holds its privileges through `console_runtime`, a group role every module's writer is granted to. The new `db-migrate-dsn` names the user that runs migrations; at start the console migrates on that connection, grants `console_runtime` to the runtime user (inherited, without `SET`), and closes it before serving. The revokes that enclose the entitlement ledger now bind the console itself. The console refuses to start, with no exemption, on a configuration that would reopen the bypass: one user for both strings, a runtime user that is a superuser or can become one, owns the database or any object in it or can act as their owner, can create objects in the database or its schemas, holds any role but `console_runtime`, or holds a privilege an enclosure withholds. Each refusal names the statement that fixes it. The checks that read nothing the migrations create run before any migration, and every check query runs with `search_path` pinned to `pg_catalog, pg_temp` and qualified names, so nothing the runtime user could plant runs in the migration user's session. With `db-migrate-dsn` left out, the console runs no migration and refuses while the schema is behind, so a host can run `migrate up` as a separate step and keep the migration password out of the running process. Four more fixes came with it, each listed in the change: - The writer roles held `ALL` on their tables, which included `TRIGGER`; a user holding a writer could put a trigger on an ordinary table that a `SECURITY DEFINER` function writes, and the trigger then ran as the function's owner and wrote the enclosed tables directly. The writers now hold only `SELECT`, `INSERT`, `UPDATE` and `DELETE`, and a catalog test fails on anything more. - The plan-act obligation move took its row lock with `FOR UPDATE` on an enclosed table, which needs `UPDATE`; it now takes the same lock through a function owned by `core_owner`. Monthly webhook partitions are created the same way, since creating a partition needs the parent table's owner. - The core baseline's initial webhook partitions are computed in UTC, as every later partition is. - `migrate`, `config` and `seed-demo` read the `-file` form of the database setting, as `start` did. A deployment changes its configuration to upgrade: the next release refuses a superuser `db-dsn`. `docs/production-deployment.md` has the steps for a deployment that has connected as one login so far; the bootstrap superuser of the official image cannot lose `SUPERUSER`, so it stays as the migration user.

cgalo5758 closed this issue

2026-10-23 13:44:28 +00:00

cgalo5758 referenced this issue

2026-10-23 13:44:29 +00:00

Objects in each schema belong to the user that runs migrations, not to the schema's owner role #252

cgalo5758 referenced this issue from a commit

2026-10-23 13:45:03 +00:00

Tick #23's landing tasks

cgalo5758 referenced this issue from a commit

2026-10-24 06:17:23 +00:00

Archive the runtime database user and the boot that serves first

Sign in to join this conversation.