Re: Log operating system user connecting via unix socket

Hi again.

About the privileges, our support can create roles / databases, drop
existing databases, dump /restore, change other users passwords. It's not
feasible right now create a 1:1 map of system users and postgres users.
Maybe in the future.

I wrote 2 possible patches, both issuing a detail message only if
log_connections is enabled.

The first one using the Stephen Frost suggestion, inside the Port struct (I
guess that this is the one, I coudn't find the Peer struct)

The second one following the same approach of cf commit 5e0b5dcab, as
pointed by Tom Lane.

Again, feel free to comment and criticize.

On Sun, Jan 17, 2016 at 3:07 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> Tom,
>
> * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> > Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > > What I think we really want here is logging of the general 'system
> > > user' for all auth methods instead of only for the 'peer' method.
> >
> > Well, we don't really know that except in a small subset of auth
> > methods. I agree that when we do know it, it's useful info to log.
>
> Right.
>
> > My big beef with the proposed patch is that the log message is emitted
> > unconditionally. There are lots and lots of users who feel that during
> > normal operation, *zero* log messages should get emitted. Those
> villagers
> > would be on our doorsteps with pitchforks if we shipped this patch as-is.
>
> Agreed.
>
> > I would propose that this information should be emitted only when
> > log_connections is enabled, and indeed that it should be part of the
> > log_connections message not a separate message. So this leads to
> > thinking that somehow, the code for individual auth methods should
> > be able to return an "additional info" field for inclusion in
> > log_connections. We already have such a concept for auth failures,
> > cf commit 5e0b5dcab.
>
> Apologies if it wasn't clear, but that's exactly what I was suggesting
> by saying to add it to PerformAuthentication, which is where we emit
> the connection info when log_connections is enabled.
>
> > > ... and also make it available in pg_stat_activity.
> >
> > That's moving the goalposts quite a bit, and I'm not sure it's necessary
> > or even desirable. Let's just get this added to log_connections output,
> > and then see if there's field demand for more.
>
> This was in context of peer_cn, which is just a specific "system user"
> value and which we're already showing in pg_stat_* info tables. I'd
> love to have the Kerberos principal available, but I don't think it'd
> make sense to have a 'pg_stat_kerberos' just for that.
>
> I agree that it's moving the goalposts for this patch and could be an
> independent patch, but I don't see it as any different, from a
> desirability and requirements perspective, than what we're doing for SSL
> connections.
>
> Thanks!
>
> Stephen
>

--
José Arthur Benetasso Villanova