Security

A node with no tokens lets everyone do everything, which is right for a lake on your laptop or on a private network. To share it, start the node with tokens. There are three, one for each role, and every door checks them: HTTP, the Python and JavaScript clients, Postgres, Kafka, Arrow Flight, MCP and the Iceberg REST catalog.

Role	May
read	query: SELECT, key lookups, watch and live, MCP reads, the Iceberg catalog’s reads, metrics, and GET /files
write	read, and write rows: INSERT, UPDATE, DELETE, MERGE, TRUNCATE, appends, Kafka produce, Flight DoPut, PUT /files and other engines’ Iceberg appends
admin	write, and change what exists: tables, views, schemas, tasks, functions, procedures and secrets, and run DO blocks

With tokens on, a request with no token or a wrong one is refused (401). Only GET /stats, a health check, stays open.

Turn tokens on

Make three random tokens and one key for secrets (below), and start a node with them in its environment. A token given as a flag would show in the process list, so use the environment. Every node of a cluster needs the same three. This page’s node listens on 8086, with Postgres on 5486, Kafka on 9486 and Flight on 8816.

cat > tokens.env <<EOF
export PONDRA_READ_TOKEN=$(python3 -c "import secrets; print(secrets.token_urlsafe(16))")
export PONDRA_WRITE_TOKEN=$(python3 -c "import secrets; print(secrets.token_urlsafe(16))")
export PONDRA_ADMIN_TOKEN=$(python3 -c "import secrets; print(secrets.token_urlsafe(16))")
export PONDRA_SECRET_KEY=$(python3 -c "import secrets; print(secrets.token_hex(32))")
EOF

. ./tokens.env
nohup pondra serve secure-lake --addr 127.0.0.1:8086 --pg 127.0.0.1:5486 \
  --kafka 127.0.0.1:9486 --flight 127.0.0.1:8816 --python auto > secure.log 2>&1 &
echo $! > secure.pid
until curl -sf localhost:8086/stats > /dev/null; do sleep 0.3; done

The rest of this page reads the tokens from that file. In your own code they come from wherever you keep secrets.

import re
import pondra

tokens = {role.lower(): token for role, token in
          re.findall(r"export PONDRA_(READ|WRITE|ADMIN)_TOKEN=(\S+)", open("tokens.env").read())}

def connect(role):
    return pondra.connect("http://localhost:8086", token=tokens[role])

connect("admin").sql("CREATE TABLE notes (id BIGINT, body VARCHAR)")
connect("write").sql("INSERT INTO notes VALUES (1, 'first')")
connect("read").sql("SELECT * FROM notes").rows()

[{'id': 1, 'body': 'first'}]

A token at every door

Each client sends the token its own way, and it means the same thing everywhere.

Python

pondra.connect("http://localhost:8086", token=tokens["read"]).sql("SELECT count(*) AS n FROM notes").rows()

JavaScript

import { readFileSync } from "node:fs";
import { connect } from "pondra";

const token = readFileSync("tokens.env", "utf8").match(/PONDRA_READ_TOKEN=(\S+)/)[1];
const db = connect("http://localhost:8086", { token });
console.log(await db.sql("SELECT count(*) AS n FROM notes"));

psql

Over Postgres the user name picks the role (reader, writer or admin) and the password is that role’s token.

. ./tokens.env
PGPASSWORD=$PONDRA_READ_TOKEN psql -h localhost -p 5486 -U reader -c "SELECT count(*) AS n FROM notes"

[{'n': 1}]

Arrow Flight takes a handshake with the token as the password, as ADBC and pyarrow’s authenticate_basic_token send it:

import json
import pyarrow.flight as flight

client = flight.FlightClient("grpc://localhost:8816")
header = client.authenticate_basic_token("reader", tokens["read"])
ticket = flight.Ticket(json.dumps({"sql": "SELECT count(*) AS n FROM notes"}).encode())
client.do_get(ticket, flight.FlightCallOptions(headers=[header])).read_all().to_pylist()

[{'n': 1}]

Kafka takes SASL/PLAIN: the user name is the role and the password is its token. A reader that tries to produce is refused. This prints what each producer’s delivery report said:

import json
from confluent_kafka import Producer

def produce(user, token):
    outcome = []
    producer = Producer({"bootstrap.servers": "localhost:9486", "security.protocol": "SASL_PLAINTEXT",
                         "sasl.mechanisms": "PLAIN", "sasl.username": user, "sasl.password": token})
    producer.produce("notes", value=json.dumps({"id": 2, "body": "via kafka"}),
                     on_delivery=lambda error, message: outcome.append(error))
    producer.flush(10)
    return outcome[0]

produce("writer", tokens["write"]), produce("reader", tokens["read"])

(None, KafkaError{code=TOPIC_AUTHORIZATION_FAILED,val=29,str="Broker: Topic authorization failed"})

The writer’s None is success. MCP takes the same Authorization: Bearer header, so an AI agent has the rights of its token: give it a read token and it can look but not write. The Iceberg REST catalog takes the token as its token (PyIceberg) or an iceberg secret (DuckDB): see other engines. pondra run --url … and a pondra sql write, which the leader records, send PONDRA_TOKEN.

What each role is refused

Try each role on each kind of statement:

import requests

def attempt(role, statement):
    try:
        result = connect(role).sql(statement)
        return result.rows() if hasattr(result, "rows") else "ok"
    except RuntimeError as error:
        return str(error).split(": ", 1)[1]

for role, statement in [
    ("read", "SELECT count(*) AS n FROM notes"),
    ("read", "INSERT INTO notes VALUES (3, 'from a reader')"),
    ("write", "INSERT INTO notes VALUES (3, 'from a writer')"),
    ("write", "CREATE TABLE more (id BIGINT)"),
    ("admin", "CREATE TABLE more (id BIGINT)"),
]:
    print(f"{role:5} {statement:45} {attempt(role, statement)}")

for label, headers in [("no token", {}), ("wrong token", {"Authorization": "Bearer not-a-token"})]:
    print(f"{label:11} {requests.post('http://localhost:8086/sql', data='SELECT 1', headers=headers).status_code}")

read  SELECT count(*) AS n FROM notes               [{'n': 2}]
read  INSERT INTO notes VALUES (3, 'from a reader') this token may not write
write INSERT INTO notes VALUES (3, 'from a writer') ok
write CREATE TABLE more (id BIGINT)                 this token may not change the lake's tables, views, schemas, routines or users (an admin token, or a superuser, does)
admin CREATE TABLE more (id BIGINT)                 ok
no token    401
wrong token 401

Code runs as its caller

A Python function or procedure runs code on the node’s machine, so only an admin makes one, and only an admin runs a DO block, which is code run once. A procedure then runs with the rights of whoever calls it, not of whoever wrote it: a reader can call a procedure that writes, and the write is refused.

connect("admin").sql("""CREATE PROCEDURE add_note(text VARCHAR) LANGUAGE python AS $$
    pondra.sql("INSERT INTO notes VALUES (100, $text)", text=text)
    return "added"
$$""")

print(attempt("write", "CREATE PROCEDURE sneaky() LANGUAGE python AS $$ return 1 $$"))
print(attempt("read", "CALL add_note('by a reader')"))
print(attempt("write", "CALL add_note('by a writer')"))

this token may not change the lake's tables, views, schemas, routines or users (an admin token, or a superuser, does)
add_note: RuntimeError: 500: this token may not write
  line 2: pondra.sql("INSERT INTO notes VALUES (100, $text)", text=text)
[{'add_note': 'added'}]

That makes a procedure a way to hand out a narrow power: a reader can’t insert, but an admin can write a procedure and let readers call it. It can only do what its caller may. Functions have no connection back to the lake at all. See functions and procedures.

Since a procedure’s code is the node’s own, --python is guarded. A node without an admin token refuses it unless it listens on 127.0.0.1 (pondra.local() passes its own):

timeout 20 pondra serve open-lake --addr 0.0.0.0:8087 --python auto 2>&1 | head -1

Error: --python lets whoever makes a procedure run code on this machine: set --admin-token, or listen on 127.0.0.1

Users, roles and grants

Tokens are three keys shared by everyone with the same role. Users sign in as themselves and may do what they are granted, down to a table’s columns. A superuser makes them in SQL; the admin token is one:

admin = connect("admin")
admin.sql("CREATE TABLE salaries (id BIGINT, name VARCHAR, pay DOUBLE)")
admin.sql("INSERT INTO salaries VALUES (1, 'ann', 100.0), (2, 'bob', 120.0)")
admin.sql("CREATE USER ann PASSWORD 'correct-horse-battery'")
admin.sql("CREATE ROLE staff")
admin.sql("GRANT SELECT (id, name) ON salaries TO staff")
admin.sql("GRANT SELECT ON notes TO staff")
admin.sql("GRANT staff TO ann")
ann = pondra.connect("http://localhost:8086", user="ann", password="correct-horse-battery")
ann.sql("SELECT id, name FROM salaries ORDER BY id").rows()

[{'id': 1, 'name': 'ann'}, {'id': 2, 'name': 'bob'}]

A column, a table or a write she isn’t granted is refused, saying which, as Postgres says it; a filter on a column she may not read is refused too, since its answer would tell:

def attempt_as(db, sql):
    try:
        getattr(db.sql(sql), "rows", list)()  # (a query runs when its rows are asked for)
        return "ran"
    except Exception as e:
        return str(e).split(" (GRANT")[0].split(": ", 1)[1]  # (after the HTTP status)

print(attempt_as(ann, "SELECT * FROM salaries"))
print(attempt_as(ann, "SELECT name FROM salaries WHERE pay > 110"))
print(attempt_as(ann, "INSERT INTO notes VALUES (2, 'second')"))

Error during planning: permission denied: SELECT (pay) on salaries
Error during planning: permission denied: SELECT (pay) on salaries
permission denied: INSERT on notes

Statement	Does
CREATE USER u PASSWORD '…' [SUPERUSER]	a user who signs in (a superuser may do everything, as the admin token)
CREATE ROLE r	grants to give users together
ALTER USER u PASSWORD '…', ALTER USER u SUPERUSER | NOSUPERUSER	a new password; superuser or not
DROP USER u, DROP ROLE r	gone, at once on every node
GRANT SELECT [(a, b)] | INSERT | UPDATE | DELETE | ALL ON t TO r	a table or view, or some of its columns
GRANT … ON SCHEMA s or ON ALL TABLES IN SCHEMA s	every table of a schema, now and later
GRANT … ON ALL TABLES	every table of the lake (and of the lakes attached to it)
GRANT USAGE ON SECRET s TO r	queries may read the URLs its scope covers (below)
GRANT r TO u, REVOKE r FROM u, REVOKE … FROM r	roles held, and grants taken back
GRANT … TO public	every user
CREATE TOKEN t FOR USER u [EXPIRES IN '90 days'], DROP TOKEN t FOR u	a token for a script or a service, shown once
SELECT * FROM pondra.users, pondra.grants	who there is, and what each holds (a user sees its own)

A user signs in at every door: over HTTP with its name and password (Basic) or a token (Bearer); POST /login gives a session, a token the lake signs and any node checks, for 12 hours (PONDRA_SESSION_HOURS), which the console uses; Postgres clients sign in with SCRAM-SHA-256, as they do with Postgres (a user with only tokens gives one as its password); Kafka’s SASL/PLAIN takes the name and password; Flight’s handshake turns them into a session. A password is kept only as SCRAM’s verifier and a token as its hash, so the catalog gives neither away.

token = admin.sql("CREATE TOKEN nightly FOR USER ann EXPIRES IN '30 days'")["token"]
pondra.connect("http://localhost:8086", token=token).sql("SELECT name FROM salaries ORDER BY id").rows()

[{'name': 'ann'}, {'name': 'bob'}]

Once a user who signs in exists, a node with no tokens set refuses whoever doesn’t sign in, too. Listings show a user what it may read: information_schema.tables, pondra.tables, the console’s Data tree. A stream of a table’s rows (Kafka, Flight’s log, /watch, a key lookup, another engine through the Iceberg catalog) needs every column of it.

Secrets

A password or key for something else, such as a bucket, a Kafka cluster or an SMTP server, goes in a secret. Only an admin makes one. It is kept in the lake encrypted with AES-256-GCM, with a key of its own, which the lake’s master key wraps; SQL can list its name, type and scope, never its values:

connect("admin").sql("CREATE SECRET mail (TYPE generic, HOST 'smtp.example.com', PASSWORD 'not-a-real-password')")
connect("read").sql("SELECT * FROM secrets()").rows()

[{'name': 'mail', 'type': 'generic', 'scope': None}]

Only the code of a procedure reads the values, with pondra.secret("mail"), and any of them the procedure prints or fails with is blanked out (***) in its notices, errors and run log. A URL a query reads (s3://…, https://…) uses the secret whose scope covers it, and a URL no secret covers is refused.

A user’s queries use a secret only with GRANT USAGE ON SECRET s TO …. And anyone signed in may bring their own for a session: CREATE TEMPORARY SECRET s (TYPE s3, …) lives in the node’s memory for that session (a Postgres connection, a client’s connection), is used before the lake’s, and is written nowhere.

The master key is PONDRA_SECRET_KEY, and every node of a cluster needs the same one: a node with another key stops with was sealed with another master key. Without it a node makes a key for its machine at ~/.pondra/secret.key, which is enough for nodes that share that file. Better, a key service keeps it: PONDRA_KMS_COMMAND names a command the node runs with wrap or unwrap and a data key on its input (a short script over aws kms, gcloud kms, az keyvault key or Vault’s transit), so the master key is never in a file, a variable or the lake. To change the master key, start the nodes with the new one and the old as PONDRA_SECRET_KEY_PREVIOUS: the leader rewraps every secret’s key when it starts (the values aren’t touched), and the old key can then go.

Quotas

A user may be held to a quota on each node: MAX_QUERIES n, how many of its statements run at once (the rest wait their turn, 30 seconds at most), and STATEMENT_TIMEOUT, how long one may run before it is stopped. PONDRA_USER_QUERIES and PONDRA_USER_TIMEOUT (seconds) set them for every user without its own; 0 lifts one. Tokens and superusers have none.

admin.sql("ALTER USER ann MAX_QUERIES 2 STATEMENT_TIMEOUT '1 second'")
print(attempt_as(ann, "SELECT sum(v) AS s FROM generate_series(1, 4000000000) AS g(v)"))
print(admin.sql("SELECT name, max_queries, statement_timeout FROM pondra.users WHERE name = 'ann'").rows())

quota: ann's statements run at most 1 s (STATEMENT_TIMEOUT); this one was stopped
[{'name': 'ann', 'max_queries': 2, 'statement_timeout': '1 second'}]

A write stopped this way may still land if it was already committing, as with Postgres’s statement_timeout on a remote commit.

The audit log

pondra.audit says who did what, through which door and from where, and what came of it. Only a superuser reads it. By default it keeps every change to users, roles, grants, tokens and secrets (role), every other change to what exists (ddl), every procedure call and DO block (function), and every refusal: a sign-in that failed, a statement its rights or its quota didn’t cover. PONDRA_AUDIT chooses the classes, as pgaudit does: role, ddl, function, write (rows written), read (queries), misc (SET, BEGIN), all or off. Rows are kept 90 days (PONDRA_AUDIT_DAYS). A statement that makes a user, a token or a secret is kept with its quoted values as '***':

for row in admin.sql("""SELECT "user", door, class, statement, outcome FROM pondra.audit
                        WHERE "user" = 'ann' OR statement LIKE '%USER ann%' ORDER BY at""").rows():
    print(row)

{'user': 'admin', 'door': 'http', 'class': 'role', 'statement': "CREATE USER ann PASSWORD '***'", 'outcome': 'ok'}
{'user': 'ann', 'door': 'http', 'class': 'read', 'statement': 'SELECT * FROM salaries', 'outcome': 'refused'}
{'user': 'ann', 'door': 'http', 'class': 'read', 'statement': 'SELECT name FROM salaries WHERE pay > 110', 'outcome': 'refused'}
{'user': 'ann', 'door': 'http', 'class': 'write', 'statement': "INSERT INTO notes VALUES (2, 'second')", 'outcome': 'refused'}
{'user': 'admin', 'door': 'http', 'class': 'role', 'statement': "CREATE TOKEN nightly FOR USER ann EXPIRES IN '***'", 'outcome': 'ok'}
{'user': 'admin', 'door': 'http', 'class': 'role', 'statement': "ALTER USER ann MAX_QUERIES 2 STATEMENT_TIMEOUT '***'", 'outcome': 'ok'}

Each row also has the time (at), the address it came from (from), the error (message), how long it took (ms) and the node that wrote it.

TLS

With a certificate and its key, every door takes TLS on its own port: HTTPS, Postgres’s sslmode=require, Kafka’s security.protocol=SSL and Flight’s grpc+tls. A plain connection is then taken only from the node’s own machine (PONDRA_TLS=optional takes it from anywhere), so a password or a token never crosses a network in the clear. A certificate for one node:

openssl req -x509 -newkey rsa:2048 -nodes -days 365 -keyout node.key -out node.pem -subj "/CN=pondra" \
  -addext "subjectAltName=IP:127.0.0.1,DNS:localhost" -addext "basicConstraints=critical,CA:FALSE" 2> /dev/null
nohup pondra serve tls-lake --addr 127.0.0.1:8096 --tls-cert node.pem --tls-key node.key > tls.log 2>&1 &
echo $! > tls.pid
until curl -sf --cacert node.pem https://127.0.0.1:8096/stats > /dev/null; do sleep 0.3; done
curl -s --cacert node.pem https://127.0.0.1:8096/sql -d "SELECT 'over TLS' AS hello"

[{"hello":"over TLS"}]

Clients trust it as they trust any server’s: the system’s authorities, or the file itself (pondra.connect(url, ca="node.pem"), sslrootcert=node.pem, NODE_EXTRA_CA_CERTS for JavaScript, tls_root_certs for pyarrow’s Flight).

print(pondra.connect("https://127.0.0.1:8096", ca="node.pem").sql("SELECT 'from Python' AS hello").rows())

[{'hello': 'from Python'}]

The nodes of a cluster share the certificate, and call each other over HTTPS. Give them an authority too (--tls-ca or PONDRA_TLS_CA, a PEM file) and they show their certificates to each other: mutual TLS. A request made with the nodes’ own key is then taken only over a connection whose certificate that authority signed, so the key alone, taken from a node, opens nothing.

kill $(cat tls.pid)

A request can’t stop a node

A request that hits a bug in the node, at any door, is answered as an error (internal error: …, HTTP 500, SQLSTATE XX000) and the node goes on serving. Only the work the node can’t do without (committing, tiering, following the leader) still stops it on a broken invariant, as before: a crashed writer restarts and reloads its state from the catalog. Every door’s parser is fuzzed with malformed input (tools/fuzz_doors.py).

What a node’s SQL can’t touch

Whatever the token, SQL sent to a node never reads or writes the node’s own disk: no COPY … TO to a path, no SELECT * FROM 'notes.parquet'. Only the program that started a node (the shell, pondra.local()) does that, by a key only it knows.

print(attempt("admin", "SELECT * FROM 'notes.parquet'"))

notes.parquet is a file on the node's machine: only the program that started the node (the shell, local()) reads those

A file in a bucket or at a URL needs a secret that covers it, and that is how a node reads other people’s data: see files and other lakes.

What tokens don’t do

Caution

• Tokens guard nodes, not the bucket. With the bucket’s own credentials anyone runs pondra sql --dir s3://… and reads the lake with no node at all, so give people read-only bucket keys, and keep the keys as secret as the data.
• A token is the whole login, for everything. A read token reads every table: give people users instead, with grants. Rotate a token by restarting every node with the new values.
• Row filters and masks come later. A view doesn’t yet lend its owner’s rights: a user reading one needs SELECT on what it reads.
• Quotas are each node’s. A user held to 4 statements at once may run 4 on each node.

Stop the node when you’re done:

kill $(cat secure.pid)