Re: pg_dumpall with flag --no-role-passwords omits roles comments as well
> On 21 Mar 2024, at 14:27, Alvaro Herrera wrote: > > On 2024-Mar-21, Daniel Gustafsson wrote: > >> On 21 Mar 2024, at 13:28, Alvaro Herrera 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
Re: pg_dumpall with flag --no-role-passwords omits roles comments as well
On 2024-Mar-21, Daniel Gustafsson wrote: > On 21 Mar 2024, at 13:28, Alvaro Herrera 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 HerreraBreisgau, 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
Re: pg_dumpall with flag --no-role-passwords omits roles comments as well
> On 21 Mar 2024, at 13:28, Alvaro Herrera 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
Re: pg_dumpall with flag --no-role-passwords omits roles comments as well
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 >From baa4bd18f751cde68c5637c4cb8065cf94e92c1c Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Thu, 21 Mar 2024 13:27:37 +0100 Subject: [PATCH v1] fix dump of role comments with --no-role-passwords --- src/bin/pg_dump/pg_dumpall.c | 12 ++-- 1 file changed, 6 insertions(+), 6 deletions(-) diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c index 491311fe79..72c30fc66d 100644 --- a/src/bin/pg_dump/pg_dumpall.c +++ b/src/bin/pg_dump/pg_dumpall.c @@ -776,21 +776,21 @@ dumpRoles(PGconn *conn) "rolcreaterole, rolcreatedb, " "rolcanlogin, rolconnlimit, rolpassword, " "rolvaliduntil, rolreplication, rolbypassrls, " - "pg_catalog.shobj_description(oid, '%s') as rolcomment, " + "pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, " "rolname = current_user AS is_current_user " "FROM %s " "WHERE rolname !~ '^pg_' " - "ORDER BY 2", role_catalog, role_catalog); + "ORDER BY 2", role_catalog); else if (server_version >= 90500) printfPQExpBuffer(buf, "SELECT oid, rolname, rolsuper, rolinherit, " "rolcreaterole, rolcreatedb, " "rolcanlogin, rolconnlimit, rolpassword, " "rolvaliduntil, rolreplication, rolbypassrls, " - "pg_catalog.shobj_description(oid, '%s') as rolcomment, " + "pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, " "rolname = current_user AS is_current_user " "FROM %s " - "ORDER BY 2", role_catalog, role_catalog); + "ORDER BY 2", role_catalog); else printfPQExpBuffer(buf, "SELECT oid, rolname, rolsuper, rolinherit, " @@ -798,10 +798,10 @@ dumpRoles(PGconn *conn) "rolcanlogin, rolconnlimit, rolpassword, " "rolvaliduntil, rolreplication, " "false as rolbypassrls, " - "pg_catalog.shobj_description(oid, '%s') as rolcomment, " + "pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, " "rolname = current_user AS is_current_user " "FROM %s " - "ORDER BY 2", role_catalog, role_catalog); + "ORDER BY 2", role_catalog); res = executeQuery(conn, buf->data); -- 2.39.2
Re: pg_dumpall with flag --no-role-passwords omits roles comments as well
On Thu, Mar 21, 2024 at 11:52 AM Dominique Devienne wrote: > On Thu, Mar 21, 2024 at 11:46 AM Daniel Gustafsson > 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 > ... > ``` >
Re: pg_dumpall with flag --no-role-passwords omits roles comments as well
On Thu, Mar 21, 2024 at 11:46 AM Daniel Gustafsson 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
Re: pg_dumpall with flag --no-role-passwords omits roles comments as well
> On 20 Mar 2024, at 18:40, Bartosz Chroł 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
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 Windows 11 and Ubuntu 20) and 14.0 (on Windows Server 2019), same everywhere. Best regards Bartek
