Re: pgaudit - an auditing extension for PostgreSQL

Abhijit,

* Abhijit Menon-Sen (ams(at)2ndQuadrant(dot)com) wrote:
> As before, the pgaudit code is at https://github.com/2ndQuadrant/pgaudit
> I did a quick round of testing to make sure things still work.

Thanks!

I had a very interesting discussion about auditing rules and the need to
limit what gets audited by user, table, column, policy, etc recently and
an idea came up (not my own) about how to support that granularity
without having to modify the existing PG catalogs or use GUCs or
reloptions, etc. It goes something like this-

Create an 'audit' role.

Every command run by roles which are granted to the 'audit' role are
audited.

Every 'select' against tables which the 'audit' role has 'select' rights
on are audited. Similairly for every insert, update, delete.

Every 'select' against columns of tables which the 'audit' role has
'select' rights on are audited- and only those columns are logged.
Similairly for every insert, update, delete.

etc, etc, throughout the various objects which can have permissions.

We don't currently have more granular permissions for roles (it's
"all-or-nothing" currently regarding role membership) and so if we want
to support things like "audit all DML for this role" we need multiple
roles, eg:

Create an 'audit_rw' role. DML commands run by roles granted to the
'audit_rw' role are audited. Similairly for 'audit_ro' or other
permutations.

The 'audit*' roles would need to be configured for pgAudit in some
fashion, but that's acceptable and is much more reasonable than having
an independent config file which has to keep track of the specific roles
or tables being audited.

The 'audit_ro' and 'audit_rw' roles lead to an interesting thought about
supporting something like which I want to mention here before I forget
about it, but probably should be a new thread if folks think it's
interesting:

GRANT role1 (SELECT) to role2;

Such that 'role2' would have role1's SELECT rights, but not role1's
INSERT, or UPDATE, or DELETE rights. There would be more complexity if
we want to support this for more than just normal relations, of course.

Thanks,

Stephen