Main site

Data access events

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

  1. Statement received - statement accepted by the proxy prior to execution by the downstream database
  2. Statement complete - execution results received by the proxy from the downstream database
  3. 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.

FieldDescriptionNotes
idID for this eventVersion 4 UUID
workspace_idWorkspace Id the proxy is running in
statement_idId for the statementVersion 4 UUID. Links Received, Complete, and Data Access events
created_atUTC datetime of the eventRFC 3339 and ISO 8601 date
identitySubject (sub) value extracted from JWT tokenRequires Identify
audienceAudience (aud) value extracted from JWT tokenRequires Identify
contextContext data passed with SET CS_CONTEXTRequires Identify
statementRedacted statement sqlAll static values stripped. Empty if parsing fails and prevents redaction.
statement_fingerprintPostgreSQL statement fingerprintGenerated by pg_query, see below
databaseName of the connected database
database_pool_nameName of the database pool
database_hostHost of the database
database_usernameUsername of the database
created_atUTC datetime of the eventRFC 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 SQLRedacted SQL
SELECT a, b FROM cSELECT 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 **

SQLFingerprint
SELECT a, b FROM cfb1f305bea85c2f6
SELECT b, a FROM cfb1f305bea85c2f6

Statement complete

Execution results received by the proxy from the downstream database.

FieldDescriptionType
idID for this eventVersion 4 UUID
workspace_idWorkspace Id the proxy is running in
statement_idUUID for the statementVersion 4 UUID. Links Received, Complete, and Data Access events
created_atUTC datetime of the eventRFC 3339 and ISO 8601 date
statement_duration_msStatement execution time in ms
statement_errorError message if statement returns errorRaw error string as returned by Postgres
rows_returned_countRows returned by statement
rows_updated_countRows altered by statementApplies to INSERT and UPDATE
created_atUTC datetime of the eventRFC 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

FieldDescriptionType
idID for this eventVersion 4 UUID
workspace_idWorkspace Id the proxy is running in
statement_idUUID for the statementVersion 4 UUID. Links Received, Complete, and Data Access events
created_atUTC datetime of the eventRFC 3339 and ISO 8601 date
rows_accessedMap of Primary Keys for access tablesSee below.
columns_accessedList of tables and columns accessedSee 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"]]