Re: pgaudit - an auditing extension for PostgreSQL - Mailing list pgsql-hackers

At 2015-01-19 08:26:59 -0500, sfrost@snowman.net wrote:
>
> I'm confused by this statement..

Let me see if I've understood your clarification:

Suppose you have pgaudit.roles set to 'r0, r1', and that you have
granted r0 to u0.

Now, if you're connected to the database as r0 or r1, then everything
you do is logged. But if you're connected as u0, then only those things
are logged that can be derived (in a manner discussed later) from the
permissions explicitly granted to r0 (but not u0)?

So when I'm trying to decide what to audit, I have to:
   (a) check if the current user is mentioned in .roles; if so, audit.
   (b) check if the current user is a descendant of one of the roles       mentioned in .roles; if not, no audit.
   (c) check for permissions granted to the "root" role and see if that       tells us to audit.

Is that right? If so, it would work fine. I don't look forward to trying
to explain it to people, but yes, it would work (for anything you could
grant permissions for).

> You can't say that you want r1 to have X actions logged, with r2
> having Y actions logged.

Right. But how do you do that for non-GRANT-able actions in your scheme?
For example, what if I want to see all the tables created and dropped by
a particular user?

> Have you considered splitting pgaudit.c into multiple files, perhaps
> along the pre/post deparse lines?

If such a split were done properly, then could the backwards-compatible
version be more acceptable for inclusion in contrib in 9.5? If so, I'll
look into it.

> One thought might be to provide the intersection between LOGSTMT and
> ObjectTypes.

Hm. OK.

> The key part above is "any".  We're using the grant system as a proxy
> for saying "I want to audit column X".  That's different from "I want
> to audit commands which would be allowed if I *only* had access to
> column X".  If I want to audit access to column X, then:
> 
> select A, B, X from mytable;
> 
> Should be audited, even if the audit role doesn't have access to
> columns A or B.

Yes, that's what the code already does (modulo handling of the audit
role's oid, which I tweaked to match the behaviour described earlier
in this mail). I did the following:
   create table x(a int,b int,c int);   insert into x(a,b,c) values (1,2,3);
   create user y;   grant select on x to y;
   create role x;   grant select (a) on table x to x;   grant x to y;

Then, as user y, I did «select a,b,c from x» and «select b,c from x».
Only the former was logged:

LOG:  AUDIT,2015-01-20 11:19:02.156441+05:30,postgres,y,y,READ,SELECT,TABLE,public.x,select a,b,c from x;

> Yeah- but are we always going to have to deal with a partial event
> trigger set?

As a practical matter, yes. I don't know if there are current plans to
extend event trigger support to the commands and object types that are
yet unsupported.

-- Abhijit