Thread: pg_dumpall with flag --no-role-passwords omits roles comments as well

Hello,
I've tried to dump roles using the following call to pg_dumpall:
pg_dumpall.exe --roles-only --no-role-passwords
However I noticed that comments on roles are also omitted from the dump, as if --no--comments flag was set - but it
wasn't.
When I call `pg_dumpall.exe --roles-only` than it works as expected - both passwords and comments are dumped.

Is it correct behaviour? It doesn't look like to me, but maybe I'm missing something. I've checked PostgreSQL 16.2 (on
Windows11 and Ubuntu 20) and 14.0 (on Windows Server 2019), same everywhere. 

Best regards
Bartek

> On 20 Mar 2024, at 18:40, Bartosz Chroł <bartosz.chrol@handen.pl> wrote:
>
> Hello,
> I've tried to dump roles using the following call to pg_dumpall:
> pg_dumpall.exe --roles-only --no-role-passwords
> However I noticed that comments on roles are also omitted from the dump, as if --no--comments flag was set - but it
wasn't.
> When I call `pg_dumpall.exe --roles-only` than it works as expected - both passwords and comments are dumped.
>
> Is it correct behaviour? It doesn't look like to me, but maybe I'm missing something. I've checked PostgreSQL 16.2
(onWindows 11 and Ubuntu 20) and 14.0 (on Windows Server 2019), same everywhere. 

Comments on roles are stored against the pg_authid catalog relation which is
the catalog used for dumping roles, but when using --no-role-passwords we
instead switch to using the pg_roles catalog relation.  Since comments are
dumped for the relations which are dumped, this means that the comments on
roles are omitted when --no-role-passwords is used.

It's not clear whether that was intentional or not, I'm failing to find the
thread where it was discussed on a quick mailing list search.  It kind of feels
like an accidental bug since the restored role will be in pg_authid where the
comment should be attached.

--
Daniel Gustafsson

On 2024-Mar-21, Daniel Gustafsson wrote:

> Comments on roles are stored against the pg_authid catalog relation which is
> the catalog used for dumping roles, but when using --no-role-passwords we
> instead switch to using the pg_roles catalog relation.  Since comments are
> dumped for the relations which are dumped, this means that the comments on
> roles are omitted when --no-role-passwords is used.
> 
> It's not clear whether that was intentional or not, I'm failing to find the
> thread where it was discussed on a quick mailing list search.

Here it is:
https://www.postgresql.org/message-id/flat/CAEP4nAz9V4H41_4ESJd1Gf0v%3DdevkqO1%3Dpo91jUw-GJSx8Hxqg%40mail.gmail.com

I very much doubt that they realized that comments were going to be
omitted.  But clearly it's just a mistake, and easily fixed.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"After a quick R of TFM, all I can say is HOLY CR** THAT IS COOL! PostgreSQL was
amazing when I first started using it at 7.2, and I'm continually astounded by
learning new features and techniques made available by the continuing work of
the development team."
Berend Tober, http://archives.postgresql.org/pgsql-hackers/2007-08/msg01009.php

> On 21 Mar 2024, at 13:28, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
>
> On 2024-Mar-21, Daniel Gustafsson wrote:
>
>> Comments on roles are stored against the pg_authid catalog relation which is
>> the catalog used for dumping roles, but when using --no-role-passwords we
>> instead switch to using the pg_roles catalog relation.  Since comments are
>> dumped for the relations which are dumped, this means that the comments on
>> roles are omitted when --no-role-passwords is used.
>>
>> It's not clear whether that was intentional or not, I'm failing to find the
>> thread where it was discussed on a quick mailing list search.
>
> Here it is:
> https://www.postgresql.org/message-id/flat/CAEP4nAz9V4H41_4ESJd1Gf0v%3DdevkqO1%3Dpo91jUw-GJSx8Hxqg%40mail.gmail.com

Aha, thanks! I too see no mention of it being intentional here.

> I very much doubt that they realized that comments were going to be
> omitted.  But clearly it's just a mistake, and easily fixed.

It sure looks like a search/replace kind of bug.  I had just typed up the exact
same patch with the addition of a comment on why pg_authid is used and was
about to hit send when your email came =) Are you committing it or do you want
me to take care of it?

--
Daniel Gustafsson

On 2024-Mar-21, Daniel Gustafsson wrote:

> On 21 Mar 2024, at 13:28, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> 
> > I very much doubt that they realized that comments were going to be
> > omitted.  But clearly it's just a mistake, and easily fixed.
> 
> It sure looks like a search/replace kind of bug.  I had just typed up the exact
> same patch with the addition of a comment on why pg_authid is used and was
> about to hit send when your email came =) Are you committing it or do you want
> me to take care of it?

Hah :-)  Please do.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"Here's a general engineering tip: if the non-fun part is too complex for you
to figure out, that might indicate the fun part is too ambitious." (John Naylor)
https://postgr.es/m/CAFBsxsG4OWHBbSDM%3DsSeXrQGOtkPiOEOuME4yD7Ce41NtaAD9g%40mail.gmail.com

> On 21 Mar 2024, at 14:27, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
>
> On 2024-Mar-21, Daniel Gustafsson wrote:
>
>> On 21 Mar 2024, at 13:28, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
>>
>>> I very much doubt that they realized that comments were going to be
>>> omitted.  But clearly it's just a mistake, and easily fixed.
>>
>> It sure looks like a search/replace kind of bug.  I had just typed up the exact
>> same patch with the addition of a comment on why pg_authid is used and was
>> about to hit send when your email came =) Are you committing it or do you want
>> me to take care of it?
>
> Hah :-)  Please do.

Done, backpatched all the way since it's been broken since 2017.

--
Daniel Gustafsson