pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPermission select pg_stat_replication
15+ messages / 5 participants
[nested] [flat]
* Permission select pg_stat_replication
@ 2015-03-31 12:47 jesse.waters@gmail.com
2015-03-31 13:12 ` Re: Permission select pg_stat_replication Stephen Frost <sfrost@snowman.net>
2015-04-01 15:46 ` Re: Permission select pg_stat_replication Denish Patel <denish@omniti.com>
0 siblings, 2 replies; 15+ messages in thread
From: jesse.waters@gmail.com @ 2015-03-31 12:47 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Permission select pg_stat_replication
2015-03-31 12:47 Permission select pg_stat_replication jesse.waters@gmail.com
@ 2015-03-31 13:12 ` Stephen Frost <sfrost@snowman.net>
2015-03-31 14:29 ` Re: Permission select pg_stat_replication Gilberto Castillo <gilberto.castillo@etecsa.cu>
1 sibling, 1 reply; 15+ messages in thread
From: Stephen Frost @ 2015-03-31 13:12 UTC (permalink / raw)
To: jesse.waters@gmail.com; +Cc: pgsql-admin
Jesse,
* jesse.waters@gmail.com (jesse.waters@gmail.com) wrote:
> Could someone tell me what permission is required to
> select * from pg_stat_replication; ?
Currently, you're required to have superuser rights.
> I like to setup a monitor to query database with minimal privileges necessary.
I agree 110% and am actively working to fix exactly this issue. I hope
to have a patch in the next day or so which will allow you to GRANT
rights to such a monitor user which will allow that user to see all the
contents of pg_stat_replication.
One thing which would be really great is if you have time to test with
the patch I'm working up (it's against 9.5, but this is strictly
functionality testing and should be just in in a dev/test environment,
I wouldn't suggest running 9.5 in production, of course!).
Thanks!
Stephen
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Permission select pg_stat_replication
2015-03-31 12:47 Permission select pg_stat_replication jesse.waters@gmail.com
2015-03-31 13:12 ` Re: Permission select pg_stat_replication Stephen Frost <sfrost@snowman.net>
@ 2015-03-31 14:29 ` Gilberto Castillo <gilberto.castillo@etecsa.cu>
2015-03-31 13:31 ` Re: Permission select pg_stat_replication Stephen Frost <sfrost@snowman.net>
0 siblings, 1 reply; 15+ messages in thread
From: Gilberto Castillo @ 2015-03-31 14:29 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: jesse.waters@gmail.com; pgsql-admin
> Jesse,
>
> * jesse.waters@gmail.com (jesse.waters@gmail.com) wrote:
>> Could someone tell me what permission is required to
>> select * from pg_stat_replication; ?
>
> Currently, you're required to have superuser rights.
>
>> I like to setup a monitor to query database with minimal privileges
>> necessary.
>
> I agree 110% and am actively working to fix exactly this issue. I hope
> to have a patch in the next day or so which will allow you to GRANT
> rights to such a monitor user which will allow that user to see all the
> contents of pg_stat_replication.
>
> One thing which would be really great is if you have time to test with
> the patch I'm working up (it's against 9.5, but this is strictly
> functionality testing and should be just in in a dev/test environment,
> I wouldn't suggest running 9.5 in production, of course!).
Hello,
This can functional?
SET SESSION AUTHORIZATION postgres;
GRANT SELECT ON pg_stat_replication TO usuario1;
¿?¿?¿?¿?¿?
Saludos,
Gilberto Castillo
La Habana, Cuba
---
This message was processed by Kaspersky Mail Gateway 5.6.28/RELEASE running at host imx3.etecsa.cu
Visit our web-site: <http://www.kaspersky.com;, <http://www.viruslist.com;
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Permission select pg_stat_replication
2015-03-31 12:47 Permission select pg_stat_replication jesse.waters@gmail.com
2015-03-31 13:12 ` Re: Permission select pg_stat_replication Stephen Frost <sfrost@snowman.net>
2015-03-31 14:29 ` Re: Permission select pg_stat_replication Gilberto Castillo <gilberto.castillo@etecsa.cu>
@ 2015-03-31 13:31 ` Stephen Frost <sfrost@snowman.net>
2015-03-31 14:42 ` Re: Permission select pg_stat_replication Gilberto Castillo <gilberto.castillo@etecsa.cu>
0 siblings, 1 reply; 15+ messages in thread
From: Stephen Frost @ 2015-03-31 13:31 UTC (permalink / raw)
To: Gilberto Castillo <gilberto.castillo@etecsa.cu>; +Cc: jesse.waters@gmail.com; pgsql-admin
Gilberto,
* Gilberto Castillo (gilberto.castillo@etecsa.cu) wrote:
> > * jesse.waters@gmail.com (jesse.waters@gmail.com) wrote:
> >> Could someone tell me what permission is required to
> >> select * from pg_stat_replication; ?
> >
> > Currently, you're required to have superuser rights.
> >
> >> I like to setup a monitor to query database with minimal privileges
> >> necessary.
> >
> > I agree 110% and am actively working to fix exactly this issue. I hope
> > to have a patch in the next day or so which will allow you to GRANT
> > rights to such a monitor user which will allow that user to see all the
> > contents of pg_stat_replication.
> >
> > One thing which would be really great is if you have time to test with
> > the patch I'm working up (it's against 9.5, but this is strictly
> > functionality testing and should be just in in a dev/test environment,
> > I wouldn't suggest running 9.5 in production, of course!).
[...]
> SET SESSION AUTHORIZATION postgres;
>
> GRANT SELECT ON pg_stat_replication TO usuario1;
This is (essentially) what I'm hoping to enable. Note that this won't
do anything for you today as the view is already available to all users
on the system and it's actually the function underneath which is
filtering the result set.
Thanks!
Stephen
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Permission select pg_stat_replication
2015-03-31 12:47 Permission select pg_stat_replication jesse.waters@gmail.com
2015-03-31 13:12 ` Re: Permission select pg_stat_replication Stephen Frost <sfrost@snowman.net>
2015-03-31 14:29 ` Re: Permission select pg_stat_replication Gilberto Castillo <gilberto.castillo@etecsa.cu>
2015-03-31 13:31 ` Re: Permission select pg_stat_replication Stephen Frost <sfrost@snowman.net>
@ 2015-03-31 14:42 ` Gilberto Castillo <gilberto.castillo@etecsa.cu>
2015-03-31 13:44 ` Re: Permission select pg_stat_replication Stephen Frost <sfrost@snowman.net>
0 siblings, 1 reply; 15+ messages in thread
From: Gilberto Castillo @ 2015-03-31 14:42 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: Gilberto Castillo <gilberto.castillo@etecsa.cu>; jesse.waters@gmail.com; pgsql-admin
> Gilberto,
>
> * Gilberto Castillo (gilberto.castillo@etecsa.cu) wrote:
>> > * jesse.waters@gmail.com (jesse.waters@gmail.com) wrote:
>> >> Could someone tell me what permission is required to
>> >> select * from pg_stat_replication; ?
>> >
>> > Currently, you're required to have superuser rights.
>> >
>> >> I like to setup a monitor to query database with minimal privileges
>> >> necessary.
>> >
>> > I agree 110% and am actively working to fix exactly this issue. I
>> hope
>> > to have a patch in the next day or so which will allow you to GRANT
>> > rights to such a monitor user which will allow that user to see all
>> the
>> > contents of pg_stat_replication.
>> >
>> > One thing which would be really great is if you have time to test with
>> > the patch I'm working up (it's against 9.5, but this is strictly
>> > functionality testing and should be just in in a dev/test environment,
>> > I wouldn't suggest running 9.5 in production, of course!).
> [...]
>> SET SESSION AUTHORIZATION postgres;
>>
>> GRANT SELECT ON pg_stat_replication TO usuario1;
>
> This is (essentially) what I'm hoping to enable. Note that this won't
> do anything for you today as the view is already available to all users
> on the system and it's actually the function underneath which is
> filtering the result set.
>
Uhmmm,
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT SELECT ON VIEWS TO
usuario1;
Nor does it work?
Saludos,
Gilberto Castillo
La Habana, Cuba
---
This message was processed by Kaspersky Mail Gateway 5.6.28/RELEASE running at host imx3.etecsa.cu
Visit our web-site: <http://www.kaspersky.com;, <http://www.viruslist.com;
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Permission select pg_stat_replication
2015-03-31 12:47 Permission select pg_stat_replication jesse.waters@gmail.com
2015-03-31 13:12 ` Re: Permission select pg_stat_replication Stephen Frost <sfrost@snowman.net>
2015-03-31 14:29 ` Re: Permission select pg_stat_replication Gilberto Castillo <gilberto.castillo@etecsa.cu>
2015-03-31 13:31 ` Re: Permission select pg_stat_replication Stephen Frost <sfrost@snowman.net>
2015-03-31 14:42 ` Re: Permission select pg_stat_replication Gilberto Castillo <gilberto.castillo@etecsa.cu>
@ 2015-03-31 13:44 ` Stephen Frost <sfrost@snowman.net>
2015-03-31 14:47 ` Re: Permission select pg_stat_replication Gilberto Castillo <gilberto.castillo@etecsa.cu>
0 siblings, 1 reply; 15+ messages in thread
From: Stephen Frost @ 2015-03-31 13:44 UTC (permalink / raw)
To: Gilberto Castillo <gilberto.castillo@etecsa.cu>; +Cc: jesse.waters@gmail.com; pgsql-admin
Gilberto,
* Gilberto Castillo (gilberto.castillo@etecsa.cu) wrote:
> > * Gilberto Castillo (gilberto.castillo@etecsa.cu) wrote:
> >> > * jesse.waters@gmail.com (jesse.waters@gmail.com) wrote:
> >> >> Could someone tell me what permission is required to
> >> >> select * from pg_stat_replication; ?
> >> >
> >> > Currently, you're required to have superuser rights.
> >> >
> >> >> I like to setup a monitor to query database with minimal privileges
> >> >> necessary.
> >> >
> >> > I agree 110% and am actively working to fix exactly this issue. I
> >> hope
> >> > to have a patch in the next day or so which will allow you to GRANT
> >> > rights to such a monitor user which will allow that user to see all
> >> the
> >> > contents of pg_stat_replication.
> >> >
> >> > One thing which would be really great is if you have time to test with
> >> > the patch I'm working up (it's against 9.5, but this is strictly
> >> > functionality testing and should be just in in a dev/test environment,
> >> > I wouldn't suggest running 9.5 in production, of course!).
> > [...]
> >> SET SESSION AUTHORIZATION postgres;
> >>
> >> GRANT SELECT ON pg_stat_replication TO usuario1;
> >
> > This is (essentially) what I'm hoping to enable. Note that this won't
> > do anything for you today as the view is already available to all users
> > on the system and it's actually the function underneath which is
> > filtering the result set.
>
> ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT SELECT ON VIEWS TO
> usuario1;
>
> Nor does it work?
No. Feel free to try, but there's an explicit check in the C code which
is what the SQL-level function that's under the view is calling. In
current 9.5/master, at least, it's at:
src/backend/replication/walsender.c:2797
if (!superuser())
{
/*
* Only superusers can see details. Other users only get the pid
* value to know it's a walsender, but no details.
*/
MemSet(&nulls[1], true, PG_STAT_GET_WAL_SENDERS_COLS - 1);
}
Thanks,
Stephen
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Permission select pg_stat_replication
2015-03-31 12:47 Permission select pg_stat_replication jesse.waters@gmail.com
2015-03-31 13:12 ` Re: Permission select pg_stat_replication Stephen Frost <sfrost@snowman.net>
2015-03-31 14:29 ` Re: Permission select pg_stat_replication Gilberto Castillo <gilberto.castillo@etecsa.cu>
2015-03-31 13:31 ` Re: Permission select pg_stat_replication Stephen Frost <sfrost@snowman.net>
2015-03-31 14:42 ` Re: Permission select pg_stat_replication Gilberto Castillo <gilberto.castillo@etecsa.cu>
2015-03-31 13:44 ` Re: Permission select pg_stat_replication Stephen Frost <sfrost@snowman.net>
@ 2015-03-31 14:47 ` Gilberto Castillo <gilberto.castillo@etecsa.cu>
2015-03-31 17:52 ` Re: Permission select pg_stat_replication jesse.waters@gmail.com
0 siblings, 1 reply; 15+ messages in thread
From: Gilberto Castillo @ 2015-03-31 14:47 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: Gilberto Castillo <gilberto.castillo@etecsa.cu>; jesse.waters@gmail.com; pgsql-admin
> Gilberto,
>
> * Gilberto Castillo (gilberto.castillo@etecsa.cu) wrote:
>> > * Gilberto Castillo (gilberto.castillo@etecsa.cu) wrote:
>> >> > * jesse.waters@gmail.com (jesse.waters@gmail.com) wrote:
>> >> >> Could someone tell me what permission is required to
>> >> >> select * from pg_stat_replication; ?
>> >> >
>> >> > Currently, you're required to have superuser rights.
>> >> >
>> >> >> I like to setup a monitor to query database with minimal
>> privileges
>> >> >> necessary.
>> >> >
>> >> > I agree 110% and am actively working to fix exactly this issue. I
>> >> hope
>> >> > to have a patch in the next day or so which will allow you to GRANT
>> >> > rights to such a monitor user which will allow that user to see all
>> >> the
>> >> > contents of pg_stat_replication.
>> >> >
>> >> > One thing which would be really great is if you have time to test
>> with
>> >> > the patch I'm working up (it's against 9.5, but this is strictly
>> >> > functionality testing and should be just in in a dev/test
>> environment,
>> >> > I wouldn't suggest running 9.5 in production, of course!).
>> > [...]
>> >> SET SESSION AUTHORIZATION postgres;
>> >>
>> >> GRANT SELECT ON pg_stat_replication TO usuario1;
>> >
>> > This is (essentially) what I'm hoping to enable. Note that this won't
>> > do anything for you today as the view is already available to all
>> users
>> > on the system and it's actually the function underneath which is
>> > filtering the result set.
>>
>> ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT SELECT ON VIEWS TO
>> usuario1;
>>
>> Nor does it work?
>
> No. Feel free to try, but there's an explicit check in the C code which
> is what the SQL-level function that's under the view is calling. In
> current 9.5/master, at least, it's at:
>
> src/backend/replication/walsender.c:2797
>
> if (!superuser())
> {
> /*
> * Only superusers can see details. Other users only get the
> pid
> * value to know it's a walsender, but no details.
> */
> MemSet(&nulls[1], true, PG_STAT_GET_WAL_SENDERS_COLS - 1);
> }
>
Thanks Stephen for you information.
Rgds,
Gilberto Castillo
La Habana, Cuba
---
This message was processed by Kaspersky Mail Gateway 5.6.28/RELEASE running at host imx3.etecsa.cu
Visit our web-site: <http://www.kaspersky.com;, <http://www.viruslist.com;
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Permission select pg_stat_replication
2015-03-31 12:47 Permission select pg_stat_replication jesse.waters@gmail.com
2015-03-31 13:12 ` Re: Permission select pg_stat_replication Stephen Frost <sfrost@snowman.net>
2015-03-31 14:29 ` Re: Permission select pg_stat_replication Gilberto Castillo <gilberto.castillo@etecsa.cu>
2015-03-31 13:31 ` Re: Permission select pg_stat_replication Stephen Frost <sfrost@snowman.net>
2015-03-31 14:42 ` Re: Permission select pg_stat_replication Gilberto Castillo <gilberto.castillo@etecsa.cu>
2015-03-31 13:44 ` Re: Permission select pg_stat_replication Stephen Frost <sfrost@snowman.net>
2015-03-31 14:47 ` Re: Permission select pg_stat_replication Gilberto Castillo <gilberto.castillo@etecsa.cu>
@ 2015-03-31 17:52 ` jesse.waters@gmail.com
2015-03-31 20:24 ` Re: Permission select pg_stat_replication Payal Singh <payal@omniti.com>
2015-04-01 00:35 ` Re: Permission select pg_stat_replication Stephen Frost <sfrost@snowman.net>
0 siblings, 2 replies; 15+ messages in thread
From: jesse.waters@gmail.com @ 2015-03-31 17:52 UTC (permalink / raw)
To: pgsql-admin
Stephen,
Thanks for the information. We are currently running 9.2 instance
which we are using streaming replication for DR. I do not see an
upgrade anytime in the near future to 9.4 or later due to specfic
requirements for our application.
Will your modifications be backported to 9.2?
TIA,
Jesse
ps, sorry gilberto, selected wrong send to
On Tue, Mar 31, 2015 at 10:47 AM, Gilberto Castillo
<gilberto.castillo@etecsa.cu> wrote:
>
>
>> Gilberto,
>>
>> * Gilberto Castillo (gilberto.castillo@etecsa.cu) wrote:
>>> > * Gilberto Castillo (gilberto.castillo@etecsa.cu) wrote:
>>> >> > * jesse.waters@gmail.com (jesse.waters@gmail.com) wrote:
>>> >> >> Could someone tell me what permission is required to
>>> >> >> select * from pg_stat_replication; ?
>>> >> >
>>> >> > Currently, you're required to have superuser rights.
>>> >> >
>>> >> >> I like to setup a monitor to query database with minimal
>>> privileges
>>> >> >> necessary.
>>> >> >
>>> >> > I agree 110% and am actively working to fix exactly this issue. I
>>> >> hope
>>> >> > to have a patch in the next day or so which will allow you to GRANT
>>> >> > rights to such a monitor user which will allow that user to see all
>>> >> the
>>> >> > contents of pg_stat_replication.
>>> >> >
>>> >> > One thing which would be really great is if you have time to test
>>> with
>>> >> > the patch I'm working up (it's against 9.5, but this is strictly
>>> >> > functionality testing and should be just in in a dev/test
>>> environment,
>>> >> > I wouldn't suggest running 9.5 in production, of course!).
>>> > [...]
>>> >> SET SESSION AUTHORIZATION postgres;
>>> >>
>>> >> GRANT SELECT ON pg_stat_replication TO usuario1;
>>> >
>>> > This is (essentially) what I'm hoping to enable. Note that this won't
>>> > do anything for you today as the view is already available to all
>>> users
>>> > on the system and it's actually the function underneath which is
>>> > filtering the result set.
>>>
>>> ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT SELECT ON VIEWS TO
>>> usuario1;
>>>
>>> Nor does it work?
>>
>> No. Feel free to try, but there's an explicit check in the C code which
>> is what the SQL-level function that's under the view is calling. In
>> current 9.5/master, at least, it's at:
>>
>> src/backend/replication/walsender.c:2797
>>
>> if (!superuser())
>> {
>> /*
>> * Only superusers can see details. Other users only get the
>> pid
>> * value to know it's a walsender, but no details.
>> */
>> MemSet(&nulls[1], true, PG_STAT_GET_WAL_SENDERS_COLS - 1);
>> }
>>
>
>
> Thanks Stephen for you information.
>
>
> Rgds,
> Gilberto Castillo
> La Habana, Cuba
>
> ---
> This message was processed by Kaspersky Mail Gateway 5.6.28/RELEASE running at host imx3.etecsa.cu
> Visit our web-site: <http://www.kaspersky.com;, <http://www.viruslist.com;
>
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Permission select pg_stat_replication
2015-03-31 12:47 Permission select pg_stat_replication jesse.waters@gmail.com
2015-03-31 13:12 ` Re: Permission select pg_stat_replication Stephen Frost <sfrost@snowman.net>
2015-03-31 14:29 ` Re: Permission select pg_stat_replication Gilberto Castillo <gilberto.castillo@etecsa.cu>
2015-03-31 13:31 ` Re: Permission select pg_stat_replication Stephen Frost <sfrost@snowman.net>
2015-03-31 14:42 ` Re: Permission select pg_stat_replication Gilberto Castillo <gilberto.castillo@etecsa.cu>
2015-03-31 13:44 ` Re: Permission select pg_stat_replication Stephen Frost <sfrost@snowman.net>
2015-03-31 14:47 ` Re: Permission select pg_stat_replication Gilberto Castillo <gilberto.castillo@etecsa.cu>
2015-03-31 17:52 ` Re: Permission select pg_stat_replication jesse.waters@gmail.com
@ 2015-03-31 20:24 ` Payal Singh <payal@omniti.com>
2015-04-01 00:34 ` Re: Permission select pg_stat_replication Stephen Frost <sfrost@snowman.net>
1 sibling, 1 reply; 15+ messages in thread
From: Payal Singh @ 2015-03-31 20:24 UTC (permalink / raw)
To: jesse.waters@gmail.com; +Cc: pgsql-admin
As an adhoc solution on 9.2, you can do something like this:
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
Payal Singh,
Database Administrator,
OmniTI Computer Consulting Inc.
Phone: 240.646.0770 x 253
On Tue, Mar 31, 2015 at 1:52 PM, <jesse.waters@gmail.com> wrote:
> Stephen,
>
> Thanks for the information. We are currently running 9.2 instance
> which we are using streaming replication for DR. I do not see an
> upgrade anytime in the near future to 9.4 or later due to specfic
> requirements for our application.
>
> Will your modifications be backported to 9.2?
>
> TIA,
>
> Jesse
>
>
> ps, sorry gilberto, selected wrong send to
>
> On Tue, Mar 31, 2015 at 10:47 AM, Gilberto Castillo
> <gilberto.castillo@etecsa.cu> wrote:
> >
> >
> >> Gilberto,
> >>
> >> * Gilberto Castillo (gilberto.castillo@etecsa.cu) wrote:
> >>> > * Gilberto Castillo (gilberto.castillo@etecsa.cu) wrote:
> >>> >> > * jesse.waters@gmail.com (jesse.waters@gmail.com) wrote:
> >>> >> >> Could someone tell me what permission is required to
> >>> >> >> select * from pg_stat_replication; ?
> >>> >> >
> >>> >> > Currently, you're required to have superuser rights.
> >>> >> >
> >>> >> >> I like to setup a monitor to query database with minimal
> >>> privileges
> >>> >> >> necessary.
> >>> >> >
> >>> >> > I agree 110% and am actively working to fix exactly this issue. I
> >>> >> hope
> >>> >> > to have a patch in the next day or so which will allow you to
> GRANT
> >>> >> > rights to such a monitor user which will allow that user to see
> all
> >>> >> the
> >>> >> > contents of pg_stat_replication.
> >>> >> >
> >>> >> > One thing which would be really great is if you have time to test
> >>> with
> >>> >> > the patch I'm working up (it's against 9.5, but this is strictly
> >>> >> > functionality testing and should be just in in a dev/test
> >>> environment,
> >>> >> > I wouldn't suggest running 9.5 in production, of course!).
> >>> > [...]
> >>> >> SET SESSION AUTHORIZATION postgres;
> >>> >>
> >>> >> GRANT SELECT ON pg_stat_replication TO usuario1;
> >>> >
> >>> > This is (essentially) what I'm hoping to enable. Note that this
> won't
> >>> > do anything for you today as the view is already available to all
> >>> users
> >>> > on the system and it's actually the function underneath which is
> >>> > filtering the result set.
> >>>
> >>> ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT SELECT ON VIEWS TO
> >>> usuario1;
> >>>
> >>> Nor does it work?
> >>
> >> No. Feel free to try, but there's an explicit check in the C code which
> >> is what the SQL-level function that's under the view is calling. In
> >> current 9.5/master, at least, it's at:
> >>
> >> src/backend/replication/walsender.c:2797
> >>
> >> if (!superuser())
> >> {
> >> /*
> >> * Only superusers can see details. Other users only get the
> >> pid
> >> * value to know it's a walsender, but no details.
> >> */
> >> MemSet(&nulls[1], true, PG_STAT_GET_WAL_SENDERS_COLS - 1);
> >> }
> >>
> >
> >
> > Thanks Stephen for you information.
> >
> >
> > Rgds,
> > Gilberto Castillo
> > La Habana, Cuba
> >
> > ---
> > This message was processed by Kaspersky Mail Gateway 5.6.28/RELEASE
> running at host imx3.etecsa.cu
> > Visit our web-site: <http://www.kaspersky.com;, <
> http://www.viruslist.com;
> >
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Permission select pg_stat_replication
2015-03-31 12:47 Permission select pg_stat_replication jesse.waters@gmail.com
2015-03-31 13:12 ` Re: Permission select pg_stat_replication Stephen Frost <sfrost@snowman.net>
2015-03-31 14:29 ` Re: Permission select pg_stat_replication Gilberto Castillo <gilberto.castillo@etecsa.cu>
2015-03-31 13:31 ` Re: Permission select pg_stat_replication Stephen Frost <sfrost@snowman.net>
2015-03-31 14:42 ` Re: Permission select pg_stat_replication Gilberto Castillo <gilberto.castillo@etecsa.cu>
2015-03-31 13:44 ` Re: Permission select pg_stat_replication Stephen Frost <sfrost@snowman.net>
2015-03-31 14:47 ` Re: Permission select pg_stat_replication Gilberto Castillo <gilberto.castillo@etecsa.cu>
2015-03-31 17:52 ` Re: Permission select pg_stat_replication jesse.waters@gmail.com
2015-03-31 20:24 ` Re: Permission select pg_stat_replication Payal Singh <payal@omniti.com>
@ 2015-04-01 00:34 ` Stephen Frost <sfrost@snowman.net>
0 siblings, 0 replies; 15+ messages in thread
From: Stephen Frost @ 2015-04-01 00:34 UTC (permalink / raw)
To: Payal Singh <payal@omniti.com>; +Cc: pgsql-admin
Payal,
This doesn't actually do what you appear to think it does..
* Payal Singh (payal@omniti.com) wrote:
> As an adhoc solution on 9.2, you can do something like this:
>
> 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;
This function is now available to be used by all users on the system.
Should you revoke EXECUTE rights on this function from PUBLIC, the view
you build on top of it will no longer work for users who have access to
it.
Functions in view definitions are run with the privileges of the user
selecting against the view, *not* the owner of the view. Privilege
checks for *tables* referenced in views is done as the owner of the
view.
> 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();
Even if this actually worked (which it doesn't, see above), I suspect
you'd have to mark this a security barrier view.
> 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
What you link to here is better- it revokes access both to the function
and to the view. Both would have to then be granted specifically to the
monitor user. What sucks about all of this, of course, is that the
monitoring code has already been written and there isn't a standard set
of functions/views like this in core for it to depend on.
The approach I'm going for is to remove the hard-coded checks and
re-implement them at the SQL level, allowing administrators to grant
access to the base functions for monitoring users and then have the
monitoring systems updated to use those functions instead.
This makes more sense, to me at least, than duplicating everything or
having wrapper functions which do the check and are the "secured"
version and then functions also exposed at the SQL level which are
"unsecured" and then have privileges to them REVOKE'd.
I'm certainly interested in other thoughts on this though and there is a
thread over on -hackers about it, which is where further discussion on
this belongs.
Thanks!
Stephen
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Permission select pg_stat_replication
2015-03-31 12:47 Permission select pg_stat_replication jesse.waters@gmail.com
2015-03-31 13:12 ` Re: Permission select pg_stat_replication Stephen Frost <sfrost@snowman.net>
2015-03-31 14:29 ` Re: Permission select pg_stat_replication Gilberto Castillo <gilberto.castillo@etecsa.cu>
2015-03-31 13:31 ` Re: Permission select pg_stat_replication Stephen Frost <sfrost@snowman.net>
2015-03-31 14:42 ` Re: Permission select pg_stat_replication Gilberto Castillo <gilberto.castillo@etecsa.cu>
2015-03-31 13:44 ` Re: Permission select pg_stat_replication Stephen Frost <sfrost@snowman.net>
2015-03-31 14:47 ` Re: Permission select pg_stat_replication Gilberto Castillo <gilberto.castillo@etecsa.cu>
2015-03-31 17:52 ` Re: Permission select pg_stat_replication jesse.waters@gmail.com
@ 2015-04-01 00:35 ` Stephen Frost <sfrost@snowman.net>
1 sibling, 0 replies; 15+ messages in thread
From: Stephen Frost @ 2015-04-01 00:35 UTC (permalink / raw)
To: jesse.waters@gmail.com; +Cc: pgsql-admin
Jesse,
No, it's a new feature and will not be back-patched.
9.2 will be deprecated in a few years anyway, so I suggest you work out
what your upgrade plans will be sooner rather than later as staying on
9.2 indefinitely is not a supportable option.
Thanks!
Stephen
* jesse.waters@gmail.com (jesse.waters@gmail.com) wrote:
> Stephen,
>
> Thanks for the information. We are currently running 9.2 instance
> which we are using streaming replication for DR. I do not see an
> upgrade anytime in the near future to 9.4 or later due to specfic
> requirements for our application.
>
> Will your modifications be backported to 9.2?
>
> TIA,
>
> Jesse
>
>
> ps, sorry gilberto, selected wrong send to
>
> On Tue, Mar 31, 2015 at 10:47 AM, Gilberto Castillo
> <gilberto.castillo@etecsa.cu> wrote:
> >
> >
> >> Gilberto,
> >>
> >> * Gilberto Castillo (gilberto.castillo@etecsa.cu) wrote:
> >>> > * Gilberto Castillo (gilberto.castillo@etecsa.cu) wrote:
> >>> >> > * jesse.waters@gmail.com (jesse.waters@gmail.com) wrote:
> >>> >> >> Could someone tell me what permission is required to
> >>> >> >> select * from pg_stat_replication; ?
> >>> >> >
> >>> >> > Currently, you're required to have superuser rights.
> >>> >> >
> >>> >> >> I like to setup a monitor to query database with minimal
> >>> privileges
> >>> >> >> necessary.
> >>> >> >
> >>> >> > I agree 110% and am actively working to fix exactly this issue. I
> >>> >> hope
> >>> >> > to have a patch in the next day or so which will allow you to GRANT
> >>> >> > rights to such a monitor user which will allow that user to see all
> >>> >> the
> >>> >> > contents of pg_stat_replication.
> >>> >> >
> >>> >> > One thing which would be really great is if you have time to test
> >>> with
> >>> >> > the patch I'm working up (it's against 9.5, but this is strictly
> >>> >> > functionality testing and should be just in in a dev/test
> >>> environment,
> >>> >> > I wouldn't suggest running 9.5 in production, of course!).
> >>> > [...]
> >>> >> SET SESSION AUTHORIZATION postgres;
> >>> >>
> >>> >> GRANT SELECT ON pg_stat_replication TO usuario1;
> >>> >
> >>> > This is (essentially) what I'm hoping to enable. Note that this won't
> >>> > do anything for you today as the view is already available to all
> >>> users
> >>> > on the system and it's actually the function underneath which is
> >>> > filtering the result set.
> >>>
> >>> ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT SELECT ON VIEWS TO
> >>> usuario1;
> >>>
> >>> Nor does it work?
> >>
> >> No. Feel free to try, but there's an explicit check in the C code which
> >> is what the SQL-level function that's under the view is calling. In
> >> current 9.5/master, at least, it's at:
> >>
> >> src/backend/replication/walsender.c:2797
> >>
> >> if (!superuser())
> >> {
> >> /*
> >> * Only superusers can see details. Other users only get the
> >> pid
> >> * value to know it's a walsender, but no details.
> >> */
> >> MemSet(&nulls[1], true, PG_STAT_GET_WAL_SENDERS_COLS - 1);
> >> }
> >>
> >
> >
> > Thanks Stephen for you information.
> >
> >
> > Rgds,
> > Gilberto Castillo
> > La Habana, Cuba
> >
> > ---
> > This message was processed by Kaspersky Mail Gateway 5.6.28/RELEASE running at host imx3.etecsa.cu
> > Visit our web-site: <http://www.kaspersky.com;, <http://www.viruslist.com;
> >
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Permission select pg_stat_replication
2015-03-31 12:47 Permission select pg_stat_replication jesse.waters@gmail.com
@ 2015-04-01 15:46 ` Denish Patel <denish@omniti.com>
2015-04-01 15:53 ` Re: Permission select pg_stat_replication Stephen Frost <sfrost@snowman.net>
1 sibling, 1 reply; 15+ messages in thread
From: Denish Patel @ 2015-04-01 15:46 UTC (permalink / raw)
To: jesse.waters@gmail.com; +Cc: 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
http://www.pateldenish.com
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Permission select pg_stat_replication
2015-03-31 12:47 Permission select pg_stat_replication jesse.waters@gmail.com
2015-04-01 15:46 ` Re: Permission select pg_stat_replication Denish Patel <denish@omniti.com>
@ 2015-04-01 15:53 ` Stephen Frost <sfrost@snowman.net>
2015-04-01 16:53 ` Re: Permission select pg_stat_replication Denish Patel <denish@omniti.com>
0 siblings, 1 reply; 15+ messages in thread
From: Stephen Frost @ 2015-04-01 15:53 UTC (permalink / raw)
To: Denish Patel <denish@omniti.com>; +Cc: pgsql-admin
* Denish Patel (denish@omniti.com) wrote:
> you should be able to use secure_check_postgres method to avoid granting
> SUPER permission on monitoring user.
[...]
Denish,
Please see my reply to Payal. This doesn't work. At the very least,
the permissions on the pg_stat_repl() function need to be adjusted to be
only GRANT'd to the monitoring user, otherwise the information is
available to everyone. If that's the intent, then the view might as
well be granted to PUBLIC.
Recall that, by defualt, EXECUTE on a function is granted to PUBLIC.
Thanks,
Stephen
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Permission select pg_stat_replication
2015-03-31 12:47 Permission select pg_stat_replication jesse.waters@gmail.com
2015-04-01 15:46 ` Re: Permission select pg_stat_replication Denish Patel <denish@omniti.com>
2015-04-01 15:53 ` Re: Permission select pg_stat_replication Stephen Frost <sfrost@snowman.net>
@ 2015-04-01 16:53 ` Denish Patel <denish@omniti.com>
2015-04-01 17:05 ` Re: [ADMIN] Permission select pg_stat_replication Stephen Frost <sfrost@snowman.net>
0 siblings, 1 reply; 15+ messages in thread
From: Denish Patel @ 2015-04-01 16:53 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: pgsql-admin
Fair enough but they should be able to achieve their goal to avoid granting
SUPER to monitoring user. They have to tweak the grant/revoke as desired.
On Wed, Apr 1, 2015 at 11:53 AM, Stephen Frost <sfrost@snowman.net> wrote:
> * Denish Patel (denish@omniti.com) wrote:
> > you should be able to use secure_check_postgres method to avoid granting
> > SUPER permission on monitoring user.
> [...]
>
> Denish,
>
> Please see my reply to Payal. This doesn't work. At the very least,
> the permissions on the pg_stat_repl() function need to be adjusted to be
> only GRANT'd to the monitoring user, otherwise the information is
> available to everyone. If that's the intent, then the view might as
> well be granted to PUBLIC.
>
> Recall that, by defualt, EXECUTE on a function is granted to PUBLIC.
>
> Thanks,
>
> Stephen
>
--
Denish Patel,
OmniTI Computer Consulting Inc.
Database Architect,
http://omniti.com/does/data-management
http://www.pateldenish.com
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: [ADMIN] Permission select pg_stat_replication
2015-03-31 12:47 Permission select pg_stat_replication jesse.waters@gmail.com
2015-04-01 15:46 ` Re: Permission select pg_stat_replication Denish Patel <denish@omniti.com>
2015-04-01 15:53 ` Re: Permission select pg_stat_replication Stephen Frost <sfrost@snowman.net>
2015-04-01 16:53 ` Re: Permission select pg_stat_replication Denish Patel <denish@omniti.com>
@ 2015-04-01 17:05 ` Stephen Frost <sfrost@snowman.net>
0 siblings, 0 replies; 15+ messages in thread
From: Stephen Frost @ 2015-04-01 17:05 UTC (permalink / raw)
To: Denish Patel <denish@omniti.com>; +Cc: jesse.waters@gmail.com; pgsql-hackers@postgresql.org
Denish, all,
Moved over to -hackers to discuss specifics around addressing this.
* Denish Patel (denish@omniti.com) wrote:
> Fair enough but they should be able to achieve their goal to avoid granting
> SUPER to monitoring user. They have to tweak the grant/revoke as desired.
That's correct, but the problem we currently have is that none of the
monitoring systems will "just work" with this approach because they're
hard-coded to what we actually provide by default.
I'm hoping to fix that problem by having a C function which returns
everything and then locking *that* down and only allowing it to be
executed by a superuser by default. Administrators would then be able
to grant access to those functions and the monitoring systems could be
built on top of using those functions, and they'd work just fine if the
monitoring user is a superuser but they'd also work if the monitoring
user *isn't*, provided the correct GRANTs are done.
What's getting tricky about all of this is making our existing views
work against the C function but without depending on it to handle the
filtering and instead doing it in SQL. That sounds simple until you
look at the filtering we're actually doing and that functions defined in
views run as the querier of the view, which means you have to have a
security definer function involved to query the protected function
underneath, and that function has to be callable by everyone, but it has
to return values based on the permissions of the querier, which it
doesn't know because we don't provide that information anywhere.
I'm working on solving that problem by having a function which can
return the value of "who called this function", a capability a *lot* of
people have asked me about in the past, but that's pretty darn grotty
given how GUCs work (we have "show_hook"s, but those operate against
whatever the C variable is currently set to, and I really don't want to
be playing with setting/resetting that just for this..). Rather than
try to re-engineer how GUCs work, I'm looking at doing this specifically
for this specific case of role information. I don't hear a lot of
people asking for the value of other GUCs (except perhaps search_path,
but that's easier since we don't have a show_hook for that..).
Would certainly appreciate any thoughts from others on all of the above.
Thanks!
Stephen
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2015-04-01 17:05 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-03-31 12:47 Permission select pg_stat_replication jesse.waters@gmail.com
2015-03-31 13:12 ` Stephen Frost <sfrost@snowman.net>
2015-03-31 14:29 ` Gilberto Castillo <gilberto.castillo@etecsa.cu>
2015-03-31 13:31 ` Stephen Frost <sfrost@snowman.net>
2015-03-31 14:42 ` Gilberto Castillo <gilberto.castillo@etecsa.cu>
2015-03-31 13:44 ` Stephen Frost <sfrost@snowman.net>
2015-03-31 14:47 ` Gilberto Castillo <gilberto.castillo@etecsa.cu>
2015-03-31 17:52 ` jesse.waters@gmail.com
2015-03-31 20:24 ` Payal Singh <payal@omniti.com>
2015-04-01 00:34 ` Stephen Frost <sfrost@snowman.net>
2015-04-01 00:35 ` Stephen Frost <sfrost@snowman.net>
2015-04-01 15:46 ` Denish Patel <denish@omniti.com>
2015-04-01 15:53 ` Stephen Frost <sfrost@snowman.net>
2015-04-01 16:53 ` Denish Patel <denish@omniti.com>
2015-04-01 17:05 ` Stephen Frost <sfrost@snowman.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox