Re: How to restore a dump containing CASTs into a database with a new user? - Mailing list pgsql-general

From Thorsten Schöning
Subject Re: How to restore a dump containing CASTs into a database with a new user?
Date
Msg-id 1097194106.20200720071316@am-soft.de
Whole thread Raw
In response to Re: How to restore a dump containing CASTs into a database with a new user?  (Christophe Pettus <xof@thebuild.com>)
Responses Re: How to restore a dump containing CASTs into a database with a new user?
List pgsql-general
Guten Tag Christophe Pettus,
am Sonntag, 19. Juli 2020 um 23:38 schrieben Sie:

> In this case, you may need to change the ownership of the various
> objects directly in the database, rather than using dump/restore as
> a way of changing ownership all at once.

Does Postgres support that in an easy way, without the need to reverse
engineer an otherwise unknown the schema?

What I've read it does not, one always needs to know which objects are
used, needs to additionally handle newly created objects not used yet
etc. That seems very complicated when one simply wants to restore a
backup into a newly created database.

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. Is that per database restored into or for the whole
cluster now? Because I easily have multiple of the same databases for
different tenants and all those tenants are individual users which
would need to own that type "inet" to be able to restore their
individual databases.

> This is not infrequent
> when you have an existing database in which a superuser owns everything

In my setup it only seems to be necessary for the CASTs, everything
else seems to be properly reowned how I restore. And that approach
seems much easier than rewoning individual objects.

So depending on who owns the type "inet" on which level in the end,
Postgres could reown those types automatically during restores as
well. Looking at the provided arguments and used commands, it's very
clear what the user wants.

OTOH, if that type is owned on a higher level than the restored
database itself only, that seems to be a design issue. Because that
would prevent other users owning that type for their individual
databases as well.

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




pgsql-general by date:

Previous
From: Thomas Munro
Date:
Subject: Re: PG 9.5.5 cores on AIX 7.1
Next
From: Christophe Pettus
Date:
Subject: Re: How to restore a dump containing CASTs into a database with a new user?