Add New Table for Audit Logs

[kjsbot]

Summary

Add support for schema change for new table to store auditing logs.

Motivation

At LinkedIn, we need logging support for admin operations regarding backends. The lack of traceability and context can make it difficult when debugging issues. To satisfy this requirement, we need a schema change to that'll allow us to store audit logs for ease of querying. Currently, when an admin performs an operation, there is no audit trail in the gateway database. This makes it hard to answer questions such as:

• Who changed a backend or routing group?

• What exactly changed (before vs after) and what's the status of the change?

• When did the change occur?

• From where (IP) and why (comment) was the change made?

Having an auditing in the gateway would make debugging easier and support internal compliance requirements.

Proposed Solution

Change Trino-Gateway mySQL schema to include a new table, gw_audit_events, that'll write each Gateway operation as a new row with user ID, IP, timestamp, operation, context, and comment.

Rough idea of the schema

CREATE TABLE gw_audit_events (
    user_id              VARCHAR(256) NOT NULL,
    timestamp      TIMESTAMP(3) NOT NULL, -- timestamp in UTC time
    ip_address      VARCHAR(45) NULL,
    operation       VARCHAR(128) NOT NULL, -- e.x: CREATE, UPDATE
    target     VARCHAR(256) NULL, -- which backend was the change made to
    status     VARCHAR(256) NOT NULL, -- success/failure status of the operation
    context         TEXT NULL, -- from which method was the request made ex: UI, CLI
    comment         TEXT NULL
);

Furthermore, we should also log the information using the Logger class:

{"message":"GW_AUDIT_LOG: user=admin, ipAddress=0:0:0:0:0:0:0:1, backend=trino-1, action=DEACTIVATE, context=trino-gw-ui"}

With these changes, the logs will be able to be queried and stored long term as well as be used for short term monitoring.

I am willing to participate in design reviews to help move this feature forward. Please let me know how best to proceed with further review and/or collaboration!

[mosabua]

In general I am supportive of this work, but details probably have to be hashed out more. Did you want do do this in here or in a PR?

Also note it needs to be implemented for all three databases..

[kjsbot]

Yes that makes sense. What details do you need clarity on?

[xkrogen]

@kjsbot The biggest missing pieces I see are how the audit information will be collected (are comments required? how do you collect a comment from the toggle button on the UI?), and how audit entries will be cleaned up (presumably we do not want to keep them forever). Also I think the DB schema is not quite right (some of the types look off e.g. VARCHAR is not the right way to store an IP) but IMO that's better suited to discuss in a PR.

[xkrogen]

Also let's discuss how this interacts with @Peiyingy 's work: #794

[prakhar10]

We have this implemented internally as well, I can raise a PR if we want to go ahead with it.

We have below table structure -

resource_name : Cluster that was changed
user_name - name of the user who made the change
action - ADD, UPDATE, DELETE
changed_field - What field was changed, for example - status, url, routingGroup etc
old_value - what was the old value of the changed field
new_value - what was the new value of the changed field
change_time - timestamp when the change was done

audit_logs (
                            id SERIAL PRIMARY KEY,
                            resource_name varchar(256),
                            user_name     varchar(255),
                            action        varchar(256),
                            changed_field varchar(256),
                            old_value     varchar(256),
                            new_value     varchar(256),
                            change_time   timestamp default CURRENT_TIMESTAMP
)

Open for any suggestions or feedback.

[Peiyingy]

Also let's discuss how this interacts with @Peiyingy 's work: #794

Given this is only auditing operations on gateway_backend, I don't think we need to worry about the write buffer on query_history. Also, given we don't allow admin operations when DB is not available, there's no need to create a write buffer for gateway_backend and audit that.

[kjsbot]

@xkrogen

Whenever an Admin operation on a backend is made to the backend (ie: CREATE, ACTIVATE) the metadata about the operation will be collected and it will write a row into the table. Currently we are planning to have a confirmation popup for the toggle button that'll allow the user to write a comment. For audit entries, we will have data retention policies to clear out the logs after a period of time.

[kjsbot]

@prakhar10

We have a slightly different schema (for example, we collect IP and comments from the user) but we can discuss that in a PR if you'd like. Also I was wondering, how are you currently collecting the auditing information?