Thread: BUG #16772: Options --disable-triggers in combination with --superuser does not have the expected result

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

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

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