Re: Audit of logout - Mailing list pgsql-hackers

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Fujii Masao <masao.fujii@gmail.com> writes:
> > That's harmful for audit purpose. I think that we should make
> > log_disconnections PGC_SUSET rather than PGC_BACKEND in order
> > to forbid non-superusers from changing its setting. Attached
> > patch does this.

I tend to agree with this- those are things which regular users really
shouldn't be able to modify.  Policy enforcement can be done when there
isn't system enforcement, but you really don't want to fall back to
policy enforcement when you don't need to.

> TBH, this complaint seems entirely artificial.  If somebody needs to audit
> disconnections, and they see a connection record with no disconnection
> record but the session's no longer there, they certainly know that a
> disconnection occurred.  And if there wasn't a server crash to explain it,
> they go slap the wrist of whoever violated corporate policy by turning off
> log_disconnections.  Why do we need system-level enforcement of this?

Going through every log file, and writing something to address log file
changes, to hunt for those cases is no small amount of effort which
you're asking every administrator with an audit requirement to write
code to do to provide something which we make it appear as if we're
doing for them.  It's certainly a violation of POLA that users can
decide on their own that their disconnections don't get logged.

> Moreover, your proposed fix breaks the entire point of the PGC_BACKEND
> setting, which was to try to prevent disconnections from being logged
> or not logged when the corresponding connection was not logged or logged
> respectively.  If an auditor wants the system to enforce that there are
> matched pairs of those records, he's not exactly going to be satisfied by
> being told that only superusers can cause them to not match.

This is only accurate when a superuser exists in the system and even so,
superuser access can be much more easily reviewed as it's going to be a
lot less traffic and there may be other auditing mechanisms in place for
that access.

> I wonder whether we should just get rid of log_disconnections as a
> separate variable, instead logging disconnections when log_connections
> is set.
>
> Another answer is to make both variables PGC_SIGHUP, on the grounds
> that it doesn't make much sense for them not to be applied system-wide;
> except that I think there was some idea that logging might be enabled
> per-user or per-database using ALTER ROLE/DATABASE.

Both of these options look pretty reasonable to me as a way to improve
the current situation.  I'm not really sure that I see the use-case for
only logging connections/disconnections on a per-user or per-database
basis; in my experience it's not a lot of traffic to log it all and I
don't recall ever seeing anyone set those anything other than
system-wide.

I like the idea of flexibility in what's logged, I just don't see this
specific case as really needing it.
Thanks,
    Stephen