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.';