Security — Application Role Minimum Privilege Enforcement

Why This Matters

The principle of least privilege requires that every component holds only the permissions it needs to function, and nothing more. For a financial application, this is not a best practice — it is a structural security requirement. The database role the application connects as is the most exposed identity in the system: every API request, every authenticated user action, every background job runs under that role. Any privilege it holds that is not needed for legitimate operation is an attack surface.

This release removes three privileges from the application database role that had no legitimate runtime use case. Each represented a distinct and non-trivial risk class.

This finding emerged during the role audit conducted immediately after Milestone 1 deployment, as part of the pre-Milestone 2 verification process. The SpeyBooks database has a clean role architecture: a superuser (postgres) for emergency OS-level operations, a schema owner (william) for DDL and migrations, and an application role for all runtime operations. The application role should hold exactly the four privileges needed for data access: SELECT, INSERT, UPDATE, DELETE. It was holding seven.

Application Role Privilege Reduction

TRUNCATE — Revoked

The most significant of the three. TRUNCATE is categorically different from DELETE: it bypasses row-level security entirely, removes all rows from a table in a single atomic operation, does not fire per-row triggers, and leaves no per-row audit trail. In a multi-tenant system with RLS as the isolation boundary, TRUNCATE is the single operation that renders RLS irrelevant.

There is no legitimate reason for the application runtime to hold this privilege. No API endpoint, no background job, and no business operation requires it. In the hands of a compromised session — a stolen API key, an injection vulnerability, a dependency supply chain attack — TRUNCATE would constitute a complete, immediate, and largely unrecoverable data wipe of a tenant’s financial records.

TRIGGER — Revoked

Allows creating and dropping triggers on tables. Triggers are schema-level DDL: they modify how the database behaves in response to data changes. Trigger management belongs to the schema owner role operating during planned migrations. The application runtime has no business modifying trigger definitions. Holding this privilege created an unnecessary escalation path: a compromised application session could drop an immutability trigger, make changes that would otherwise be blocked, and optionally restore the trigger — leaving no trace in the data of what had been changed.

REFERENCES — Revoked

Allows creating foreign key constraints referencing tables. Foreign key relationships are established at migration time by the schema owner and do not change at runtime. No API operation requires this privilege. It was superfluous and has been removed.

Verified Posture

The application role now holds exactly four privileges across all tables in the accounting schema: SELECT, INSERT, UPDATE, DELETE. Verified by direct catalog query after revocation. No application behaviour is affected — all legitimate runtime operations use only these four privileges.

Role Architecture

SpeyBooks maintains a clear three-tier database role structure:

• postgres — superuser, OS peer authentication only, emergency access
• william — schema owner, runs migrations, holds DDL privileges, cannot be used by the application
• speybooks_app — application runtime, SELECT/INSERT/UPDATE/DELETE only, no DDL, no bypass RLS

The separation between schema owner and application role is the structural control that makes minimum privilege meaningful. The application role cannot modify its own constraints, drop its own triggers, or alter its own policies — because it does not own the schema.

Operational Impact

• Application role reduced from 7 table privileges to 4
• TRUNCATE revoked — RLS bypass via bulk delete eliminated
• TRIGGER revoked — trigger DDL escalation path closed
• REFERENCES revoked — foreign key creation at runtime eliminated
• Minimum privilege posture verified by catalog query
• No application behaviour affected — all runtime operations use only the four retained privileges

Files Changed

Backend: No application code changes. Privilege revocation applied directly to the database role via the schema owner.

Discovered and resolved during the pre-Milestone 2 role audit. The clean role architecture it establishes is a prerequisite for the trigger-based enforcement arriving in Milestone 2 — triggers that the application role can now neither create nor drop.