Re: pgaudit - an auditing extension for PostgreSQL - Mailing list pgsql-hackers

Simon,

* Simon Riggs (simon@2ndQuadrant.com) wrote:
> On 1 July 2014 18:32, Stephen Frost <sfrost@snowman.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
tomodifying 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'tfeel this is accurate- we get a lot of complaints about the lack of flexibility in our logging system and my hope
isthat 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.
Wemight be able to start with this but I'd expect us to need more control very quickly- auditor A should be able to
controlthe auditing for tables in the 'low security' schema, while auditor B should be able to control the auditing for
tablesin 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
auditingfor what, and then have to implement the permissions handling inside of the audit configuration functions, and
thenrelax the permissions on those functions?  Perhaps you see a better approach and I'm just missing it, but if so,
it'dbe 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