search

2ndQuadrant / audit-trigger

Simple, easily customised trigger-based auditing for PostgreSQL (Postgres). See also pgaudit.
Other
657 stars 241 forks source link

Is it possible to log variables used in "where" clause? #37

Closed jhm713 closed 5 years ago

jhm713 commented 5 years ago

In audit.logged_actions, I find that I have client_query values like:

update some_table set some_column = $1 where where_column = $2

row_data and changed_fields both show me what's in $1, but I don't have $2 saved, which is making it nearly impossible to make use of my audit data.

Not sure that this is a bug, but I can't figure out how to get around it. Any advice would be most appreciated.

ringerc commented 5 years ago

Bind parameters (the values of $1, $2 etc) are not recorded.

I don't think PostgreSQL offers any SQL-accessible API to get the bind parameters of the current_query(). Not to mention the limitation that it only shows the top-level query and there may be layers of nesting in triggers, SQL or plpgsql procedures, SPI calls from functions in other languages, etc.

For diagnostic and audit purposes a way to capture bind parameters at the SQL level would certainly be handy. But it'd require a C extension, since it'd need to install a hook at parameter bind time in the executor and capture the parameters there.

I'd be happy to accept a patch that added that, but definitely will not have any time to write one. If it's sufficiently important to you you can contact 2ndQuadrant to get one of the team here to develop such an extension for you on a commercial basis. I'm afraid I won't have time to advise you on the details of how to write it if you want to do it yourself, beyond directing you to parameter bind hooks. You'd probably want to start by reading the pg_stat_statements extension to examine its hooks use etc - but you won't need all its shared memory handling etc since you'd only be capturing the statement within the current session.

Also check out PgAudit.

I'm closing this issue as it's not a bug and it's not really a viable feature request given the lack of required PostgreSQL core functionality for it. But you can still comment.

jhm713 commented 5 years ago

Thank you for the thorough and prompt response. The clear answer and follow-up suggestions are greatly appreciated.