Re: Allow pg_dumpall to work without pg_authid

On 22 February 2017 at 07:33, Robins Tharakan <tharakan(at)gmail(dot)com> wrote:
> Stephen,
>
> On 20 February 2017 at 08:50, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>>
>> The other changes to use pg_roles instead of pg_authid when rolpassword
>> isn't being used look like they should just be changed to use pg_roles
>> instead of using one or the other. That should be an independent patch
>> from the one which adds the option we are discussing.
>
>
> Sure. Attached are 2 patches, of which 1 patch just replaces pg_authid with
> pg_roles in pg_dumpall. The only exceptions there are buildShSecLabels()
> & pg_catalog.binary_upgrade_set_next_pg_authid_oid() which I thought
> should still use pg_authid.

Patch, and life, is simpler if we use just one or the other, IMHO.

>> Perhaps --no-role-passwords instead?
>>
> Makes Sense. The updated patch uses this name.
>
>>
>> > pg_dumpall --no-pgauthid --globals-only > a.sql
>>
>> Does that then work with a non-superuser account on a regular PG
>> instance also? If not, I'd like to suggest that we consider follow-on
>> patches to provide options for whatever else currently requires
>> superuser on a regular install.
>>
> If I understand that correctly, the answer is Yes. I didn't test all db
> objects,
> but trying to do a pg_dumpall using a non-priviledge user does successfully
> complete with all existing users dumped successfully.
>
> pg_dumpall --globals-only --no-role-password > a.sql

It looks to me like you'd need to use --no-security-labels as well, in
most cases since that accesses pg_authid also.

The patch seemed to be missing substantial chunks of coding, so I've
added those also.

I've also added checks to prevent it running with other mutually
exclusive options.

Reworded doc changes also.

Tested, but no tests added for this.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services