Re: [HACKERS] Information of pg_stat_ssl visible to all users

[Magnus Hagander]

On Sep 1, 2015 4:37 AM, "Michael Paquier"  wrote:
>
> On Tue, Sep 1, 2015 at 4:23 AM, Peter Eisentraut  wrote:
> > On 8/31/15 9:13 AM, Andres Freund wrote:
> >> I'm just saying that we should strive to behave at least somewhat
> >> consistently, and change everything at once, not piecemal. Because the
> >> latter will not decrease the pain of migrating to a new model in a
> >> relevant way while making the system harder to understand.
> >
> > Well, we already hide a fair chunk of information from pg_stat_activity
> > from unprivileged users, including everything related to the connection
> > origin of other users.  So from that precedent, the entire SSL
> > information ought to be considered privileged.
>
> That being said we may want as well to bite the bullet and to hide
> more information in pg_stat_activity, like datname, usename and
> application_name, or simply hide completely those tuples for
> non-privileged users.

That's likely to break every single monitoring tool ever written for
postgresql...

We're going to have to do that eventually, but I think we should wait until
we have a complete solution (which would be either column permissions,
monitoring role, or something like that (or combination thereof)).

/Magnus

Re: [HACKERS] Information of pg_stat_ssl visible to all users

[Peter Eisentraut]

On 8/31/15 9:13 AM, Andres Freund wrote:
> I'm just saying that we should strive to behave at least somewhat
> consistently, and change everything at once, not piecemal. Because the
> latter will not decrease the pain of migrating to a new model in a
> relevant way while making the system harder to understand.

Well, we already hide a fair chunk of information from pg_stat_activity
from unprivileged users, including everything related to the connection
origin of other users.  So from that precedent, the entire SSL
information ought to be considered privileged.



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Information of pg_stat_ssl visible to all users

[Michael Paquier]

On Tue, Sep 1, 2015 at 4:23 AM, Peter Eisentraut  wrote:
> On 8/31/15 9:13 AM, Andres Freund wrote:
>> I'm just saying that we should strive to behave at least somewhat
>> consistently, and change everything at once, not piecemal. Because the
>> latter will not decrease the pain of migrating to a new model in a
>> relevant way while making the system harder to understand.
>
> Well, we already hide a fair chunk of information from pg_stat_activity
> from unprivileged users, including everything related to the connection
> origin of other users.  So from that precedent, the entire SSL
> information ought to be considered privileged.

That being said we may want as well to bite the bullet and to hide
more information in pg_stat_activity, like datname, usename and
application_name, or simply hide completely those tuples for
non-privileged users.
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Information of pg_stat_ssl visible to all users

[Stephen Frost]

* Magnus Hagander (mag...@hagander.net) wrote:
> On Sat, Aug 29, 2015 at 10:27 PM, Bruce Momjian  wrote:
> > I can see them having problems with a user being able to see the SSL
> > remote user names of all connected users.
> 
> I'm pretty sure Heroku don't use client certificates.
> 
> And if they did, I would assume the client certificate would be issued to
>  aafgrwewediiqz, or possibly aafgrwewedi...@customer.heroku.com or
> something along that line.
> 
> Client certificates don't show anything other than the username, unless you
> explicitly choose to put sensitive information in the CN. But we don't
> limit the view of the username in pg_stat_activity, even though people do
> put sensitive things in there (such as the customer name in case of shared
> hosting - everybody doesn't do what Heroku does).
> 
> So pg_stat_ssl doesn't show something that's not already visible.

I don't particularly disagree with any of the above but would instead
reiterate my up-thread comment: we already get grief from various
people, rightly in my mind, that we give unprivileged users too much
information about what other unprivileged users are on the system and
adding more information is going in the wrong direction, even if it's of
the same sensitivity level as what we already allow.

Perhaps it really isn't moving the bar all that much but at least for a
number of our users, it's increasing what they have to be worrying about
("well, we knew usernames were an issue, but now we also have to worry
about system usersnames and the CN in the certificate and...").

The answer, in my view at least, isn't necessairly to seperate the CN
from the username and make them differently levels of access or
sensitivity, but rather to allow administrators to control access to
that collective set of information.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] Information of pg_stat_ssl visible to all users

[Andres Freund]

On 2015-08-31 09:06:27 -0400, Stephen Frost wrote:
> Perhaps it really isn't moving the bar all that much but at least for a
> number of our users, it's increasing what they have to be worrying about
> ("well, we knew usernames were an issue, but now we also have to worry
> about system usersnames and the CN in the certificate and...").

And to the majority it makes this behave entirely incoherent…

Who would realistically have a randomized username that people log in
with, and then CNs with meaningful contents? That'd mean you'd have to
write complex user mappings between CNs and usernames.


> The answer, in my view at least, isn't necessairly to seperate the CN
> from the username and make them differently levels of access or
> sensitivity, but rather to allow administrators to control access to
> that collective set of information.

I don't think anybody argues against that.

I'm just saying that we should strive to behave at least somewhat
consistently, and change everything at once, not piecemal. Because the
latter will not decrease the pain of migrating to a new model in a
relevant way while making the system harder to understand.

Greetings,

Andres Freund


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Information of pg_stat_ssl visible to all users

[Magnus Hagander]

On Mon, Aug 31, 2015 at 2:17 PM, Michael Paquier 
wrote:

> On Mon, Aug 31, 2015 at 9:04 PM, Magnus Hagander 
> wrote:
> >
> >
> > On Sun, Aug 30, 2015 at 5:35 AM, Michael Paquier <
> michael.paqu...@gmail.com>
> > wrote:
> >>
> >>
> >>
> >> On Sun, Aug 30, 2015 at 5:27 AM, Bruce Momjian wrote:
> >>>
> >>> I know I am coming in late here, but I know Heroku uses random user
> >>> names to allow a cluster to have per-user databases without showing
> >>> external user name details:
> >>> [...]
> >>> I can see them having problems with a user being able to see the SSL
> >>> remote user names of all connected users.
> >>
> >>
> >> Yep, and I can imagine that this is the case of any company managing
> cloud
> >> nodes with Postgres embedded, and at least to me that's a real concern.
> >
> >
> >
> > How is it a concern that  a CN field with a random username in it is
> > visible, when showing the actual random username isn't? That's not very
> > consistent...
>
> How can you be sure as well that all such deployments would use random
> CN fields and/or random usernames? We have no guarantee of that as
> well.
>


Sure.

But how can we be sure that all such deployments use random usernames? We
can't, and we still show the username to people. (And I'm willing to be
it's a *lot* more common that hosters have sensitive information in the
username - like customer name - than that they have it in a client
certificate. Simply because most of them don't use client certificates).

If we're worried about that, we should also blank out username and maybe
database name from pg_stat_activity. And probably application_name as well.
If we're not doing that, then we're not being at all consistent. If we are
talking about doing that (before we have figured out the granular
permissions part), then we should do the pg_stat_ssl CN at the same time, I
definitely agree with that.

I can agree that there are some other fields that potentially leak some
information, like which crypto is in use. But I'm with Andres in the
summary - I think the way to fix that is once we have figured out actual
more granular permissions, and not by locking it down now.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [HACKERS] Information of pg_stat_ssl visible to all users

[Andres Freund]

On 2015-08-31 14:29:10 +0200, Andres Freund wrote:
> On 2015-08-31 21:17:48 +0900, Michael Paquier wrote:
> > How can you be sure as well that all such deployments would use random
> > CN fields and/or random usernames? We have no guarantee of that as
> > well.
> 
> Sorry, but this is a bit ridiculous.

And this email was incomplete, sorry for that.

The username isn't guaranteed to be randomized. Application name will
very rarely be given it's set by the client. We show all of that
today. To me the fix for all this is to actually improve the situation
(by allowing proper permissions on pg_stat_activity) rather than incur
pain to everyone because of an absolutely marginal improvement in
security.

Greetings,

Andres Freund


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Information of pg_stat_ssl visible to all users

[Magnus Hagander]

On Sun, Aug 30, 2015 at 5:35 AM, Michael Paquier 
wrote:

>
>
> On Sun, Aug 30, 2015 at 5:27 AM, Bruce Momjian wrote:
>
>> I know I am coming in late here, but I know Heroku uses random user
>> names to allow a cluster to have per-user databases without showing
>> external user name details:
>> [...]
>> I can see them having problems with a user being able to see the SSL
>> remote user names of all connected users.
>>
>
> Yep, and I can imagine that this is the case of any company managing cloud
> nodes with Postgres embedded, and at least to me that's a real concern.
>


How is it a concern that  a CN field with a random username in it is
visible, when showing the actual random username isn't? That's not very
consistent...

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [HACKERS] Information of pg_stat_ssl visible to all users

[Magnus Hagander]

On Sat, Aug 29, 2015 at 10:27 PM, Bruce Momjian  wrote:

> On Tue, Jul  7, 2015 at 12:57:58PM -0400, Tom Lane wrote:
> > Andres Freund  writes:
> > > On 2015-07-07 12:03:36 -0400, Peter Eisentraut wrote:
> > >> I think the DN is analogous to the remote user name, which we don't
> > >> expose for any of the other authentication methods.
> >
> > > Huh?
> >
> > Peter's exactly right: there is no other case where you can tell what
> > some other connection's actual OS username is.  You might *guess* that
> > it's the same as their database username, but you don't know that,
> > assuming you don't know how they authenticated.
> >
> > I'm not sure how security-critical this info really is, though.
>
> I know I am coming in late here, but I know Heroku uses random user
> names to allow a cluster to have per-user databases without showing
> external user name details:
>
> => \du
> List of roles
>Role name|   Attributes   |
> Member of
>
> ++---
>  aafgrwewediiqz | 20 connections |
> {}
>  aaszwkfnholarh | 20 connections |
> {}
>  aatbelxbaeriwy | 20 connections |
> {}
>  aaxiwolkcxmbxo | 20 connections |
> {}
>  abbyljzgqaonjb | 20 connections |
> {}
>
> I can see them having problems with a user being able to see the SSL
> remote user names of all connected users.
>


I'm pretty sure Heroku don't use client certificates.

And if they did, I would assume the client certificate would be issued to
 aafgrwewediiqz, or possibly aafgrwewedi...@customer.heroku.com or
something along that line.

Client certificates don't show anything other than the username, unless you
explicitly choose to put sensitive information in the CN. But we don't
limit the view of the username in pg_stat_activity, even though people do
put sensitive things in there (such as the customer name in case of shared
hosting - everybody doesn't do what Heroku does).

So pg_stat_ssl doesn't show something that's not already visible.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [HACKERS] Information of pg_stat_ssl visible to all users

[Andres Freund]

On 2015-08-31 21:17:48 +0900, Michael Paquier wrote:
> How can you be sure as well that all such deployments would use random
> CN fields and/or random usernames? We have no guarantee of that as
> well.

Sorry, but this is a bit ridiculous.

Greetings,

Andres Freund


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Information of pg_stat_ssl visible to all users

[Michael Paquier]

On Mon, Aug 31, 2015 at 9:04 PM, Magnus Hagander  wrote:
>
>
> On Sun, Aug 30, 2015 at 5:35 AM, Michael Paquier 
> wrote:
>>
>>
>>
>> On Sun, Aug 30, 2015 at 5:27 AM, Bruce Momjian wrote:
>>>
>>> I know I am coming in late here, but I know Heroku uses random user
>>> names to allow a cluster to have per-user databases without showing
>>> external user name details:
>>> [...]
>>> I can see them having problems with a user being able to see the SSL
>>> remote user names of all connected users.
>>
>>
>> Yep, and I can imagine that this is the case of any company managing cloud
>> nodes with Postgres embedded, and at least to me that's a real concern.
>
>
>
> How is it a concern that  a CN field with a random username in it is
> visible, when showing the actual random username isn't? That's not very
> consistent...

How can you be sure as well that all such deployments would use random
CN fields and/or random usernames? We have no guarantee of that as
well.
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Information of pg_stat_ssl visible to all users

[Andres Freund]

On 2015-08-30 11:33:28 -0400, Stephen Frost wrote:
 Yeah, I'm not really thrilled with all of this information being
 available to everyone on the system.  We already get ding'd by people
 for not limiting who can see what connections there are to the database
 and this is doubling-down on that.

I don't buy that the relevant piece of information is the CN when the
connection itself is visible. Neither do I buy the argument that later
hiding this for ssl once we have more granular permissions is going to
be relevantly painful in comparison to changing the contents of
pg_stat_activity itself.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Information of pg_stat_ssl visible to all users

[Stephen Frost]

* Michael Paquier (michael.paqu...@gmail.com) wrote:
 On Sun, Aug 30, 2015 at 5:27 AM, Bruce Momjian wrote:
 
  I know I am coming in late here, but I know Heroku uses random user
  names to allow a cluster to have per-user databases without showing
  external user name details:
  [...]
  I can see them having problems with a user being able to see the SSL
  remote user names of all connected users.
 
 
 Yep, and I can imagine that this is the case of any company managing cloud
 nodes with Postgres embedded, and at least to me that's a real concern.

Yeah, I'm not really thrilled with all of this information being
available to everyone on the system.  We already get ding'd by people
for not limiting who can see what connections there are to the database
and this is doubling-down on that.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] Information of pg_stat_ssl visible to all users

[Bruce Momjian]

On Tue, Jul  7, 2015 at 12:57:58PM -0400, Tom Lane wrote:
 Andres Freund and...@anarazel.de writes:
  On 2015-07-07 12:03:36 -0400, Peter Eisentraut wrote:
  I think the DN is analogous to the remote user name, which we don't
  expose for any of the other authentication methods.
 
  Huh?
 
 Peter's exactly right: there is no other case where you can tell what
 some other connection's actual OS username is.  You might *guess* that
 it's the same as their database username, but you don't know that,
 assuming you don't know how they authenticated.
 
 I'm not sure how security-critical this info really is, though.

I know I am coming in late here, but I know Heroku uses random user
names to allow a cluster to have per-user databases without showing
external user name details:

= \du
List of roles
   Role name|   Attributes   | 
Member of

++---
 aafgrwewediiqz | 20 connections | {}
 aaszwkfnholarh | 20 connections | {}
 aatbelxbaeriwy | 20 connections | {}
 aaxiwolkcxmbxo | 20 connections | {}
 abbyljzgqaonjb | 20 connections | {}

I can see them having problems with a user being able to see the SSL
remote user names of all connected users.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Information of pg_stat_ssl visible to all users

[Michael Paquier]

On Sun, Aug 30, 2015 at 5:27 AM, Bruce Momjian wrote:

 I know I am coming in late here, but I know Heroku uses random user
 names to allow a cluster to have per-user databases without showing
 external user name details:
 [...]
 I can see them having problems with a user being able to see the SSL
 remote user names of all connected users.


Yep, and I can imagine that this is the case of any company managing cloud
nodes with Postgres embedded, and at least to me that's a real concern.
-- 
Michael

Re: [HACKERS] Information of pg_stat_ssl visible to all users

[Andres Freund]

On 2015-07-07 12:03:36 -0400, Peter Eisentraut wrote:
 I think the DN is analogous to the remote user name, which we don't
 expose for any of the other authentication methods.

Huh?

Datum
pg_stat_get_activity(PG_FUNCTION_ARGS)
{
/* Values available to all callers */
values[0] = ObjectIdGetDatum(beentry-st_databaseid);
values[1] = Int32GetDatum(beentry-st_procpid);
values[2] = ObjectIdGetDatum(beentry-st_userid);
...

Isn't that like, essentially, all of them? Sure, if you have a ident
mapping set up, then not, but I have a hard time seing that as a
relevant use case.

 I think the default approach for security and authentication related
 information should be conservative, even if there is not a specific
 reason.  Or to put it another way: What is the motivation for showing
 this information at all?

That we already show equivalent information and that hiding it from
another place will just be crufty and make monitoring setups more
complex.

Greetings,

Andres Freund


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Information of pg_stat_ssl visible to all users

[Peter Eisentraut]

On 7/2/15 3:29 PM, Magnus Hagander wrote:
 On Thu, Jul 2, 2015 at 5:40 PM, Peter Eisentraut pete...@gmx.net
 mailto:pete...@gmx.net wrote:
 
 On 6/10/15 2:17 AM, Magnus Hagander wrote:
  AIUI that one was just about the DN field, and not about the rest. If I
  understand you correctly, you are referring to the whole thing, not just
  one field?
 
 I think at least the DN field shouldn't be visible to unprivileged
 users.
 
 What's the argument for that? I mean, the DN field is the equivalent of
 the username, and we show the username in pg_stat_activity already. Are
 you envisioning a scenario where there is actually something secret in
 the DN?

I think the DN is analogous to the remote user name, which we don't
expose for any of the other authentication methods.

 Actually, I think the whole view shouldn't be accessible to unprivileged
 users, except maybe your own row.
 
 
 I could go for some of the others if we think there's reason, but I
 don't understand the dn part?
 
 I guess there's some consistency in actually blocking exactly everything...

I think the default approach for security and authentication related
information should be conservative, even if there is not a specific
reason.  Or to put it another way: What is the motivation for showing
this information at all?




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Information of pg_stat_ssl visible to all users

[Tom Lane]

Andres Freund and...@anarazel.de writes:
 On 2015-07-07 12:03:36 -0400, Peter Eisentraut wrote:
 I think the DN is analogous to the remote user name, which we don't
 expose for any of the other authentication methods.

 Huh?

Peter's exactly right: there is no other case where you can tell what
some other connection's actual OS username is.  You might *guess* that
it's the same as their database username, but you don't know that,
assuming you don't know how they authenticated.

I'm not sure how security-critical this info really is, though.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Information of pg_stat_ssl visible to all users

[Magnus Hagander]

On Tue, Jul 7, 2015 at 6:03 PM, Peter Eisentraut pete...@gmx.net wrote:

 On 7/2/15 3:29 PM, Magnus Hagander wrote:
  On Thu, Jul 2, 2015 at 5:40 PM, Peter Eisentraut pete...@gmx.net
  mailto:pete...@gmx.net wrote:
 
  On 6/10/15 2:17 AM, Magnus Hagander wrote:
   AIUI that one was just about the DN field, and not about the rest.
 If I
   understand you correctly, you are referring to the whole thing,
 not just
   one field?
 
  I think at least the DN field shouldn't be visible to unprivileged
  users.
 
  What's the argument for that? I mean, the DN field is the equivalent of
  the username, and we show the username in pg_stat_activity already. Are
  you envisioning a scenario where there is actually something secret in
  the DN?

 I think the DN is analogous to the remote user name, which we don't
 expose for any of the other authentication methods.

  Actually, I think the whole view shouldn't be accessible to
 unprivileged
  users, except maybe your own row.
 
 
  I could go for some of the others if we think there's reason, but I
  don't understand the dn part?
 
  I guess there's some consistency in actually blocking exactly
 everything...

 I think the default approach for security and authentication related
 information should be conservative, even if there is not a specific
 reason.  Or to put it another way: What is the motivation for showing
 this information at all?


To make it accessible to monitoring systems that don't run as superuser
(which should be most monitoring systems, but we have other cases making
that hard as has already been mentioned upthread).

I'm having a hard time trying to figure out a consensus in this thread. I
think there are slightly more arguments for limiting the access though.

The question then is, if we want to hide everything, do we care about doing
the NULL dance, or should we just throw an error for non-superusers
trying to access it?

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [HACKERS] Information of pg_stat_ssl visible to all users

[Josh Berkus]

On 07/07/2015 11:29 AM, Stephen Frost wrote:
 * Josh Berkus (j...@agliodbs.com) wrote:
 On 07/07/2015 09:06 AM, Magnus Hagander wrote:

 To make it accessible to monitoring systems that don't run as superuser
 (which should be most monitoring systems, but we have other cases making
 that hard as has already been mentioned upthread). 

 I'm having a hard time trying to figure out a consensus in this thread.
 I think there are slightly more arguments for limiting the access though.

 The question then is, if we want to hide everything, do we care about
 doing the NULL dance, or should we just throw an error for
 non-superusers trying to access it?

 I'm going to vote against blocking the entire view for non-superusers.
 One of the things people will want to monitor is are all connection
 from subnet X using SSL? which is most easily answered by joining
 pg_stat_activity and pg_stat_ssl.

 If we force users to use superuser privs to find this out, then we're
 encouraging them to run monitoring as superuser, which is something we
 want to get *away* from.
 
 I agree with all of this, but I'm worried that if we make it available
 now then we may not be able to hide it later, even once we have the
 monitoring role defined, because of backwards compatibility concerns.
 
 If we aren't worried about that, then perhaps we can leave it less
 strict for 9.5 and then make it stricter for 9.6.
 
 I'd be OK with concealing some columns:

 postgres=# select * from pg_stat_ssl;
  pid | ssl | version |   cipher| bits | compression
 | clientdn
 -+-+-+-+--+-+--
   37 | t   | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 |  256 | f   |

 I can see NULLifying cipher and DN columns.  The other columns, it's
 hard to imagine what use an attacker could put them to that they
 wouldn't be able to find out the same information easily using other routes.
 
 Perhaps not, but I'm not sure how useful those columns would be to a
 monitoring system either..  I'd rather keep it simple.

So what about making just pid, ssl and compression available?  That's
mostly what anyone would want to monitor, except for periodic security
audits.  Audits could be done by superuser, no problem.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Information of pg_stat_ssl visible to all users

[Josh Berkus]

On 07/07/2015 09:06 AM, Magnus Hagander wrote:
 
 To make it accessible to monitoring systems that don't run as superuser
 (which should be most monitoring systems, but we have other cases making
 that hard as has already been mentioned upthread). 
 
 I'm having a hard time trying to figure out a consensus in this thread.
 I think there are slightly more arguments for limiting the access though.
 
 The question then is, if we want to hide everything, do we care about
 doing the NULL dance, or should we just throw an error for
 non-superusers trying to access it?

I'm going to vote against blocking the entire view for non-superusers.
One of the things people will want to monitor is are all connection
from subnet X using SSL? which is most easily answered by joining
pg_stat_activity and pg_stat_ssl.

If we force users to use superuser privs to find this out, then we're
encouraging them to run monitoring as superuser, which is something we
want to get *away* from.

I'd be OK with concealing some columns:

postgres=# select * from pg_stat_ssl;
 pid | ssl | version |   cipher| bits | compression
| clientdn
-+-+-+-+--+-+--
  37 | t   | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 |  256 | f   |

I can see NULLifying cipher and DN columns.  The other columns, it's
hard to imagine what use an attacker could put them to that they
wouldn't be able to find out the same information easily using other routes.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Information of pg_stat_ssl visible to all users

[Stephen Frost]

* Josh Berkus (j...@agliodbs.com) wrote:
 On 07/07/2015 09:06 AM, Magnus Hagander wrote:
  
  To make it accessible to monitoring systems that don't run as superuser
  (which should be most monitoring systems, but we have other cases making
  that hard as has already been mentioned upthread). 
  
  I'm having a hard time trying to figure out a consensus in this thread.
  I think there are slightly more arguments for limiting the access though.
  
  The question then is, if we want to hide everything, do we care about
  doing the NULL dance, or should we just throw an error for
  non-superusers trying to access it?
 
 I'm going to vote against blocking the entire view for non-superusers.
 One of the things people will want to monitor is are all connection
 from subnet X using SSL? which is most easily answered by joining
 pg_stat_activity and pg_stat_ssl.
 
 If we force users to use superuser privs to find this out, then we're
 encouraging them to run monitoring as superuser, which is something we
 want to get *away* from.

I agree with all of this, but I'm worried that if we make it available
now then we may not be able to hide it later, even once we have the
monitoring role defined, because of backwards compatibility concerns.

If we aren't worried about that, then perhaps we can leave it less
strict for 9.5 and then make it stricter for 9.6.

 I'd be OK with concealing some columns:
 
 postgres=# select * from pg_stat_ssl;
  pid | ssl | version |   cipher| bits | compression
 | clientdn
 -+-+-+-+--+-+--
   37 | t   | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 |  256 | f   |
 
 I can see NULLifying cipher and DN columns.  The other columns, it's
 hard to imagine what use an attacker could put them to that they
 wouldn't be able to find out the same information easily using other routes.

Perhaps not, but I'm not sure how useful those columns would be to a
monitoring system either..  I'd rather keep it simple.

Thanks!

Stephen



signature.asc
Description: Digital signature

Re: [HACKERS] Information of pg_stat_ssl visible to all users

[Michael Paquier]

On Wed, Jul 8, 2015 at 3:29 AM, Stephen Frost sfr...@snowman.net wrote:
 * Josh Berkus (j...@agliodbs.com) wrote:
 On 07/07/2015 09:06 AM, Magnus Hagander wrote:
 
  To make it accessible to monitoring systems that don't run as superuser
  (which should be most monitoring systems, but we have other cases making
  that hard as has already been mentioned upthread).
 
  I'm having a hard time trying to figure out a consensus in this thread.
  I think there are slightly more arguments for limiting the access though.
 
  The question then is, if we want to hide everything, do we care about
  doing the NULL dance, or should we just throw an error for
  non-superusers trying to access it?

 I'm going to vote against blocking the entire view for non-superusers.
 One of the things people will want to monitor is are all connection
 from subnet X using SSL? which is most easily answered by joining
 pg_stat_activity and pg_stat_ssl.

 If we force users to use superuser privs to find this out, then we're
 encouraging them to run monitoring as superuser, which is something we
 want to get *away* from.

 I agree with all of this, but I'm worried that if we make it available
 now then we may not be able to hide it later, even once we have the
 monitoring role defined, because of backwards compatibility concerns.

 If we aren't worried about that, then perhaps we can leave it less
 strict for 9.5 and then make it stricter for 9.6.

Agreed. It is better to make things strict first and relax afterwards
from the user prospective, so I would vote for something in this
direction. We could relax it with this monitoring ACL afterwards in
9.6, still what I think we are missing here are reactions from the
field, and I suspect that taking the most careful approach (hiding a
maximum of fields to non-authorized users) will pay better in the long
term. I am also suspecting that if we let it as-is cloud deployments
of Postgres (Heroku for example) are going to patch this view with ACL
checks.
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Information of pg_stat_ssl visible to all users

[Stephen Frost]

* Magnus Hagander (mag...@hagander.net) wrote:
 On Thu, Jul 2, 2015 at 10:06 PM, Andres Freund and...@anarazel.de wrote:
 
  On 2015-07-02 16:52:01 -0300, Alvaro Herrera wrote:
   If there's interest in closing these holes, this might be a first
 
  I don't think such an isolated attempt buys us anything except maybe
  unsatisfied users.
 
  I can see a benefit in allowing to restrict information about users and
  such in other clusters, but changing stat_ssl seeems to be an
  inconsequentially small problem on that path.
 
 
 We discussed earlier having a monitoring role or attribute or something
 like that, and I think this would be another case of that. We definitely
 want to go towards something like that, but that's not happening in 9.5...

Agreed, but if we make this visible to all in 9.5 then we're going to
have a tough time restricting it to just the monitoring role in 9.6, I'm
afraid...

We realize it's a problem, for my 2c, I'd rather not double-down on it
by providing more information which should really be limited to
privileged users.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] Information of pg_stat_ssl visible to all users

[Peter Eisentraut]

On 6/10/15 2:17 AM, Magnus Hagander wrote:
 AIUI that one was just about the DN field, and not about the rest. If I
 understand you correctly, you are referring to the whole thing, not just
 one field?

I think at least the DN field shouldn't be visible to unprivileged users.

Actually, I think the whole view shouldn't be accessible to unprivileged
users, except maybe your own row.



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Information of pg_stat_ssl visible to all users

[Magnus Hagander]

On Thu, Jul 2, 2015 at 5:40 PM, Peter Eisentraut pete...@gmx.net wrote:

 On 6/10/15 2:17 AM, Magnus Hagander wrote:
  AIUI that one was just about the DN field, and not about the rest. If I
  understand you correctly, you are referring to the whole thing, not just
  one field?

 I think at least the DN field shouldn't be visible to unprivileged users.


What's the argument for that? I mean, the DN field is the equivalent of the
username, and we show the username in pg_stat_activity already. Are you
envisioning a scenario where there is actually something secret in the DN?




 Actually, I think the whole view shouldn't be accessible to unprivileged
 users, except maybe your own row.


I could go for some of the others if we think there's reason, but I don't
understand the dn part?

I guess there's some consistency in actually blocking exactly everything...

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [HACKERS] Information of pg_stat_ssl visible to all users

[Alvaro Herrera]

Magnus Hagander wrote:
 On Thu, Jul 2, 2015 at 5:40 PM, Peter Eisentraut pete...@gmx.net wrote:

  Actually, I think the whole view shouldn't be accessible to unprivileged
  users, except maybe your own row.
 
 I could go for some of the others if we think there's reason, but I don't
 understand the dn part?
 
 I guess there's some consistency in actually blocking exactly everything...

One case that I remember popping up every so often was I don't want
people to know what other customers I have in the same database
cluster.  We leak these details all over the place (catalogs that can
be queried directly, as well as pg_stat_activity itself, etc), so just
changing the new view would accomplish nothing.  If there's interest in
closing these holes, this might be a first step.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Information of pg_stat_ssl visible to all users

[Magnus Hagander]

On Thu, Jul 2, 2015 at 10:06 PM, Andres Freund and...@anarazel.de wrote:

 On 2015-07-02 16:52:01 -0300, Alvaro Herrera wrote:
  If there's interest in closing these holes, this might be a first

 I don't think such an isolated attempt buys us anything except maybe
 unsatisfied users.

 I can see a benefit in allowing to restrict information about users and
 such in other clusters, but changing stat_ssl seeems to be an
 inconsequentially small problem on that path.



We discussed earlier having a monitoring role or attribute or something
like that, and I think this would be another case of that. We definitely
want to go towards something like that, but that's not happening in 9.5...

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [HACKERS] Information of pg_stat_ssl visible to all users

[Magnus Hagander]

On Tue, Jun 9, 2015 at 10:55 PM, Michael Paquier michael.paqu...@gmail.com
wrote:

 On Tue, Jun 9, 2015 at 3:27 PM, Magnus Hagander mag...@hagander.net
 wrote:
 
  On Jun 9, 2015 6:00 AM, Michael Paquier michael.paqu...@gmail.com
 wrote:
 
  Hi all,
 
  I should have noticed that before, but it happens that pg_stat_ssl
  leaks information about the SSL status of all the users connected to a
  server. Let's imagine for example:
  1) Session 1 connected through SSL with a superuser:
  =# create role toto login;
  CREATE ROLE
  =# select * from pg_stat_ssl;
pid  | ssl | version |   cipher| bits |
  compression | clientdn
 
 
 ---+-+-+-+--+-+--
   33348 | t   | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 |  256 | t
  |
  (1 row)
  2) New session 2 with previously created user:
  = select * from pg_stat_ssl;
pid  | ssl | version |   cipher| bits |
  compression | clientdn
 
 
 ---+-+-+-+--+-+--
   33348 | t   | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 |  256 | t
  |
   33367 | t   | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 |  256 | t
  |
  (2 rows)
 
  Attached is a patch to mask those values to users that should not have
  access to it, similarly to the other fields of pg_stat_activity.
 
  I don't have the thread around right now (on phone), but didn't we
 discuss
  this back around the original submission and decide that this was wanted
  behavior?

 Looking back at this thread, it is mentioned here:
 http://www.postgresql.org/message-id/31891.1405175...@sss.pgh.pa.us



AIUI that one was just about the DN field, and not about the rest. If I
understand you correctly, you are referring to the whole thing, not just
one field?



 What actual sensitive data is leaked? If knowing the cipher type makes it
  easier to hack you have a broken cipher, don't you?

 I am just wondering if it is a good idea to let other users know the
 origin of a connection to all the users. Let's imagine the case where
 for example the same user name is used for non-SSL and SSL sessions.
 This could give a hint of the activity on the server..

 However, feel free to ignore those concerns if you think the current
 situation is fine...



Well, I do think the current one is OK, but I don't want to ignore the
comment anyway :) Happy to hear comments from others as well.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [HACKERS] Information of pg_stat_ssl visible to all users

[Magnus Hagander]

On Jun 9, 2015 6:00 AM, Michael Paquier michael.paqu...@gmail.com wrote:

 Hi all,

 I should have noticed that before, but it happens that pg_stat_ssl
 leaks information about the SSL status of all the users connected to a
 server. Let's imagine for example:
 1) Session 1 connected through SSL with a superuser:
 =# create role toto login;
 CREATE ROLE
 =# select * from pg_stat_ssl;
   pid  | ssl | version |   cipher| bits |
 compression | clientdn

---+-+-+-+--+-+--
  33348 | t   | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 |  256 | t
 |
 (1 row)
 2) New session 2 with previously created user:
 = select * from pg_stat_ssl;
   pid  | ssl | version |   cipher| bits |
 compression | clientdn

---+-+-+-+--+-+--
  33348 | t   | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 |  256 | t
 |
  33367 | t   | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 |  256 | t
 |
 (2 rows)

 Attached is a patch to mask those values to users that should not have
 access to it, similarly to the other fields of pg_stat_activity.

I don't have the thread around right now (on phone), but didn't we discuss
this back around the original submission and decide that this was wanted
behavior?

What actual sensitive data is leaked? If knowing the cipher type makes it
easier to hack you have a broken cipher, don't you?

/Magnus

Re: [HACKERS] Information of pg_stat_ssl visible to all users

[Michael Paquier]

On Tue, Jun 9, 2015 at 3:27 PM, Magnus Hagander mag...@hagander.net wrote:

 On Jun 9, 2015 6:00 AM, Michael Paquier michael.paqu...@gmail.com wrote:

 Hi all,

 I should have noticed that before, but it happens that pg_stat_ssl
 leaks information about the SSL status of all the users connected to a
 server. Let's imagine for example:
 1) Session 1 connected through SSL with a superuser:
 =# create role toto login;
 CREATE ROLE
 =# select * from pg_stat_ssl;
   pid  | ssl | version |   cipher| bits |
 compression | clientdn

 ---+-+-+-+--+-+--
  33348 | t   | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 |  256 | t
 |
 (1 row)
 2) New session 2 with previously created user:
 = select * from pg_stat_ssl;
   pid  | ssl | version |   cipher| bits |
 compression | clientdn

 ---+-+-+-+--+-+--
  33348 | t   | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 |  256 | t
 |
  33367 | t   | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 |  256 | t
 |
 (2 rows)

 Attached is a patch to mask those values to users that should not have
 access to it, similarly to the other fields of pg_stat_activity.

 I don't have the thread around right now (on phone), but didn't we discuss
 this back around the original submission and decide that this was wanted
 behavior?

Looking back at this thread, it is mentioned here:
http://www.postgresql.org/message-id/31891.1405175...@sss.pgh.pa.us

 What actual sensitive data is leaked? If knowing the cipher type makes it
 easier to hack you have a broken cipher, don't you?

I am just wondering if it is a good idea to let other users know the
origin of a connection to all the users. Let's imagine the case where
for example the same user name is used for non-SSL and SSL sessions.
This could give a hint of the activity on the server..

However, feel free to ignore those concerns if you think the current
situation is fine...
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Information of pg_stat_ssl visible to all users

[Michael Paquier]

Hi all,

I should have noticed that before, but it happens that pg_stat_ssl
leaks information about the SSL status of all the users connected to a
server. Let's imagine for example:
1) Session 1 connected through SSL with a superuser:
=# create role toto login;
CREATE ROLE
=# select * from pg_stat_ssl;
  pid  | ssl | version |   cipher| bits |
compression | clientdn
---+-+-+-+--+-+--
 33348 | t   | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 |  256 | t   |
(1 row)
2) New session 2 with previously created user:
= select * from pg_stat_ssl;
  pid  | ssl | version |   cipher| bits |
compression | clientdn
---+-+-+-+--+-+--
 33348 | t   | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 |  256 | t   |
 33367 | t   | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 |  256 | t   |
(2 rows)

Attached is a patch to mask those values to users that should not have
access to it, similarly to the other fields of pg_stat_activity.
Regards,
-- 
Michael
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index f7c9bf6..159860b 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -626,21 +626,6 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
 		else
 			nulls[15] = true;
 
-		if (beentry-st_ssl)
-		{
-			values[16] = BoolGetDatum(true);	/* ssl */
-			values[17] = CStringGetTextDatum(beentry-st_sslstatus-ssl_version);
-			values[18] = CStringGetTextDatum(beentry-st_sslstatus-ssl_cipher);
-			values[19] = Int32GetDatum(beentry-st_sslstatus-ssl_bits);
-			values[20] = BoolGetDatum(beentry-st_sslstatus-ssl_compression);
-			values[21] = CStringGetTextDatum(beentry-st_sslstatus-ssl_clientdn);
-		}
-		else
-		{
-			values[16] = BoolGetDatum(false);	/* ssl */
-			nulls[17] = nulls[18] = nulls[19] = nulls[20] = nulls[21] = true;
-		}
-
 		/* Values only available to role member */
 		if (has_privs_of_role(GetUserId(), beentry-st_userid))
 		{
@@ -761,6 +746,22 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
 	nulls[13] = true;
 }
 			}
+
+			/* ssl information */
+			if (beentry-st_ssl)
+			{
+values[16] = BoolGetDatum(true);	/* ssl */
+values[17] = CStringGetTextDatum(beentry-st_sslstatus-ssl_version);
+values[18] = CStringGetTextDatum(beentry-st_sslstatus-ssl_cipher);
+values[19] = Int32GetDatum(beentry-st_sslstatus-ssl_bits);
+values[20] = BoolGetDatum(beentry-st_sslstatus-ssl_compression);
+values[21] = CStringGetTextDatum(beentry-st_sslstatus-ssl_clientdn);
+			}
+			else
+			{
+values[16] = BoolGetDatum(false);	/* ssl */
+nulls[17] = nulls[18] = nulls[19] = nulls[20] = nulls[21] = true;
+			}
 		}
 		else
 		{
@@ -775,6 +776,13 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
 			nulls[11] = true;
 			nulls[12] = true;
 			nulls[13] = true;
+			/* ssl information */
+			nulls[16] = true;
+			nulls[17] = true;
+			nulls[18] = true;
+			nulls[19] = true;
+			nulls[20] = true;
+			nulls[21] = true;
 		}
 
 		tuplestore_putvalues(tupstore, tupdesc, values, nulls);

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers