Skip to main content

DEPLOY.PREFLIGHT.MISSING_PRIVILEGE — the role running the migrations may not do what they ask for

  • Category: safety
  • Severity: critical
  • Level: 0
  • Downtime class: blocking — the finding this check emits carries it. A deploy that cannot get the privilege it needs stops where it stands, and the axis says so
  • Stability: stable
  • Suites: deploy
  • Applies to: PostgreSQL and MySQL — one check per engine, and they ask genuinely different questions

What it reports​

Something the pending migrations will do that the role running them is not allowed to do.

The run starts from the pending work, not from the database: with nothing pending, or with a pending set from which no requirement could be derived, the check passes without asking the server anything. Otherwise every pending statement contributes one requirement per object it names — expressed as a class (create, alter, drop, references, write, ownership) rather than as an engine's privilege name, because the two engines spell the same permission differently and one of them does not spell it as a permission at all. Each driver then translates the class into a question its own server can answer.

PostgreSQL​

It asks the server instead of reasoning about grants here: has_table_privilege(role, object, privilege) follows role inheritance on its own, so a role that holds the grant through two levels of membership answers true without anybody reimplementing that traversal.

Per requirement, in this order:

  • Existence first, with to_regclass(?) is not null. has_table_privilege raises for an object the catalog does not have, and a migration that creates a table names exactly such an object.
  • An absent object, or the create class — has_schema_privilege(role, schema, 'CREATE'). The schema is the qualifier in the object name, or public when the name carries none. That is the right question anyway: creating a table needs CREATE on the schema, not anything on a table that is not there.
  • references — has_table_privilege(role, object, 'REFERENCES'), asked on the referenced table. A role very often owns the child and holds nothing on the parent.
  • write — has_table_privilege(role, object, 'INSERT') and has_table_privilege(role, object, 'UPDATE'), the backfill half of a migration.
  • ownership and drop — pg_has_role(role, c.relowner, 'USAGE') over pg_class. Not a privilege question: ALTER TABLE and DROP cannot be granted on PostgreSQL, they require ownership, and membership in the owning role is what the server itself checks before it allows the statement.

Those last two do not come out under this id. A false answer there is reported as DEPLOY.CONTEXT.GRANT.OWNERSHIP_MISSING, and the split is deliberate: a role can hold every grant on a table and still be unable to ALTER it. One id for both would let somebody read "missing privilege", run the GRANT the message names, and watch the next deploy fail identically.

If the privilege question comes back with no row at all — the object dropped between the existence read and the privilege read — nothing was established. Answering true there would certify a permission nobody checked, so the object is carried as unanswered instead.

MySQL​

MySQL has no has_table_privilege. information_schema.APPLICABLE_ROLES lists the roles of the current user, and the privilege views list grants held directly, never those reached through a role. That reverses which mistake is dangerous: on PostgreSQL the trap is a false green, here it is a false red. So the resolution is asymmetric on purpose.

  • The account has to exist before anything is said about it — select count(*) from mysql.user where user = ?. A name that is no account answers undetermined (grant_subject_missing), never a finding: a finding would advise a GRANT to an account that does not exist, which fails in turn, handed to somebody inside a deploy window. When mysql.user cannot be read, "cannot tell" is not turned into "does not exist".
  • Held privileges are read as one set from information_schema.USER_PRIVILEGES, SCHEMA_PRIVILEGES and TABLE_PRIVILEGES for that grantee. Global, schema and table grants are collapsed on purpose — a global ALTER answers the question as well as a table-level one.
  • The class becomes a privilege name: create → CREATE, alter and ownership → ALTER, drop → DROP, references → REFERENCES, write → INSERT. MySQL has no owner concept, so this driver never emits an ownership finding.
  • A privilege found is definitive. Grants do not lie about themselves.
  • A privilege not found is only a finding when the run can also establish that the user holds no roles at all, which needs SELECT on mysql.role_edges (select count(*) from mysql.role_edges where to_user = ?). Without that reading the object goes undetermined with the reason named. Reporting it missing would be wrong for every setup that grants through roles, which is the modern MySQL 8 one.

When it answers undetermined​

  • migration_role_unknown — the configuration does not say which role runs the migrations, so whose privileges to ask about is unknown. Set the username on the migration connection; an empty one is not a role, and asking about it would certify a role that does not exist.
  • privilege_check_incomplete — one or more objects could not be answered, each naming its own reason: a requirement whose class this build could not derive, a server that refused the privilege question, or the MySQL role case above. Findings already established are carried along with it, so a partial answer never hides the part that did answer.
  • grants_unreadable (MySQL) — the privilege tables could not be read.
  • grant_subject_missing (MySQL) — mysql.user holds no account by the name the configuration gives, so nothing can be established about what it may do. The whole run answers this, and no finding is emitted with it.

An unresolvable requirement is carried into that list rather than dropped, and that is the whole reason it exists: an object nobody classified must not end up looking like an object nobody had to be allowed to touch.

Why it matters​

migrate --force does not fail at the start. It applies what it can and stops at the statement whose privilege is missing — so the schema ends up half-migrated while the application is already deployed against the other half, and the fix is a rollback under time pressure. Moving that failure out of the deploy window and into the minute before it is the entire purpose of the check.

That is why it is critical, and it is the one finding in the deploy family that is about a deploy which cannot start rather than one that should not. The finding it emits attaches the blocking downtime class rather than online, for the same reason: the application is live against a schema that is only partly there.

sqlens:predeploy is fail-closed, so undetermined blocks as well. --allow-undetermined proceeds only when every blocking result is one that could not answer; a real finding still blocks.

What to do about it​

The finding names the role, the object and what has to change. Three shapes, and only the first is a GRANT.

A missing grant. On MySQL the message carries a statement that runs as it stands: GRANT ALTER ON <object> TO '<user>'@'…'; (the write class prints INSERT, UPDATE).

On PostgreSQL it does not always. The message spells the grant out of the requirement class — GRANT <CLASS> ON <object> TO <role>, with the class name in upper case — and of the classes that reach this id, only REFERENCES is a table privilege of that name. Two need translating before they will run:

  • GRANT WRITE … is the write class, and PostgreSQL has no WRITE privilege. The grant is GRANT INSERT, UPDATE ON <object> TO <role> — the two privileges the check actually asked about.
  • GRANT CREATE ON <table> … is the create class, and the question was asked about the schema, so that is where the grant belongs: GRANT CREATE ON SCHEMA <schema> TO <role>.

Missing ownership on PostgreSQL (reported as DEPLOY.CONTEXT.GRANT.OWNERSHIP_MISSING). This one is a decision, not a grant, because no GRANT produces ownership: either transfer it with ALTER TABLE <object> OWNER TO <role>, or make the role a member of the role that owns the object. Running a GRANT here changes nothing, and the next deploy fails in the same place.

An undetermined you have to resolve rather than fix. For migration_role_unknown, set the username on the migration connection. For the MySQL role case, decide which you want: grant the reading connection SELECT on mysql.role_edges, so every future run can answer definitively — or leave that view closed and confirm by hand that the role path really carries the privilege.

What it does not claim​

  • It does not model your grants, it asks the server. On PostgreSQL that is the point: has_table_privilege resolves role inheritance itself, and a traversal reimplemented here would be a second opinion about the server's own rules.
  • It cannot see a privilege granted through a role membership the catalog does not expose. On MySQL that limit shapes the whole check — a privilege reached through a role is invisible to the privilege views, which is exactly why a not-found privilege is withheld unless mysql.role_edges shows there are no roles.
  • On MySQL it checks the privilege NAME, not its scope. Global, schema and table grants are deliberately collapsed into one set, so ALTER held anywhere counts as ALTER for every object in the run.
  • It asks only about what the derivation could name. A statement kind this build has no privilege mapping for, and a statement naming no object it could resolve, are reported as unanswered — never as needing nothing.
  • The write class is asked as INSERT and UPDATE on PostgreSQL and as INSERT on MySQL. No other DML privilege is part of the question.
  • On PostgreSQL, ownership is not reported under this id. A run that is quiet here has not said the role can ALTER or DROP anything; that answer arrives under DEPLOY.CONTEXT.GRANT.OWNERSHIP_MISSING.
  • It reports what was true when it looked. A DEPLOY.PREFLIGHT.* check is asked immediately before the migration runs, against the target, and answers whether this deploy should start at all.