`dump` should support providing `--restrict-key` for Postgres >= 17.6

[leoschweizer]

Description Starting from version 17.6, pg_dump has altered behaviour and adds \restrict and \unrestrict commands to the schema output (cf. release notes). When not explicitly provided as argument via --restrict-key=restrict_key, this will be a random value. Thus, the output provided by dump will no longer be deterministic, and each invocation of dump will produce a different output even when there are no schema changes! This breaks workflows that rely on deterministic output for schema diff detection.

• Version: 2.28.0
• Database: Postgres
• Operating System: debian bookworm

Steps To Reproduce Run dump (or up) twice with no schema changes; Two differing schema.sql files will be created.

Expected Behavior The output should be deterministic and produce identical schema.sql files.

[leoschweizer]

I can think of a few ways to tackle this and I'm willing to create a PR (even though I'm not really familiar with Go), however I would like some feedback from maintainers first what would have a chance to get accepted to not waste anyones time:

• Add a hardcoded restrict key like dumped-by-dbmate; easiest quick fix to make the output deterministic again, but very inflexible and might not suite all use cases
• Add a cli option like --pg-restrict-key either to the root options or all subcommand options that need it; clean solution except that driver-specific options are introduced, which currently I think isn't the case
• There was an entirely different approach at #677 of stripping the commands from the output; not the way I would go, especially given that this is a security measure, but maintainers have to judge what best fits dbmates philosophy

Not sure who would be the responsible maintainers @amacneil @dossy @Flamefork

[dossy]

I would like to see a clean way of adding extra command line arguments to the schema dump command which could be used to solve a number of issues, although I suspect that could introduce some potential security issues.

I agree that I'm not a fan of the approach taken in #677, as the change introduced by Postgres is specifically a security measure. dbmate's parser should be enhanced to recognize and handle it, but not filter it out.

This change is a backwards-compatibility problem for lots of Postgres clients and applications. Not ideal, but using sed to remove these new lines outside of any applications, for testing purposes, might be the best short term solution, mentioned in this comment in sqlc-dev/sqlc #4065:

sed '/^\\restrict /d;/^\\unrestrict /d' dump.sql > dump.sql.tmp && mv dump.sql.tmp dump.sql

[leoschweizer]

@dossy I understand that you prefer a generic solution over introducing special handling for particular options. But about this case, I disagree. Given that a) the default dbmate behaviour is to always create a dump and b) the official usage recommendation is to check the dump file into source control, in my opinion the default behaviour should be to provide deterministic output. I would expect most of the users who use dbmate in that way would not want to end up with dirty dump files after each invocation. So this should not even be an option everyone has to provide, but the default behaviour that can be opted-out of if needed.

To not make the restrict-key entirely predictable when provided by default, it could be appended with the latest version number from the schema migrations table, which gets queried anyways for the dump. Then the output would still be stable as long as no schema changes happen.

[ppechkurov]

Hi! I would like to use the tool for one of my production tools. However, due to the \restrict thing, dbmate load gives me this:

Reading: ./db/schema.sql
Error: pq: syntax error at or near "\"

What are my options here? Should I downgrade my postgres to < 17.6? I'm using dbmate v2.28.0.

Thanks!

[paul-brinkmeier]

Just another data point, this makes it very annoying to include dbmate in any CLI tooling/commit hooks/etc. You're basically forced to wrap it in some shell script.

I'd suggest another way to go about this: Make it possible to pass flags to pg_dump, maybe a bit too powerful but very future-proof. Happy to provide a patch for this or any other approach :) I've been liking dbmate a lot so far.

[paul-brinkmeier]

I see now that this was also suggested in #688 :)