Thread: pg_upgrade not preserving comments on predefined roles

Hi hackers,

I recently did a pg_upgrade to 13 at $work, and noticed it did not
preserve the comments I had added locally on the pg_* predefined roles.

We have a bgworker that runs periodically and makes a report of existing
roles, memberships, and grants, showing the comments on the roles, so
I had added comments on the predefined ones so they would not look naked
and unexplained in the report.

All I had to do was go back to a pre-upgrade version of the report
and re-add the comments.

Is there an inherent technical or policy reason for pg_upgrade not to
preserve comments on predefined roles (or on predefined objects generally)?

For that matter, would it be objectionable for the predefined roles to
come with comments right out of the box? I guess one possible objection
could be "what next? comments on everything in pg_catalog?", but perhaps
there is a way to distinguish the case of predefined roles: they are a
relatively recent, um, encroachment into a namespace traditionally
managed by the admin, so maybe there's that much extra reason for them
to come with explanations attached.

Another objection might be that they'd presumably be subject to translation,
and would need some way for initdb to install the proper localized versions.

So maybe it is simpler to leave them uncommented by default, but perhaps
desirable for pg_upgrade to preserve comments locally added.

I've appended the comments we use for them at $work, anyway.

Regards,
-Chap





COMMENT ON ROLE pg_execute_server_program IS 'Allow executing programs on
the database server as the user the database runs as with COPY and other
functions which allow executing a server-side program. Since PG 11.';

COMMENT ON ROLE pg_monitor IS 'Read/execute various monitoring views and
functions. This role is a member of pg_read_all_settings, pg_read_all_stats
and pg_stat_scan_tables. Since PG 10.';

COMMENT ON ROLE pg_read_all_settings IS 'Read all configuration variables,
even those normally visible only to superusers. Since PG 10.';

COMMENT ON ROLE pg_read_all_stats IS 'Read all pg_stat_* views and use
various statistics related extensions, even those normally visible only to
superusers. Since PG 10.';

COMMENT ON ROLE pg_read_server_files IS 'Allow reading files from any
location the database user can access on the server with COPY and other
file-access functions. Since PG 11.';

COMMENT ON ROLE pg_signal_backend IS 'Send signals to other backends (eg:
cancel query, terminate). Since PG 9.6.';

COMMENT ON ROLE pg_stat_scan_tables IS 'Execute monitoring functions that
may take ACCESS SHARE locks on tables, potentially for a long time. Since PG
10.';

COMMENT ON ROLE pg_write_server_files IS 'Allow writing to files in any
location the database user can access on the server with COPY and other
file-access functions. Since PG 11.';

Chapman Flack <chap@anastigmatix.net> writes:
> Is there an inherent technical or policy reason for pg_upgrade not to
> preserve comments on predefined roles (or on predefined objects generally)?

I think this is absolutely out of scope for pg_dump.  We generally expect
that system objects' properties are not dumped, because they might be
different in a newer version, and overwriting the system definition with
a possibly-obsolete version would be a bad thing.

You could quibble about comments being a different matter, but I don't
buy it.

Also, our one venture into this space (allowing custom modifications of
system-object privileges to be propagated by pg_dump) has IMV been an
unmitigated disaster.  Years later, it *still* has unresolved bugs and
definitional issues.  So I'm going to run away screaming from any proposal
to do likewise for other object properties.

> For that matter, would it be objectionable for the predefined roles to
> come with comments right out of the box?

That, however, seems reasonable enough.  We deliver built-in functions and
operators with comments, so why not roles?

> Another objection might be that they'd presumably be subject to translation,
> and would need some way for initdb to install the proper localized versions.

We've not worried about that for functions/operators.

> I've appended the comments we use for them at $work, anyway.

IMO these would have to be shortened quite a bit to be friendly for
"\du+" displays.  I'm not against the concept though.

            regards, tom lane

On 05/02/21 19:25, Tom Lane wrote:
> Chapman Flack <chap@anastigmatix.net> writes:
>> I've appended the comments we use for them at $work, anyway.
> 
> IMO these would have to be shortened quite a bit to be friendly for
> "\du+" displays.  I'm not against the concept though.

I'm certainly not wedded to the exact wording we used here.

That said, our particular reason for having them (the bgworker-produced
roles/memberships/permissions graphviz thing) is not meant just for the
eyes of technical staff, but also to be presentable to, say, compliance
folks coming around asking about security provisions. So making the
comments so terse they'd require expansion for such an audience
might kind of defeat that purpose.

In pg_proc there seem to be a handful of things with 80 to 90 character
comments and one (a statistics function) at 111. Could that be taken
for precedent as to reasonable length?

Regards,
-Chap