Amazon Redshift Integration Guide

This guide covers how to connect an Amazon Redshift cluster to BurnLedger, configure query templates, set up a read-only Redshift user that BurnLedger will accept, open the right network path, and choose the hash scope for your compliance needs.

Redshift speaks the PostgreSQL wire protocol, so BurnLedger drives it with the same pgx driver used for PostgreSQL: the DSN format and the $1 parameter placeholder are the PostgreSQL ones. Everything else — privilege model, default PUBLIC grants, networking — is Redshift-specific and is where integrations actually fail. Read Network Access and Read-Only User Setup before you register a system; those two sections cover the two failures that account for nearly every rejected Redshift registration.

Connection Configuration

BurnLedger connects to Redshift using a single DSN (Data Source Name) connection string.

Connection config keys

There is no read_only flag for Redshift (unlike the object-store and key-value connectors). BurnLedger introspects the credential's privileges directly on the cluster and refuses write-capable credentials — see What BurnLedger checks.

Do not copy "read_only": true over from the S3 / Redis / MongoDB guides. The Redshift factory decodes the whole connection config as a string→string map, so a non-string value anywhere in the object — a bool, a number, a nested object — makes the decode fail with invalid config JSON before the DSN is even read. Extra keys whose values are strings are ignored harmlessly; "read_only": true breaks the connection outright.

Format:

postgres://<user>:<password>@<cluster-endpoint>:5439/<database>?sslmode=verify-full

postgresql:// is accepted as an equivalent scheme. The default Redshift port is 5439, not 5432 — always state it explicitly.

Percent-encode special characters in the password. A DSN is a URI, so characters such as @ : / ? # & must be escaped (@ → %40, # → %23, / → %2F) or the host is parsed incorrectly and the connection is rejected as targeting an invalid or blocked host. Redshift requires a password of 8–64 characters with at least one uppercase letter, one lowercase letter and one digit — you can satisfy that with letters, digits, - and _ only, which is the simplest way to avoid encoding problems entirely.

Example:

postgres://burnledger_ro:REPLACE-WITH-YOUR-PASSWORD@analytics.abc123xyz.us-east-1.redshift.amazonaws.com:5439/dev?sslmode=verify-full

REPLACE-WITH-YOUR-PASSWORD is a placeholder, not a suggestion: choose your own password when you create the user below, and substitute it wherever the placeholder appears in this guide.

When registering the system via the SDK:

TypeScript

const system = await bl.registerSystem({
  name: "analytics-redshift",
  connectorType: "redshift",
  connectionConfig: {
    dsn: "postgres://burnledger_ro:REPLACE-WITH-YOUR-PASSWORD@analytics.abc123xyz.us-east-1.redshift.amazonaws.com:5439/dev?sslmode=verify-full",
  },
  subjectQuery: "SELECT * FROM analytics.events WHERE user_email = $1",
});

connectionConfig: { dsn } and the dsn convenience parameter are equivalent — the SDK turns dsn: "..." into {"dsn": "..."}. Passing both throws.

Python

system = bl.register_system(
    name="analytics-redshift",
    connector_type="redshift",
    connection_config={
        "dsn": "postgres://burnledger_ro:REPLACE-WITH-YOUR-PASSWORD@analytics.abc123xyz.us-east-1.redshift.amazonaws.com:5439/dev?sslmode=verify-full",
    },
    subject_query="SELECT * FROM analytics.events WHERE user_email = $1",
)

Known limitation in the Python SDK. register_system() parses the API response through the ConnectorType enum, which does not yet include redshift. The system is created server-side, but the client raises ValueError: 'redshift' is not a valid ConnectorType while parsing the response. Until the enum is updated, register the system once over REST and reuse the returned id:

bash curl -X POST https://api.burnledger.io/v1/systems \ -H "Authorization: Bearer $BURNLEDGER_API_KEY" \ -H "Content-Type: application/json" \ -d '{ "name": "analytics-redshift", "connector_type": "redshift", "connection_config": {"dsn": "postgres://burnledger_ro:REPLACE-WITH-YOUR-PASSWORD@analytics.abc123xyz.us-east-1.redshift.amazonaws.com:5439/dev?sslmode=verify-full"}, "subject_query": "SELECT * FROM analytics.events WHERE user_email = $1", "proof_mode": "merkle" }'

Attestation, verification and verification record calls work normally in Python; only system registration/read is affected.

Connection requirements:

Network Access

Two Redshift-specific things bite here. Both cost real debugging time, and neither produces an error message that points at the actual cause — you just get CONNECTION_FAILED after a TCP timeout.

1. Split-horizon DNS: the security group must allow the host's PRIVATE IP

If your Redshift cluster is in the same VPC as the BurnLedger host, AWS split-horizon DNS resolves …redshift.amazonaws.com to the cluster's private IP, and the connection therefore arrives at the cluster from the host's private IP. A security group rule that allows the host's public or Elastic IP has no effect — the packets never carry that address.

Inbound rule on the cluster's security group
  Type:   Custom TCP
  Port:   5439
  Source: <private IP or /32 of the BurnLedger host>   ← not its public IP

Practical checks from the BurnLedger host:

# What does the endpoint actually resolve to from this host?
dig +short analytics.abc123xyz.us-east-1.redshift.amazonaws.com

# What source address will the cluster see?
ip route get $(dig +short analytics.abc123xyz.us-east-1.redshift.amazonaws.com | head -1)

# Is the port open at all?
nc -vz analytics.abc123xyz.us-east-1.redshift.amazonaws.com 5439

If the resolved address is private (10.x, 172.16–31.x, 192.168.x), a self-hosted deployment must also run with DP_ALLOW_PRIVATE_NETWORKS=true; otherwise the SSRF dialer validates the resolved IP at connect time and blocks the dial before it reaches the cluster. On BurnLedger Cloud private addresses are always blocked, so the cluster must be reachable over a public endpoint.

2. Reaching the cluster from outside the VPC

If the BurnLedger host is not in the cluster's VPC, the cluster must have Publicly accessible = Yes, the VPC needs an internet gateway and a route for the cluster's subnet, and the security group must allow inbound TCP 5439 from the BurnLedger egress IP (for a self-hosted deployment, your NAT gateway / host IP). With "Publicly accessible" off, the endpoint resolves but nothing outside the VPC can connect, regardless of security-group rules.

Query Template Format

A query template is a SELECT statement with $1 as the placeholder for the data subject identifier. BurnLedger passes the subject value as a bound parameter over the PostgreSQL wire protocol — never string interpolation.

$1 is the only placeholder this connector substitutes. There is no universal placeholder across BurnLedger connectors: {identifier} (S3, GCS, Redis, HBase), $IDENTIFIER (MongoDB, Elasticsearch) and ? (Snowflake, Databricks) belong to other connectors and mean nothing here. A Redshift template without $1 is rejected at registration with INVALID_QUERY_TEMPLATE. That rejection is deliberate: a template with no placeholder would run as a fixed query, match nothing for the subject, and let BurnLedger issue a valid, signed verification record asserting "0 records" — a cryptographically signed false negative telling a compliance team there was nothing to delete while the subject's data sits untouched.

Rules (all enforced at registration):

• Must be a single statement beginning with SELECT or WITH … SELECT.
• Must contain $1.
• Must contain a WHERE clause (a template without one would full-table-scan, and is rejected).
• No embedded ; — a single trailing ; is tolerated by the validator but will break execution, because the template is wrapped as a subquery. Leave the semicolon off.
• No data-modifying keywords (INSERT, UPDATE, DELETE, TRUNCATE, CREATE, DROP, ALTER, …), including inside CTEs.
• No dollar-quoted strings ($$ … $$).
• No FOR UPDATE / FOR SHARE locking clauses (they take row locks, which is a write effect).

SELECT * is allowed but produces a warning: adding or dropping a column changes the row hash. Listing columns explicitly is more stable.

Examples:

-- Single table, subject identified by email
SELECT * FROM analytics.events WHERE user_email = $1

-- Explicit columns (recommended)
SELECT event_id, user_email, event_type, occurred_at
FROM analytics.events
WHERE user_email = $1

-- Join across tables for a complete subject record
SELECT e.event_id, e.user_email, u.customer_id, u.signup_date
FROM analytics.events e
LEFT JOIN analytics.users u ON u.email = e.user_email
WHERE e.user_email = $1

-- CTE form (WITH … SELECT is accepted; still needs $1 and WHERE)
WITH subject AS (
  SELECT * FROM analytics.events WHERE user_email = $1
)
SELECT event_id, event_type, occurred_at FROM subject

At attestation time BurnLedger wraps your template as a subquery (SELECT * FROM (<your template>) AS _dp_inner LIMIT <max_records+1>) and runs it inside a READ ONLY transaction, so a write that slipped past validation still fails at the engine level. Your template must therefore be valid as a subquery: no trailing semicolon, no statement-level keywords.

Read-Only User Setup

BurnLedger requires a dedicated read-only Redshift user and actively verifies it at connection time. This is a security property of the product, not a suggestion: BurnLedger must not be able to modify the data it attests to.

What BurnLedger checks

Immediately after connecting, the connector runs one introspection query and rejects the credential if any column comes back true:

SELECT
    COALESCE(BOOL_OR(u.usesuper), false)                               AS is_super,
    has_database_privilege(current_user, current_database(), 'CREATE') AS db_create,
    has_schema_privilege(current_user, 'public', 'CREATE')             AS schema_create
FROM pg_user u
WHERE u.usename = current_user;

• Any true → registration fails with WRITE_ACCESS_DETECTED and the pool is closed.
• If the query itself errors, BurnLedger fails closed: it cannot prove the credential is read-only, so it refuses with CONNECTION_FAILED ("failed to verify Redshift credential is read-only"). The credential must be able to read pg_user and call the has_*_privilege functions.

Scope of the check, stated plainly:

• TEMP/TEMPORARY is not treated as write access. Creating temp tables does not mutate your data, and Redshift grants TEMP on the database to PUBLIC by default — flagging it would reject every genuinely read-only credential.
• Per-table INSERT/UPDATE/DELETE grants are not enumerated (probing every table is unbounded). Superuser plus database- and schema-level CREATE cover the mutation surface that matters. Do not grant your BurnLedger user table-level write privileges just because the check would not catch them.
• The schema check covers public only. If your subject data lives in another schema, do not grant that schema's CREATE to the BurnLedger user either.

Gotcha: Redshift grants CREATE ON SCHEMA public to PUBLIC by default

This is the single most common Redshift rejection. On a fresh Redshift cluster, every user — including a brand-new one you created with only SELECT grants — inherits CREATE on schema public (and CREATE/TEMP on the database) through the PUBLIC group. has_schema_privilege(...,'public','CREATE') therefore returns true, and BurnLedger refuses the credential with WRITE_ACCESS_DETECTED even though you granted it nothing but SELECT.

Fix it by revoking the default PUBLIC grants (as a superuser, e.g. awsuser):

-- REQUIRED: removes the implicit CREATE that makes every user write-capable
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE CREATE ON DATABASE dev FROM PUBLIC;

-- Optional hygiene. NOT required by BurnLedger: TEMP is not treated as write access.
REVOKE TEMPORARY ON DATABASE dev FROM PUBLIC;

This is a cluster-wide change. Redshift has no way to revoke a privilege from one user below the PUBLIC baseline, so the revoke affects every non-superuser on that database. Any application that relied on creating objects in public needs an explicit grant afterwards: GRANT CREATE ON SCHEMA public TO <that_user>;. Coordinate this before running it on a shared cluster.

Create the read-only user

As a superuser, on the database named in your DSN:

-- 1. The login principal (Redshift uses CREATE USER; passwords need
--    8-64 chars with an uppercase letter, a lowercase letter and a digit)
CREATE USER burnledger_ro PASSWORD 'REPLACE-WITH-YOUR-PASSWORD';

-- 2. Read access to the schema and the tables the query template touches
GRANT USAGE ON SCHEMA analytics TO burnledger_ro;
GRANT SELECT ON analytics.events TO burnledger_ro;
GRANT SELECT ON analytics.users TO burnledger_ro;

-- Or every current table in the schema
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO burnledger_ro;

-- Cover tables created later
ALTER DEFAULT PRIVILEGES IN SCHEMA analytics
  GRANT SELECT ON TABLES TO burnledger_ro;

-- 3. Remove the default PUBLIC grants (see the gotcha above)
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE CREATE ON DATABASE dev FROM PUBLIC;

Do not grant CREATE, do not make the user a superuser (CREATEUSER), and do not add it to a group that holds either.

Verify before you register

Run exactly what BurnLedger runs, substituting the user name. All three columns must be false:

SELECT
    u.usesuper                                                            AS is_super,
    has_database_privilege('burnledger_ro', current_database(), 'CREATE') AS db_create,
    has_schema_privilege('burnledger_ro', 'public', 'CREATE')             AS schema_create
FROM pg_user u
WHERE u.usename = 'burnledger_ro';

 is_super | db_create | schema_create
----------+-----------+---------------
 f        | f         | f

If schema_create or db_create is t, the REVOKE … FROM PUBLIC statements have not been applied to this database. has_database_privilege is evaluated against current_database(), so run the verification while connected to the same database that appears in your DSN.

Then confirm the user can actually read, and cannot write:

-- Connect as burnledger_ro, then:
SELECT * FROM analytics.events LIMIT 1;                    -- succeeds
INSERT INTO analytics.events (event_id) VALUES (1);        -- permission denied
UPDATE analytics.events SET event_type = 'x' WHERE 1 = 0;  -- permission denied
DELETE FROM analytics.events WHERE 1 = 0;                  -- permission denied
CREATE TABLE public.probe (id int);                        -- permission denied

TLS

sslmode=verify-full is required, and it validates the server certificate against the Redshift CA bundle as well as binding it to the endpoint hostname. Redshift accepts TLS on 5439 by default.

Weaker modes are refused before the connector is built, so the DSN is no longer handed to the driver as written: disable, allow and the prefer default are plaintext, while require and verify-ca encrypt without ever binding the certificate to the hostname — an on-path attacker can terminate the session, be the peer, and choose the record count BurnLedger signs into a certificate. A refused config is stored unhealthy with the generic connection failed, and its attestations fail with the verdict itself: "connector redshift: transport security X is below the required minimum verified". See connector transport security.

Development and test deployments can lower the floor with DP_ALLOW_UNVERIFIED_TRANSPORT=true, which permits plaintext and unverified-TLS links (never a config that cannot be classified at all) and leaves behind only a WARN log line per connector construction. It is a downgrade switch — do not set it in production. It is independent of DP_ALLOW_PRIVATE_NETWORKS; a local cluster typically needs both.

Private CA (ca_cert)

A cluster reached through an internal CA or a re-signing proxy presents a certificate the system trust store cannot verify, and verify-full then fails with x509: certificate signed by unknown authority. Supply the root in the connection config as ca_cert — base64 of the PEM bundle, snake_case in both SDKs:

base64 -w0 ca.pem      # macOS: base64 -i ca.pem | tr -d '\n'

{
  "dsn": "postgres://burnledger_ro:pw@analytics.abc.us-east-1.redshift.amazonaws.com:5439/db?sslmode=verify-full",
  "ca_cert": "LS0tLS1CRUdJTiBDRVJUSUZJQ0FURS0tLS0t..."
}

ca_cert does not change the transport classification: a custom trust root is still verification, so the config stays verified and is admitted by the ADR-012 floor. It also never turns TLS on — supplying it alongside a DSN that puts no TLS on the data path is refused when the connector is built, rather than connecting with a trust root that does nothing.

sslrootcert=<path> is not an alternative in the hosted deployment: it names a file inside the process that dials, and the enclave has no way to receive one.

Independently of BurnLedger, set the require_ssl parameter to true in the cluster's parameter group if you want the cluster itself to reject non-TLS connections from every client.

Endpoint role

POST /v1/systems/test-connection reports, under replication, what the endpoint said about its own place in a replication topology. On a real Redshift cluster the expected answer is unknown, and the field says so: the probe is PostgreSQL's pg_is_in_recovery(), which arrived in PostgreSQL 9.0, and Redshift descends from 8.0. No Redshift function reports whether a cluster is a data-sharing consumer of another one either.

The probe is issued rather than skipped so the answer comes from your cluster instead of from an assumption about what AWS ships. Nothing is refused either way. See endpoint role.

Hash Scope Options

The hash_scope (hashScope) parameter declares what an attestation is meant to cover. If you omit it, it defaults to existence.

What actually runs is chosen by proof_mode, not by hash_scope. On Redshift, hash_scope is recorded on the system and echoed on every attestation, but the Redshift connector does not branch on it — only the object-store connectors (S3, GCS, Azure Blob, MarkLogic) vary their behavior by hash_scope. The proof_mode you pass to attest() is what selects the work: proof_mode: "count" (the SDK default) runs the bounded COUNT(*) and records a record count only; proof_mode: "merkle" fetches the rows, canonicalizes each one and builds a Merkle tree. Set hash_scope to state your intent, and set proof_mode to get the behavior — and keep the two consistent.

full

Declares that the attestation covers the complete content of every row returned. Pair it with proofMode: "merkle", which is what actually fetches and hashes the rows. Each row is then canonicalized by column: values are typed per the PostgreSQL type OIDs Redshift reports, fields are sorted by column name (so SELECT a, b and SELECT b, a hash identically), and NULL is a distinct sentinel — so any change to any column value produces a different hash.

const system = await bl.registerSystem({
  // ...
});

Use when: you need to prove the exact data that existed, or prove data was deleted rather than merely nulled out (nullifying PII fields is not the same as deletion under some interpretations of GDPR).

existence

Declares that the attestation covers only whether rows exist and how many, not their content. Pair it with proofMode: "count" (the SDK default), which is materially cheaper on Redshift: it runs a bounded COUNT(*) instead of returning and canonicalizing every row across the cluster's slices.

const system = await bl.registerSystem({
  // ...
  hashScope: "existence",
});

Use when: you only need to prove records existed and were later removed, the result set is large, or you would rather not read PII content at all.

Comparison

"Detects" here is bounded by what a merkle certificate actually carries: a signed Merkle root over the row hashes and nothing else — BurnLedger serves no per-record inclusion proof, so a changed value is demonstrable only to someone who already holds every matching row and rebuilds the tree, and from format 7.0 the leaves are keyed inside the enclave so no party, BurnLedger included, can open the root at all (see Certificate Scope §4).

Each column describes the scope paired with its matching proof_mode (full + merkle, existence + count). Because the Redshift connector ignores hash_scope, a mismatched pair gives you the behavior of the proof_mode: registering hash_scope: "existence" and then attesting with proof_mode: "merkle" still reads and hashes every row.

Either way, a result set larger than max_records fails with RECORD_LIMIT_EXCEEDED rather than being silently truncated.

End-to-End Example

1. Cluster setup (run once, as a superuser)

CREATE USER burnledger_ro PASSWORD 'REPLACE-WITH-YOUR-PASSWORD';
GRANT USAGE ON SCHEMA analytics TO burnledger_ro;
GRANT SELECT ON analytics.events, analytics.users TO burnledger_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA analytics GRANT SELECT ON TABLES TO burnledger_ro;

-- Without these two, registration fails with WRITE_ACCESS_DETECTED
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE CREATE ON DATABASE dev FROM PUBLIC;

-- Confirm: all three must be false
SELECT u.usesuper,
       has_database_privilege('burnledger_ro', current_database(), 'CREATE'),
       has_schema_privilege('burnledger_ro', 'public', 'CREATE')
FROM pg_user u WHERE u.usename = 'burnledger_ro';

Then add the inbound rule on the cluster's security group: TCP 5439 from the BurnLedger host's address — its private IP if the host shares the cluster's VPC.

2. Application code

TypeScript

import { BurnLedger } from "burnledger";
import fs from "node:fs/promises";

const bl = new BurnLedger({ apiKey: process.env.BURNLEDGER_API_KEY! });

// Register the system (run once, store the system ID)
const system = await bl.registerSystem({
  name: "analytics-redshift",
  connectorType: "redshift",
  dsn: "postgres://burnledger_ro:REPLACE-WITH-YOUR-PASSWORD@analytics.abc123xyz.us-east-1.redshift.amazonaws.com:5439/dev?sslmode=verify-full",
  subjectQuery:
    "SELECT e.event_id, e.user_email, e.event_type, e.occurred_at, u.customer_id " +
    "FROM analytics.events e " +
    "LEFT JOIN analytics.users u ON u.email = e.user_email " +
    "WHERE e.user_email = $1",
});

// --- When a deletion request comes in ---

const subjectEmail = "jane.doe@example.com";

// Step 1: attest that the data currently exists
const attestation = await bl.attest(subjectEmail, {
  systemIds: [system.id],
  proofMode: "merkle",
});

for (const s of attestation.systems) {
  console.log(`${s.systemName}: ${s.recordCount} records found`);
}

// Step 2: delete the data in your own pipeline
// await redshift.query("DELETE FROM analytics.events WHERE user_email = $1", [subjectEmail]);

// Step 3: verify the deletion
const result = await bl.verify(attestation.id, subjectEmail, { timeout: 60 });

for (const s of result.systems) {
  console.log(`${s.systemName}: now ${s.recordCount} records`);
}

// Step 4: download the certificate
if (result.certificate) {
  const pdf = await bl.downloadPdf(result.certificate.id);
  await fs.writeFile(`erasure-cert-${subjectEmail}.pdf`, pdf);
  console.log(`Certificate ${result.certificate.id} (${result.certificate.transparencyStatus})`);
}

Python

import os
from burnledger import BurnLedger

bl = BurnLedger(api_key=os.environ["BURNLEDGER_API_KEY"])

# Register the system once (see the Python SDK limitation above -- if
# register_system() raises on the ConnectorType enum, register over REST
# and hard-code the returned system id here).
SYSTEM_ID = "9d3f2b7c-51a4-4e6f-b0c8-2f1a7e5d4c3b"  # a UUID, as returned by the API

subject_email = "jane.doe@example.com"

# Step 1: attest that the data currently exists
attestation = bl.attest(
    subject_email,
    system_ids=[SYSTEM_ID],
    proof_mode="merkle",
)
for s in attestation.systems:
    print(f"{s.system_name}: {s.record_count} records found")

# Step 2: delete the data in your own pipeline
# cursor.execute("DELETE FROM analytics.events WHERE user_email = %s", (subject_email,))
# connection.commit()

# Step 3: verify the deletion
result = bl.verify(attestation.id, subject_email, timeout=60)
for s in result.systems:
    print(f"{s.system_name}: now {s.record_count} records")

# Step 4: download the certificate
if result.certificate:
    bl.save_pdf(result.certificate.id, f"erasure-cert-{subject_email}.pdf")
    print(f"Certificate issued: {result.certificate.id}")

Troubleshooting