Implement audit logging (application query level, 2 of 3) #1612
Assignees
Comments
This was referenced Apr 16, 2025
|
Something that neither the database triggers nor the application queries will capture is UNsuccessful calls, such as those that have an expired token and are not retried, and so forth. I think that is something that level 3 (HTTP requests, #1613) can most easily capture. |
bickelj
added a commit
that referenced
this issue
Apr 16, 2025
Database triggers provide a low-level audit trail that is available regardless of whether a data change was made by the application or by a user manually logged into the database, for example, via `psql`. The low-level audit rows are in `audit_logs`. This change extends the audit capabilities with a new table `audit_logs_2` and causes inserts to that table whenever various generated SQL function calls succeed. The design of the new table is related to the original such that a view, also added here as `audit_logs_unified`, can provide friendlier access to all the audit logs. The `audit_logs_unified` view can in future (and now does) select relevant columns, filter out extraneous rows, and expand short text codes into human-readable form. Issue #1612 Implement audit logging (application query level, 2 of 3)
bickelj
added a commit
that referenced
this issue
Apr 16, 2025
Database triggers provide a low-level audit trail that is available regardless of whether a data change was made by the application or by a user manually logged into the database, for example, via `psql`. The triggers can be referred to as "level 1" and were implemented in pull request #1607. The SQL function call logging here can be referred to as "level 2." The low-level audit rows are in `audit_logs`. This change extends the audit capabilities with a new table `audit_logs_2` and causes inserts to that table whenever various generated SQL function calls succeed. The design of the new table is related to the original such that a view, also added here as `audit_logs_unified`, can provide friendlier access to all the audit logs. The `audit_logs_unified` view can in future (and now does) select relevant columns, filter out extraneous rows, and expand short text codes into human-readable form. Issue #1612 Implement audit logging (application query level, 2 of 3)
bickelj
added a commit
that referenced
this issue
Apr 29, 2025
Database triggers provide a low-level audit trail that is available regardless of whether a data change was made by the application or by a user manually logged into the database, for example, via `psql`. The triggers can be referred to as "level 1" and were implemented in pull request #1607. The SQL function call logging implemented in this commit can be referred to as "level 2" audit logs. The low-level audit rows are in `audit_logs`. This change extends the audit capabilities with a new table `db_operations_audit_logs` and causes inserts to that table whenever various generated SQL function calls succeed. The design of the new table is related to the original such that a view, also added here as `unified_audit_logs`, can provide friendlier access to all the audit logs of all levels. The `unified_audit_logs` view can in future (and now does) select relevant columns, filter out extraneous rows, and expand short text codes into human-readable form. Issue #1612 Implement audit logging (application query level, 2 of 3)
bickelj
added a commit
that referenced
this issue
Apr 30, 2025
Database triggers provide a low-level audit trail that is available regardless of whether a data change was made by the application or by a user manually logged into the database, for example, via `psql`. The triggers can be referred to as "level 1" and were implemented in pull request #1607. The SQL function call logging implemented in this commit can be referred to as "level 2" audit logs. The low-level audit rows are in `audit_logs`. This change extends the audit capabilities with a new table `db_operations_audit_logs` and causes inserts to that table whenever various generated SQL function calls succeed. The design of the new table is related to the original such that a view, also added here as `unified_audit_logs`, can provide friendlier access to all the audit logs of all levels. The `unified_audit_logs` view can in future (and now does) select relevant columns, filter out extraneous rows, and expand short text codes into human-readable form. Issue #1612 Implement audit logging (application query level, 2 of 3)
|
In prod now. |
|
Reopening to complete revisions in response to review on #1615 |
bickelj
added a commit
that referenced
this issue
May 1, 2025
The previous merge had an unsightly contraction/initialism "db" in the names of tables and associated types. This commit renames the `db_operation_audit_logs` table and `DbOperationAuditLog` type to `service_query_audit_logs` and `ServiceQueryAuditLog` respectively. This commit clarifies an unusual import that was added in a test. This commit uses imports in the usual manner. Follows #1615 review of 54368a3 Issue #1612: Implement audit logging (application query level, 2 of 3)
bickelj
added a commit
that referenced
this issue
May 4, 2025
The previous merge had an unsightly contraction/initialism "db" in the names of tables and associated types. This commit renames the `db_operation_audit_logs` table and `DbOperationAuditLog` type to `service_query_audit_logs` and `ServiceQueryAuditLog` respectively. This commit clarifies an unusual import that was added in a test. This commit uses imports in the usual manner. Follows #1615 review of 54368a3 Issue #1612: Implement audit logging (application query level, 2 of 3)
bickelj
added a commit
that referenced
this issue
May 4, 2025
The previous merge had an unsightly contraction/initialism "db" in the names of tables and associated types. This commit renames the `db_operation_audit_logs` table and `DbOperationAuditLog` type to `service_query_audit_logs` and `ServiceQueryAuditLog` respectively. This commit clarifies an unusual import that was added in a test. This commit uses imports in the usual manner. Follows #1615 review of 54368a3 Issue #1612: Implement audit logging (application query level, 2 of 3)
bickelj
added a commit
that referenced
this issue
May 4, 2025
The previous merge had an unsightly contraction/initialism "db" in the names of tables and associated types. This commit renames the `db_operation_audit_logs` table and `DbOperationAuditLog` type to `service_query_audit_logs` and `ServiceQueryAuditLog` respectively. This commit clarifies an unusual import that was added in a test. This commit uses imports in the usual manner. Follows #1615 review of 54368a3 Issue #1612: Implement audit logging (application query level, 2 of 3)
|
Really actually done now : ) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Issue #1559 ended up being associated with the first increment of work on audit logging using database triggers, the stuff originally thought of in #1355. Implementation of table triggers to capture changes was straightforward but it would have been awkward to integrate user context into every log there, so we decided to have multiple levels of audit logs rather than relying solely on database triggers. The advantage of triggers on tables is it captures data changes made outside the application. The goal in this ticket is to augment the lower-level audit logs with logs produced by the application. The plan is to modify functions that generate functions that call SQL to do
INSERTs into a new mid-level audit log table.The text was updated successfully, but these errors were encountered: