Database roles

On Postgres, Manablox can use two database roles instead of one:

Role	Used by	Rights
Owner (manablox_owner in the compose file)	the migrations: manablox migrate, the migrate container, manablox migrate-db	owns the database, the schema and every table
App (manablox_app)	the CMS at runtime: the management API, the public API and the site process	reads and writes rows, creates no tables, never deletes or changes audit entries

The production compose file of the CMS repository and projects generated by manablox create set this up; the development stack of the CMS repository keeps one role.

Why

The audit log is append-only. A trigger refuses every UPDATE and DELETE on audit_entries, and retention pruning deletes only through the function audit_entries_prune. With one role, that role owns the table: it could switch the trigger off, or set the flag the pruning function sets, and delete entries. The trigger stops accidents, not a role that means to delete.

With two roles, the app role holds no UPDATE, DELETE or TRUNCATE right on audit_entries and owns nothing it could alter. Pruning still works: audit_entries_prune runs with the owner’s rights (SECURITY DEFINER), and the app role may call it. The trigger stays as a second guard. A leaked app connection string, or a bug that runs arbitrary SQL, can then neither rewrite the log nor change the schema.

How it works

Set two URLs:

Variable	Role
DATABASE_URL	the app role, the only URL the CMS connects with
MIGRATION_DATABASE_URL	the owner role, used only to migrate

The config field is database.migrationUrl; databaseConfigFromEnv() reads it from MIGRATION_DATABASE_URL.

When MIGRATION_DATABASE_URL is set, every migration run connects as the owner, applies the migrations, then asks the server which role DATABASE_URL logs in as (current_user) and grants it:

• SELECT, INSERT, UPDATE and DELETE on every table in public, and the same by default for tables the owner creates later;
• USAGE and SELECT on every sequence, now and later;
• SELECT on drizzle.__drizzle_migrations, so the control API can report the migration state;
• EXECUTE on audit_entries_prune, which no other role may run;
• no UPDATE, DELETE or TRUNCATE on audit_entries.

It also revokes CREATE on the schema public from everyone, so the app role creates no tables. The grants are applied again after every migration run, so a table a new release adds is covered even where the default privileges missed it. Running them twice changes nothing.

The role is read from the connection rather than from a setting, so it is always the role the CMS really uses, whatever the URL encodes or a pooler maps. A migration run stops with an error when the app role is the owner role, a superuser, or owns a table: in each case the grants would not hold.

manablox migrate-db --to <owner-url> grants the app role the same way after it created the schema. Pass --app-role manablox_app, or leave it out to grant the roles that had these rights on the target before, for example when --replace recreates its schema.

Nothing at runtime needs more than the app role: the extensions (ltree, pg_trgm, btree_gin) are created by the migrations, locks are advisory locks, and sequences are reset only by migrate-db, as the owner.

Without MIGRATION_DATABASE_URL, one role does both. A management process with NODE_ENV=production on Postgres logs a warning once at start. SQLite has no roles; it ignores MIGRATION_DATABASE_URL and a migration run says so.

The production compose file

The CMS repository’s docker/compose.yml creates both roles on the first start of an empty Postgres volume, with the init script docker/postgres-init/10-roles.sh, and hands the URLs out (a project from manablox create does the same with postgres/init/10-roles.sh):

Variable	Default	Purpose
POSTGRES_USER, POSTGRES_PASSWORD	manablox, required	The superuser, for administration only
DATABASE_OWNER_USER	manablox_owner	The owner role; owns the database and the schema public
DATABASE_OWNER_PASSWORD	required	Its password
DATABASE_APP_USER	manablox_app	The app role, LOGIN, owns nothing
DATABASE_APP_PASSWORD	required	Its password

The migrate service gets DATABASE_URL for the app role and MIGRATION_DATABASE_URL for the owner, and grants the app role on every run. The api service gets the same two, and the public API and the site process connect as the app role unless PUBLIC_DATABASE_URL or SITE_DATABASE_URL name a read-only role.

Your own Postgres

Create the roles once as a superuser, then point the two URLs at them:

create role manablox_owner login password 'owner-secret';
create role manablox_app login password 'app-secret';
create database manablox owner manablox_owner;
\c manablox
alter schema public owner to manablox_owner;

DATABASE_URL=postgres://manablox_app:app-secret@db:5432/manablox
MIGRATION_DATABASE_URL=postgres://manablox_owner:owner-secret@db:5432/manablox
npx manablox migrate

The app role must not own anything in the database and must not be a superuser. The owner role needs CREATE on the database for the extensions, which owning it gives.

Moving from one role to two

An instance that runs with one role owning everything (every role in one DATABASE_URL) can split it later. Keep that role as the owner and add an app role; nothing is reassigned.

1. Create the app role as a superuser: create role manablox_app login password 'app-secret';
2. Set MIGRATION_DATABASE_URL to the URL you use today, and DATABASE_URL to the app role.
3. Run the migrations once (npx manablox migrate, or docker compose run --rm migrate). They grant the app role.
4. Restart the CMS processes.

With the compose file, a volume that already holds a database skips the init script, so set the variables to match: DATABASE_OWNER_USER and DATABASE_OWNER_PASSWORD to the role you have today (POSTGRES_USER and POSTGRES_PASSWORD, unless you changed them), and DATABASE_APP_USER and DATABASE_APP_PASSWORD to the role you created in step 1:

docker compose exec postgres psql -U manablox -c "create role manablox_app login password 'app-secret'"
docker compose run --rm migrate
docker compose up -d

To go back, point DATABASE_URL at the owner again and remove MIGRATION_DATABASE_URL.