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 partial revoke counts. With partial_revokes on, a global grant can be taken back on single databases, and MySQL keeps those restrictions in mysql.user, not in the privilege views. The check reads them there, and a global grant does not reach a database a restriction names. Without SELECT on mysql.user the global grant is undetermined on such a server, with the reason named.
  • 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 = ?) and an empty @@mandatory_roles. MySQL grants a mandatory role to every account without a row in role_edges, so an empty role_edges rules nothing out while @@mandatory_roles names a role. Without that reading, with a role there, or with a mandatory role, 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. A privilege that is missing for certain is a fail instead, whatever stayed unanswered beside it: the migration stops at that statement either way, and waiving this reason must not let it through. The unanswered objects are reported once the missing privilege is granted.
  • 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.
  • GRANT TRIGGER ON <object> TO <role> for the table a trigger is put on. The table's owner holds it already.
  • GRANT CREATE ON DATABASE <database> TO <role> for an extension PostgreSQL marks as trusted. Any other extension needs a superuser, so the message asks for one to install it before the deploy.

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.

A DO block, which only its migration can answer. What a block needs depends on the code it runs, so the check leaves it open unless the migration names each right on the class or on up(), one attribute per object and right:

use Pushery\SQLens\Attributes\DoBlockPrivilege;
use Pushery\SQLens\Deploy\PrivilegeClass;
use Pushery\SQLens\Subjects\SchemaObjectType;

return new class extends Migration
{
#[DoBlockPrivilege('public.touch_updated_at', SchemaObjectType::Routine, PrivilegeClass::Ownership,
reason: 'the block pins the search path of the trigger function')]
#[DoBlockPrivilege('public.invoices', SchemaObjectType::Table, PrivilegeClass::Trigger,
reason: 'and recreates the trigger that calls it')]
public function up(): void
{
DB::statement(<<<'SQL'
DO $$ BEGIN
ALTER FUNCTION public.touch_updated_at() SET search_path = public;
DROP TRIGGER IF EXISTS invoices_touch ON public.invoices;
CREATE TRIGGER invoices_touch BEFORE UPDATE ON public.invoices
FOR EACH ROW EXECUTE FUNCTION public.touch_updated_at();
END $$
SQL);
}
};

The check then asks about those rights in the block's place, as it asks about a statement it can read, and names a missing one the same way. A routine the declaration names that isn't there at all is one an earlier pending migration creates, as on a first deploy: the check asks for CREATE on its schema, since the role that creates it owns it afterwards, the way it asks about a table a migration creates. A name that several routines share is there, and stays open until it is written with its argument types. The declaration answers the DO blocks of its own migration and nothing else: a block in another pending migration stays open, and so does any other statement the check cannot read. The pre-scan reads the attribute from the source, so only literal values count, the object as a string and the type and the class as cases of their enums. A declaration with a value it cannot read, or with an empty reason, answers nothing.

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 and @@mandatory_roles names none.
  • 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.
  • A recognized statement without a target is answered by the engine. A session setting any role may make needs nothing: SET LOCAL lock_timeout = '5s', SET CONSTRAINTS ALL DEFERRED and a RESET such as RESET ALL on PostgreSQL, SET SESSION lock_wait_timeout = 5 and SET FOREIGN_KEY_CHECKS=0 on MySQL. COMMENT ON TABLE, COMMENT ON COLUMN and CREATE, ALTER or DROP POLICY ask for ownership of their table. CREATE FUNCTION, CREATE PROCEDURE, CREATE VIEW, CREATE MATERIALIZED VIEW and CREATE TYPE ask for CREATE on the schema the object lands in, CREATE TRIGGER for TRIGGER on its table, and CREATE EXTENSION asks the database: an extension installed already needs nothing, a trusted one CREATE on the database, any other one a superuser. Replacing a routine or a view that exists already needs its ownership as well, which the check does not ask. DROP TRIGGER asks for ownership of the trigger's table, and DROP FUNCTION, DROP PROCEDURE and DROP ROUTINE for ownership of each routine they name, by its argument types where the statement gives them. On a first deploy, where an earlier pending migration creates that table or routine, the question goes to its schema, as for a table a migration creates. A plain SELECT needs nothing the check asks about, so the reads Laravel records from a migration that walks its rows or looks a routine up pass; one that creates a table with INTO, locks rows with FOR UPDATE or FOR SHARE, or calls a function that sets a sequence or reaches outside the database stays unanswered. SET ROLE, SET SESSION AUTHORIZATION, a global or persisted variable, a parameter this build does not list, GRANT and REVOKE stay unanswered: each needs more than the session has, or a right the check does not ask about.
  • A DO block is answered only by its migration. What it needs depends on the code it runs, which no reading of the statement can tell. With #[DoBlockPrivilege] the check asks about the rights the migration names, and it does not check that they are all the block needs. Without the attribute the block stays unanswered and says so; a deploy that may go on without the answer waives privilege_check_incomplete with --allow-undetermined or sqlens.deploy.predeploy.allow_undetermined.
  • 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.