Re: Permission select pg_stat_replication

Hey Jesse,

you should be able to use secure_check_postgres method to avoid granting
SUPER permission on monitoring user.

Example:

1. Create a function that extracts all from pg_stat_replication:

create or replace function pg_stat_repl() returns setof
pg_catalog.pg_stat_replication as $$begin return query(select * from
pg_catalog.pg_stat_replication); end$$ language plpgsql security definer;

2. Create a view that uses this function to get data in it:

create view public.pg_stat_repl as select * from pg_stat_repl();

3. Grant select on this view to your unprivileged user, sat 'common_user' :

grant select on public.pg_stat_repl to common_user;

After this, you can do a select on this view to get the required
information. You can do this for other pg_catalog functions as well.
Reference -
https://github.com/xzilla/secure_check_postgres/blob/master/sql/pg_stat_activity.sql

Hope this helps.

On Tue, Mar 31, 2015 at 8:47 AM, <jesse(dot)waters(at)gmail(dot)com> wrote:

> Hello,
>
> Could someone tell me what permission is required to
> select * from pg_stat_replication; ?
>
> I like to setup a monitor to query database with minimal privileges
> necessary.
>
> TIA,
>
> Jesse
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>

--
Denish Patel,
OmniTI Computer Consulting Inc.
Database Architect,
http://omniti.com/does/data-management
http://www.pateldenish.com