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

Guten Tag Thorsten Schöning,
am Sonntag, 19. Juli 2020 um 21:51 schrieben Sie:

> If they are not only created by superusers, how can I restore CASTs to
> a database owned by some other user? There are no other users than
> the one owning the database in my case.

I've retried things and must have done something wrong before, but the
following works now:

> dropdb ams_sm_mtg
> createdb --encoding=UTF-8 --locale=de_DE.UTF-8 --owner=ams_sm_mtg --template=template0 ams_sm_mtg
> psql --dbname=ams_sm_mtg
> ALTER TYPE inet OWNER TO ams_sm_mtg;

The latter is the important part and needs to be done as someone who
owns the type right now. I might have missed issuing ALTER in the
correct database before. The following doesn't work as well:

> ams_sm_mtg=# SET ROLE ams_sm_mtg;
> SET
> ams_sm_mtg=> ALTER TYPE inet OWNER TO ams_sm_mtg;
> ERROR: must be owner of type inet

So only owners of types can give them to someone else. What I still
don't understand is if that ownership is per database or per user or
per cluster or per schema or ...?

"\dT+" doesn't show that type in "ams_sm_mtg", only really customly
created ones. But I could have different databases with different
users deploying the same schema for the same apps and each fo those
would need to own that type individually. Is that possible?

Mit freundlichen Grüßen,

Thorsten Schöning

--
Thorsten Schöning E-Mail: Thorsten(dot)Schoening(at)AM-SoFT(dot)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