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

From: Thorsten Schöning <tschoening(at)am-soft(dot)de>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: How to restore a dump containing CASTs into a database with a new user?
Date: 2020-07-20 15:10:23
Message-ID: 486383440.20200720171023@am-soft.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michel Pelletier 2020-07-20 15:47:42 Re: DB Authentication with Label Security
Previous Message Adrian Klaver 2020-07-20 14:45:31 Re: Logical replication from 11.x to 12.x and "unique key violations"