security(db): replace schema-wide DML grants with per-role least-privilege grants #683

Parent epic: #679

Evidence

sql/013_awcms_mini_enforce_rls_least_privilege.sql grants SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public and matching default privileges to awcms_mini_app. Global tables without RLS include modules, permissions, schema migrations, setup state, and tenants.

Objective

Separate migration, runtime, worker, and bootstrap database authority and grant only the operations each path requires.

Scope

• Add a migration using the next available number at implementation time.
• Define/document roles such as migration owner, web runtime, background worker, and bootstrap/setup.
• Revoke schema-wide/default DML grants from runtime roles.
• Grant explicit table/sequence/function privileges per role.
• Keep tenant isolation enforced by withTenant, RLS, and FORCE RLS.
• Add negative privilege tests for global and tenant-scoped tables.
• Update deployment/credential rotation/runbook docs.

Guardrails

• No production role may own application tables.
• Setup/bootstrap authority must not be reused by normal web requests.
• Migrations must be reversible or include a tested recovery path.
• Do not expose credentials in commands, logs, or process arguments.

Acceptance criteria

• Web runtime cannot mutate modules, permissions, schema migrations, setup state, or tenants unless explicitly required and tested.
• Worker role cannot access unrelated feature tables.
• App queries still work under the correct tenant context.
• Missing tenant context and cross-tenant access fail closed.
• Migration and fresh-install integration tests pass on PostgreSQL.
• Security/readiness checks detect over-privileged runtime credentials.