18/09 - Query and audit logging in cassandra4.0

Query & Audit Logging in Cassandra 4.0

1.Query & Audit Logging in Cassandra 4.0 Vinay Chella Cloud Database Engineering




5.Vinay Chella Senior Software Engineer Cloud Database Engineering @ Netflix

6.Query & Audit Logging in Cassandra 4.0 Vinay Chella Cloud Database Engineering

7.Overview ● Background ○ Importance & Gotchas ● Design ● Full Query Logging ● Audit Logging ● Configurations ● Performance

8.What is logging means to a database ● What is an Audit Log: An audit log is the simplest, yet also one of the most effective forms of tracking temporal information. The goal of audit log is that any time something significant happens you write some record indicating what happened and when it happened. ● What is a Query Log: Query log in general refers to logging every query received from a client.

9.Importance Query Log Audit Log ● Live traffic capturing/ Traffic replay ● Security ● Debugging ○ Identifying unauthorized access ● Migrations* ● Compliance ○ SOX, PCI, GDPR etc., ● Making C* enterprise ready ○ Banks ○ Insurance domains etc.,

10.Gotchas ● Performance ○ Are we holding up main resource which are busy serving requests? ○ Are we stepping too much into mission critical code path? ○ Sync/ Async ○ What guarantees are we promising? ● Usability ○ Complex configurations ○ Logging unnecessary data ● Pluggability

11.Design goals. Performant. Accurate. Usable & Extensible.

12.Design. Where should it go in the first place? ● Why isn’t it the client’s job? ● Why not via dynamic tracing (e.g. cqltrace)?

13.Design. Once we’re in the database ● Why not log to database itself? ● Why is it in files? ○ Should it be restricted to one type of implementation? ● Why not log everything?


15.Query Logging. Logs every incoming request to your database.

16.Query Logging. ● Logs every incoming CQL request ● BinLog ● Highly performant ● Droppable jars for custom loggers ● Binary format output ● Live traffic capturing ● fqltool to make it readable ● Can be used to replay traffic

17.What does it log. ● Protocol version ● Query time ● Query ● Query values ● Type ○ single, batch etc.,

18.How to configure.

19. Ease of use with NodeTool $ ./nodetool enablefullquerylog --blocking: If the queue is full whether to block producers or drop samples. Default true. --max-log-size: How many bytes of log data to store before dropping segments. Default 16 gigabytes. --max-queue-weight: Maximum number of bytes of query data to queue to disk before blocking or dropping samples. Default 256 megabytes. --path: Path to store the full query log at. -pp, --print-port: Operate in 4.0 mode with hosts disambiguated by port number --roll-cycle: How often to roll the log file (MINUTELY, HOURLY, DAILY). Default HOURLY. $ ./nodetool resetfullquerylog $ ./nodetool disablefullquerylog

20. Sample logs. Type: single Protocol version: 4 Query time: 1536871241290 Query: USE "dev1" Values: Type: single Protocol version: 4 Query time: 1536871301568 Query: INSERT INTO emp(key , column1 , value ) VALUES ( 't1',1,'tvalue'); Values: Type: single Protocol version: 4 Query time: 1536871306065 Query: SELECT * from emp ; Values:

21.Audit Logging. Keeps an audit trail of your database.

22.Audit Logging. ● Audits everything ● BinLog ● Yaml based configuration ● Default implementations ● Highly performant ○ BinAuditLogger ● Pluggable ○ FileAuditLogger ● Droppable jars for custom loggers

23.What does it log. ● User ● Category: ● Host ○ DDL, DML, etc., ● Keyspace ● Source ip address ● Scope ● Source port ○ Table name, Function name etc., ● Timestamp ● Operation ● Type ○ Select * from tbl1 limit 2; ○ SELECT, INSERT, etc.,

24.How to configure.

25. Ease of use with NodeTool $ ./nodetool enableauditlog StorageService.java:5420 - AuditLog is enabled with logger: [BinAuditLogger], included_keyspaces: [movies, thumbs_ratings], excluded_keyspaces: [star_ratings], included_categories: [DDL, DML, DCL], excluded_categories: [QUERY],included_users: [prod_user1, prod_user2], excluded_users: [admin_user, ops_user] $ ./nodetool disableauditlog

26.Live Demo.

27. Sample logs. user:anonymous|host:localhost/X.X.X.X|source:/X.X.X.X|port:60878|timestamp:1521158923615|type:USE_KS |category:DDL|ks:dev1|operation:USE "dev1" user:anonymous|host:null|source:/X.X.X.X|port:57229|timestamp:1524127488487|type:USE_KEYSPACE|catego ry:OTHER|ks:dev1|operation:use dev1 ; user:anonymous|host:null|source:/X.X.X.X|port:57229|timestamp:1524127494445|type:SELECT|category:QUE RY|ks:dev1|scope:emp|operation:SELECT * from emp limit 2; user:anonymous|host:null|source:/X.X.X.X|port:57229|timestamp:1524127536425|type:CREATE_TABLE|catego ry:DDL|ks:dev1|scope:emp2|operation:create TABLE emp2 (id int, c1 text, c2 text, PRIMARY KEY (id )); user:anonymous|host:null|source:/X.X.X.X|port:57229|timestamp:1528147672512|type:DROP_TABLE|category :DDL|ks:dev1|scope:emp2|operation:DROP TABLE emp2;

28.Performance. Well, it does not kill your Cassandra.