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

New Issue

2026-10-23T07:13:25Z

cgalo5758 commented

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

What a contributor runs into

Every module's schema has an owner role ({key}_owner), but its tables, views and sequences belong to whichever user ran the migrations. Only core_owner owns anything: the ledger's SECURITY DEFINER functions and, once #23 lands, core.webhook_events with its partitions.

Why it costs

Ownership is tied to a login. Replacing the migration user with an ordinary user that is only a member of the owner roles, which hosted Postgres services require, means moving every object by hand. The upstream design describes the owner roles as the ones that run migrations.

Where

Each module's migrations: core, domains, stripe, fedwiki, discourse
The migration runner (internal/db/database.go, ConnectAndMigrate)
docs/database-management.md
docs/building-an-integration.md

Done when

Every object in a module's schema belongs to its owner role, migrations create objects as that role, and a test fails when an object belongs to anyone else.

### What a contributor runs into Every module's schema has an owner role (`{key}_owner`), but its tables, views and sequences belong to whichever user ran the migrations. Only `core_owner` owns anything: the ledger's `SECURITY DEFINER` functions and, once #23 lands, `core.webhook_events` with its partitions. ### Why it costs Ownership is tied to a login. Replacing the migration user with an ordinary user that is only a member of the owner roles, which hosted Postgres services require, means moving every object by hand. The upstream design describes the owner roles as the ones that run migrations. ### Where - Each module's migrations: core, domains, stripe, fedwiki, discourse - The migration runner (`internal/db/database.go`, `ConnectAndMigrate`) - `docs/database-management.md` - `docs/building-an-integration.md` ### Done when Every object in a module's schema belongs to its owner role, migrations create objects as that role, and a test fails when an object belongs to anyone else.

cgalo5758 added the

kind

debt

area/ops labels 2026-10-23 07:13:25 +00:00

cgalo5758 commented

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

What #23 changed here: core.webhook_events and its partitions now belong to core_owner, and new partitions are created by a SECURITY DEFINER function it owns. The rest still belongs to the migration user.

Moving the rest is not needed for the fence. The console refuses to start when its runtime user owns, or can act as the owner of, the database or any object in it, whoever that owner is, so ownership by the migration user leaves the enclosures binding.

What the move needs, for whoever takes it up:

A per-object ALTER ... OWNER TO {key}_owner loop over every table, view, sequence, function, type and partition, because Postgres refuses REASSIGN OWNED BY for the bootstrap superuser of the official image, which owns them today.
SET membership in each owner role for the migration user, so that it can create objects as that role and alter their owner.
scripts/db-reader-role.sql runs as the owner of the schemas; the move changes who that is, and its instructions with it.

What #23 changed here: `core.webhook_events` and its partitions now belong to `core_owner`, and new partitions are created by a `SECURITY DEFINER` function it owns. The rest still belongs to the migration user. Moving the rest is not needed for the fence. The console refuses to start when its runtime user owns, or can act as the owner of, the database or any object in it, whoever that owner is, so ownership by the migration user leaves the enclosures binding. What the move needs, for whoever takes it up: - A per-object `ALTER ... OWNER TO {key}_owner` loop over every table, view, sequence, function, type and partition, because Postgres refuses `REASSIGN OWNED BY` for the bootstrap superuser of the official image, which owns them today. - `SET` membership in each owner role for the migration user, so that it can create objects as that role and alter their owner. - `scripts/db-reader-role.sql` runs as the owner of the schemas; the move changes who that is, and its instructions with it.

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: wiki-cafe/member-console#252