Re: Read access for pg_monitor to pg_replication_origin_status view

On Tue, 9 Jun 2020 at 15:11, Michael Paquier <michael(at)paquier(dot)xyz> wrote:
>
> On Mon, Jun 08, 2020 at 05:44:56PM +0900, Kyotaro Horiguchi wrote:
> > Mmm. Right.
>
> Yep. I bumped on that myself. I am not sure about 0002 and 0004 yet,
> and IMO they are not mandatory pieces, but from what I can see in the
> set 0001 and 0003 can just be squashed together to remove those
> superuser checks, and no spots within the twelve functions calling
> replorigin_check_prerequisites() are missing a REVOKE. So something
> like the attached could just happen first, no? If the rights of
> pg_read_all_stats need to be extended, it would always be possible to
> do so once the attached is done with a custom script.

One thing I'm concerned with this change is that we will end up
needing to grant both execute on pg_show_replication_origin_status()
and select on pg_replication_origin_status view when we want a
non-super user to access pg_replication_origin_status. It’s unlikely
that the user can grant both privileges at once as
pg_show_replication_origin_status() is not documented.

>
> Also, why don't we use this occation to do the same thing for the
> functions working on replication slots? While we are looking at this
> area, we may as well just do it. Here is the set of functions that
> would be involved:
> - pg_create_physical_replication_slot
> - pg_create_logical_replication_slot
> - pg_replication_slot_advance
> - pg_drop_replication_slot
> - pg_copy_logical_replication_slot (3 functions)
> - pg_copy_physical_replication_slot (2 functions)

A user having a replication privilege already is able to execute these
functions. Do you mean to ease it so that a user also executes them
without replication privilege?

Regards,

--
Masahiko Sawada http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services