0.9.11-beta SchemaIntegrityVerifier rejects valid schemas: Text default casts + multi-role / AllTablesInSchema grants

[MelbourneDeveloper]

Summary

The new SchemaIntegrityVerifier in 0.9.11-beta fails the migrate exit code on every valid schema we have. The migration itself succeeds — the live database is correctly converged — but the post-migrate integrity check rejects valid YAML it can't reconcile against what its inspector reads back from Postgres.

Two regressions vs 0.9.10-beta, both reproducible against an empty postgres:16 container with the schema below.

Repro

docker run -d --rm --name pg -e POSTGRES_PASSWORD=postgres -p 5499:5432 postgres:16
dotnet tool update -g DataProviderMigrate --version 0.9.11-beta
DataProviderMigrate migrate \
  --schema schema.yaml \
  --output "Host=localhost;Database=postgres;Username=postgres;Password=postgres;Port=5499" \
  --provider postgres --allow-destructive

Minimal schema.yaml triggering all three failure modes (full repro in the NAP repo at migrations/schema.yaml):

name: repro
roles:
  - { name: app_user, grantTo: [postgres] }
  - { name: app_admin, grantTo: [postgres] }
grants:
  - schema: public
    target: Schema
    privileges: [USAGE]
    roles: [app_user, app_admin]
  - schema: public
    target: AllTablesInSchema
    privileges: [SELECT, INSERT, UPDATE, DELETE]
    roles: [app_user, app_admin]
tables:
  - name: t
    schema: public
    columns:
      - { name: id, type: Uuid, isNullable: false }
      - { name: status, type: Text, isNullable: false, defaultValue: "'pending'" }
    primaryKey: { columns: [id] }

Output

Migration completed successfully
SCHEMA INTEGRITY CHECK FAILED
public.t.status: default expected 'pending' but found 'pending'::text
grant Schema public: missing grant
grant AllTablesInSchema public: missing grant

Exit code 1, despite the database matching the YAML in every meaningful way.

Bug 1 — Text default round-trip

When you ALTER COLUMN ... SET DEFAULT 'pending' on a text column, Postgres stores the default in pg_attrdef as 'pending'::text. The verifier compares the raw YAML literal ('pending') against the inspector's reading ('pending'::text) and reports drift.

Affected file: Migration/Nimblesite.DataProvider.Migration.Core/SchemaIntegrityVerifier.cs. Whatever comparison decides "default expected X but found Y" needs to canonicalise away trailing ::<type> casts so 'pending' ≡ 'pending'::text.

Reproduces for every plain string default. Numeric / boolean / '[]'::jsonb (explicit cast) defaults are unaffected — only naked string literals.

Bug 2 — Multi-role grants

PostgresSupportSchemaInspector.ToGrantDefinitions groups inspector rows by (ObjectName, Role) and emits one PostgresGrantDefinition per group:

.Select(g => new PostgresGrantDefinition {
    ...
    Roles = [g.Key.Role],
    Privileges = g.Select(r => r.Privilege).Distinct().OrderBy(p => p).ToList(),
})

So the inspector always returns single-role grants. Meanwhile SameGrant in the verifier uses zip-equality:

private static bool SameIdentifiers(IReadOnlyList<string> actual, IReadOnlyList<string> expected) =>
    actual.Count == expected.Count
    && actual.Zip(expected).All(pair => SameIdentifier(pair.First, pair.Second));

Any YAML grant with roles: [app_user, app_admin] has Count == 2, while every inspector grant has Count == 1 — never matches.

Suggested fix: in the verifier, expand a desired multi-role grant into one expected entry per role before searching live.Grants, or have the inspector group only by ObjectName (emitting Roles = [a, b, ...] per object).

Bug 3 — AllTablesInSchema target never inspected

PostgresSupportSchemaInspector.InspectGrants returns two kinds of grants:

• InspectSchemaGrants → PostgresGrantTarget.Schema
• InspectTableGrants → PostgresGrantTarget.Table

Nothing ever emits PostgresGrantTarget.AllTablesInSchema. But the YAML serialiser accepts target: AllTablesInSchema (PostgresGrantTargetYamlConverter), and the DDL generator presumably honours it on the way in. So the YAML can declare AllTablesInSchema, the migration applies it (we verified every table in public has the expected per-table privileges in information_schema.table_privileges), but SameGrant's actual.Target == expected.Target always fails because no inspector ever returns AllTablesInSchema.

Suggested fix: before calling SameGrant, the verifier should expand a desired AllTablesInSchema grant into one Table grant per table in the schema (cross-product with desired.Tables), then look each up in live.Grants. Alternatively, after InspectTableGrants, detect "every table has identical (role, privileges)" and synthesise an AllTablesInSchema reading.

Impact

This blocks any consumer of DP that uses multi-role grants or AllTablesInSchema from upgrading past 0.9.10-beta. The migration works; the verifier just refuses to accept its own emitted output.

Filed by Claude Code on behalf of NimblesiteAgenticPlatform. Happy to send a PR if you want — let me know.