Re: pgaudit - an auditing extension for PostgreSQL

Abhijit,

* Abhijit Menon-Sen (ams(at)2ndQuadrant(dot)com) wrote:
> At 2015-01-19 08:26:59 -0500, sfrost(at)snowman(dot)net wrote:
> > I'm confused by this statement..
>
> Let me see if I've understood your clarification:

Thanks much for the example use-case and for working this through with
me. I actually think I've come up with a further specification which
might allow us to make this extremely flexible, but also simple for
those who want to keep it simple.

Consider this:

Everything is single-level to the roles mentioned in the GUC.

Is the logged in role a member of one of the GUC roles?
Yes -> Audit

Now to cover the "user X for table Y" case:

Did any of the GUC value roles grant SELECT rights for this table to the
current role?
Yes -> Audit SELECT on the table by the current role

Did any of the GUC value roles grant INSERT rights for this table to the
current role?
Yes -> Audit INSERT on the table by the current role

etc.

For the 'log all access to an object' case, under this scheme, I'm
afraid we'd need some special role to GRANT the access to. We wouldn't
want that to simply be 'public' since then it might actually be
granted access rights that we don't want to. We can't simply use the
same role because you need to grant that role whatever access 'with
grant option' in order for it to be able to re-grant the privilege.

With the special role, it becomes:

Does the special role have SELECT rights on the table?
Yes -> Audit SELECTs on the table

Does the special role have INSERT rights on the table?
Yes -> Audit INSERTs on the table

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

Not quite- I wasn't thinking you'd grant r0 to u0 but rather the other
way around- u0 is granted to r0. If you granted r0 to u0, then u0 would
have all of r0's rights which could be quite a bit larger than you want
u0 to have. It only works in the other direction.

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

This is pretty close- (a) and (b) are mostly correct, though I would
strongly discourage users from actually logging in as an audit role.
The one caveat with (b) is that 'if not, no audit' is not correct- all
cases are essentially OR'd together when it comes to auditing. Roles
can be audited even if they are not descendants of the roles mentioned
in .roles.

Review the opening of this email though and consider that we could look
at "what privileges has the audit role granted to the current role?" as
defining what is to be audited.

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

I hadn't been intending to address that with this scheme, but I think we
have that by looking for privilege grants where the audit role is the
grantee and the role-to-be-audited the grantor.

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

As Robert says, the short answer is 'no'- but it might make it easier to
get the 9.5 bits into 9.5.. :)

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

Ok, I had tried something similar and it didn't work for me and so I
ended up assuming it wasn't operating the way I was expecting.

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

Well, I was primairly digging for someone to say "yes, the object access
stuff will be reworked to be based on event triggers, thus removing the
need for the object access bits". Let's just say that I'm still hopeful
for that. That won't get us completely away from ProcessUtility, but at
least we would only have to deal with that..

Thanks!

Stephen