Date: Mon, 24 Mar 2025 17:05:52 +0100 (CET)
From: Dimitrios Apostolou <jimis@gmx.net>
To: Adrian Klaver <adrian.klaver@aklaver.com>
cc: pgsql-general@lists.postgresql.org
Subject: Re: Experience and feedback on pg_restore --data-only
In-Reply-To: <9e8852ec-d8fa-4fb6-a2d3-cd188ce0744a@aklaver.com>
Message-ID: <0be9c9d4-a191-b08f-0f49-9dbd2ffacb33@gmx.net>
References: <53760c70-4a87-a453-9e02-57abc9cb2e54@gmx.net> <9e8852ec-d8fa-4fb6-a2d3-cd188ce0744a@aklaver.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="0-541055152-1742832353=:1515035"
UI-OutboundReport: notjunk:1;M01:P0:aVD8ue5lSAs=;GmUssm9XRzx6TzS5nfTjJMvVJO5
 tuppzCRxR51UTu+VTf4o6fGWSgTIbZSBc4ah9IxZ+vgDYKXZ2jE68i3ackyb1GpWTUiX63WRr
 r6FsR0SKA1dWgFHCtUcEN+MJbICdFNaPcuCNaHwrKnI0PR4WCZL+LFXO+jtxDnf4oCiyxFsb3
 CUNaiY8EUxY50nzO1ip/bitOKjS7fToiz2am/TimR7K4TjdvJayt3psoKthFOm64JZjoI/gAm
 79KAtmUtAjHkNt11Lr8LCznIHZFfvZCD0+1oJhm6zHz4dA1b24P1PnTfgtNNV7WSMODJSa8lG
 cmazCX4CFlsoRedaY3HogMqOxbKhQyYH9B5dkIx0Olluszud7SAPEbczzsn2ZcIz98wvJL53a
 m14zjUkzKXe2jyop3QglQB5T/HeXla1I3cp1IPNHjmTlXx1Oe3Hsno/AiilHCgTrtxkx5anOv
 wdiCJ8muW68k9YpYn/j/bvNa+iSDZtzLpUsVodkrw5O2oIqziwxKyoTTfOwlh5J8A7B08TrYK
 FjyQBC7wzHn6sXzPiiWGoxtPKWTS5vdR83e2KaShRgaJjl6VtTTR2BMZdY5veGUpPT255WxmO
 QLcKRufQLtmXwN1WpJHc4cCd/RbHbr+2kWRQ5HXmUWRgY+RVig/7xHZAwR1pgf931+v/tidk/
 9zYdf2fcx7YLsCsapy8oXQEshpjOrERp89vKn4e8onOCGSNYeeJmkk4Lxzm5WSCUEC3QCOMl1
 GYMUt+O4qfNn3CPuucVHm0DxRStPEuIN4bX4A5MMP7NZMRNCiVDom/4iFJnbodYvPbFvDFU67
 3TjHsAndRwCtPnCixH22bXD+i8kl8VetPPotge89H2NNImAkKcoOtiOTqA7Ex4jr/dJ4c5g1V
 by1ySSG4fdMxkwykFXZMVM90+BgJT/rEbUdTW5MkjQ3Lw5e4oQ1bSPK5j8ZDuxg4K8CCSfyzP
 opNEuNi4zchZ73H89fJXZKMR9gCQU256b8NKBfcZ2GOCpaC6zDZx3gFfFUkG5w8zCSj/5WlJ/
 3RB0Stlt7T0cypVKu015STmX7z7LH5dfrgmqh2cWVO6I0FDxffIkLh6kaDGAMuhn1MKDdctag
 b1czQM7tgvn+Ra6xNc7NDnwFQ5MzPG+91GjZnG64UXLB7La8nhBosDwn+R75wFdGBrTG3uYUE
 AIzA68WiRU4nM1n0LzNfSfGWJVvIZM0E6csO8S4x0I3Bveg0OAAUjrGGevaujQ7aBs6lzp0yq
 mMUQWtCiYOrBSJCpl1FkuAzW/xUKy8RoFpC1u2ObEMhgTQ7dXx+nFsgmIB3potQj+/KT7h33R
 8D7ZhvfVddlU13ILnjjsTNErSjukdI5JVeOytEAR0tolSwc2BrpaMOSWqxJzmyWxyxEmjcltI
 HiTjqfbJwmzFZQZ/VXB8dsBDao9Vruqi9d28ZWoLI1lGIXwDziLtYl1l3a+mfYY9bH73mG8XD
 sQ4lQxA==
Archived-At: <https://www.postgresql.org/message-id/0be9c9d4-a191-b08f-0f49-9dbd2ffacb33%40gmx.net>
Precedence: bulk

  This message is in MIME format.  The first part should be readable text,
  while the remaining parts are likely unreadable without MIME-aware tools.

--0-541055152-1742832353=:1515035
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: quoted-printable

On Sun, 23 Mar 2025, Adrian Klaver wrote:

> On 3/20/25 15:48, Dimitrios Apostolou wrote:
>>
>>  * plenty of permission denials for both ALTER OWNER or SET SESSION
>>   =C2=A0 AUTHORIZATION (depending on command line switches).=C2=A0 Both=
 of these
>>   =C2=A0 require superuser privilege, but in my case this is not really=
 needed.
>>   =C2=A0 Dbowner has CREATEROLE and is the one who creates all the role=
s (WITH
>>   =C2=A0 SET TRUE), and their private schemata in the specific database=
.=C2=A0 Things
>>   =C2=A0 would work if pg_restore did "SET ROLE" instead of "SET SESSIO=
N
>>   =C2=A0 AUTHORIZATION" to switch user. Is this a straightforward chang=
e or
>>     there are issues I don't see?
>
> If this is --data-only what are the ALTER OWNER and SET SESSION AUTHORIZ=
ATION
> for?

You are probably right, early in my trials I was running pg_restore
without --data-only as a non-superuser so it might be that the error
message comes from there. Haven't noted the exact command for this error
message unfortunately.

The point still stands though. The dbowner user is administrator for this
database, and has CREATEd the users with the right to SET ROLE as any of
them. Those other users own tables in their private schemas. But
pg_restore does SET SESSION AUTHORIZATION which requires superuser priv
instead of SET ROLE. I wonder what the reasons are for that.

Maybe pg_restore could either:

- do SET ROLE instead of SET SESSION AUTHORIZATION
- temporarily use the --superuser powers just for issuing the ALTER ROLE.

Regards,
Dimitris

--0-541055152-1742832353=:1515035--