Hello,
I wanted to resurface this thread.
The original intention I had with this patch I sent over a year ago
was to have the possibility for monitoring ROLEs like pg_monitor and
pg_read_all_stats to have read access for the replication origin
status. Seems the patch only got half way through (we removed the
superuser hardcoded restriction).
Too bad I didn't notice this until 14 got out, or I'd have done this
much earlier. Well, maybe it's time to do it now :)
Sending a patch to change the privileges of the on the view and
function called by the view.
The only thing I'm not sure, but can amend, is if we need tests for
this change (that would be something like switching ROLE to
pg_read_all_stats and query the pg_replication_origin_status, checking
we get the right result.
Kind regards, Martín
--
Martín Marqués
It’s not that I have something to hide,
it’s that I have nothing I want you to see
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 24447c0017..66efb58f2e 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -26150,8 +26150,8 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
<xref linkend="replication-origins"/>
for information about the underlying features.
Use of functions for replication origin is only allowed to the
- superuser by default, but may be allowed to other users by using the
- <literal>GRANT</literal> command.
+ superuser and pg_read_all_stats roles by default, but may be allowed
+ to other users by using the <literal>GRANT</literal> command.
Use of functions for replication slots is restricted to superusers
and users having <literal>REPLICATION</literal> privilege.
</para>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index eb560955cd..c626ad4aed 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1256,6 +1256,9 @@ CREATE VIEW pg_replication_origin_status AS
REVOKE ALL ON pg_replication_origin_status FROM public;
+GRANT EXECUTE ON FUNCTION pg_show_replication_origin_status() TO pg_read_all_stats;
+GRANT SELECT ON pg_replication_origin_status TO pg_read_all_stats;
+
-- All columns of pg_subscription except subconninfo are publicly readable.
REVOKE ALL ON pg_subscription FROM public;
GRANT SELECT (oid, subdbid, subname, subowner, subenabled, subbinary,
