Re: Information of pg_stat_ssl visible to all users

On Sat, Aug 29, 2015 at 10:27 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Tue, Jul 7, 2015 at 12:57:58PM -0400, Tom Lane wrote:
> > Andres Freund <andres(at)anarazel(dot)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.
>

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(at)customer(dot)heroku(dot)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/