Table level auditing feature

CrateDB
1

Hi All,

I would like to audit DQL and DML operations on few tables.

Is there a feature to capture audit logs in Crate or any third party plugins available?

Thanks,
Raghu

2

Generally that information would be able in the sys.jobs_log table, and one could periodically run a INSERT INTO <persistence_table> SELECT * FROM sys.jobs_log. However there is also the option to persistently log queries in the application log (and set a filter for that)

see System information — CrateDB: Reference
and Cluster-wide settings — CrateDB: Reference

Does this help you?

3

Please note that you will only see the queries for the logged in user when checking sys.jobs_log.

Logging the queries or querying sys.jobs_log with the superuser crate will include queries by all users.

4

Ok, thanks. This is what I was looking for.

One more question:

Is there way to disable these logging? It may create performance issues by logging on all users and tables.

can I enable only for specific users or tables?

Thanks,
Raghu

5

Is there way to disable these logging?

No need, as the default for persisting queries to the logs is disabled (compare documentation for stats.jobs_log_persistent_filter)

can I enable only for specific users or tables?

Yes, as documented by setting a filter for stats.jobs_log_persistent_filter. See stats.jobs_log_filter for an example how to do this.

You can set a filter like username = <user> and / or stmt ILIKE '%from <tablename>%'

6

Ok, Thanks for information and support.