Database roles
Least-privilege Postgres roles for the API, workers and backups.
Database roles
By default the server, the workers, migrations and backups all connect as one Postgres role that owns the schema. That role can create, alter and drop every table, so a compromised worker (workers parse untrusted files) could read or rewrite every tenant’s data, including credentials. DocSlurp supports four roles so each process gets only what it needs.
On this page
| Role | Used by | Privileges |
|---|---|---|
docslurp (owner) |
Migrations (MIGRATION_DATABASE_URL) and break-glass access |
Owns every object |
docslurp_app |
The API server (DATABASE_URL) |
Select, insert, update and delete on every table; usage on sequences; execute on functions. No CREATE, not an owner |
docslurp_worker |
Workers (DATABASE_URL on worker hosts) |
DML only on the tables workers use; column-level reads of user (id, email), webhooks (org_id, active, events) and project_secrets (no ciphertext). No access to auth, API key, OAuth, SSO, connector or Stripe tables |
docslurp_backup |
pg_dump |
pg_read_all_data |
The worker’s table list lives in server/db/roles.mjs (WORKER_TABLES, with the code that needs each one, and WORKER_DENIED_TABLES, with the reason for each). A test (server/src/__tests__/db-roles.test.ts) fails when a migration adds a table that is in neither list, so a new table is never silently granted or denied.
How grants are kept current
Roles are cluster-wide and need CREATEROLE, so migrations do not create them. Once they exist:
server/db/migrate.mjsreconciles grants after every run, under the migration lock, so a table a release adds is granted before the release’s code runs. Without the roles it does nothing extra.- Default privileges (
ALTER DEFAULT PRIVILEGES FOR ROLE docslurp) givedocslurp_appDML on every table the owner creates later. The worker gets nothing by default; it gets a new table only once the table is added toWORKER_TABLES. node server/db/roles.mjs(withDATABASE_URLset to the owner or a superuser) reconciles without applying migrations.
migrate.mjs refuses to run as a role that cannot create in schema public or does not own the tables, and names MIGRATION_DATABASE_URL in the error. Set it to the owner’s URL whenever DATABASE_URL points at a restricted role. This includes RUN_STARTUP_MIGRATIONS=true.
Set up
-
As a superuser, create the login roles and give the backup role
pg_read_all_data:sqlcreate role docslurp_app login password '...'; create role docslurp_worker login password '...'; create role docslurp_backup login password '...'; grant connect on database docslurp to docslurp_app, docslurp_worker, docslurp_backup; grant pg_read_all_data to docslurp_backup; -
Reconcile grants:
DATABASE_URL=postgres://docslurp:...@host/docslurp node server/db/roles.mjs. -
Set
MIGRATION_DATABASE_URLto the owner’s URL on the server, then point the workers’DATABASE_URLatdocslurp_worker, the server’s atdocslurp_appand backups atdocslurp_backup.
Rolling back is the reverse: point DATABASE_URL back at the owner. The roles and grants can stay.
Runtime requirements
Neither runtime role runs DDL. The server and workers use LISTEN/NOTIFY, advisory locks (pg_advisory_lock, pg_advisory_xact_lock) and to_regclass/information_schema checks, none of which need ownership. Most triggers and functions are SECURITY INVOKER, so the worker’s list includes the tables its writes reach through triggers (for example project_corpus_stats and credit_ledger). Foreign-key cascades run as the table owner and need no grant.
The server’s database watchdog counts its own pool’s backends in pg_stat_activity. Postgres shows a role the state of its own sessions, so the server’s pool and probe must use the same role.
Realtime publication keeps the ingestion worker separate from the native chat
executor (the latter is a server process using the application database role).
The ingestion worker has no access to chat_operations, chat_operation_events,
chat_run_dependencies or the API’s aggregate realtime_revisions and
realtime_scope_revisions views. Its run,
document, usage and page-state writes invoke validated SECURITY DEFINER triggers
owned by docslurp_realtime_publisher. That role has no login, inheritance,
superuser, role creation, database creation, replication or RLS bypass. Its only
data grants are counter DML, SELECT of (id, org_id) on projects, runs and
documents, and (migration 0153) SELECT of project_id on runs and of
(project_id, run_id) on documents, so a change also records its project and run
revision scopes. It has no schema CREATE privilege after migration. Trigger functions
validate the exact public relation, trigger name, timing, level and arguments;
all lookups are schema-qualified with a fixed pg_catalog,pg_temp search path.
PUBLIC and runtime EXECUTE on the publication functions is revoked. Runtime
roles cannot attach these triggers or call the arbitrary-org internal helpers.
The application can read the aggregate view and call only bounded compaction;
neither runtime role can read or mutate counter storage directly.
Migration 0120 installs these grants/revocations in the same transaction as the
view and trigger replacement for existing docslurp_app/docslurp_worker roles.
Reconciliation repeats them after its broad application grants, including for
custom runtime role names. Custom-named roles require the same grants in the
migration transaction before a rolling upgrade; the atomic defaults are the
standard role names above.
A cluster administrator must provision the isolated publisher role before migration when the migration owner lacks CREATEROLE:
CREATE ROLE docslurp_realtime_publisher NOLOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE
NOINHERIT NOREPLICATION NOBYPASSRLS;
GRANT docslurp_realtime_publisher TO docslurp;Use the actual migration owner in place of docslurp; never grant membership to
runtime roles. With CREATEROLE, the migration creates the role itself. Missing
privileges or an existing publisher with elevated role attributes/memberships
fail migration rather than reverting to direct runtime counter access.
When the migrated docslurp_mastra schema exists, reconciliation grants the
application role schema usage and DML/sequence access there, including default
privileges for later owner-created tables. It still revokes CREATE. Public and
ingestion-worker schema/table/sequence grants are revoked; a stale direct worker
grant is removed on the next reconciliation. The native chat executor must use
the application role, not ingestion-worker credentials.