Thread: restrict pg_stat_ssl to superuser?
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
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
Attachment
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
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
Attachment
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
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
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
Attachment
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
Attachment
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