PostgreSQL Integration Guide

This guide covers how to connect a PostgreSQL database to BurnLedger, configure query templates, set up a read-only database user, and choose the right hash scope for your compliance needs.

Connection Configuration

BurnLedger connects to PostgreSQL using a standard DSN (Data Source Name) connection string.

Format:

postgresql://<user>:<password>@<host>:<port>/<database>?sslmode=verify-full

Percent-encode special characters in the password. A connection string is a URI, so characters such as @ : / ? # & must be escaped (@ → %40, # → %23) or the host is parsed incorrectly and the connection is rejected as targeting an invalid or blocked host. If you control the credential, the simplest option is a password limited to letters, digits, - and _.

Example:

postgresql://burnledger_ro:REPLACE-WITH-YOUR-PASSWORD@db.example.com:5432/myapp?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: "users-db",
  connectorType: "postgresql",
  connectionConfig: {
    dsn: "postgresql://burnledger_ro:REPLACE-WITH-YOUR-PASSWORD@db.example.com:5432/myapp?sslmode=verify-full",
  },
  subjectQuery: "SELECT * FROM users WHERE email = $1",
});

Python

system = bl.register_system(
    name="users-db",
    connector_type="postgresql",
    connection_config={
        "dsn": "postgresql://burnledger_ro:REPLACE-WITH-YOUR-PASSWORD@db.example.com:5432/myapp?sslmode=verify-full",
    },
    subject_query="SELECT * FROM users WHERE email = $1",
)

Private CA (ca_cert)

If the server certificate is signed by a private or internal CA, the system trust store cannot verify it and sslmode=verify-full fails the handshake with x509: certificate signed by unknown authority. This is the normal case for Supabase's pooler and for internal PKI in self-hosted deployments.

Pass the CA in the connection config as ca_cert: base64 of the PEM bundle. It replaces the system trust store for this system only, and the field name is snake_case in both SDKs (ca_cert, never caCert).

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

connection: {
  dsn: "postgresql://burnledger_ro:REPLACE-WITH-YOUR-PASSWORD@db.example.com:5432/myapp?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.

Connection requirements:

Query Template Format

A query template is a SELECT statement with $1 as the placeholder for the data subject identifier. BurnLedger substitutes the subject value at attestation time using parameterized queries (never string interpolation).

Rules (enforced at registration; a template that fails any of them is rejected with INVALID_QUERY_TEMPLATE):

• Must begin with SELECT or WITH (a WITH … SELECT CTE is accepted; the CTE body is checked by the same rules).
• Must contain a $1 placeholder. Without it the template is rejected — it is never silently run unparameterized.
• Must contain a WHERE clause. A template with no WHERE is rejected, not warned about.
• Must be a single statement: no embedded ;, and no dollar-quoted strings ($$ … $$).
• Must not contain any write, DDL, locking, or session keyword anywhere in the text — including inside a CTE. The blocklist is deliberately broad: INSERT UPDATE DELETE MERGE UPSERT REPLACE TRUNCATE DROP CREATE ALTER RENAME GRANT REVOKE COMMENT INTO CALL DO EXEC EXECUTE PERFORM COPY IMPORT LOAD UNLOAD VACUUM ANALYZE LOCK REINDEX CLUSTER REFRESH SET RESET DISCARD PREPARE DEALLOCATE DECLARE FETCH MOVE CLOSE LISTEN NOTIFY UNLISTEN NEXTVAL SETVAL PRAGMA ATTACH DETACH BEGIN COMMIT ROLLBACK SAVEPOINT. Note INTO and ANALYZE are on that list, so SELECT … INTO and EXPLAIN ANALYZE forms are refused.
• Must not use FOR UPDATE / FOR SHARE / FOR NO KEY UPDATE / FOR KEY SHARE — those take row locks.
• Must not call functions with side effects. This one cannot be checked statically; it is caught at execution time, where every query runs inside a READ ONLY transaction and the database itself rejects the write.

SELECT * is allowed but produces a warning — explicit column lists are more stable across schema changes.

Examples:

-- Single table, subject identified by email
SELECT * FROM users WHERE email = $1

-- Multiple columns, subject identified by external ID
SELECT id, name, email, phone, created_at FROM customers WHERE external_id = $1

-- Join across tables for a complete subject record
SELECT u.id, u.email, p.street, p.city, p.country
FROM users u
LEFT JOIN profiles p ON p.user_id = u.id
WHERE u.email = $1

-- Soft-deleted records (include them -- you need to prove they exist or not)
SELECT * FROM users WHERE email = $1

Important: Design your query to return all records associated with the data subject that you need to prove exist (or have been deleted). If a subject's data spans multiple tables, use joins or register multiple systems.

Read-Only User Setup

BurnLedger requires a dedicated read-only database user. It actively detects write permissions during connection validation and rejects connections that have them. This is a security measure: BurnLedger should never be able to modify your data.

Create the role

Connect to your database as a superuser or a user with CREATEROLE privilege:

-- Create a role with login capability and no write privileges
CREATE ROLE burnledger_ro WITH LOGIN PASSWORD 'REPLACE-WITH-YOUR-PASSWORD';

-- Grant connect access to the database
GRANT CONNECT ON DATABASE myapp TO burnledger_ro;

-- Grant usage on the schema(s) containing your tables
GRANT USAGE ON SCHEMA public TO burnledger_ro;

-- Grant SELECT on specific tables
GRANT SELECT ON TABLE users TO burnledger_ro;
GRANT SELECT ON TABLE profiles TO burnledger_ro;

-- Or grant SELECT on all current tables in the schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO burnledger_ro;

-- Also cover tables created in the future
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO burnledger_ro;

Verify the permissions

-- Connect as the read-only user
\c myapp burnledger_ro

-- This should succeed
SELECT * FROM users LIMIT 1;

-- These should all fail with "permission denied"
INSERT INTO users (email) VALUES ('test@test.com');
UPDATE users SET email = 'changed@test.com' WHERE id = 1;
DELETE FROM users WHERE id = 1;
TRUNCATE users;

Revoke write privileges if they exist

If the role somehow has write privileges (inherited from a group role, for example):

REVOKE INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA public FROM burnledger_ro;
REVOKE CREATE ON SCHEMA public FROM burnledger_ro;
REVOKE CREATE ON DATABASE myapp FROM burnledger_ro;

The privilege check introspects pg_roles and information_schema.role_table_grants — it never writes. It flags the credential as write-capable if any of these hold, directly or through an inherited role or PUBLIC:

• the SUPERUSER, CREATEDB, or CREATEROLE role attribute;
• CREATE on the current database (not just the schema) — note PUBLIC holds this on the public schema by default in PostgreSQL 14 and earlier;
• a table-level INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, or TRIGGER grant.

Two things it does not introspect: privileges reachable only via SET ROLE into a non-inherited role, and table ownership without an explicit grant. Neither applies to a correctly scoped read-only credential, but do not rely on ownership alone to be caught.

Endpoint role

POST /v1/systems/test-connection reports, under replication, whether the endpoint that answered is the writer or a hot standby: pg_is_in_recovery(), plus pg_last_xact_replay_timestamp() for how far a standby has replayed. Both are recovery information functions that any role may call, so burnledger_ro above needs nothing added.

Nothing is refused for being a standby. It is disclosed because a standby that is merely behind reports records the primary has already deleted — which fails closed — while one that was detached or re-seeded can report zero where the primary would not, and that absence would be certified. The lag figure is the age of the last replayed transaction, so it also grows while the primary is idle: an upper bound, not a measured delay. See endpoint role.

Hash Scope Options

The hashScope parameter controls what BurnLedger hashes when it runs your query.

full (recommended for most cases)

Hashes the complete content of every row returned by the query. This means any change to any column value produces a different hash, giving you the strongest proof that data has or has not been modified.

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

Use when: You need to prove the exact data that existed, or prove that data was deleted and not merely modified (e.g., nullifying PII fields is not the same as deletion under some interpretations of GDPR).

existence

Hashes only whether rows exist and how many, not their content. The hash changes only when records are added or removed, not when column values change.

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

Use when: You only need to prove that records existed and were later removed, and you do not need to prove what was in those records. This is faster for large result sets and avoids hashing sensitive content.

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

End-to-End Example

This example walks through the full process: creating a read-only user, registering the system, creating an attestation before deletion, performing the deletion, verifying, and generating a verification record.

1. Database setup (run once)

-- As superuser
CREATE ROLE burnledger_ro WITH LOGIN PASSWORD 'REPLACE-WITH-YOUR-PASSWORD';
GRANT CONNECT ON DATABASE myapp TO burnledger_ro;
GRANT USAGE ON SCHEMA public TO burnledger_ro;
GRANT SELECT ON TABLE users, profiles, orders TO burnledger_ro;

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: "myapp-users-db",
  connectorType: "postgresql",
  connectionConfig: {
    dsn: "postgresql://burnledger_ro:REPLACE-WITH-YOUR-PASSWORD@db.example.com:5432/myapp?sslmode=verify-full",
  },
  subjectQuery: "SELECT u.id, u.email, u.name, p.phone, p.address FROM users u LEFT JOIN profiles p ON p.user_id = u.id WHERE u.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 application
// await pool.query("DELETE FROM profiles WHERE user_id = (SELECT id FROM users WHERE email = $1)", [subjectEmail]);
// await pool.query("DELETE FROM users WHERE email = $1", [subjectEmail]);

// Step 3: Verify the deletion. The certificate is ISSUED BY this call when the
// data is gone -- there is no separate "create certificate" step.
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 that verify() issued
if (result.certificate) {
  await bl.savePdf(result.certificate.id, `erasure-cert-${subjectEmail}.pdf`);
  console.log(`Certificate issued: ${result.certificate.id}`);
  console.log(`Transparency status: ${result.certificate.transparencyStatus}`);
} else {
  console.log(`Not certified: status ${result.status}`);
}

Python

import os
from burnledger import BurnLedger

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

# Register the system (run once, store the system ID)
system = bl.register_system(
    name="myapp-users-db",
    connector_type="postgresql",
    connection_config={
        "dsn": "postgresql://burnledger_ro:REPLACE-WITH-YOUR-PASSWORD@db.example.com:5432/myapp?sslmode=verify-full",
    },
    subject_query=(
        "SELECT u.id, u.email, u.name, p.phone, p.address "
        "FROM users u LEFT JOIN profiles p ON p.user_id = u.id "
        "WHERE u.email = $1"
    ),
)

# --- When a deletion request comes in ---

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 application
# cursor.execute("DELETE FROM profiles WHERE user_id = (SELECT id FROM users WHERE email = %s)", (subject_email,))
# cursor.execute("DELETE FROM users WHERE email = %s", (subject_email,))
# connection.commit()

# Step 3: Verify the deletion. The certificate is ISSUED BY this call when the
# data is gone -- there is no separate "create certificate" step.
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 that verify() issued
if result.certificate:
    bl.save_pdf(result.certificate.id, f"erasure-cert-{subject_email}.pdf")
    print(f"Certificate issued: {result.certificate.id}")
    print(f"Transparency status: {result.certificate.transparency_status.value}")
else:
    print(f"Not certified: status {result.status.value}")

Troubleshooting