Re: How to restore a dump containing CASTs into a database with a new user?

> On Jul 20, 2020, at 08:10, Thorsten Schöning <tschoening(at)am-soft(dot)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
xof(at)thebuild(dot)com