Re: [COMMITTERS] pgsql: Add pg_audit, an auditing extension

Heikki,

* Heikki Linnakangas (hlinnaka(at)iki(dot)fi) wrote:
> On 05/28/2015 11:14 AM, Stephen Frost wrote:
> >* Heikki Linnakangas (hlinnaka(at)iki(dot)fi) wrote:
> >>1. it's not flexible enough. How do you specify that all READs on
> >>super_secret table must be logged, but on less_secret table, it's
> >>enough to log only WRITEs?
> >
> >This is actually what that pg_audit.role setting was all about. To do
> >the above, you would do:
> >
> >CREATE ROLE pgaudit;
> >SET pg_audit.role = pgaudit;
> >GRANT SELECT ON TABLE super_secret TO pgaudit;
> >GRANT INSERT, UPDATE, DELETE ON TABLE less_secret TO pgaudit;
> >
> >With this, all commands executed which require SELECT rights on the
> >table super_secret are logged, and all commands execute which require
> >INSERT, UPDATE, or DELETE on the less_secret table are logged.
>
> Ah, I see. Wow, that's really Rube Goldbergian.

It's certainly not ideal. It was discussed back in January, iirc.

> >>2. GUCs can be changed easily on-the-fly, and configured flexibly.
> >>But that's not really what you want for auditing. You want to have a
> >>clear specification in one place. You'd want it to be more like
> >>pg_hba.conf than postgresql.conf. Or more like Mandatory Access
> >>Control, rather than Discretionary Access Control.
> >
> >I certainly appreciate the MAC vs. DAC discussion here, but I don't
> >believe our AUDIT capability should explicitly require restarts of PG to
> >be adjusted.
>
> Sure, I didn't mean we should require a restart. Requiring SIGHUP
> seems reasonable though.

I wouldn't have any issue with that.

> >>A separate config file in $PGDATA would be a better way to configure
> >>this. You would then have all the configuration in one place, and
> >>that file could have a more flexible format, with per-schema rules
> >>etc. (Wouldn't have to implement all that in the first version, but
> >>that's the direction this should go to)
> >
> >The difficulty with a separate config file is that we don't have any way
> >of properly attaching information to the existing tables in the
> >database- table renames, dropped columns, etc, all become an issue then.
>
> True. I wouldn't be too worried about that though. If you rename a
> table, that hopefully gets flagged in the audit log and someone will
> ask "why did you rename that table?". If you're paranoid enough, you
> could have a catch-all rule that audits everything that's not
> explicitly listed, so a renamed table always gets audited.

The general 'catch-all' approach was how we approached pg_audit in
general, so, yes, that's the kind of auditing we expect people to want
(or, at least, we would need to support it). You're right, in some
environments that may be workable, but then it also has to be entirely
managed outside of the database, which would make it extremely difficult
to use in many environments, if not impossible..

> Of course, you could still support a labeling system, similar to the
> pg_audit.role setting and GRANTs. For example, you could tag tables
> with a label like "reads_need_auditing", and then in the config
> file, you could specify that all READs on tables with that label
> need to be audited. I think security labels would be a better system
> to abuse for that than GRANT. You'd want to just label the objects,
> and specify the READ/WRITE etc. attributes in the config file.

Using SECURITY LABELs is certainly an interesting idea. GRANT was
chosen because, with it, we also get information regarding the action
that the user wants to audit (select/insert/update/delete, etc), without
having to build all of that independently with some custom structure in
the SECURITY LABEL system.

> Who do you assume you can trust? Is it OK if the table owner can
> disable/enable auditing for that table?

In an ideal world, you would segregate the rights which the table owner
has from both the permission system and the audit system. This has come
up a number of times already and is, really, an independent piece of
work. Having the permissions and auditing controlled by the same group
is better than having all three pieces controlled by the ownership role,
but having three distinct groups would be preferred.

Thanks!

Stephen