public inbox for [email protected]  

help / color / mirror / Atom feed

From: Dimitrios Apostolou <[email protected]>
To: Adrian Klaver <[email protected]>
Cc: [email protected]
Subject: Re: Experience and feedback on pg_restore --data-only
Date: Mon, 24 Mar 2025 17:05:52 +0100 (CET)
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>

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
>>     AUTHORIZATION (depending on command line switches).  Both of these
>>     require superuser privilege, but in my case this is not really needed.
>>     Dbowner has CREATEROLE and is the one who creates all the roles (WITH
>>     SET TRUE), and their private schemata in the specific database.  Things
>>     would work if pg_restore did "SET ROLE" instead of "SET SESSION
>>     AUTHORIZATION" to switch user. Is this a straightforward change or
>>     there are issues I don't see?
>
> If this is --data-only what are the ALTER OWNER and SET SESSION AUTHORIZATION
> 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

view thread (2+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Experience and feedback on pg_restore --data-only
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox