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

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.

>> 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.

>> 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.

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.

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

> I'm certainly all about adding new capabilities to pg_audit, but, as
> discussed elsewhere, I believe we really want many of those same
> capabilities in core (eg: being able to send logs to different places;
> my thinking is a rabbitMQ type of store rather than email, but perhaps
> we could support both..) and that's what I'm hoping to work towards in
> the near future.

Sure, adding features like sending logs to different places in core is
totally reasonable, independently of pg_audit. (Or not, but in any case,
it's orthogonal :-) )

- Heikki