Describe how you would separate the database account used by schema migrations from the account the application uses at runtime, and what each one is allowed to do.
answer
- owner/migration = DDL, deploy-only credentials
- runtime = DML on named tables, owns nothing
- new table needs a grant - explicit in migration or default privileges
- own objects with a role, not a person
- CI assert: runtime role's CREATE TABLE fails
basics
~20 sThe migration account owns the schema and holds DDL rights; it is used only by the deploy step and its credentials are not in the application's environment. The runtime account owns nothing and holds only DML on the tables the app touches. Migrations also issue the grants that keep the runtime account current.
solid answer
~50 sTwo identities with disjoint jobs. The **migration/owner** account creates and alters objects, owns them, and runs only during deploy - ideally from CI with credentials the application containers never see. The **runtime** account owns nothing, has no CREATE/ALTER/DROP, and holds SELECT/INSERT/UPDATE/DELETE plus schema and sequence USAGE on exactly the objects in use. The subtlety is that new objects are not automatically reachable by the runtime role, so every migration that creates a table must also grant on it. Two ways to keep that honest: put the GRANT statements in the migration alongside the CREATE, or configure default privileges so objects created by the owner are automatically granted to the runtime role. I prefer explicit grants in the migration - they are reviewable in the diff and version-controlled with the schema. Benefits: the app cannot alter schema even if compromised, deploys have an auditable actor, and privilege drift shows up in code review rather than in production.
code
sql · 9 lines-- run as app_owner (migration account)
CREATE TABLE app.invoice (
id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL,
total_cents BIGINT NOT NULL
);
GRANT SELECT, INSERT, UPDATE ON app.invoice TO app_rw;
GRANT SELECT ON app.invoice TO app_readonly;go deeper
Name the two accounts and what each may do: migrations do DDL at deploy time, the app only reads and writes rows.
Explain that new objects need explicit grants because the runtime role is not the owner, and show the two ways to handle that (grants in the migration, or default privileges).
Add operational detail: deploy-only credential handling, per-account lock and statement timeouts for DDL, ownership by a role rather than a person, and a CI check asserting the runtime role cannot do DDL.
Discuss the tradeoff between explicit grants and default privileges as an organisational control, and how the split feeds audit and incident response across many services and environments.
## Why the split exists Schema change and request serving are different jobs with different risk profiles. Schema change is rare, deliberate, performed by a pipeline, and inherently destructive-capable. Request serving is continuous, automated, exposed to untrusted input, and needs no destructive capability at all. Giving one identity both capabilities means the exposed, always-on surface carries the destructive rights. ## The two accounts **Migration / owner account.** Creates the schema and its objects, so it becomes their owner. Holds CREATE, ALTER, DROP, and the right to GRANT on what it owns. It runs in the deploy job only - a CI step, an init container, or an operator-run task. Its credentials should be issued to the pipeline, not baked into the application image or its environment variables. Sessions are short-lived and few, which makes them cheap to audit: any DDL outside a deploy window is immediately suspicious. **Runtime account.** Authenticates the connection pool. Owns nothing. Holds only the DML verbs the code actually issues on the specific tables it touches, plus `USAGE` on the schema and on sequences it inserts through. It cannot create a table, cannot drop one, and cannot grant anything to anyone. ## The grant-drift problem The consequence of non-ownership is that a table created tomorrow is invisible to the runtime role until someone grants on it. This is a feature - it means privilege expansion is an explicit act - but it must be handled or the first request after deploy fails with a permission error. Two mechanisms: 1. **Explicit grants in the migration.** The same changeset that runs `CREATE TABLE` also runs `GRANT SELECT, INSERT, UPDATE, DELETE ON ... TO app_rw`. Reviewable in the diff, replayable in every environment, and it forces a reviewer to notice when a new table becomes readable by the app. 2. **Default privileges.** Most engines let you declare that future objects created by a given role in a given schema automatically carry certain grants (Postgres `ALTER DEFAULT PRIVILEGES`, similar mechanisms elsewhere). Less repetitive, but it is invisible in the migration diff and applies blanket-style to everything the owner creates. Many teams use default privileges as a safety net and still write explicit grants for anything sensitive. ## Operational details that come up **Locking and long migrations.** Because migrations run as a distinct account, you can apply separate session settings to them - a longer statement timeout, a lock timeout so a DDL statement waiting on a lock fails fast instead of queuing every reader behind it. That is much harder when both jobs share one identity. **Emergency access.** People will ask for a break-glass path when a migration fails at 2am. Keep it as a separate, checked-out credential with auditing on, rather than quietly leaving DDL on the runtime account 'just in case'. **Ownership stability.** Objects should be owned by a *role* (say `app_owner`), not by a named human or a per-deploy account. Grant that role to whoever needs to act as owner. Otherwise ownership scatters across whoever happened to run a migration, and later ALTERs fail unpredictably. **Verification.** A useful test in CI: connect as the runtime role and assert that `CREATE TABLE` and `DROP TABLE` fail, and that the tables the app uses are readable. This turns a policy into an executable check that catches accidental over-granting. ## What it buys If the runtime credential leaks, the attacker gets data-plane access, not schema control - no dropped tables, no new function definitions, no privilege escalation via granting themselves rights. And because DDL only ever comes from one short-lived account, the audit log has a clean signal: unexpected DDL is unambiguous evidence of misuse rather than something you have to correlate against normal traffic.
- After a deploy, the app fails with 'permission denied for table invoice'. What happened and how do you prevent it recurring?The migration created the table as the owner but never granted on it, so the runtime role has no access - non-owners get nothing implicitly. Fix it by adding the GRANT to the same changeset, and prevent recurrence either with default privileges for objects the owner creates in that schema, or with a CI check that connects as the runtime role and exercises the new table.
- Where should the migration account's credentials live?In the deployment pipeline's secret store, injected only into the migration job, never into the application's runtime environment or image. If the app process cannot read them, a compromise of the app cannot use them. Short-lived or dynamically issued credentials are better still, since the DDL identity is needed for minutes per deploy rather than continuously.
saying these in an interview costs you the question
- Assuming the runtime role can use a newly created table automatically - non-owners have no implicit privileges on new objects.
- Shipping the migration credentials in the application's own environment, which erases the separation entirely.
- Letting whichever human or deploy account ran the migration become the object owner, so ownership is scattered and inconsistent.
- Granting the runtime role CREATE on the schema so the ORM's auto-DDL keeps working.