Re: Permission select pg_stat_replication - Mailing list pgsql-admin

From Denish Patel
Subject Re: Permission select pg_stat_replication
Date
Msg-id CAFddxvOFPANK222gvLhnu-0GyAmd9DCBag_GMC26tfZ4rH-n1Q@mail.gmail.com
Whole thread Raw
In response to Permission select pg_stat_replication  (<jesse.waters@gmail.com>)
Responses Re: Permission select pg_stat_replication
List pgsql-admin
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.waters@gmail.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@postgresql.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

pgsql-admin by date:

Previous
From: Stephen Frost
Date:
Subject: Re: Permission select pg_stat_replication
Next
From: Stephen Frost
Date:
Subject: Re: Permission select pg_stat_replication