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

> On Jul 19, 2020, at 22:13, Thorsten Schöning <tschoening(at)am-soft(dot)de> wrote:
> Does Postgres support that in an easy way, without the need to reverse
> engineer an otherwise unknown the schema?

It is straight-forward enough to determine the user-created objects in the schema, and then alter their ownership. For new objects, you can set default permissions appropriately.

> That seems very complicated when one simply wants to restore a
> backup into a newly created database.

The complication is arising because you are trying to do two things at the same time: Restore the backup, and use that to alter the permissions as a batch. That's not straight-forward in the case where you have user-defined CASTs. You should alter the ownership of the user-defined objects, and that will allow you to dump and restore the database, if you still need to.

> Additionally, who owns types on which level in the end? To
> successfully restore, I needed to change ownership of type "inet" to
> one new user.

No, you don't, and you (probably) can't change the ownership of "inet". "inet" is a built-in type. The issue is that you have user-defined objects which are owned by the user "postgres"; you should change those to the user that you want, leaving the CASTs owned by "postgres".

--
-- Christophe Pettus
xof(at)thebuild(dot)com