Catalog access rules

Without an access-control store, a sign-in token can carry catalog access rules: read, write or no access for each catalog, by name or glob. Hardcharger™ enforces them on every statement’s plan, and also in what the user sees: SHOW DATABASES, information_schema, duckdb_*() functions and the drivers’ metadata calls.

This suits a platform that already decides who may use which data and mints tokens for its users. For users, roles and grants managed in SQL, use access control instead: under access control the rules grant a store user nothing (its grants decide), and they still restrict an administrator’s token.

The claim

{
  "role": "user",
  "catalog_access": [
    {"catalog": "sales", "access": "read"},
    {"catalog": "scratch_*", "access": "write"},
    {"catalog": "*", "access": "none"}
  ]
}

access is read, write (which includes read) or none.
catalog is a name or a glob (*, ?, case-sensitive).
The first matching rule wins, and a catalog no rule matches gets no access. End with a catch-all rule to make that explicit.
system and _hardcharger_system stay readable; temp follows the rules like any catalog.
A token without the claim keeps full access for its role.
A malformed rule counts as none, and a claim that isn’t a list of rules ([], a string, an object, null) grants nothing.

Try it

Make a signing key, and a server that trusts it and has two catalogs:

openssl genpkey -algorithm RSA -pkeyopt rsa_keygen_bits:2048 -out signing.key
openssl pkey -in signing.key -pubout -out signing.pub
cat > init.sql <<'SQL'
ATTACH ':memory:' AS sales;
ATTACH ':memory:' AS hr;
CREATE TABLE sales.orders AS SELECT 1 AS id, 99.5 AS amount;
CREATE TABLE hr.salaries AS SELECT 'ann' AS name, 100000 AS salary;
SQL

HARDCHARGER_ADMIN_PASSWORD='change-me' hardcharger-server --health-port 31338 \
  --init-sql-commands-file init.sql \
  --token-signature-verify-cert-path signing.pub \
  --token-allowed-issuer https://issuer.example.com --token-allowed-audience hardcharger

Your platform mints the tokens. For this guide, a script that signs one with the key, for a role and a list of rules:

"""python3 mint.py ROLE RULES_JSON: print a sign-in token for ann@example.com."""
import json
import sys
import time

import jwt

now = int(time.time())
claims = {"iss": "https://issuer.example.com", "aud": "hardcharger", "sub": "ann@example.com",
          "role": sys.argv[1], "catalog_access": json.loads(sys.argv[2]), "iat": now, "exp": now + 3600}
print(jwt.encode(payload=claims, key=open("signing.key").read(), algorithm="RS256"))

A token that may read sales and nothing else:

python3 mint.py user '[{"catalog": "sales", "access": "read"}, {"catalog": "*", "access": "none"}]' > token.txt

$ export HARDCHARGER_PASSWORD="$(cat token.txt)"
$ hardcharger --host localhost --tls=false --username token --csv --command "SELECT * FROM sales.orders"
id,amount
1,99.5
$ hardcharger --host localhost --tls=false --username token --csv --command "SELECT database_name FROM duckdb_databases() WHERE NOT internal ORDER BY 1"
database_name
_hardcharger_system
sales
$ hardcharger --host localhost --tls=false --username token --command "INSERT INTO sales.orders VALUES (2, 10)" 2>&1 | grep -o "Access denied: You do not have write access to catalog 'sales'"
Access denied: You do not have write access to catalog 'sales'
$ hardcharger --host localhost --tls=false --username token --command "SELECT * FROM hr.salaries" > /dev/null 2>&1 || echo "refused"
refused

hr is invisible to this user: errors and metadata say about it what they would say about a catalog that doesn’t exist.

Roles still apply

The token’s role limits what kind of statement runs, and the rules limit where:

Role	Can
`admin`	Read and write data where the rules allow; instance administration that reaches no data (SET GLOBAL, CHECKPOINT). Not the server’s own catalogs, other sessions or KILL SESSION (see below).
`user`	Read and write data where the rules allow; no file-system or instance-level commands.
`readonly`	Read where the rules allow; nothing that modifies state.

Rules restrict an admin token too, as in GizmoSQL 1.x: an administrator’s token whose rules name only sales can’t read hr. An administrator without rules (the password administrator, or a token without the claim) is unrestricted.

$ python3 mint.py admin '[{"catalog": "sales", "access": "write"}, {"catalog": "*", "access": "none"}]' > admin-token.txt
$ HARDCHARGER_PASSWORD="$(cat admin-token.txt)" hardcharger --host localhost --tls=false --username token \
    --command "SELECT * FROM hr.salaries" 2>&1 | grep -o "Access denied: You do not have read access to catalog 'hr'"
Access denied: You do not have read access to catalog 'hr'

A restricted administrator holds none of the rights that reach data around its rules: ATTACH and DETACH (it could detach a denied catalog and attach its file under a name the rules allow), files (read_csv(), COPY ... TO), extensions, secrets and EXPORT/IMPORT DATABASE are refused, as are macros, and under access control it runs no access-control statements. It does not read the instrumentation or log catalogs (a read would expose other sessions’ SQL), sees only its own session in hardcharger_sessions(), and cannot KILL SESSION. GizmoSQL 1.x let an administrator’s token keep those; see Coming from GizmoSQL.

A view created by a token with rules gets no owner: the token’s rights are its rules, for its own sessions only. Such a view reads with each reader’s own rights (the creator’s rules included), and is refused to a reader whose rights don’t reach what it reads.

A token without role gets token_default_role; without that, it is refused.