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 the first schema on the connection's search path 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.
  • index — the table an index is built on, asked like ownership: CREATE INDEX needs the table's owner. The index's own name is a create requirement and goes to the schema like any new object, because PostgreSQL wants both.

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 per account, with their scope, from information_schema.USER_PRIVILEGES, SCHEMA_PRIVILEGES and TABLE_PRIVILEGES. The configuration names a user, and MySQL knows accounts: 'deploy'@'%' and 'deploy'@'localhost' hold separate grants. A requirement is met by a global grant, by a database-level grant whose name reaches the object's database, or by a table-level grant on that table, so an ALTER on app.audit_log does not answer ALTER TABLE app.orders. In a database-level grant _ and % are wildcards unless the server runs with partial_revokes, and when several such entries match, only what all of them hold counts, because MySQL applies one of them. An unqualified name lands in the reading connection's default database.
  • When the accounts of one user name disagree, the object goes undetermined and names them: which one migrate connects as depends on the host it connects from.
  • The privilege views are filtered to the reading connection. Without SELECT on the mysql schema they show its own grants and nobody else's. Every account has at least a USAGE row, so a user name the views show no account for answers undetermined (grants_unreadable) for that reason.
  • The class becomes a privilege name: create → CREATE, alter and ownership → ALTER, drop → DROP, references → REFERENCES, write → INSERT, index → INDEX. MySQL has no owner concept, so this driver never emits an ownership finding.
  • An index asks for a right on its table. ALTER TABLE … ADD INDEX, the form Laravel writes on MySQL, is an alteration of the table and asks ALTER; a CREATE INDEX asks INDEX. The index's own name is nothing MySQL grants on, so it asks nothing.
  • 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, the MySQL role case above, accounts of one user name that disagree, or an unqualified object with no default database to land in. 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, or the privilege views name no account by that user name, which is what they show a reading connection without SELECT on the mysql schema.
  • 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. Every statement in it names the role quoted, the way the server reads it back: on PostgreSQL "AppMigrator" is that role, where a bare AppMigrator would fold to appmigrator.

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). The user is a quoted identifier rather than a string, so a name with a quote or a backslash in it reads the same under every sql_mode.

On PostgreSQL the message names the grant for the question the check asked:

  • GRANT INSERT, UPDATE ON <object> TO <role> for a migration that writes rows, the two privileges it was asked about.
  • GRANT CREATE ON SCHEMA <schema> TO <role> for an object that does not exist yet, because a CREATE is granted on the schema the name lands in. The schema is the one the server resolved, the name's own qualifier or the first schema on the connection's search path. When that path names no schema that exists, the message says so and suggests no grant, because none would help.
  • GRANT REFERENCES ON <object> TO <role> for the table a foreign key points at.

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> (ALTER SCHEMA <schema> OWNER TO <role> for a schema the migrations drop), 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 cannot tell which host migrate connects from. When the accounts of one user name hold different grants, it names them instead of choosing one.
  • Column-level grants are not read. A privilege held only on some columns of a table does not count for that table.
  • 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.