Re: Enhancement Request

On Tue, Apr 19, 2016 at 4:37 PM, Rob Brucks <rob(dot)brucks(at)rackspace(dot)com>
wrote:

> I'd like to propose two enhancements to the PostgreSQL code, but I'm not
> sure if this is the correct mailing list. So if it's not then please let
> me know where I need to post this.
>
> These are monitoring-centric enhancement requests since I'm trying to
> implement accurate monitoring in a secure fashion.
>
> * General monitoring:
> We have a need for a "monitoring" role in PostgreSQL that has read-only
> access to any "pg_stat" view. As of 9.4, only a super-user can read all
> columns of "pg_stat_activity", "pg_stat_replication", and
> "pg_stat_archiver" (there may be other restricted views as well). These
> views provide critical insight on how well the cluster is operating and
> what is going on.
>
> There appears to be only two ways to gain access to these views:
>
> 1. grant super-user to my monitoring user
> 2. write custom functions owned by a super-user with "SECURITY
> DEFINER" and grant access to my monitoring user
>
> Option 1 is too big of a security risk to grant to a monitoring user. If
> the monitoring system gets compromised then the DB will be at risk too.
>
> Option 2 requires creating, deploying, and maintaining sets of monitoring
> functions, which is a decent chunk of work in a large environment with
> dozens or hundreds of deployments, many running different versions of
> postgres possibly needing custom versions of the functions. When you add
> the bureaucracy of a large IT organization and SOX and PCI compliance
> requirements it ends up being a PITA implementing or changing these
> functions when you only have a small DBA team.
>
>
> * Streaming Replication Monitoring:
> Make the "pg_stat_replication" view more persistent (maybe keep the rows
> for 24 hours or have a registration process?).
>
> There appears to be no way to *accurately* monitor streaming replication
> via SQL alone currently. This is due to three different problems:
>
> 1. When a standby gets disconnected from the master then the
> corresponding row is immediately removed from pg_stat_replication on the
> master. Once the row is gone you cannot tell via simple SQL whether a
> standby is behind or not (or that it even existed at all) without storing
> prior values and extrapolating from them.
> 2. On the standby, if the WAL streaming process gets disconnected from
> the master then it does not report that it is behind
> because pg_last_xlog_receive_location() has not been updated from the
> master. The standby has no idea how far ahead the master has gotten and
> just blindly reports the last value.
> 3. On a "quiet" system there may not be any update activity on the
> master for a long time, which makes the pg_last_xact_replay_timestamp()
> function report an ever-increasing interval. So it is not useable for
> accurately measuring lag on quiet systems.
>
> Ideally the master should be able to report standby lag time via SQL, even
> when there has been a disruption in connectivity with a standby.
>
> The only accurate method I have found to measure standby lag is to create
> a synthetic update that runs periodically. This works, but is less than
> ideal and requires adding a table to every cluster (which then has to be
> vacuumed frequently too) and writing and maintaining a process to update it
> and purge it.
>
>
> These two enhancements would go a long way in making it easier to monitor
> PostgreSQL clusters and replication because it would eliminate a lot of
> custom coding requirements and enable us to pull metrics directly via
> simple SQL.
>
> If anyone can provide insight on how I could accomplish these in a simple
> manner by other means then I'm all ears!
>
> Thanks,
> Rob
>

Rob,
Enhancement or feature requests should probably go to *Custormer Feedback
<https://postgresql.uservoice.com/forums/21853-general>*
https://postgresql.uservoice.com/forums/21853-general
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.