Database migrations: preserve permissions and verify rollback

The Migration Security Boundary

Database schema migrations and logical exports handle schema evolution and data portability, but they introduce significant and frequently misunderstood security boundaries. The official PostgreSQL documentation explicitly warns that restoring a dump causes the destination database to execute arbitrary code of the source superusers' choice. This occurs because an exported SQL script can contain function definitions, trigger declarations, and procedural language blocks that the restoring user will inadvertently execute.

Partial dumps and partial restores do not limit or mitigate this execution risk. If the source superusers or the source environment are not completely trusted, application engineers must meticulously inspect the dumped SQL statements before restoring them into any sensitive or elevated environment. Migration scripts must be treated as highly privileged execution artifacts, not merely declarative state definitions. A compromised lower environment can theoretically pivot to a production environment by embedding malicious logic inside a seemingly benign database migration or structural export.

Managing Access Controls and Ownership

Preserving exact ownership strings across different infrastructure tiers frequently leads to deployment failures and security regressions. By default, the export utility issues ALTER OWNER or SET SESSION AUTHORIZATION statements to set the ownership of created database objects to match the exact original database state. These statements will inherently fail when the resulting script is run, unless it is started by a database superuser or the specific user that owns all of the objects in the script.

Application engineers generating migration artifacts for cross-environment deployment frequently use the --no-owner parameter. This parameter suppresses ownership commands, forcing the restored objects to be owned by the executing user rather than the origin user. Additionally, the --no-privileges flag prevents the dumping of access privileges, specifically GRANT and REVOKE commands. Utilizing these flags forces target environments to define their own explicit access controls through distinct post-migration scripts, effectively reducing the risk of accidentally importing overly permissive development access control lists into production environments.

Row-Level Security During Extraction

Extracting data from tables protected by row-level security introduces specific export constraints. By default, the export process attempts to set the session variable row_security to off to ensure that all data is comprehensively dumped from the target table. If the executing user does not possess sufficient privileges to bypass row security policies, PostgreSQL throws an explicit error and halts the operation.

The --enable-row-security parameter explicitly instructs the utility to set row_security to on instead. This allows the user to dump only the specific parts of the table contents that their assigned role has access to. The official documentation notes that if you use this option, you must format the dump using explicit insert commands via the --column-inserts flag. This is required because the standard COPY FROM mechanism utilized during restoration does not evaluate row security. This mechanism is crucial when building synthetic migration tests or exporting tenant-specific datasets without violating application-level data isolation boundaries.

Implementation Tradeoffs and Failure Modes

Verifying migration rollbacks introduces profound performance, locking, and state consistency tradeoffs. Requesting exclusive locks on database objects while running a parallel dump causes severe failure modes. The leader process requests shared access locks on objects to ensure they are not deleted during the run. If another client requests an exclusive lock, it queues behind the shared lock, and subsequent access attempts queue behind the exclusive lock request.

To prevent this classic deadlock scenario, worker processes request locks using the NOWAIT option and immediately abort the dump if denied. Furthermore, to perform a parallel dump, the database server must support synchronized snapshots. Without synchronized snapshots, different worker jobs are not guaranteed to see the exact same data, leading to a fundamentally inconsistent backup.

Additionally, using the --disable-triggers option to suspend referential integrity checks during a data restore currently requires superuser execution privileges. Failure to execute such scripts as a superuser causes deployment failures, while granting superuser access to automated deployment pipelines violates the core principle of least privilege.

Concrete Worked Example

Consider a hypothetical synthetic fixture where a new microservice schema requires specific, tightly scoped read-only role assignments. The forward migration script creates a schema named metrics and explicitly grants USAGE to a predefined read_only_role. It then alters the default privileges within the metrics schema to automatically grant SELECT permissions on newly created tables to the read_only_role.

The verification pipeline executes this script against a localized ephemeral database instance. The corresponding rollback script must explicitly reverse these operations: it alters default privileges to revoke SELECT for the read_only_role, revokes USAGE on the metrics schema, and finally drops the schema completely.

The automated testing pipeline systematically executes the forward script, asserts that the read_only_role can successfully query a seeded synthetic test table, executes the rollback script, and finally asserts that the schema no longer exists and that any queries by the read_only_role result in immediate permission denial errors. This ensures the rollback is both syntactically valid and functionally complete.

Safe Test Matrix for Migration Roles

The following matrix defines expected allowed and denied outcomes for database roles executing the migration testing pipeline on a synthetic testing fixture.

• Superuser executing default dump: Allowed to bypass row-level security and lock tables.
• Unprivileged user executing default dump on RLS table: Denied with a privilege bypass error.
• Unprivileged user executing dump with enable-row-security on RLS table: Allowed, returning a partial dataset matching their authorization.
• Standard role executing dump with the no-owner flag: Allowed, resulting script assigns objects safely to the restoring user.
• Standard role restoring a default dump containing ALTER OWNER commands: Denied with an authorization failure due to insufficient privileges.

Sources and Acknowledgments

Drafted with Gemini and checked for source links, advisory identifiers, structure and unsupported product claims. Examples are synthetic and are not scan results.

Drafted with Gemini and checked for source links, advisory identifiers, structure and unsupported product claims. Examples are synthetic and are not scan results.

• PostgreSQL: Documentation: 18: pg_dump