CipherStash Audit
CipherStash Audit provides complete visbility of every query and row access in your database.
Audit works with CipherStash Proxy and CipherStash Identify to trace data access events in real-time, connecting sql statements with actual records and client identity.
What problem is Audit solving?
Audit
Auditing PostgreSQL data access requires a log of the actual records accessed connected with the identity of the user making the access request. PostgreSQL can log database statements, but a record of the executed SQL isn't sufficient to understand the actual records or rows returned. Analysis requires effort to combine logs from multiple services as there's often no single source of truth. Determining the extent of a data breach can be manual and time consuming.
CipherStash Audit tracks data access events in real-time by connecting SQL statements with the actual records returned, and works with CipherStash Identify to enable applications to associate client identity.
Audit enables:
- fine-grained data access monitoring including time, frequency of access, data record type, quantity of data
- post-incident audit logs
How Audit works with CipherStash Proxy
Audit uses CipherStash Proxy to mediate access to your PostgreSQL database and analyses SQL statements in real-time.
CipherStash Proxy analyses SQL and captures record identifiers of queried data, creating a complete event log of all sql statements, record ids, and associated identity and context information.
Data access events are available in the Audit Dashboad for monitoring and analysis.
What CipherStash Proxy is doing when it is auditing
Data access events
CipherStash Proxy triggers events as SQL statments are executed and process.
- Statement Received - statement accepted by the proxy prior to execution by the downstream database
- Statement Complete - execution results received by the proxy from the downstream database
- Data Access - summary of rows and columns accessed by statement execution
Statement redaction
The statement SQL is redacted before being included in an event payload. All static values in the SQL string are stripped. Table, columns and functions will be retained. If parsing fails or another issue prevents redaction. the statement will not be transmitted.
Most Postgres libraries and frameworks will default to using parametised statements and the PostgreSQL Extended Protocol, in which case values will not be included in the SQL.
** Example **
| Statement SQL | Redacted SQL |
|---|---|
SELECT a, b FROM c | SELECT a, b FROM c |
SELECT a, b FROM c WHERE id = '1' | SELECT a, b FROM c WHERE id = {REDACTED} |
Statement fingerprints
Statement fingerprints identify unique sql statements, examining the raw parse tree. Fingerprints ignore query differences, when they result in the same query intent. Fingerprints are unique across environments and time, providing a useful mechanism for identifying query patterns.
See Fingerprints in pg_query: A better way to check if two queries are identical for more details.
** Example **
| SQL | Fingerprint |
|---|---|
SELECT a, b FROM c | fb1f305bea85c2f6 |
SELECT b, a FROM c | fb1f305bea85c2f6 |
Primary key injection
Primary key injection connects SQL statements to record identifiers.
Primary key injection transparently ensures that any data access event includes the actual record identifiers. No need to instrument or change your SQL.
CipherStash Proxy uses the database schema to identify sql statements that do not reference a primary key. It injects the missing primary keys into the SQL before execution. Primary keys of accessed records can then be tracked with the data access event. The data is cleaned from the response before it is sent to the client.
The performance impact on the database is neglible as the primary key is by definition indexed, and the referenced tables are already present in the SQL statement.
When combined with CipherStash Identify, the events are also linked to client identity, providing an end-to-end view of data access.
Record reconciliation
Record reconciliation extracts the record identifiers, maps them to the appropriate tables, and includes them in the data access event payload sent to Audit.
Injected primary keys are always removed from the sql results before being returned. The process is internal to the CipherStash Proxy, the format of the result set always matches the original query.
Primary key injection & record reconciliation in action
Emitting signals
CipherStash Proxy provides:
- detailed SQL statement logging for development
- auditable Data Access Events for use with Audit
- detailed Prometheus metrics for monitoring and alerting