pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #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>
  2020-12-14 16:41 ` Re: BUG #16772: Options --disable-triggers in combination with --superuser does not have the expected result Tom Lane <tgl@sss.pgh.pa.us>
  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: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   ` Re: BUG #16772: Options --disable-triggers in combination with --superuser does not have the expected result Edwin Polkerman <edwin.polkerman@splendiddata.com>
  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: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 ` Re: BUG #16772: Options --disable-triggers in combination with --superuser does not have the expected result Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-12-14 16:46   ` Edwin Polkerman <edwin.polkerman@splendiddata.com>
  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