Use a Separate Database Identity for Migrations

Article · Interrupted execution

A deployment starts the application with the same database identity that created its tables. Ordinary requests now run with migration authority. Nothing about a Rust type or query helper removes the permissions attached to that connection.

Separate the jobs. The migration process changes the schema through a controlled owner identity. The runtime process reads and writes the rows its features need. A permission test should show both facts: useful application work succeeds, and an unintended schema change fails for the right reason.

This lab checks that boundary on PostgreSQL 18 through SQLx 0.8.6. It creates a disposable non-login owner and uses the workflow's non-owner application login. The administrative setup is part of the fixture, not a credential pattern for a production web process.

Ownership is different from an ordinary grant

An object owner has authority to change that object. Revoking one ordinary grant from a runtime identity does not help when it still owns the table or can assume its owner role. PostgreSQL's privilege documentation explains that distinction.

The fixture creates practical_release_owner with no login. An administrative connection creates a schema owned by that role. The migration connection assumes the owner role while creating a table, then resets its role before returning to the pool.

The application gets schema usage and the table's select, insert, and update privileges. It gets no schema create privilege and no membership in the owner role. The test inserts a row, changes its value from ten to eleven, and reads eleven back through the application connection.

That allowed path matters. A broken connection, wrong password, or role with no useful access would be a poor security demonstration. The test needs to prove an intentional boundary around a working feature.

Reject the exact forbidden operations

The negative checks use three statements: alter the owned table, create another table in the migration schema, and assume the owner role. Each must fail with PostgreSQL code 42501, which identifies insufficient privilege.

for statement in [
        "ALTER TABLE practical_release_roles.migration_probe ADD COLUMN forbidden integer",
        "CREATE TABLE practical_release_roles.forbidden (id integer)",
        "SET ROLE practical_release_owner",
    ] {
        let error = sqlx::query(statement)
            .execute(&app)
            .await
            .expect_err("runtime privilege boundary");
        assert_eq!(
            error.as_database_error().unwrap().code().as_deref(),
            Some("42501")
        );
    }

The complete role fixture also checks role membership and queries the catalog through the administrative observer. The forbidden column must be absent. The application role must not be an owner-role member.

Do not accept an arbitrary error as permission evidence. A misspelled table name, connection reset, or unavailable server is a different failure. Keep an allowed-path control beside the denied operation, and inspect stored structure afterward.

These checks are deliberately narrow. They do not audit every role membership, database, schema, function, or object in a production cluster. Extend the list around the operations your application actually exposes. A security-definer routine can introduce authority that a direct table-grant check does not exercise.

Keep migration credentials in the deployment step

Give the deployment job the migration credential for its bounded migration operation. Give the web process its runtime credential. Verify the effective identity from inside the running process, rather than assuming an environment variable reached the intended container.

Migration failure should lead to a deployment diagnosis. Copying the migration password into the runtime environment may make one release work while quietly removing the boundary. Fix the grants or migration path instead.

For generated IDs, test the relevant sequence access. For new tables, check how default privileges are applied by the role that actually creates them. A grant on today's tables does not automatically settle tomorrow's objects. Prefer an explicit migration assertion over a broad grant added under pressure.

The non-login owner in this lab does not require a runtime password. A real migration login needs a deliberate mechanism to assume ownership, with narrowly assigned membership and controlled secret access. Keep that login out of the request-serving environment.

Test tenant rules as the runtime user

A table owner normally bypasses row security unless the applicable configuration changes that behavior. An owner-based test can therefore make tenant isolation appear correct while missing the identity used by real requests.

The row security and Rust tenant-context guide demonstrates policy behavior with a separate application identity. Run those application checks using the real runtime role. Keep permission tests and tenant-policy tests together in the deployment review, while remembering that they establish different facts.

Separate identities also do not make every migration safe. A small DDL change can wait on a transaction lock. A destructive change can break an older application process that is still running. Schema ownership, lock behavior, application compatibility, and data recovery each need attention.

Start with a small disposable table and a useful allowed query. Add the forbidden operations that would matter during a mistake or compromise. Then run that check whenever the deployment changes roles or schema ownership. A runtime account that can do its job and cannot take the owner's job is a concrete boundary you can maintain.