Abhijit, * Abhijit Menon-Sen (a...@2ndquadrant.com) wrote: > At 2015-01-19 08:26:59 -0500, sfr...@snowman.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
signature.asc
Description: Digital signature