Thread: Information of pg_stat_ssl visible to all users

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

<p dir="ltr"><br /> On Jun 9, 2015 6:00 AM, "Michael Paquier" <<a
href="mailto:michael.paquier@gmail.com">michael.paquier@gmail.com</a>>wrote:<br /> ><br /> > Hi all,<br />
><br/> > I should have noticed that before, but it happens that pg_stat_ssl<br /> > leaks information about
theSSL status of all the users connected to a<br /> > server. Let's imagine for example:<br /> > 1) Session 1
connectedthrough SSL with a superuser:<br /> > =# create role toto login;<br /> > CREATE ROLE<br /> > =#
select* from pg_stat_ssl;<br /> >   pid  | ssl | version |           cipher            | bits |<br /> >
compression| clientdn<br /> >
-------+-----+---------+-----------------------------+------+-------------+----------<br/> >  33348 | t   | TLSv1.2
|ECDHE-RSA-AES256-GCM-SHA384 |  256 | t           |<br /> > (1 row)<br /> > 2) New session 2 with previously
createduser:<br /> > => select * from pg_stat_ssl;<br /> >   pid  | ssl | version |           cipher         
 | bits |<br /> > compression | clientdn<br /> >
-------+-----+---------+-----------------------------+------+-------------+----------<br/> >  33348 | t   | TLSv1.2
|ECDHE-RSA-AES256-GCM-SHA384 |  256 | t           |<br /> >  33367 | t   | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 | 
256| t           |<br /> > (2 rows)<br /> ><br /> > Attached is a patch to mask those values to users that
shouldnot have<br /> > access to it, similarly to the other fields of pg_stat_activity.<br /><p dir="ltr">I don't
havethe thread around right now (on phone), but didn't we discuss this back around the original submission and decide
thatthis was wanted behavior? <p dir="ltr">What actual sensitive data is leaked? If knowing the cipher type makes it
easierto hack you have a broken cipher, don't you? <p dir="ltr">/Magnus <br /> 

On Tue, Jun 9, 2015 at 3:27 PM, Magnus Hagander <magnus@hagander.net> wrote:
>
> On Jun 9, 2015 6:00 AM, "Michael Paquier" <michael.paquier@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.1405175764@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

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.

Magnus Hagander wrote:
> On Thu, Jul 2, 2015 at 5:40 PM, Peter Eisentraut <peter_e@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 Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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.

* Magnus Hagander (magnus@hagander.net) wrote:
> On Thu, Jul 2, 2015 at 10:06 PM, Andres Freund <andres@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

On 7/2/15 3:29 PM, Magnus Hagander wrote:
> On Thu, Jul 2, 2015 at 5:40 PM, Peter Eisentraut <peter_e@gmx.net
> <mailto:peter_e@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?

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

Andres Freund <andres@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

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

* Josh Berkus (josh@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

On 07/07/2015 11:29 AM, Stephen Frost wrote:
> * Josh Berkus (josh@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

On Wed, Jul 8, 2015 at 3:29 AM, Stephen Frost <sfrost@snowman.net> wrote:
> * Josh Berkus (josh@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

On Tue, Jul  7, 2015 at 12:57:58PM -0400, Tom Lane wrote:
> Andres Freund <andres@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                   |
Memberof----------------+------------------------------------------------+----------- aafgrwewediiqz | 20 connections
                             | {} aaszwkfnholarh | 20 connections                                 | {} aatbelxbaeriwy |
20connections                                 | {} 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  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +

<div dir="ltr"><br /><div class="gmail_extra"><br /><div class="gmail_quote">On Sun, Aug 30, 2015 at 5:27 AM, Bruce
Momjian<spandir="ltr"></span> wrote:<br /><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc
solid;padding-left:1ex"><spanclass=""> </span>I know I am coming in late here, but I know Heroku uses random user<br />
namesto allow a cluster to have per-user databases without showing<br /> external user name details:<br />[...]<br /> I
cansee them having problems with a user being able to see the SSL<br /> remote user names of all connected users.<span
class="HOEnZb"><fontcolor="#888888"><br /></font></span></blockquote></div><br /></div><div class="gmail_extra">Yep,
andI can imagine that this is the case of any company managing cloud nodes with Postgres embedded, and at least to me
that'sa real concern.<br />-- <br /><div class="gmail_signature">Michael<br /></div></div></div> 

* Michael Paquier (michael.paquier@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

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.

On Mon, Aug 31, 2015 at 9:04 PM, Magnus Hagander <magnus@hagander.net> wrote:
>
>
> On Sun, Aug 30, 2015 at 5:35 AM, Michael Paquier <michael.paquier@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.
-- 
Michael

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

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

* Magnus Hagander (magnus@hagander.net) wrote:
> On Sat, Aug 29, 2015 at 10:27 PM, Bruce Momjian <bruce@momjian.us> 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 aafgrwewediiqz@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

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

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.

On Tue, Sep 1, 2015 at 4:23 AM, Peter Eisentraut <peter_e@gmx.net> 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

<p dir="ltr"><br /> On Sep 1, 2015 4:37 AM, "Michael Paquier" <<a
href="mailto:michael.paquier@gmail.com">michael.paquier@gmail.com</a>>wrote:<br /> ><br /> > On Tue, Sep 1,
2015at 4:23 AM, Peter Eisentraut <<a href="mailto:peter_e@gmx.net">peter_e@gmx.net</a>> wrote:<br /> > > On
8/31/159:13 AM, Andres Freund wrote:<br /> > >> I'm just saying that we should strive to behave at least
somewhat<br/> > >> consistently, and change everything at once, not piecemal. Because the<br /> > >>
latterwill not decrease the pain of migrating to a new model in a<br /> > >> relevant way while making the
systemharder to understand.<br /> > ><br /> > > Well, we already hide a fair chunk of information from
pg_stat_activity<br/> > > from unprivileged users, including everything related to the connection<br /> > >
originof other users.  So from that precedent, the entire SSL<br /> > > information ought to be considered
privileged.<br/> ><br /> > That being said we may want as well to bite the bullet and to hide<br /> > more
informationin pg_stat_activity, like datname, usename and<br /> > application_name, or simply hide completely those
tuplesfor<br /> > non-privileged users.<br /><p dir="ltr">That's likely to break every single monitoring tool ever
writtenfor postgresql... <p dir="ltr">We're going to have to do that eventually, but I think we should wait until we
havea complete solution (which would be either column permissions, monitoring role, or something like that (or
combinationthereof)). <p dir="ltr">/Magnus