Re: pgaudit - an auditing extension for PostgreSQL

At 2014-05-04 08:52:42 -0400, sfrost@snowman.net wrote:
>
> This also addresses things like anonymous DO blocks and functions
> then..?  With enough information to be useful for forensics?

For DML, it addresses anything that goes through InitPlan (which, via
ExecCheckRTPerms, calls the ExecutorCheckPerms_hook). Yes, that does
include table accesses within DO blocks:

LOG:  [AUDIT],2014-05-04 19:15:06.130771+05:30,ams,ams,ams,WRITE,DELETE,TABLE,public.a,do $$begin delete from a;
end;$$;

…and functions:

LOG:  [AUDIT],2014-05-04 19:16:20.336499+05:30,ams,ams,ams,DEFINITION,CREATE FUNCTION,,,create function x(int) returns
textas $$select b from a where a=$1;$$ language sql;
 
LOG:  [AUDIT],2014-05-04 19:16:48.112725+05:30,ams,ams,ams,FUNCTION,EXECUTE,FUNCTION,public.x,select * from x(3);
LOG:  [AUDIT],2014-05-04 19:16:48.112922+05:30,ams,ams,ams,READ,SELECT,TABLE,public.a,select * from x(3);

> We'd need to also consider permissions and how these are managed.

Yes. For pgaudit, we kept it simple and made pgaudit.log
superuser-only[1].

I haven't given much thought to this area, because I didn't know what
mechanism to use to set per-object auditing parameters. For a contrib
module, extensible reloptions would have been convenient. But in-core
auditing support could use a proper reloption, I suppose. It's a pity
that extensions can't add reloptions.

Personally, I don't think it's important to support GRANT-ing the
ability to set audit parameters. I think it would be reasonable even to
say that only the superuser could do it (but I can imagine people being
unhappy if the owner couldn't[2]).

Definitely lots to discuss.

-- Abhijit

1. I wish it were possible to prevent even the superuser from disabling  audit logging once it's enabled, so that if
someonegained superuser  access without authorisation, their actions would still be logged.  But I don't think there's
anyway to do this.
 

2. On the other hand, I can also imagine a superuser being justifiably  annoyed if she were to carefully configure
auditing,and random users  then enabled audit-everything for their newly-created tables and  filled the audit table
withjunk.