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 486383440.20200720171023@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 Montag, 20. Juli 2020 um 16:34 schrieben Sie:

> I'm not sure I understand exactly how this "feature" would work.

Make internal types used in CASTs owned by the restoring user, like
all other objects are owned automatically as well. Postgres obviously
is able to detect those problems already and reowning automatically
would prevent people from the need to research first and do things
manually afterwards. One could optionally introduce an additional
flag, but if things like "--role" and "--no-owner" are specified,
intention of the users seem pretty clear to me.

In the worst case this problem makes people even NOT use individual
DB-users and use superuser for everything.

Just search for the topic about backing up databases using a read-only
user: It's that difficult, that people suggest creating a special
superuser set into read-only mode, from which it can recover itself
and stuff like that. Simply because granting permissions on unknown
schemas and maintaining default permissions for objects nobody knows
if they ever will be needed isn't really straightforward.

Using one concrete owner for everything right from the start is the
best one can do and that includes being able to reown in case of
restoring backups. And Postgres supports most of that already, only
the CAST-thing has not been thought to an end I guess.

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: Adrian Klaver
Date:
Subject: Re: Logical replication from 11.x to 12.x and "unique key violations"
Next
From: Michel Pelletier
Date:
Subject: Re: DB Authentication with Label Security