CipherStash: Data access events

Data access events are triggered when CipherStash Proxy executes SQL statments.

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 received

Statement accepted by the proxy prior to execution by the downstream database.

Field	Description	Notes
id	ID for this event	Version 4 UUID
workspace_id	Workspace Id the proxy is running in
statement_id	Id for the statement	Version 4 UUID. Links Received, Complete, and Data Access events
created_at	UTC datetime of the event	RFC 3339 and ISO 8601 date
identity	`Subject (sub)` value extracted from JWT token	Requires Identify
audience	`Audience (aud)` value extracted from JWT token	Requires Identify
context	Context data passed with `SET CS_CONTEXT`	Requires Identify
statement	Redacted statement sql	All static values stripped. Empty if parsing fails and prevents redaction.
statement_fingerprint	PostgreSQL statement fingerprint	Generated by pg_query, see below
database	Name of the connected database
database_pool_name	Name of the database pool
database_host	Host of the database
database_username	Username of the database
created_at	UTC datetime of the event	RFC 3339 and ISO 8601 date

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

Statement complete

Execution results received by the proxy from the downstream database.

Field	Description	Type
id	ID for this event	Version 4 UUID
workspace_id	Workspace Id the proxy is running in
statement_id	UUID for the statement	Version 4 UUID. Links Received, Complete, and Data Access events
created_at	UTC datetime of the event	RFC 3339 and ISO 8601 date
statement_duration_ms	Statement execution time in ms
statement_error	Error message if statement returns error	Raw error string as returned by Postgres
rows_returned_count	Rows returned by statement
rows_updated_count	Rows altered by statement	Applies to INSERT and UPDATE
created_at	UTC datetime of the event	RFC 3339 and ISO 8601 date

statement_error

Example of statement_error payload:

1statement_error: "Severity: ERROR Code: 42703 Message: column \"vtha\" does not exist Position: 8 File: parse_relation.c Line: 3666 Routine: errorMissingColumn "

Data access

Field	Description	Type
id	ID for this event	Version 4 UUID
workspace_id	Workspace Id the proxy is running in
statement_id	UUID for the statement	Version 4 UUID. Links Received, Complete, and Data Access events
created_at	UTC datetime of the event	RFC 3339 and ISO 8601 date
rows_accessed	Map of Primary Keys for access tables	See below.
columns_accessed	List of tables and columns accessed	See below.

rows_accessed

Map of accessed primary keys for each accessed table. Includes keys for all tables that have data included in the results.

1  # SELECT * FROM employees e INNER JOIN employee_territories t ON e.employee_id = t.employee_id;
2  [
3    "employees": [{employee_id}, {employee_id}, {employee_id}]
4    "employee_territories": [{employee_territories_id}, {employee_territories_id}, {employee_territories_id}]
5  ]

columns_accessed

List of unique column names of accessed data as table.column. Data is a nested array, grouping columns accessed via functions together.

1  # SELECT dob, department, concat(first_name, last_name) as name FROM employees;
2  [["employees.dob"], ["employees.department"], ["employees.first_name", "employees.last_name"]]