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....
Show quoted text
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