Re: Audit of logout

On Fri, Jun 13, 2014 at 11:29 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Fujii Masao <masao(dot)fujii(at)gmail(dot)com> writes:
>> On Thu, Jun 12, 2014 at 8:51 PM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>>> Some users enable log_disconnections in postgresql.conf to audit all logouts.
>>> But since log_disconnections is defined with PGC_BACKEND, it can be changed
>>> at connection start. This means that any client (even nonsuperuser) can freely
>>> disable log_disconnections not to log his or her logout even when the
>>> system admin
>>> enables it in postgresql.conf. Isn't this problematic for audit?
>
>> 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.
>
> 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?
>
> 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.
>
> 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.

But, IIUC, since PGC_BACKEND parameters cannot be set per-role and per-database,
such idea seems impractical. No? ISTM that there is no big
user-visible problematic
change of the behavior even if we redefine log_connections and
log_disconnections
as PGC_SIGHUP.

Regards,

--
Fujii Masao