Skip to content

Database roles and privileges

AWA can run with one database user, but production deployments should separate schema management from runtime execution.

Role model

awa_owner       NOLOGIN   owns the schema and its objects
└── awa_migrator  LOGIN   runs migrations; member of awa_owner

awa_runtime     LOGIN     workers, producers, admin UI and CLI operations

Create the roles as a superuser or a role with the cluster-wide CREATEROLE attribute. Database ownership alone cannot create roles:

CREATE ROLE awa_owner NOLOGIN;
CREATE ROLE awa_migrator LOGIN PASSWORD 'replace-me';
CREATE ROLE awa_runtime LOGIN PASSWORD 'replace-me';

GRANT awa_owner TO awa_migrator;
GRANT CONNECT ON DATABASE mydb TO awa_migrator, awa_runtime;
GRANT CREATE ON DATABASE mydb TO awa_owner;

Run migrations through the migrator login while making awa_owner the effective role for every migration connection:

PGOPTIONS='-c role=awa_owner' \
  awa --database-url "$AWA_MIGRATOR_DATABASE_URL" migrate

This makes the awa schema and the tables, sequences, functions, and standalone enum/domain types created by migrations belong to the non-login owner from the outset. It also avoids relying on membership or default privileges to repair ownership later. The awa_migrator login must be allowed to SET ROLE awa_owner; on PostgreSQL 16 and newer, preserve that option when granting the membership.

Runtime grants

The 0.6 runtime and current 0.7 development runtime use SECURITY INVOKER triggers and maintenance helpers, so their grants are intentionally broader than an application's enqueue-only privileges:

GRANT USAGE ON SCHEMA awa TO awa_runtime;
GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE
  ON ALL TABLES IN SCHEMA awa TO awa_runtime;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA awa TO awa_runtime;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA awa TO awa_runtime;

REVOKE EXECUTE ON FUNCTION
  awa.install_queue_storage_substrate(TEXT, INT, INT, INT, BOOLEAN)
  FROM awa_runtime;

The runtime needs TRUNCATE for guarded ring-partition reclamation. Compatibility COPY through InsertOpts::copy() also needs TEMP on the database; direct queue-storage COPY does not use that temporary staging table.

Set matching default privileges for every role that creates objects during migrations:

ALTER DEFAULT PRIVILEGES FOR ROLE awa_owner IN SCHEMA awa
  GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON TABLES TO awa_runtime;
ALTER DEFAULT PRIVILEGES FOR ROLE awa_owner IN SCHEMA awa
  GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO awa_runtime;
ALTER DEFAULT PRIVILEGES FOR ROLE awa_owner IN SCHEMA awa
  GRANT EXECUTE ON FUNCTIONS TO awa_runtime;

If an existing installation created objects as awa_migrator without first setting the owner role, transfer every existing schema object to awa_owner before relying on owner-scoped defaults. New migrations should use the effective-role command above.

Provision queues before starting restricted runtimes

From schema v047, runtime calls use existing lane sequences without issuing CREATE SEQUENCE. Run provisioning as the migrator for every queue before starting its producers or workers:

PGOPTIONS='-c role=awa_owner' \
  awa --database-url "$AWA_MIGRATOR_DATABASE_URL" storage prepare-queue \
  --queue email --enqueue-shards 1

The command provisions all four priorities in one transaction and is safe to repeat on a live queue: existing sequence cursors keep their positions. It does not change routing or the queue's configured shard count. Prepare additional shards before increasing enqueue_shards; match --queue-stripe-count to the runtime's queue-storage configuration. With PartitionedQueue, provision each physical queue. Use --schema for a custom storage schema.

Apply the sequence grants above after provisioning, or set the owner's default privileges first. Lane cursors need UPDATE for setval, in addition to USAGE and SELECT. CREATE SEQUENCE IF NOT EXISTS requires schema CREATE even when the sequence already exists, so provisioning alone does not repair pre-v047 helpers: apply the migration first. Schema owners retain lazy creation for single-role installations; a restricted runtime encountering an unprepared lane fails with a provisioning hint.

Custom queue-storage schemas

Repeat the schema, table, sequence, function, and default-privilege grants for a custom queue-storage schema. Prepare custom schemas as the migrator; the runtime needs DML, sequence access, function execution, and TRUNCATE, but never DDL. See Queue storage.

Why the grants are broad

Runtime triggers maintain queue counts, descriptors, uniqueness claims, and other metadata as the invoking role. The elected maintenance task promotes and rescues jobs, refreshes metadata, and reclaims eligible ring slots. Consequently a login holding awa_runtime is trusted Awa infrastructure: do not grant it to a producer-only application or a public callback service.

ADR-042 and ADR-043 describe accepted/proposed boundaries for a narrower application finalizer and capability-specific runtime roles. They are not implemented by the current 0.6 or 0.7 development runtime.

Verify the split

  • Connect as the migrator to run awa migrate.
  • Connect as the runtime to start workers and run ordinary awa job / awa queue commands.
  • Confirm the runtime cannot create or alter schema objects.
  • Keep the migrator credential out of worker and admin-service configuration.