Passing identity with database queries

Overview

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

We will use the JWT that sits behind your CLI session for this example. You can use any JWT you have access to.

Prerequisites

This assumes that you understand the basic concepts of End-to-end Identity, and that you 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. Add a trusted issuer and audience to the workspace

In order to validate a JWT, CipherStash Proxy needs to know that the issuer and audience are trusted.

The JWT is configured with:

Use the stash cli to add these to your workspace:

1stash identify provider trust --issuer https://auth.cipherstash.com/ --audience https://dashboard.cipherstash.com

3. Copy the CLI JWT

The CLI token lives in ~/.cipherstash/console-auth.json

Open this file, copy the accessToken value, and keep it somewhere handy as you will need it again. The accessToken is a JWT.

You can use https://jwt.io/ to decode the accessToken.

The payload should have the correct iss (issuer) and aud (audience) values. The other fields don't matter in this context.

1{
2  "iss": "https://auth.cipherstash.com/",
3  "sub": "auth0|g7NQlZuxGeaPOXJrfLVb3VTr",
4  "aud": "https://dashboard.cipherstash.com",
5  "iat": 1714094477,
6  "exp": 1714180877,
7  "scope": "offline_access",
8  "azp": "CqTQn35a4WDA6UhrcNB30Rd067bP2kDQ"
9}

4. Pass a JWT to CipherStash Proxy

In psql execute the SET CS_IDENTITY command, passing the accessToken token value you copied earlier.

1SET CS_IDENTITY '3jDqolpbdEz9ueP4uwPyIjWRzQQ47dTesYexkR6EuDwegTb2c158LwbltooWRI8Id6quf2h7vnLCVLdAUtkcr3MO69ztX2KTcrYyfdaHGMlBrSBVgRysjwOk';

CipherStash Proxy will verify the token and extract the sub value.

5. 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. The payload will include the sub and audience values extracted from the token.

1{
2  "identity": "auth0|g7NQlZuxGeaPOXJrfLVb3VTr",
3  "audience": "https://dashboard.cipherstash.com",
4  "statement": "SELECT {REDACTED} as value"
5}

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 Data Access Events for complete details of the payload.

6. Clear identity

In psql execute the RESET CS_IDENTITY command:

1RESET CS_IDENTITY

CipherStash Proxy will clear the current identity context.

7. 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 identity information.

8. 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.