Re: pgaudit - an auditing extension for PostgreSQL

Simon,

* Simon Riggs (simon(at)2ndQuadrant(dot)com) wrote:
> On 1 July 2014 18:32, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > Having functions to control the auditing would work, but it's not
> > exactly the ideal approach, imv, and
>
> What aspect is less than ideal?

I certainly don't like passing table names to functions, just to point
out one item. It's necessary in cases where we want to programatically
get access to information (eg: pg_total_relation_size(),
has_*_privilege, etc). I do not see any functions today which take a
'name' type argument (or, at a quick look, any of the other ones) and
update a catalog object- and I don't think that's a mistake. Reading
further, I take it that you are advocating pgaudit have functions along
these lines:

-- Enable auditing for a table
pgaudit_add_audit_to_table(name);

-- Audit only a given role's actions on the table
pgaudit_add_audit_to_table(name,role);

-- Audit specific actions of a role on a table
pgaudit_add_audit_to_table(name,role,aclitem);

whereby this information would end up stored somehow through reloptions.
I'd be happy to be incorrect here as that's not a design that I like,
but if that isn't correct, getting feedback as to what the future plan
*is* would be great.

> > the only reason it's being
> > discussed here is because it might be a way to allow an extension to
> > provide the auditing- not because it's actually a benefit to anyone.
>
> That is a false statement, as well as being a personal one. It's sad
> to hear personal comments in this.

Simon, it wasn't intended as a personal attack. My frustration with
this thread lead me to overstate it but there has been very little
discussion about why functions are the *right* solution for controlling
auditing. The arguments for it today have been, to try and summarize:

- Keeps the grammar small

While this is a general benefit, I don't view it as a reason for using
functions instead of new grammar when it comes to modifying objects in
the system.

- Not everyone will want auditing

I fully support this- not everyone wants column level privileges
either though, or JSON in their database. I also don't feel this is
accurate- we get a lot of complaints about the lack of flexibility in
our logging system and my hope is that an auditing system will help to
address that. The two are not identical but I expect them to share a
lot of the infrastructure.

- Permissions can be set up on functions

I admit that I had not considered this, but even so- that's very
coarse- you can either execute the function or not. We might be able
to start with this but I'd expect us to need more control very
quickly- auditor A should be able to control the auditing for tables
in the 'low security' schema, while auditor B should be able to
control the auditing for tables in both the 'low security' and 'high
security' schemas. In the end, I don't see this as being a workable
solution- and then what? To add that control, we add more functions
to allow the admin to control who can define auditing for what, and
then have to implement the permissions handling inside of the audit
configuration functions, and then relax the permissions on those
functions? Perhaps you see a better approach and I'm just missing it,
but if so, it'd be great if you could outline that.

> It seems strange to be advocating new grammar at a time when we are
> actively reducing the size of it (see recent commits and long running
> hackers discussions). Functions don't carry the same overhead, in fact
> they cost nothing if you're not using them, which is the most
> important point.

I do not agree that new grammar for auditing should be off the table
because it adds to the grammar. I agree that we don't want to add new
grammar without good justification for it and further agree that we
should be looking at opportunities to keep the grammar small. I view
auditing as similar to the GRANT system though and while we could reduce
our grammar by moving everything done via GRANT into functions, I don't
feel that's a good solution.

> The right to execute functions can be delegated easily to any group
> that wants access. There is no special benefit to SQL grammar on that
> point.

Addressed above.

> > However, if we have such functions in a contrib extension, I worry what
> > the pg_upgrade path is from that extension to an in-core solution.
>
> Functions are already used heavily for many aspects of PostgreSQL.
> http://www.postgresql.org/docs/devel/static/functions-admin.html
>
> Presumably you don't advocate an "in core" solution to replace
> pg_cancel_backend() etc?

I don't view cancelling a backend as being part of the grammar's
responsibility. One of the grammar's roles is to define the objects
which exist in the system and to further support modification of those
objects. We don't have a function called 'create_table', for example.

> My proposed route for making this "in-core" is simply to accept that
> the extension is "in core". Auditing should, in my view, always be
> optional, since not everyone needs it.

Including auditing in the grammar does not require anyone to use it and
therefore it is optional. Column-level privileges are a good example of
a similar optional capability which exists in the grammar- there's no
requirement that it be part of the grammar, there's nothing preventing a
user from using views to emulate it, or simply accepting that table
level privileges are the only option and designing around that.

> Cryptographic functions aren't
> in-core either and I'm guessing various security conscious
> organizations will use them and be happy. How does pgaudit differ from
> pgcrypto?

pgcrypto does not include references or configuration associated with
the objects in the system. Are you suggesting that we will enhance
pgcrypto to support encrypting columns- such that there is a custom
attoption entry for a column which says "this column is encrypted with
pgcrypto" and further that selects against that table would have the
encrypted data from that column flow through a hook that decrypts it
using a key stored in a GUC or previously set up during the session?

While an interesting tangent, I'd be curious to see what that would buy
us over recommending views which happen to include pgcrypto functions
and further I'd like to take time to review what other RDBMS's do in
this area. In the end, I feel like I'd advocate extending the catalog
and providing grammar for setting up column or table encryption rather
than having the encryption configuration defined through functions.

> Given the tone of this discussion, I don't see it going anywhere
> further anytime soon - that is good since there is no big rush.

I'd like it to move further as I'd really like to see auditing happen
for PG sooner rather than later. :(

> pgaudit is a sincere attempt to add audit functionality to Postgres.

I'm very appreciative of it and expect to be using it in the future for
existing PG installations as it's far better than anything else
currently available.

> If you or anyone else wants to make a similarly sincere attempt to add
> audit functionality to Postgres, lets see the design and its
> connection to requirements.

I've made a few attempts to outline the requirements as I see them and
have also pointed to specific requirements documents. I'm a bit
occupied with RLS currently to flesh out a full design for auditing, but
it's definitely on my list of things to look at this fall. That said, I
do feel it's quite reasonable to ask about the future plans and designs
of a new extension which is being suggested for inclusion in PG.

Thanks,

Stephen