Re: How to restore a dump containing CASTs into a database with a new user? - Mailing list pgsql-general

Guten Tag Christophe Pettus,
am Montag, 20. Juli 2020 um 19:21 schrieben Sie:

> On a database with multiple users, you can't just get away
> with changing the ownership of the types; you have to make sure that
> the USAGE is granted appropriately to other users.

If a user specifies "--no-owner" and "--role=...", it's pretty clear
that the user wants things to be reowned. And that use case could be
supported automatically. If other use cases need additional manual
work that would be fine, but this concrete one does not in theory.

> * A database has user-defined objects in it that only a superuser can create, and,
> * The rest of the database objects are owned by that superuser, and,
> * You want to change the ownership of the database objects that can be changed, and,
> * You want to have a single backup that you can restore multiple
> times, changing the ownership in a different way each time, and,
> * You want to use pg_restore to do it.

And pg_restore does all that already, it only misses the special case
of CASTs.

> This would require a fair amount of surgery to pg_restore.  Right
> now, pg_restore doesn't really have a "remap these users"
> functionality.

It's not necessarily about remapping users in general in theory, but
instead something like recognizing that creating a CAST first needs to
make associated types reowned as well. This could be a fallback
strategy like trying to create the CAST, recognizing that it fails
because of wrong ownership of types and simply reown the types to the
current role.

Or creating the CAST itself could be changed as well to optionally do
that. In setups like mine with one user per database it's absolutely
safe and totally makes sense to reown types for an individual DB,
otherwise creating the CAST fails anyway. But obviously I want that
CAST, so would do it manually, which is unnecessary in theory and
which one could tell the statement with some additional flag or else.

Even in cases with multiple different users per DB reowning types make
sense, because one has the problem when creating the CAST anyway. So
either there's some user designed to create the CAST with, which by
definition needs to own the associated type anyway. Or it is done as
superuser in which case Postgres could simply not reown because it's
not necessary. Depends on if even different superusers need to own
types or not.

>   --no-owner *looks* like it does that, and can be
> used for that in certain cases, but the user-remapping functionality
> of it is really a side-effect.  It happens to change the user
> because instead of altering the user to what it is in the backup, it
> just accepts the default ownership based on the user it is connected as.

And why do I need to care why things work like they totally make sense
and I need them? :-) I just see that things work already besides one
minor annoyance. So what is a side-effect in your opinion now could
easily be communicated as feature as well.

> You can accomplish the same thing by restoring as the superuser,
> not having to alter the ownership of any internal type, and then
> changing the ownership of the user-created objects in the new
> database once it is restored.[...]

But that is far more complicated, because one needs to know ALL
objects in the restored schema to reown them manually. It takes more
time because one needs to do it manually and for each object
individually. It is error prone because one can easily miss things,
especially if schemas are developed further over time etc.

My current, already supported approach is far easier. I only need to
take care about those CASTs manually now, nothing else yet.

Mit freundlichen Grüßen,

Thorsten Schöning

--
Thorsten Schöning       E-Mail: Thorsten.Schoening@AM-SoFT.de
AM-SoFT IT-Systeme      http://www.AM-SoFT.de/

Telefon...........05151-  9468- 55
Fax...............05151-  9468- 88
Mobil..............0178-8 9468- 04

AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln
AG Hannover HRB 207 694 - Geschäftsführer: Andreas Muchow