> On Jul 20, 2020, at 08:10, Thorsten Schöning <tschoen...@am-soft.de> wrote:
> Make internal types used in CASTs owned by the restoring user, like
> all other objects are owned automatically as well.
I don't think that we want to do that, or that we even have to.
Having a restore tool make automatic changes to the ownership of objects in the
database it is restoring into seems like a bad idea, especially when those
ownership changes are not part of the backup itself. 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.
Again, this is to support a very specific use-case:
* 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.
This would require a fair amount of surgery to pg_restore. Right now,
pg_restore doesn't really have a "remap these users" functionality. --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.
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. This can be
done entirely with existing tools, and doesn't need any changes to pg_restore,
or even having to do ownership changes of internal types (which I strongly
suspect will bite you later).
--
-- Christophe Pettus
x...@thebuild.com
