Product DocumentationAppearance
User Audit Log
The database can optionally retain detailed logging for key operations pertaining to user authentication attempts as well as changes to roles, users and grants. This detailed log, called the User Audit Log, is persisted outside the database itself in a tamper-proof fashion and can be retrieved even when the database is not running.
Since this feature is designed for auditing, any auditable events that take place in a transaction will be logged even if the transaction is rolled back or the operation fails due to lack of permission. Normal database users can't view or retrieve the audit log; it must be retrieved by copying a file from out of the underlying storage infrastructure. See instructions below on how to do so. This allows the retrieval of the audit log even if the database is offline.
Enabling the User Audit Log
User Audit Log is a new feature that must be enabled by setting the enable_user_audit configuration parameter, after which a full database restart is required. If the configuration parameter is not set, the other settings described here will have no effect.
Regular vs. Extended Logging
The User Audit Log can record basic operations and extended operations. Basic operations are normally enough to meet auditability requirements, but some highly regulated deployments have further requirements and can use extended logging. Note that extended logging will slightly reduce the performance of login operations and consume a little more storage space. Extended operations are enabled by enabling the configuration parameter enable_user_audit_extended.
The differences between basic and extended logging are summarised in the table below. The Other user/s affected column denotes whether an affected user is logged in the respective column also.
| Type | Basic/Extended | Other user/s affected |
|---|---|---|
| LOGIN_FAIL | Basic | |
| LOCKED_OUT | Basic | |
| PASSWORD_CHANGE | Basic | ✅ |
| PASSWORD_POLICY_CHANGE | Basic | ✅ (empty for SYSTEM) |
| CREATE_USER | Basic | ✅ |
| ALTER_USER | Basic | ✅ (empty for ALL) |
| DROP_USER | Basic | ✅ |
| ALTER_SYSTEM | Basic | |
| GRANT_ATTEMPT | Basic | ✅ |
| GRANT_FAIL | Basic | ✅ |
| GRANT_SUCCESS | Basic | ✅ |
| REVOKE_ATTEMPT | Basic | ✅ |
| REVOKE_FAIL | Basic | ✅ |
| REVOKE_SUCCESS | Basic | ✅ |
| ALTER_DEFAULT_PRIVILEGES_ATTEMPT | Basic | |
| ALTER_DEFAULT_PRIVILEGES_FAIL | Basic | |
| ALTER_DEFAULT_PRIVILEGES_SUCCESS | Basic | |
| LOGIN_SUCCESS | Extended | |
| LOGOUT_SUCCESS | Extended | |
| LOGOUT_TIMEOUT | Extended | |
| LOGOUT_KILL | Extended | ✅ |
| SET_ROLE | Extended | ✅ (user being set to) |
File Format
The user audit logs are stored in a CSV (comma separated value) format for easy inspection and import into external tools. No header row is present. The order of the columns is as follows:
- Timestamp
- Name of the event
- Name of the user performing the action
- Name of the user affected by the action (if applicable)
- IP of remote end of the connection, or "[local]" if done locally
- Session Id for the connection that performed the action (0, if not yet available)
- Additional details (empty, showing options or SQL)
Log File Storage, Rotation and Retrieval
The user audit log files are stored in the following directories:
- Appliance deployments:
/mnt/ybdata/ybd/postgresql/build/db/log/ - Cloud deployments:
/mnt/ybdata/log/
Log files are stored and rotated like other system logs: The active log file is called user_audit_log.csv and those compressed and rotated are named user_audit_log.csv.N.gz where N is numeric.
Two configuration variables control the rotation of the log files. user_audit_max_bytes_per_file controls how many bytes can accumulate in the active, uncompressed log file before it's compressed and archived. user_audit_max_files controls the maximum number of rotated files that are retained.
On appliance platforms, Log files are accessed and retrieved on the manager node using SSH tools. On cloud platforms, kubectl access to the ybinst-pg pod is used instead. Examples are as follows:
| Use case | Cloud platforms | Appliance platforms |
|---|---|---|
| Get last 10 audited log events | kubectl exec -n testuser-dev-ns -it ybinst-testuser-inst-0 -c ybinst-pg -- tail -n 10 /mnt/ybdata/log/user_audit_log.csv | sudo tail -n 10 /mnt/ybdata/ybd/postgresql/build/db/log/user_audit_log.csv |
| Get all audited log events | kubectl exec -n testuser-dev-ns -it ybinst-testuser-inst-0 -c ybinst-pg -- cat /mnt/ybdata/log/user_audit_log.csv | sudo cat /mnt/ybdata/ybd/postgresql/build/db/log/user_audit_log.csv |
| Copy file from a path to local | kubectl cp testuser-dev-ns/ybinst-testuser-inst-0:/mnt/ybdata/log/user_audit_log.csv -c ybinst-pg ./user_audit_log.csv | sudo scp /mnt/ybdata/ybd/postgresql/build/db/log/user_audit_log.csv username@dest:path |
Example Content
Examples of data stored in the CSV file are as follows (with commas replaced with tabs for readability):
txt
2024-08-06T21:10:59.486751 LOGIN_FAIL testuser1 testuser2 10.10.80.2 0
2024-08-06T21:10:59.508518 LOCKED_OUT testuser1 testuser2 10.10.80.2 18532
2024-08-06T21:10:59.544257 PASSWORD_CHANGE testuser1 testuser2 10.10.80.2 18534
2024-08-06T21:10:59.526155 PASSWORD_POLICY_CHANGE testuser1 testuser2 10.10.80.2 18533 ALTER SYSTEM ALTER PASSWORD_POLICY WITH max_attempts=7;
2024-08-06T21:11:53.820660 CREATE_USER testuser1 testuser2 10.10.80.2 18574 options: canlogin=1, superuser=1
2024-08-06T21:12:01.165818 ALTER_USER testuser1 testuser2 10.10.80.2 18590 options: createdb=0
2024-08-06T21:12:03.781664 DROP_USER testuser1 testuser2 10.10.80.2 18595
2024-08-06T21:11:02.659091 ALTER_SYSTEM testuser1 testuser2 10.10.80.2 18540 ALTER SYSTEM SET user_audit_max_bytes_per_file TO '12345678';
2024-08-06T21:11:04.234346 GRANT_ATTEMPT testuser1 testuser2 10.10.80.2 18544 glinda_test_utf8: GRANT ALL PRIVILEGES ON TABLE "mytable" TO "testuser2";
2024-08-06T21:11:04.241732 GRANT_FAIL testuser1 testuser2 10.10.80.2 18544 glinda_test_utf8: GRANT ALL PRIVILEGES ON TABLE "mytable" TO "testuser2";
2024-08-06T21:11:04.235329 GRANT_SUCCESS testuser1 testuser2 10.10.80.2 18544 glinda_test_utf8: GRANT ALL PRIVILEGES ON TABLE "mytable" TO "testuser2";
2024-08-06T21:11:04.244128 REVOKE_ATTEMPT testuser1 testuser2 10.10.80.2 18544 glinda_test_utf8: REVOKE ALL PRIVILEGES ON TABLE "mytable" FROM "testuser2";
2024-08-06T21:11:04.244573 REVOKE_FAIL testuser1 testuser2 10.10.80.2 18544 glinda_test_utf8: REVOKE ALL PRIVILEGES ON TABLE "mytable" FROM "testuser2";
2024-08-06T21:11:04.764052 REVOKE_SUCCESS testuser1 testuser2 10.10.80.2 18545 glinda_test_utf8: REVOKE sysadmin FROM "testuser2";
2024-08-06T21:11:05.297111 ALTER_DEFAULT_PRIVILEGES_ATTEMPT testuser1 testuser2 10.10.80.2 18546 glinda_test_utf8: ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO "testuser2";
2024-08-06T21:11:05.297741 ALTER_DEFAULT_PRIVILEGES_FAIL testuser1 testuser2 10.10.80.2 18546 glinda_test_utf8: ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO "testuser2";
2024-08-06T21:12:05.894674 ALTER_DEFAULT_PRIVILEGES_SUCCESS testuser1 testuser2 10.10.80.2 18600 glinda_test_utf8: ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO "testuser2";
2024-08-06T21:11:13.761428 LOGIN_SUCCESS testuser1 testuser2 [local] 0
2024-08-06T21:11:13.823209 LOGOUT_SUCCESS testuser1 testuser2 [local] 18549
2024-08-06T21:11:54.907913 LOGOUT_TIMEOUT testuser1 testuser2 10.10.80.2 18576 idle_session_timeout exceeded
2024-08-06T21:11:58.501585 LOGOUT_KILL testuser1 testuser2 10.10.80.2 18580 PID:178698
2024-08-06T21:11:51.157413 SET_ROLE testuser1 testuser2 [local] 18562