Re: pg_dump/pg_restore vs default_transaction_read_only under PG 13.2

Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net> writes:
> I am testing the pg_restore of a database with
> default_transaction_read_only=on.

Hm. It's intentional that we reconnect after applying the database
properties, so that they are in effect during the restore. It seems
likely that failing to do so could result in misbehaviors.

Hence, the only way to make this scenario work would be for the
restore script to explicitly override default_transaction_read_only.
That seems like a darn bad idea, really. If pg_dump thinks it's
authorized to ignore that, why shouldn't every other application?

Also, doing so would result in ignoring default_transaction_read_only
no matter what the source of that was. I think it's probably not
hard to construct scenarios where someone would really not be happy
about such behavior.

In short, I'm inclined to say "don't do that". Maybe it'd be
a better idea to apply default_transaction_read_only to particular
roles (and not run pg_restore under such a role).

regards, tom lane