From: Stephen Frost <sfrost@snowman.net>
To: Payal Singh <payal@omniti.com>
Cc: jesse.waters@gmail.com, "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: Permission select pg_stat_replication
Date: Tue, 31 Mar 2015 20:34:34 -0400
Message-ID: <20150401003434.GM3663@tamriel.snowman.net> (raw)
In-Reply-To: <CANUg7LBdmn2RE=zwNCSG78obj0hPteADQZXsz+7BbfPapdzAdA@mail.gmail.com>
References: <CAL0F7ned0Lbcu_FnzcoLTRS8zpQsmkA0fyw7jXOz9X=WHyJTYw@mail.gmail.com>
<20150331131252.GI3663@tamriel.snowman.net>
<45998.192.168.207.54.1427812184.squirrel@webmail.etecsa.cu>
<20150331133130.GK3663@tamriel.snowman.net>
<46009.192.168.207.54.1427812926.squirrel@webmail.etecsa.cu>
<20150331134440.GL3663@tamriel.snowman.net>
<46016.192.168.207.54.1427813265.squirrel@webmail.etecsa.cu>
<CAL0F7nd=ObUBzDG-SCELm4d-zz6cN71E9dzi1bo8BEFkw3mJcw@mail.gmail.com>
<CANUg7LBdmn2RE=zwNCSG78obj0hPteADQZXsz+7BbfPapdzAdA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: sfrost@snowman.net, payal@omniti.com
Subject: Re: Permission select pg_stat_replication
In-Reply-To: <20150401003434.GM3663@tamriel.snowman.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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