Horton

Thread

Commits

  1. Hide other user's pg_stat_ssl rows

  2. doc: Add security information about pg_stat_activity

  1. restrict pg_stat_ssl to superuser?

    Peter Eisentraut <peter.eisentraut@2ndquadrant.com> — 2019-02-07T08:30:38Z

    As discussed in [0], should we restrict access to pg_stat_ssl to
superusers (and an appropriate pg_ role)?

If so, is there anything in that view that should be made available to
non-superusers?  If not, then we could perhaps do this via a simple
permission change instead of going the route of blanking out individual
columns.


[0]:
<https://www.postgresql.org/message-id/flat/398754d8-6bb5-c5cf-e7b8-22e5f0983caf@2ndquadrant.com>

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

  2. Re: restrict pg_stat_ssl to superuser?

    Michael Paquier <michael@paquier.xyz> — 2019-02-12T06:40:07Z

    On Thu, Feb 07, 2019 at 09:30:38AM +0100, Peter Eisentraut wrote:
> If so, is there anything in that view that should be made available to
> non-superusers?  If not, then we could perhaps do this via a simple
> permission change instead of going the route of blanking out individual
> columns.

Hm.  It looks sensible to move to a per-permission approach for that
view.  Now, pg_stat_get_activity() is not really actually restricted,
and would still return the information on direct calls, so the idea
would be to split the SSL-related data into its own function?
--
Michael

  3. Re: restrict pg_stat_ssl to superuser?

    Peter Eisentraut <peter.eisentraut@2ndquadrant.com> — 2019-02-15T13:04:59Z

    On 2019-02-12 07:40, Michael Paquier wrote:
> On Thu, Feb 07, 2019 at 09:30:38AM +0100, Peter Eisentraut wrote:
>> If so, is there anything in that view that should be made available to
>> non-superusers?  If not, then we could perhaps do this via a simple
>> permission change instead of going the route of blanking out individual
>> columns.
> 
> Hm.  It looks sensible to move to a per-permission approach for that
> view.  Now, pg_stat_get_activity() is not really actually restricted,
> and would still return the information on direct calls, so the idea
> would be to split the SSL-related data into its own function?

We could remove default privileges from pg_stat_get_activity().  Would
that be a problem?

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

  4. Re: restrict pg_stat_ssl to superuser?

    Michael Paquier <michael@paquier.xyz> — 2019-02-18T03:58:13Z

    On Fri, Feb 15, 2019 at 02:04:59PM +0100, Peter Eisentraut wrote:
> We could remove default privileges from pg_stat_get_activity().  Would
> that be a problem?

I don't think so, still I am wondering about the impact that this
could have for monitoring tools calling it directly as we document
it.. 
--
Michael

  5. Re: restrict pg_stat_ssl to superuser?

    Peter Eisentraut <peter.eisentraut@2ndquadrant.com> — 2019-02-19T15:57:52Z

    On 2019-02-18 04:58, Michael Paquier wrote:
> On Fri, Feb 15, 2019 at 02:04:59PM +0100, Peter Eisentraut wrote:
>> We could remove default privileges from pg_stat_get_activity().  Would
>> that be a problem?
> 
> I don't think so, still I am wondering about the impact that this
> could have for monitoring tools calling it directly as we document
> it.. 

Actually, this approach isn't going to work anyway, because function
permissions in a view are checked as the current user, not the view owner.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

  6. Re: restrict pg_stat_ssl to superuser?

    Robert Haas <robertmhaas@gmail.com> — 2019-02-19T17:44:51Z

    On Thu, Feb 7, 2019 at 3:30 AM Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> wrote:
> As discussed in [0], should we restrict access to pg_stat_ssl to
> superusers (and an appropriate pg_ role)?
>
> If so, is there anything in that view that should be made available to
> non-superusers?  If not, then we could perhaps do this via a simple
> permission change instead of going the route of blanking out individual
> columns.

Shouldn't unprivileged users be able to see their own entries, or
entries for roles which they could assume via SET ROLE?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  7. Re: restrict pg_stat_ssl to superuser?

    Peter Eisentraut <peter.eisentraut@2ndquadrant.com> — 2019-02-20T10:51:08Z

    On 2019-02-19 16:57, Peter Eisentraut wrote:
> On 2019-02-18 04:58, Michael Paquier wrote:
>> On Fri, Feb 15, 2019 at 02:04:59PM +0100, Peter Eisentraut wrote:
>>> We could remove default privileges from pg_stat_get_activity().  Would
>>> that be a problem?
>>
>> I don't think so, still I am wondering about the impact that this
>> could have for monitoring tools calling it directly as we document
>> it.. 
> 
> Actually, this approach isn't going to work anyway, because function
> permissions in a view are checked as the current user, not the view owner.

So here is a patch doing it the "normal" way of nulling out all the rows
the user shouldn't see.

I haven't found any documentation of these access restrictions in the
context of pg_stat_activity.  Is there something that I'm not seeing or
something that should be added?

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

  8. Re: restrict pg_stat_ssl to superuser?

    Michael Paquier <michael@paquier.xyz> — 2019-02-21T08:11:24Z

    On Wed, Feb 20, 2019 at 11:51:08AM +0100, Peter Eisentraut wrote:
> So here is a patch doing it the "normal" way of nulling out all the rows
> the user shouldn't see.

That looks fine to me.

> I haven't found any documentation of these access restrictions in the
> context of pg_stat_activity.  Is there something that I'm not seeing or
> something that should be added?

Yes, there is nothing.  I agree that it would be good to mention that
some fields are set to NULL and only visible to superusers or members of
pg_read_all_stats with a note for pg_stat_activity and
pg_stat_get_activity().  Here is an idea:
"Backend and SSL information are restricted to superusers and members
of the <literal>pg_read_all_stats</literal> role. Access may be
granted to others using <command>GRANT</command>.

Getting that back-patched would be nice where pg_read_all_stats was
introduced.
--
Michael

  9. Re: restrict pg_stat_ssl to superuser?

    Peter Eisentraut <peter.eisentraut@2ndquadrant.com> — 2019-02-21T18:56:31Z

    On 2019-02-21 09:11, Michael Paquier wrote:
> On Wed, Feb 20, 2019 at 11:51:08AM +0100, Peter Eisentraut wrote:
>> So here is a patch doing it the "normal" way of nulling out all the rows
>> the user shouldn't see.
> 
> That looks fine to me.

Committed, thanks.

>> I haven't found any documentation of these access restrictions in the
>> context of pg_stat_activity.  Is there something that I'm not seeing or
>> something that should be added?
> 
> Yes, there is nothing.  I agree that it would be good to mention that
> some fields are set to NULL and only visible to superusers or members of
> pg_read_all_stats with a note for pg_stat_activity and
> pg_stat_get_activity().  Here is an idea:
> "Backend and SSL information are restricted to superusers and members
> of the <literal>pg_read_all_stats</literal> role. Access may be
> granted to others using <command>GRANT</command>.
> 
> Getting that back-patched would be nice where pg_read_all_stats was
> introduced.

I added something.  I don't know if it's worth backpatching.  This
situation goes back all the way to when pg_stat_activity was added.
pg_read_all_stats does have documentation, it's just not linked from
everywhere.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services