Re: pgaudit - an auditing extension for PostgreSQL

At 2014-05-04 08:52:42 -0400, sfrost(at)snowman(dot)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 text as $$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 someone gained superuser
access without authorisation, their actions would still be logged.
But I don't think there's any way 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 with junk.