Horton

Thread

Commits

  1. Fix dumping role comments when using --no-role-passwords

  1. pg_dumpall with flag --no-role-passwords omits roles comments as well

    Bartosz Chroł <bartosz.chrol@handen.pl> — 2024-03-20T17:40:55Z

    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 Windows 11 and Ubuntu 20) and 14.0 (on Windows Server 2019), same everywhere.

Best regards
Bartek

  2. Re: pg_dumpall with flag --no-role-passwords omits roles comments as well

    Daniel Gustafsson <daniel@yesql.se> — 2024-03-21T10:46:10Z

    > 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 (on Windows 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

  3. Re: pg_dumpall with flag --no-role-passwords omits roles comments as well

    Dominique Devienne <ddevienne@gmail.com> — 2024-03-21T10:52:12Z

    On Thu, Mar 21, 2024 at 11:46 AM Daniel Gustafsson <daniel@yesql.se> wrote:

> > However I noticed that comments on roles are also omitted from the dump,
> as if --no--comments flag was set - but it wasn't.
>
> Comments on roles are stored against the pg_authid catalog relation


Hi. What do you mean? ROLEs are not stored in that relation.
And AFAIK, only accessible via functions using the OID or NAME.

So the relation used, pg_authid or pg_roles, shouldn't matter, no?

Here's my own query for example:
```
select rolname, rolsuper, rolinherit, rolcreaterole,
       rolcreatedb, rolcanlogin, rolreplication, rolbypassrls,
       oid, shobj_description(oid, 'pg_authid')
  from pg_roles
...
```
--DD

  4. Re: pg_dumpall with flag --no-role-passwords omits roles comments as well

    Dominique Devienne <ddevienne@gmail.com> — 2024-03-21T12:16:35Z

    On Thu, Mar 21, 2024 at 11:52 AM Dominique Devienne <ddevienne@gmail.com>
wrote:

> On Thu, Mar 21, 2024 at 11:46 AM Daniel Gustafsson <daniel@yesql.se>
> wrote:
>
>> > However I noticed that comments on roles are also omitted from the
>> dump, as if --no--comments flag was set - but it wasn't.
>>
>> Comments on roles are stored against the pg_authid catalog relation
>
>
> Hi. What do you mean? COMMENTs are not stored *in* that relation.
> And AFAIK, only accessible via functions using the OID or NAME.
>
> So the relation used, pg_authid or pg_roles, shouldn't matter, no?
>

OK, I see now you meant shobj_description(oid, 'pg_authid'),
i.e. that 'pg_authid' literal in the function call. Thus your use of
"against" in the above sentence. Apologies for my misunderstanding.

But that literal in the function call is separate from which relation,
pg_authid or pg_roles, one actually SELECT from, as already shown. --DD

```
> select ...,  shobj_description(oid, 'pg_authid')
>   from pg_roles
> ...
> ```
>

  5. Re: pg_dumpall with flag --no-role-passwords omits roles comments as well

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2024-03-21T12:28:56Z

    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

  6. Re: pg_dumpall with flag --no-role-passwords omits roles comments as well

    Daniel Gustafsson <daniel@yesql.se> — 2024-03-21T12:32:49Z

    > 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

  7. Re: pg_dumpall with flag --no-role-passwords omits roles comments as well

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2024-03-21T13:27:30Z

    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

  8. Re: pg_dumpall with flag --no-role-passwords omits roles comments as well

    Daniel Gustafsson <daniel@yesql.se> — 2024-03-21T23:23:15Z

    > 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