Re: Addition of authenticated ID to pg_stat_activity

Greetings,

* Michael Paquier (michael(at)paquier(dot)xyz) wrote:
> On Mon, Apr 26, 2021 at 03:21:46PM -0400, Stephen Frost wrote:
> > * Andres Freund (andres(at)anarazel(dot)de) wrote:
> >> I'm getting a bit worried about the incremental increase in
> >> pg_stat_activity width - it's probably by far the view that's most
> >> viewed interactively. I think we need to be careful not to add too niche
> >> things to it. This is especially true for columns that may be wider.
> >>
> >> It'd be bad for discoverability, but perhaps something like this, that's
> >> not that likely to be used interactively, would be better done as a
> >> separate function that would need to be used explicitly?
> >
> > I mean.. we already have separate functions and views for this, though
> > they're auth-method-specific currently, but also provide more details,
> > since it isn't actually a "one size fits all" kind of thing like this
> > entire approach is imagining it to be.
>
> Referring to pg_stat_ssl and pg_stat_gssapi here, right? Yes, that
> would be very limited as this leads to no visibility for LDAP, all
> password-based authentications and more.

Yes, of course. The point being made was that we could do the same for
the other auth methods rather than adding something to pg_stat_activity.

> I am wondering if we should take this as an occasion to move some data
> out of pg_stat_activity into a separate biew, dedicated to the data
> related to the connection that remains set to the same value for the
> duration of a backend's life, as of the following set:
> - the backend PID
> - client_addr
> - client_hostname
> - client_port
> - authenticated ID
> - application_name? (well, this one could change on reload, so I am
> lying).

application_name certainly changes, as pointed out elsewhere.

> It would be tempting to move the database name and the username but
> these are popular fields with monitoring. Maybe we could name that
> pg_stat_connection_status, pg_stat_auth_status or just
> pg_stat_connection?

I don't know that there's really any of the existing fields that
aren't "popular fields with monitoring".. The issue that Andres brought
up wasn't about monitoring though- it was about users looking
interactively. Monitoring systems can adjust their queries for the new
major version to do whatever joins, et al, they need and that's a
once-per-major-version to do. On the other hand, people doing:

table pg_stat_activity;

Would like to get the info they really want out of that and not anything
else. If we're going to adjust the fields returned from that then
that's really the lens we should use.

So, what fields are people really looking at when querying
pg_stat_activity interactively? User, database, pid, last query,
transaction start, query start, state, wait event info, maybe backend
xmin/xid? I doubt most people looking at pg_stat_activity interactively
actually care about the non-user backends (autovacuum, et al).

Thanks,

Stephen