Re: pgaudit - an auditing extension for PostgreSQL

At 2014-12-22 08:05:57 -0500, robertmhaas(at)gmail(dot)com wrote:
>
> On Tue, Dec 16, 2014 at 1:28 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > … "ok, does the audit role have any permissions here?" and, if the
> > result is yes, then the command is audited. …
>
> This is a little weird because you're effectively granting an
> anti-permission.

Yes, it's a very clever solution, but also pretty weird. I think that's
why I didn't understand it. I've been looking into writing the code, but
I haven't quite gotten over the weirdness yet.

> I'm not sure whether that ought to be regarded as a serious problem,
> but it's a little surprising.

I'm not sure either.

Stephen likes the idea, obviously; Simon also said he liked it, but I
now wonder if he may have liked the part I implemented (which allows a
hot standby to have a different auditing configuration than the primary)
but not fully realised the remainder of the proposal.

Before I go much further, how do others feel about it?

To summarise for people who haven't followed the thread in detail, the
idea is that you would do:

grant select on foo to audit;

…and the server would audit-log any "select … from foo …" queries (by
any user). One immediate consequence is that only things you could grant
permissions for could be audited (by this mechanism), but I guess that's
a problem only in the short term. Another consequence is that you can't
audit selects on foo only by role x and selects on bar only by role y.

> Also, what makes the "audit" role magical?

I think it's because it exists only to receive these "negative" grants,
there's no other magic involved. Stephen also said «Note that this role,
from core PG's perspective, wouldn't be special at all».

-- Abhijit