pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #16772: Options --disable-triggers in combination with --superuser does not have the expected result
3+ messages / 3 participants
[nested] [flat]
* BUG #16772: Options --disable-triggers in combination with --superuser does not have the expected result
@ 2020-12-14 16:22 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2020-12-14 16:22 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: edwin.polkerman@splendiddata.com
The following bug has been logged on the website:
Bug reference: 16772
Logged by: Edwin Polkerman
Email address: edwin.polkerman@splendiddata.com
PostgreSQL version: 13.1
Operating system: CentOS 7.8.2003 (x86_64)
Description:
Trying to create a dump with following command line:
pg_dumpall -h <database host> -p 5432 -U user1 -f /tmp/dump.out
--disable-triggers -S user2 -v
User1 is a regular user and user2 is a superuser like the documentation
states: "Specify the superuser user name to use when disabling triggers"
Expected result:
The dump will complete without error's
Observed result:
Directly after starting the command following error is shown:
pg_dumpall: executing SELECT pg_catalog.set_config('search_path', '',
false);
pg_dumpall: executing SELECT oid, rolname, rolsuper, rolinherit,
rolcreaterole, rolcreatedb, rolcanlogin, rolconnlimit, rolpassword,
rolvaliduntil, rolreplication, rolbypassrls,
pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, rolname =
current_user AS is_current_user FROM pg_authid WHERE rolname !~ '^pg_' ORDER
BY 2
pg_dumpall: error: query failed: ERROR: permission denied for table
pg_authid
pg_dumpall: error: query was: SELECT oid, rolname, rolsuper, rolinherit,
rolcreaterole, rolcreatedb, rolcanlogin, rolconnlimit, rolpassword,
rolvaliduntil, rolreplication, rolbypassrls,
pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, rolname =
current_user AS is_current_user FROM pg_authid WHERE rolname !~ '^pg_' ORDER
BY 2
Although a superuser is provided with -S, it looks like this is not used but
it uses the user (user1) making the connection in the connection string.
Running the command as superuser (-U user2) and omitting -S works as
expected
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #16772: Options --disable-triggers in combination with --superuser does not have the expected result
@ 2020-12-14 16:41 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2020-12-14 16:41 UTC (permalink / raw)
To: edwin.polkerman@splendiddata.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> Trying to create a dump with following command line:
> pg_dumpall -h <database host> -p 5432 -U user1 -f /tmp/dump.out
> --disable-triggers -S user2 -v
> User1 is a regular user and user2 is a superuser like the documentation
> states: "Specify the superuser user name to use when disabling triggers"
I think you misunderstood the docs; perhaps there's an opportunity to
clarify them. The pg_dumpall run itself has to run as superuser
so that it can extract role passwords, therefore user1 has to be
superuser. What "-S" is for is to specify a superuser role name that
the dump's output script can use *at restore time* to monkey with
trigger settings. That switch exists mainly because you might be planning
to restore into a different installation with different superusers.
Therefore, that name is *not* used during the dump.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #16772: Options --disable-triggers in combination with --superuser does not have the expected result
@ 2020-12-14 16:46 Edwin Polkerman <edwin.polkerman@splendiddata.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Edwin Polkerman @ 2020-12-14 16:46 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
Thank you very much for the clarification. I indeed misunderstood the
documentation on this one....
On 12/14/20 5:41 PM, Tom Lane wrote:
> PG Bug reporting form <noreply@postgresql.org> writes:
>> Trying to create a dump with following command line:
>> pg_dumpall -h <database host> -p 5432 -U user1 -f /tmp/dump.out
>> --disable-triggers -S user2 -v
>> User1 is a regular user and user2 is a superuser like the documentation
>> states: "Specify the superuser user name to use when disabling triggers"
> I think you misunderstood the docs; perhaps there's an opportunity to
> clarify them. The pg_dumpall run itself has to run as superuser
> so that it can extract role passwords, therefore user1 has to be
> superuser. What "-S" is for is to specify a superuser role name that
> the dump's output script can use *at restore time* to monkey with
> trigger settings. That switch exists mainly because you might be planning
> to restore into a different installation with different superusers.
> Therefore, that name is *not* used during the dump.
>
> regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2020-12-14 16:46 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-12-14 16:22 BUG #16772: Options --disable-triggers in combination with --superuser does not have the expected result PG Bug reporting form <noreply@postgresql.org>
2020-12-14 16:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-12-14 16:46 ` Edwin Polkerman <edwin.polkerman@splendiddata.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox