Обсуждение: How to restore a dump containing CASTs into a database with a new user?

Hi all,

one of my apps and databases uses custom CASTs and is used with the
user "postgres" for historical reasons. I would like to change that to
use a non-superuser for that app+database only. So I dumped the DB
using the C-format and tried to restore into a newly creeated DB:

> createuser --encrypted --pwprompt ams_sm_mtg
> createdb --encoding=UTF-8 --locale=de_DE.UTF-8 --owner=ams_sm_mtg --template=template0 ams_sm_mtg
> pg_restore --dbname=ams_sm_mtg --no-owner --role=ams_sm_mtg < pg_dump/dump.c

Expectation was that whatever gets created in that DB is owned by the
new user. But I'm running into the following error:

> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 2721; 2605 342334 CAST CAST (character varying AS inet)
> pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of type character varying or type inet
>     Command was: CREATE CAST (character varying AS inet) WITH FUNCTION public.fn_cast_inet_from_varchar(character
varying,integer, boolean) AS ASSIGNMENT; 

Most things I've found are related to that topic is about PLPGSQL,
which is not the problem I have (yet?). Somewhere the use of "-n" has
been suggested and restoring succeeds with that, but my CASTs are
missing afterwards. So that is not a solution.

> pg_restore --dbname=ams_sm_mtg --no-owner --role=ams_sm_mtg -n public < pg_dump/dump.c

I'm having trouble to understand the root cause and how things should
work:

Is there some wrong owner in the dump related to anything of the CAST?

Or can the CAST really only be created when the new DB-owner owns
those types? Is that necessary per database then or globally for the
public schema or one of the template databases or ...?

What is the proper way to restore a dump containing arbitrary CASTs? I
would have expected that pg_restore is handling everything, like it
does for all other objects.

Thanks!

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

=?utf-8?Q?Thorsten_Sch=C3=B6ning?= <tschoening@am-soft.de> writes:
> Expectation was that whatever gets created in that DB is owned by the
> new user. But I'm running into the following error:

>> pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of type character varying or type inet

The error message seems pretty clear to me.

> Or can the CAST really only be created when the new DB-owner owns
> those types? Is that necessary per database then or globally for the
> public schema or one of the template databases or ...?

It's a security thing.  A user who can create such a cast can thereby
change the behavior of other people's queries.

            regards, tom lane

Guten Tag Tom Lane,
am Sonntag, 19. Juli 2020 um 20:37 schrieben Sie:

> It's a security thing.  A user who can create such a cast can thereby
> change the behavior of other people's queries.

I'm not sure what your are telling me: Can CASTs only be created by
superusers? I didn't read that in the docs.

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.

Even if there was, I don't see why CASTs should be handled differently
than all other objects successfully restored to have a new owner, when
CASTs seem to be per database as well. Looking at the source database,
the CASTs in question are only shown for the database they are used
in, no other.

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

=?windows-1250?Q?Thorsten_Sch=F6ning?= <tschoening@am-soft.de> writes:
> Guten Tag Tom Lane,
> am Sonntag, 19. Juli 2020 um 20:37 schrieben Sie:
>> It's a security thing.  A user who can create such a cast can thereby
>> change the behavior of other people's queries.

> I'm not sure what your are telling me: Can CASTs only be created by
> superusers? I didn't read that in the docs.

The CREATE CAST man page says

    To be able to create a cast, you must own the source or the target
    data type and have USAGE privilege on the other type.

Right after that it says

    To create a binary-coercible cast, you must be superuser. (This
    restriction is made because an erroneous binary-coercible cast
    conversion can easily crash the server.)

but that is *not* what's at issue in your case.

> If they are not only created by superusers, how can I restore CASTs to
> a database owned by some other user?

In this case, you have to restore the cast as superuser, because nobody
else is going to be treated as owning these built-in types.

> Even if there was, I don't see why CASTs should be handled differently
> than all other objects successfully restored to have a new owner, when
> CASTs seem to be per database as well.

The restriction is there primarily because casts don't have names in any
normal sense, so users might invoke them without realizing it.  There's
no way to protect yourself against that by, say, using a restricted
search_path.

            regards, tom lane

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.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

Guten Tag Tom Lane,
am Sonntag, 19. Juli 2020 um 22:37 schrieben Sie:

> In this case, you have to restore the cast as superuser, because nobody
> else is going to be treated as owning these built-in types.

How do I do that when I have large dumps with lots of those CASTs and
in worst case don't even know that, because it's of some proprietary
app?

Would I need to restore the whole dump as super user? But how do I own
all those restored contents to some other database user afterwards?

And I don't mean individually GRANTing permissions on tables and
sequences and ... and taking additionally care of defauolts in case
new tables and sequences and ... get added. That reads so complicated.
I simply want to make everything in a database owned by one user, like
is easily possible in e.g. file systems and is the case with how I
restored.

But then CASTs don't work easily.

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

> On Jul 19, 2020, at 14:25, Thorsten Schöning <tschoening@am-soft.de> wrote:
> Would I need to restore the whole dump as super user? But how do I own
> all those restored contents to some other database user afterwards?

In this case, you may need to change the ownership of the various objects directly in the database, rather than using
dump/restoreas a way of changing ownership all at once.  This is not infrequent when you have an existing database in
whicha superuser owns everything 
--
-- Christophe Pettus
   xof@thebuild.com

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

> On Jul 19, 2020, at 22:13, Thorsten Schöning <tschoening@am-soft.de> wrote:
> Does Postgres support that in an easy way, without the need to reverse
> engineer an otherwise unknown the schema?

It is straight-forward enough to determine the user-created objects in the schema, and then alter their ownership.  For
newobjects, you can set default permissions appropriately. 

> That seems very complicated when one simply wants to restore a
> backup into a newly created database.

The complication is arising because you are trying to do two things at the same time: Restore the backup, and use that
toalter the permissions as a batch.  That's not straight-forward in the case where you have user-defined CASTs.  You
shouldalter the ownership of the user-defined objects, and that will allow you to dump and restore the database, if you
stillneed to. 

> 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.

No, you don't, and you (probably) can't change the ownership of "inet".  "inet" is a built-in type.  The issue is that
youhave user-defined objects which are owned by the user "postgres"; you should change those to the user that you want,
leavingthe CASTs owned by "postgres". 

--
-- Christophe Pettus
   xof@thebuild.com

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

> On Jul 20, 2020, at 02:28, Thorsten Schöning <tschoening@am-soft.de> wrote:
>
> 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.

I'm not sure I understand exactly how this "feature" would work.  It seems to be "in the case that I am using CASTs
thatinclude internal types and restoring to a different, non-superuser user than the original one in the database that
wasdumped from, change the owner of internal types to make sure that my CAST restores work."  That strikes me as a
*very*ad hoc feature indeed. 

--
-- Christophe Pettus
   xof@thebuild.com

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

> On Jul 20, 2020, at 08:10, Thorsten Schöning <tschoening@am-soft.de> wrote:
> Make internal types used in CASTs owned by the restoring user, like
> all other objects are owned automatically as well.

I don't think that we want to do that, or that we even have to.

Having a restore tool make automatic changes to the ownership of objects in the database it is restoring into seems
likea bad idea, especially when those ownership changes are not part of the backup itself.  On a database with multiple
users,you can't just get away with changing the ownership of the types; you have to make sure that the USAGE is granted
appropriatelyto other users. 

Again, this is to support a very specific use-case:

* A database has user-defined objects in it that only a superuser can create, and,
* The rest of the database objects are owned by that superuser, and,
* You want to change the ownership of the database objects that can be changed, and,
* You want to have a single backup that you can restore multiple times, changing the ownership in a different way each
time,and, 
* You want to use pg_restore to do it.

This would require a fair amount of surgery to pg_restore.  Right now, pg_restore doesn't really have a "remap these
users"functionality.  --no-owner *looks* like it does that, and can be used for that in certain cases, but the
user-remappingfunctionality of it is really a side-effect.  It happens to change the user because instead of altering
theuser to what it is in the backup, it just accepts the default ownership based on the user it is connected as. 

You can accomplish the same thing by restoring as the superuser, not having to alter the ownership of any internal
type,and then changing the ownership of the user-created objects in the new database once it is restored.  This can be
doneentirely with existing tools, and doesn't need any changes to pg_restore, or even having to do ownership changes of
internaltypes (which I strongly suspect will bite you later). 

--
-- Christophe Pettus
   xof@thebuild.com

Guten Tag Christophe Pettus,
am Montag, 20. Juli 2020 um 19:21 schrieben Sie:

> On a database with multiple users, you can't just get away
> with changing the ownership of the types; you have to make sure that
> the USAGE is granted appropriately to other users.

If a user specifies "--no-owner" and "--role=...", it's pretty clear
that the user wants things to be reowned. And that use case could be
supported automatically. If other use cases need additional manual
work that would be fine, but this concrete one does not in theory.

> * A database has user-defined objects in it that only a superuser can create, and,
> * The rest of the database objects are owned by that superuser, and,
> * You want to change the ownership of the database objects that can be changed, and,
> * You want to have a single backup that you can restore multiple
> times, changing the ownership in a different way each time, and,
> * You want to use pg_restore to do it.

And pg_restore does all that already, it only misses the special case
of CASTs.

> This would require a fair amount of surgery to pg_restore.  Right
> now, pg_restore doesn't really have a "remap these users"
> functionality.

It's not necessarily about remapping users in general in theory, but
instead something like recognizing that creating a CAST first needs to
make associated types reowned as well. This could be a fallback
strategy like trying to create the CAST, recognizing that it fails
because of wrong ownership of types and simply reown the types to the
current role.

Or creating the CAST itself could be changed as well to optionally do
that. In setups like mine with one user per database it's absolutely
safe and totally makes sense to reown types for an individual DB,
otherwise creating the CAST fails anyway. But obviously I want that
CAST, so would do it manually, which is unnecessary in theory and
which one could tell the statement with some additional flag or else.

Even in cases with multiple different users per DB reowning types make
sense, because one has the problem when creating the CAST anyway. So
either there's some user designed to create the CAST with, which by
definition needs to own the associated type anyway. Or it is done as
superuser in which case Postgres could simply not reown because it's
not necessary. Depends on if even different superusers need to own
types or not.

>   --no-owner *looks* like it does that, and can be
> used for that in certain cases, but the user-remapping functionality
> of it is really a side-effect.  It happens to change the user
> because instead of altering the user to what it is in the backup, it
> just accepts the default ownership based on the user it is connected as.

And why do I need to care why things work like they totally make sense
and I need them? :-) I just see that things work already besides one
minor annoyance. So what is a side-effect in your opinion now could
easily be communicated as feature as well.

> You can accomplish the same thing by restoring as the superuser,
> not having to alter the ownership of any internal type, and then
> changing the ownership of the user-created objects in the new
> database once it is restored.[...]

But that is far more complicated, because one needs to know ALL
objects in the restored schema to reown them manually. It takes more
time because one needs to do it manually and for each object
individually. It is error prone because one can easily miss things,
especially if schemas are developed further over time etc.

My current, already supported approach is far easier. I only need to
take care about those CASTs manually now, nothing else yet.

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