Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

YugabyteDB audit log support #1331

Closed
sanjimoh opened this issue May 8, 2019 · 8 comments
Closed

YugabyteDB audit log support #1331

sanjimoh opened this issue May 8, 2019 · 8 comments
Assignees
@frozenspider @ramsrivatsa
Labels
area/ysql Yugabyte SQL (YSQL) community/request Issues created by external users kind/new-feature This is a request for a completely new feature roadmap-tracking-issue This issue tracks a major roadmap item, and usually appears in the roadmap list.
Milestone
v2.3

Comments

@sanjimoh
Copy link

sanjimoh commented May 8, 2019 •
edited by ameyb
Loading

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

Analytics
@kmuthukk kmuthukk added the area/ysql Yugabyte SQL (YSQL) label May 13, 2019
@kmuthukk
Copy link
Collaborator

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
Copy link
Author

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

@yugabyte-ci yugabyte-ci added the community/request Issues created by external users label Jul 17, 2019
@ttyusupov ttyusupov added the kind/new-feature This is a request for a completely new feature label Jul 18, 2019
@CH-JosephBironas
Copy link

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

@ajcaldera1
Copy link
Contributor

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.

@ajcaldera1 ajcaldera1 mentioned this issue May 8, 2020
Open
@schoudhury
Copy link
Contributor

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

@rkarthik007 rkarthik007 added this to the v2.3 milestone Jun 5, 2020
@rkarthik007 rkarthik007 changed the title Audit logging support YugabyteDB activity monitoring and audit log support Jul 8, 2020
@rkarthik007 rkarthik007 added the roadmap-tracking-issue This issue tracks a major roadmap item, and usually appears in the roadmap list. label Jul 24, 2020
@ndeodhar ndeodhar assigned frozenspider and unassigned ndeodhar Aug 5, 2020
frozenspider added a commit that referenced this issue Sep 25, 2020
@frozenspider
#1331: [YCQL] Operations audit logging
fdec43a 
Summary:
Audit logging feature for YCQL is similar to Apache Cassandra 4.0-beta2, with some inspiration taken from pgAudit.
* Audit is controlled and configured through runtime gflags, which can changed using `yb-ts-cli` without restarting a `yb-tserver` process.
* Audit log in written into a general log, with `AUDIT: ` prefix.
* Output format follows that of Cassandra binary log as seen through `auditlogviewer`.
* We also follow Cassandra's audit approach for event types, categories and scopes.
* We rely on `glog` for logging, which results in logging being done pseudo-asynchronously (i.e. buffered and sometimes flushed).
* Operations that fail at parse or analyze time will be logged once.
* Operations that fail at execution time will be logged twice - before attempting to execute it, and before reporting an error. This differs from both Cassandra and pgAudit.
* For `CREATE/ALTER ROLE`, password will be replaced with `<REDACTED>` (only if the statement was written without grammatical errors!).

# Usage

Audit is disabled by default and is configured using `yb-tserver` gflags. The most important one is `--ycql_enable_audit_log`, enabling which would by default audit everything except system table queries. Without it, other audit config flags would have no effect.

`--ycql_audit_log_level` controls a log level at which audit records will be logged. It defaults to `ERROR`, but might be set to `INFO` or `WARNING` instead.
Note that only `ERROR`-level logs are immediately flushed to disk, lower levels might be buffered.

Here are few use cases:
| **Audit use case**             | **`yb-tserver` gflags (in addition to `--ycql_enable_audit_log`)**             |
| Auth events only               | `--ycql_audit_included_categories=AUTH`                                        |
| DDLs on  `ks1`                 | `--ycql_audit_included_categories=DDL --ycql_audit_included_keyspaces=ks1`     |
| SELECTs and DMLs by `user1`    | `--ycql_audit_included_categories=QUERY,DML --ycql_audit_included_users=user1` |
| DCLs by everyone but `dbadmin` | `--ycql_audit_included_categories=DCL --ycql_audit_excluded_users=dbadmin`     |

# Sample output

```
E0919 17:45:33.313267 344780800 audit_logger.cc:564] AUDIT: user:null|host:127.0.0.1:9042|source:127.0.0.1|port:52784|timestamp:1600519533313|type:LOGIN_ERROR|category:AUTH|operation:LOGIN FAILURE; Provided username cassandra and/or password are incorrect
E0919 17:45:59.617997 212246528 audit_logger.cc:564] AUDIT: user:cassandra|host:127.0.0.1:9042|source:127.0.0.1|port:52789|timestamp:1600519559617|type:LOGIN_SUCCESS|category:AUTH|operation:LOGIN SUCCESSFUL
E0919 17:46:20.512369 345853952 audit_logger.cc:564] AUDIT: user:cassandra|host:127.0.0.1:9042|source:127.0.0.1|port:52790|timestamp:1600519580512|type:SELECT|category:QUERY|ks:k|scope:t|operation:SELECT * FROM k.t;
E0919 17:46:35.132189 344780800 audit_logger.cc:564] AUDIT: user:cassandra|host:127.0.0.1:9042|source:127.0.0.1|port:52790|timestamp:1600519595132|type:REQUEST_FAILURE|category:ERROR|operation:SELECT * FROM k.tx;; Object Not Found
```

# Known issues:

* Operations restarted internally are logged upon restart.
* Client-side only operations (`DESCRIBE`, `LIST`) are not logged.
* Login events are logged twice (could have something to do with control connection).
* More comprehensive Java tests are needed.

Test Plan: ybd --java-test org.yb.cql.TestAudit

Reviewers: neil, oleg, mihnea, dmitry, sergei

Reviewed By: dmitry, sergei

Subscribers: sergei, yql

Differential Revision: https://phabricator.dev.yugabyte.com/D9376
frozenspider added a commit that referenced this issue Sep 25, 2020
@frozenspider
[Backport to 2.2] #1331: [YCQL] Operations audit logging
8287db9 
Summary:
Audit logging feature for YCQL is similar to Apache Cassandra 4.0-beta2, with some inspiration taken from pgAudit.
* Audit is controlled and configured through runtime gflags, which can changed using `yb-ts-cli` without restarting a `yb-tserver` process.
* Audit log in written into a general log, with `AUDIT: ` prefix.
* Output format follows that of Cassandra binary log as seen through `auditlogviewer`.
* We also follow Cassandra's audit approach for event types, categories and scopes.
* We rely on `glog` for logging, which results in logging being done pseudo-asynchronously (i.e. buffered and sometimes flushed).
* Operations that fail at parse or analyze time will be logged once.
* Operations that fail at execution time will be logged twice - before attempting to execute it, and before reporting an error. This differs from both Cassandra and pgAudit.
* For `CREATE/ALTER ROLE`, password will be replaced with `<REDACTED>` (only if the statement was written without grammatical errors!).

# Usage

Audit is disabled by default and is configured using `yb-tserver` gflags. The most important one is `--ycql_enable_audit_log`, enabling which would by default audit everything except system table queries. Without it, other audit config flags would have no effect.

`--ycql_audit_log_level` controls a log level at which audit records will be logged. It defaults to `ERROR`, but might be set to `INFO` or `WARNING` instead.
Note that only `ERROR`-level logs are immediately flushed to disk, lower levels might be buffered.

Here are few use cases:
| **Audit use case**             | **`yb-tserver` gflags (in addition to `--ycql_enable_audit_log`)**             |
| Auth events only               | `--ycql_audit_included_categories=AUTH`                                        |
| DDLs on  `ks1`                 | `--ycql_audit_included_categories=DDL --ycql_audit_included_keyspaces=ks1`     |
| SELECTs and DMLs by `user1`    | `--ycql_audit_included_categories=QUERY,DML --ycql_audit_included_users=user1` |
| DCLs by everyone but `dbadmin` | `--ycql_audit_included_categories=DCL --ycql_audit_excluded_users=dbadmin`     |

# Sample output

```
E0919 17:45:33.313267 344780800 audit_logger.cc:564] AUDIT: user:null|host:127.0.0.1:9042|source:127.0.0.1|port:52784|timestamp:1600519533313|type:LOGIN_ERROR|category:AUTH|operation:LOGIN FAILURE; Provided username cassandra and/or password are incorrect
E0919 17:45:59.617997 212246528 audit_logger.cc:564] AUDIT: user:cassandra|host:127.0.0.1:9042|source:127.0.0.1|port:52789|timestamp:1600519559617|type:LOGIN_SUCCESS|category:AUTH|operation:LOGIN SUCCESSFUL
E0919 17:46:20.512369 345853952 audit_logger.cc:564] AUDIT: user:cassandra|host:127.0.0.1:9042|source:127.0.0.1|port:52790|timestamp:1600519580512|type:SELECT|category:QUERY|ks:k|scope:t|operation:SELECT * FROM k.t;
E0919 17:46:35.132189 344780800 audit_logger.cc:564] AUDIT: user:cassandra|host:127.0.0.1:9042|source:127.0.0.1|port:52790|timestamp:1600519595132|type:REQUEST_FAILURE|category:ERROR|operation:SELECT * FROM k.tx;; Object Not Found
```

# Known issues:

* Operations restarted internally are logged upon restart.
* Client-side only operations (`DESCRIBE`, `LIST`) are not logged.
* Login events are logged twice (could have something to do with control connection).
* More comprehensive Java tests are needed.

Test Plan:
ybd --java-test org.yb.cql.TestAudit
Jenkins: urgent, rebase: 2.2

Reviewers: sergei, dmitry, mihnea

Reviewed By: mihnea

Subscribers: yql

Differential Revision: https://phabricator.dev.yugabyte.com/D9468
@frozenspider
Copy link
Contributor

frozenspider commented Sep 25, 2020 •
edited
Loading

YCQL audit was implemented in fdec43a, with the following limitations:

  • Operations restarted internally are logged upon restart.
  • Client-side only operations (DESCRIBE, LIST) are not logged.
  • Login events are logged twice (probably caused by maintaining a control connection).

Follow-up issue to track this: #5851

@frozenspider frozenspider mentioned this issue Sep 28, 2020
Open
@m-iancu
Copy link
Contributor

m-iancu commented Sep 30, 2020 •
edited
Loading

Closing as this is done, any follow-up improvements will be tracked in separate issues.
Re-opening as only the YCQL side is done.

@m-iancu m-iancu closed this as completed Sep 30, 2020
@m-iancu m-iancu changed the title YugabyteDB activity monitoring and audit log support [YCQL] YugabyteDB activity monitoring and audit log support Sep 30, 2020
@m-iancu m-iancu changed the title [YCQL] YugabyteDB activity monitoring and audit log support YugabyteDB activity monitoring and audit log support Sep 30, 2020
@m-iancu m-iancu reopened this Sep 30, 2020
@stevebang stevebang mentioned this issue Oct 7, 2020
Closed
@ramsrivatsa ramsrivatsa self-assigned this Oct 19, 2020
ramsrivatsa added a commit that referenced this issue Oct 22, 2020
@ramsrivatsa
[#1331][YSQL] Audit logging support for YSQL
fb7ce2b 
Summary:
The diff enables Audit Logging support for YSQL. It utilizes the postgres-compatible feature [[ https://github.com/pgaudit/pgaudit | pgAudit ]] for enabling Audit logging.

Then, to enable auditing on a database run:
```
CREATE EXTENSION IF NOT EXISTS pgaudit;
```

To configure auditing you can use the `ysql_pg_conf` flag.
For instance, to enable auditing of DDLs and raising the log level to notice, do:
```
ysql_pg_conf="pgaudit.log='DDL',pgaudit.log_level=notice"
```
Similarly, there exists other options like //log_client, log_catalog, log_parameter, log_relation, log_statement_once and role//. All options can be set as a cluster-level setting by using `ysql_pg_conf` (as above).
They can also be set per-session (connection) using the `SET` command. For example, log_level can be set using the following `SET` command. However, these settings are alive only for the session during which we set these variables.

```
SET pgaudit.log_level="notice";
```

Test Plan:
The associated test for Audit logging can be execute by running the following command.

```
ybd --java-test org.yb.pgsql.TestPgRegressExtension
```

Reviewers: mihnea

Reviewed By: mihnea

Subscribers: kannan, yql

Differential Revision: https://phabricator.dev.yugabyte.com/D9667
@ramsrivatsa
Copy link
Contributor

ramsrivatsa commented Oct 22, 2020 •
edited by m-iancu
Loading

Both YSQL(commit link) and YCQL audit logging support is enabled.

ramsrivatsa added a commit that referenced this issue Oct 28, 2020
@ramsrivatsa
[Backport to 2.3][#1331][YSQL] Audit logging support for YSQL
91f4574 
Summary:
The diff enables Audit Logging support for YSQL. It utilizes the postgres-compatible feature [[ https://github.com/pgaudit/pgaudit | pgAudit ]] for enabling Audit logging.

Then, to enable auditing on a database run:
```
CREATE EXTENSION IF NOT EXISTS pgaudit;
```

To configure auditing you can use the `ysql_pg_conf` flag.
For instance, to enable auditing of DDLs and raising the log level to notice, do:
```
ysql_pg_conf="pgaudit.log='DDL',pgaudit.log_level=notice"
```
Similarly, there exists other options like //log_client, log_catalog, log_parameter, log_relation, log_statement_once and role//. All options can be set as a cluster-level setting by using `ysql_pg_conf` (as above).
They can also be set per-session (connection) using the `SET` command. For example, log_level can be set using the following `SET` command. However, these settings are alive only for the session during which we set these variables.

```
SET pgaudit.log_level="notice";
```

Test Plan:
Jenkins: rebase: 2.3
The associated test for Audit logging can be execute by running the following command.

```
ybd --java-test org.yb.pgsql.TestPgRegressExtension
```

Reviewers: mihnea

Reviewed By: mihnea

Subscribers: yql, kannan

Differential Revision: https://phabricator.dev.yugabyte.com/D9762
@rkarthik007 rkarthik007 changed the title YugabyteDB activity monitoring and audit log support YugabyteDB audit log support Oct 28, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@frozenspider frozenspider

@ramsrivatsa ramsrivatsa

Labels
area/ysql Yugabyte SQL (YSQL) community/request Issues created by external users kind/new-feature This is a request for a completely new feature roadmap-tracking-issue This issue tracks a major roadmap item, and usually appears in the roadmap list.
Projects
None yet
Milestone
v2.3
Development

No branches or pull requests