Audit 'truncate table' statements only

[ksiddi01]

I have a need to audit 'truncate table' statements without INSERT, UPDATE and DELETE.

Any help to accomplish this will be very much appreciated.

Thanks.

[dwsteele]

Truncate is included in the WRITE class: https://github.com/pgaudit/pgaudit#pgauditlog. Object logging is not possible with truncate.

[ksiddi01]

Is there a way to update the code so that WRITE is 'truncate table' only?

[dwsteele]

That would be an interface break, so no. It's possible that it could be added to a new class but that would not happen until PG11 as we don't add features to past versions. I am not in favor since truncate would then be in two classes.

Thoughts @sfrost, @jconway?

[ksiddi01]

Here is the issue I am trying to resolve. WRITE includes INSERT, UPDATE, DELETE, TRUNCATE, and COPY. We would like to audit TRUNCATE only. INSERT, UPDATE and DELETE will generate many audit logs and TRUNCATE is not very common. Any recommendation for auditing TRUNCATE only? Is it permissible/recommended to modify the code for local use? If it is, can I have WRITE for TRUNCATE only?

[simonat2ndQuadrant]

On 6 March 2018 at 15:23, David Steele ***@***.***> wrote: That would be an interface break, so no. It's possible that it could be added to a new class but that would not happen until PG11 as

None of the existing classes for pgaudit.log are the same as names of commands. ISTM that we could add many new classes that match the first keyword of a command. i.e. Allow INSERT, UPDATE, DELETE, TRUNCATE, COPY as individual classes. That would give a much finer grained ability to filter and would be easy enough to implement.

we don't add features to past versions.

Surely that is the benefit of an extension? Later versions of the extension can enhance the behavior for previous major releases of PostgreSQL. We can still have stability in pgaudit while allowing useful new functionality in older versions.

I am not in favor since truncate would then be in two classes.

I don't see why that would cause a problem as long as it is documented.

…

-- Simon Riggs http://www.2ndQuadrant.com/ <http://www.2ndquadrant.com/> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

[dwsteele]

ISTM that we could add many new classes that match the first keyword of a command. i.e. Allow INSERT, UPDATE, DELETE, TRUNCATE, COPY as individual classes. That would give a much finer grained ability to filter and would be easy enough to implement.

This seems like a reasonable idea.

we don't add features to past versions.

Surely that is the benefit of an extension?

Generally, yes, but the goal is to keep pgaudit as stable as possible so we have only been back-patching bug fixes, in the model of Postgres core.

This would be a non-trivial change. All commands are now assigned to a single class so a number of places in the code would need to be touched to allow a command in two classes and log it appropriately. I don't see this as an important enough feature to be worth the risk.

I am not in favor since truncate would then be in two classes.
I don't see why that would cause a problem as long as it is documented.

Fair enough.

I would be open to a patch to implement this functionality but don't have time to spend on it myself.