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 07:19 schrieben Sie:

> No, you don't, and you (probably) can't change the ownership of
> "inet". "inet" is a built-in type.

I'm somewhat sure I did and can answer my own questions 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;

This makes this type really owned by the given user, but as it seems
only for the current database! This can be seen in the table
"pg_type", in which the column "typowner" really changes to the new
user-ID:

> psql --dbname=ams_sm_mtg
> ALTER TYPE inet OWNER TO postgres;

> inet    11      10
> _inet   11      10

vs.

> psql --dbname=ams_sm_mtg
> ALTER TYPE inet OWNER TO ams_sm_mtg;

> inet    11      16389
> _inet   11      16389

"pg_type" contains some other user created types and those have the
same user-ID. The important thing is that doing this with another
database doesn't seem to influence the one of interest:

> psql --dbname=template1
> ALTER TYPE inet OWNER TO postgres;

This keeps the following in the table of interest:

> inet    11      16389
> _inet   11      16389

Using other manually created databases and users results in the same:

> psql --dbname=ams_db_login
> ALTER TYPE inet OWNER TO ams_db_login;

This leads to the following in the given table:

> inet    11      16390
> _inet   11      16390

But keeps things in other tables:

> inet    11      16389
> _inet   11      16389

Additionally, when viewing "pg_table" connected as and to
"ams_db_login", it shows different types than are available when
viewing "pg_table" as and for "ams_sm_mtg". This is another strong
hint that those types are managed per database.

So things seem to work as expected, with the only caveat that one
needs to make some types being owned by new users BEFORE actually
restoring. The problem of course is to know which types those are,
seems one needs to restore, look for errors, ALTER, drop, restore
again etc.

Would be far easier if Postgres would do that automatically like it
seems to do for most other objects. The important point is that owning
those types seems to be per database, so things should be safe to do
automatically.

> [...]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".

The error messages and docs say otherwise and changing the owner to a
user which doesn't exist at all in the source-cluster doesn't make
sense as well. When creating the dump, I can't know into which target
database owned by which user it gets restored at some point.

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