Re: Audit of logout - Mailing list pgsql-hackers

On Mon, Jun 16, 2014 at 4:14 PM, Stephen Frost <sfrost@snowman.net> wrote:
> * 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.

+1.

>> 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.

+1.

>> 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.

I don't really like either of these ideas much, and I don't really see
the problem with Fujii Masao's original proposal.  It's true that some
installations may find it valuable to preserve the property that a
disconnect is logged whenever they connect is logged, but if that's
really what's at issue, then presumably the superuser is not going to
be flipping these settings on and off on the fly *anyway*.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company