YugabyteDB audit log support

[sanjimoh]

Support audit logging (CONNECT, DCL events for example) with YugabyteDB.

Status	Feature	Comments
✅	Design doc for audit logging	Audit Logging Official Doc
✅	YCQL audit logging #5887	Configure (include or exclude) what to audit along three dimensions: (statement or event) categories, users, and keyspaces.
✅	YSQL audit logging #6199	Enable pgAudit extension on each node for audit logging.
✅	Pre-package pgAudit by default	Each node of the YugabyteDB cluster would need the extension installed, simplify this by pre-packaging the extension with the code by default.

Goals

The primary aim of this feature is to monitor and audit database activity in order to identify and report on fraudulent, illegal or other undesirable behavior.

Note that the audit log and database activity data will be stored outside the database (in log files), in order to ensure that the log is immutable.

Scope of what will be monitored

Below are the guidelines for what needs to be monitored:

• Monitoring privileged users: database users that have elevated privileges to access sensitive information within the database.
• Monitoring sensitive objects: Access to database objects (tables, views, etc.) including who accessed these objects, at what time, from what IP address, and the specific SQL statement that was used.
• Misc: Monitor all accesses into the DB including grant/revoke of Access, SQL errors, and failed logins.

To achieve the above, the monitored activities include SQL events such as Select, Insert, Update, Delete, Drop, Create, and Alter. The following data should be logged:

• Date/Timestamp
• User ID (OS User and DB User Name)
• Client and Server IP Address
• Server Type
• Source Program
• Activity/Error (e.g., SQL Statement)

Monitoring across the different APIs

YSQL

It should be possible to achieve most of the above by using something like pgAudit.

YCQL

For YCQL, we should follow the format specified at: Audit Logging Cassandra. We could start with connections events, (LOGIN, ALTER ROLE, CREATE ROLE, GRANT, REVOKE ROLE, DROP ROLE, GRANT PERMISSION, REVOKE PERMISSION.

Additional features/integrations

In order to consume these, we would need a number of integrations.

⬜️ Splunk forwarder for audit logs #4422
⬜️ Integration with thirdparty tools for policy violation reporting

[kmuthukk]

hi @sanjimoh - we currently do not have audit logging support, but it is on the roadmap.

Are you interested primarily in tracking all DDL operations (such as CREATE , ALTER, DROP ... ) or also write operations (such as tracking every modificiation also - UPDATE/INSERT)

[sanjimoh]

From a security point of view, CONNECT, DCL & DDL Queries would be my priorities.

[CH-JosephBironas]

+1 to this, but also for ycql (assuming it doesn't already exist).

[ajcaldera1]

For YCQL, we should follow the format specified at: Audit Logging Cassandra. We could start with connections events, (LOGIN, ALTER ROLE, CREATE ROLE, GRANT, REVOKE ROLE, DROP ROLE, GRANT PERMISSION, REVOKE PERMISSION.

[schoudhury]

Status update - design and planning for this feature to start in early July 2020.