Passing context data with database queries

Overview

CipherStash end-to-end identity works with CipherStash Proxy to enable individual customer identity and context information to be associated with data access event logs for auditing.

Prerequisites

This assumes that you understand the basic concepts of End-to-end Identity, and have:

• Cipherstash CLI configured
• A PostgreSQL database
• Cipherstash Proxy running as a proxy to your database
• CipherStash Audit enabled; see Using Audit for details
• psql installed (or a similar way of executing an SQL statement)

Refer to Proxy Identity for details.

Step-by-step guide

1. Connect psql to CipherStash Proxy

1psql postgresql://$PROXY_USERNAME:$PROXY_PASSWORD@$PROXY_HOST:$PROXY_PORT/$DATABASE_NAME

If this doesn't work, double check that you're connected to CipherStash Proxy and not directly to the database.

2. Pass a JSON object to CipherStash Proxy

In psql execute the SET CS_CONTEXT command, passing a JSON value.

1SET CS_CONTEXT '{"group": "developers"}';

A parse error will be returned if the data is not valid JSON.

3. Execute an SQL statement

Execute some SQL; any query will suffice.

1SELECT 1 as value;

CipherStash Proxy will track the SQL execution and push access details to CipherStash Audit.

1{
2  "context": { "group": "developers" },
3  "statement": "SELECT {REDACTED} as value"
4}

Note that the raw value in the SQL statement is automatically {REDACTED} by CipherStash Proxy. CipherStash Proxy will attempt to parse and redact any raw values in the input SQL statement to minimise exposure of any sensitive data. Most frameworks and the core PostgreSQL driver will default to using parameterized SQL statements. Redaction is not required for parameterized SQL.

See the Data Access Event Reference for complete details of the payload.

4. Clear identity

In psql execute the RESET CS_CONTEXT command:

1RESET CS_CONTEXT

The Proxy will clear the current context.

5. Execute another SQL Statement

Execute some more SQL.

1SELECT 1 as value;

CipherStash Proxy will track the SQL execution and push access details to CipherStash Audit. The payload will not include any context information.

6. View the Data Access Event Log in CipherStash Audit

Access your workspace in the Dashboard to view the data access events.

The Workspaces page displays the workspaces you have access to.

• Click Manage workspace of the workspace configured to send events to CipherStash
• Select Audit in the nav bar to view the data access events