Adopt a function-mediated PostgreSQL capability boundary

[hardbyte]

Problem

Awa's documented runtime role currently receives SELECT, INSERT, UPDATE, DELETE, TRUNCATE on all Awa tables and EXECUTE on all functions. Compatibility triggers and helpers are predominantly SECURITY INVOKER, so the runtime must hold the underlying privileges even when application code never addresses those internal relations directly.

ADR-042 introduces one deliberately narrow SECURITY DEFINER completion capability for an application role. The same principle can reduce the ordinary runtime blast radius, but converting every existing function to SECURITY DEFINER would turn internal, generic, and DDL helpers into privileged entry points.

Direction

Adopt ADR-043's function-mediated capability boundary:

• expose an allowlisted set of role-specific capability entry points as hardened SECURITY DEFINER functions;
• use clean public domain names (awa.insert_job, <queue_storage_schema>.complete_job), reserving _compat for internal representation/upgrade shims and _runtime for binary-coupled helpers;
• give each installed public definer name one exact signature; avoid overload/default-parameter variants that complicate ACL and function resolution; keep clean names canonical while staged breaking changes use separately granted, temporary versioned coexistence names;
• keep internal helpers non-executable by runtime/application roles;
• keep DDL, installer, and arbitrary-schema helpers SECURITY INVOKER and migrator-only;
• revoke PUBLIC function execution and replace blanket function/table grants with an explicit capability manifest;
• own definer functions with a dedicated NOLOGIN, non-superuser role that has only the table/sequence privileges needed by those functions; and
• preserve a documented trusted-runtime profile until direct COPY and every Rust/Python lifecycle path have a capability-mediated replacement.

Deliverables

• ADR-043 accepted with a complete routine/role classification, naming policy, and threat model.
• Catalog-generated inventory of functions, triggers, owners, prosecdef, search_path, and ACLs.
• Explicit producer, executor, maintenance, admin, callback, and caller-finalizer capability sets.
• Hardened entry points with fixed trusted search_path, qualified objects, no caller-controlled identifiers, transactional PUBLIC revoke, and exact-signature grants.
• awa doctor role/grant/owner diagnostics and a machine-readable expected capability manifest.
• Negative tests proving each role cannot read or mutate unrelated Awa tables or invoke migration/DDL helpers.
• Search-path, overload/default-resolution, temporary-object shadowing, SQLSTATE, stale-token, and dynamic-SQL abuse tests.
• Mixed-version expand/use/tighten rehearsal under ADR-041.
• A measured replacement or explicit privileged-profile decision for queue-storage direct COPY.

Acceptance

• The strict runtime profile completes every supported Rust and Python lifecycle with no direct grants on internal Awa tables.
• Every executable definer function is allowlisted, catalog-audited, and owned by the bounded execution owner.
• Public SQL capability entry points retain ADR-036 versioning and clean names; internal helpers remain free to evolve with schema/binary compatibility.
• Existing broad-grant deployments upgrade additively and tighten grants only after all connected binaries advertise the capability surface.

Follow-on to #91. Related: ADR-036, ADR-041, ADR-042.