Quick start

This page walks through one session against a local UltraFabric: a data node, a gateway, a token, and the SQL that works today. Every command here is taken from the repository's end-to-end test, which runs the same steps and compares the answers with DuckDB.

1. Start the node and the gateway

The data node ufd serves one storage engine over a Unix socket and never listens on the network. The gateway ufgw speaks the PostgreSQL protocol in front of it. Both take the same key set and issuer, because both verify every token.

# the data node: a data directory, a socket, and the keys it trusts
ufd --dir /var/lib/uf/data --socket /run/uf/ufd.sock \
    --jwks jwks.json --issuer https://iam.example

# the gateway, in front of the node
ufgw -listen 127.0.0.1:45432 -node /run/uf/ufd.sock \
     -jwks jwks.json -issuer https://iam.example \
     [-audience ultrafabric] [-tls-cert cert.pem -tls-key key.pem] [-max-conns 200]

The gateway allows 10 seconds for a login and accepts only small messages (16 KiB) until the token is verified. After login a query string may be up to 16 MiB.

2. Get a token

The password you give the gateway is a bearer token: an RS256 JWT whose kid names a key in the JWKS. The gateway verifies it, and the node verifies it again on its own; a token signed with any other key, with alg none or an HMAC algorithm, with the wrong issuer or audience, or outside its nbf/exp window is refused at login with 28P01.

In production the tokens come from your identity provider. For a local run the repository has a test key and a small tool that signs with it; the matching JWKS must be the only key the test node trusts.

$ cd go && go build ./cmd/uf-test-token
$ OPERATOR=$(./uf-test-token -key ../testdata/auth/test_rsa.pem -operator)
$ TOKEN_A=$(./uf-test-token -key ../testdata/auth/test_rsa.pem)                 # tenant 0a0a0a0a-…-000000000001
$ TOKEN_B=$(./uf-test-token -key ../testdata/auth/test_rsa.pem \
            -tenant 0b0b0b0b-0000-4000-8000-000000000002)
# -issuer https://iam.test and -ttl 1h are the defaults

3. Connect with psql

The token is the password. The user and database names are carried by the protocol but it is the token that identifies you; the test scripts use app and uf.

$ PGPASSWORD=$TOKEN_A psql -h 127.0.0.1 -p 45432 -U app -d uf
uf=> SELECT count(*) FROM orders;
ERROR:  relation "orders" does not exist           -- 42P01: nothing created yet

4. Create tables with the operator credential

The schema belongs to the application, so creating tables needs the operator credential. A tenant token, with or without uf_roles: ["admin"], gets 42501. Every table needs a primary key: rows are keyed by it within each tenant. WITH (storage = columnar) makes an append-only, column-oriented table for analytics; the default is row storage.

$ PGPASSWORD=$OPERATOR psql -h 127.0.0.1 -p 45432 -U app -d uf
uf=> CREATE TABLE orders (id bigint PRIMARY KEY, cust int NOT NULL,
       status text, amount numeric(12,2), placed date);
CREATE TABLE
uf=> CREATE TABLE customers (id int PRIMARY KEY, region text) WITH (storage = columnar);
CREATE TABLE
uf=> CREATE TABLE IF NOT EXISTS orders (id bigint PRIMARY KEY);
CREATE TABLE                                        -- no change; without IF NOT EXISTS: 42P07
uf=> SELECT count(*) FROM orders;
ERROR:  … 42501                                     -- an operator has no tenant and cannot read rows

Column names are plain identifiers: letters, digits and single underscores, up to 63 characters. There is no tenant column to declare; the gateway adds the hidden tenant key, and the name tenant_id is reserved (42939).

5. Insert

Back as tenant A. INSERT … VALUES takes one or many rows of constants (or $n parameters). Unlisted nullable columns become NULL. A primary key that already exists for your tenant, or is repeated inside the statement, fails the whole statement with 23505 and writes nothing; it is never a silent replace.

uf=> INSERT INTO orders (id, cust, status, amount, placed) VALUES
       (1, 7, 'paid', 120.50, '2024-03-02'),
       (2, 7, 'open', 80.00, '2024-03-05'),
       (3, 9, 'void', 15.25, '2024-04-01');
INSERT 0 3
uf=> INSERT INTO customers VALUES (7, 'north'), (9, 'south');
INSERT 0 2
uf=> INSERT INTO orders (id, cust) VALUES (1, 1);
ERROR:  duplicate key … (SQLSTATE 23505)
uf=> INSERT INTO orders (id, status) VALUES (4, 'open');
ERROR:  … (SQLSTATE 23502)                           -- cust is NOT NULL

Tenant B, with its own token, can insert its own orders row 1: primary keys are unique per tenant, and neither tenant can see the other's rows.

6. Update and delete

Each UPDATE … SET … WHERE and DELETE … WHERE is applied as one atomic step at the node, and only to your tenant's rows. The command tag reports the count PostgreSQL would report.

uf=> UPDATE orders SET amount = amount * 2, status = 'paid' WHERE cust <= 7;
UPDATE 2
uf=> DELETE FROM orders WHERE status = 'void' AND id > 2;
DELETE 1
uf=> UPDATE orders SET id = 10 WHERE id = 1;
ERROR:  changing a primary key column is not supported (SQLSTATE 0A000)
uf=> UPDATE orders SET cust = NULL WHERE id = 1;
ERROR:  … (SQLSTATE 23502)
uf=> DELETE FROM customers WHERE id = 9;
ERROR:  UPDATE and DELETE on columnar tables (they are append-only) … (SQLSTATE 0A000)

An UPDATE of a table that has a column above your clearance is refused with 42501 rather than risk writing the hidden value back as NULL; a DELETE of a table whose primary key is above your clearance is refused the same way.

7. Query

SELECT with WHERE, inner and left joins, GROUP BY, the aggregates count, sum, avg, min, max, ORDER BY (by name, alias or position), LIMIT and OFFSET. A lookup or range on the primary key reads only the matching rows. These are the queries the end-to-end test checks against DuckDB, after loading 10,000 generated orders:

SELECT status, count(*), sum(amount), min(placed), max(amount)
  FROM orders GROUP BY status ORDER BY status;

SELECT id, amount FROM orders
  WHERE amount > 9000.50 AND status = 'paid' ORDER BY id LIMIT 7;

SELECT cust, count(*) FROM orders
  WHERE placed BETWEEN '2024-03-01' AND '2024-06-30'
  GROUP BY cust ORDER BY 2 DESC, 1 LIMIT 5;

SELECT count(*) FROM orders
  WHERE status IN ('open', 'void') AND cust NOT IN (1, 2, 3);

SELECT sum(amount * 2 - 1) FROM orders WHERE id <= 100;

SELECT c.region, count(*) FROM orders o JOIN customers c ON o.cust = c.id
  GROUP BY c.region ORDER BY 1;

SELECT count(*) FROM orders o LEFT JOIN customers c ON o.cust = c.id
  WHERE c.region IS NULL;

Numbers follow PostgreSQL's typing: numeric(12,2) * 2 keeps two decimal places, sum of a numeric is a numeric, avg is double precision, and text compared with a number is 42883, not a cast.

8. What fails with 0A000

Anything outside the subset fails at once with SQLSTATE 0A000 and a message naming the feature. Nothing is approximated. Some examples you will hit early:

uf=> BEGIN;                                          -- transaction statements
uf=> WITH x AS (SELECT 1) SELECT * FROM x;           -- WITH
uf=> SELECT DISTINCT status FROM orders;             -- DISTINCT
uf=> SELECT status FROM orders GROUP BY status HAVING count(*) > 1;   -- HAVING
uf=> SELECT upper(status) FROM orders;               -- functions other than the five aggregates
uf=> SELECT * FROM orders WHERE id IN (SELECT id FROM orders);        -- subqueries
uf=> INSERT INTO orders (id, cust) VALUES (5, 1) RETURNING id;        -- RETURNING, ON CONFLICT
uf=> SELECT amount / 3.0 FROM orders;                -- decimal division (no exact operator yet)
uf=> SELECT 1;                                       -- SELECT without FROM
uf=> INSERT INTO orders (id, cust) VALUES (6, 1); SELECT count(*) FROM orders;
       -- several statements in one string when one of them writes

The full list is in the SQL reference.

9. Connect with Go (pgx)

The end-to-end test drives the gateway with pgx in both of its modes. The simple protocol sends the SQL text as psql does; the default mode (QueryExecModeCacheStatement) uses the extended protocol: Parse, Describe, Bind, Execute, with $n parameters in binary format.

import (
    "context"
    "fmt"
    "net/url"

    "github.com/jackc/pgx/v5"
)

func open(token string, mode pgx.QueryExecMode) (*pgx.Conn, error) {
    dsn := fmt.Sprintf("postgres://app:%[email protected]:45432/uf?sslmode=disable", url.QueryEscape(token))
    cfg, err := pgx.ParseConfig(dsn)
    if err != nil {
        return nil, err
    }
    cfg.DefaultQueryExecMode = mode   // pgx.QueryExecModeSimpleProtocol, or the default
    return pgx.ConnectConfig(context.Background(), cfg)
}

// prepared statements: the type of $n comes from Parse or from its partner
var n int64
err = conn.QueryRow(ctx,
    "SELECT count(*) FROM orders WHERE status = $1 AND cust <= $2", "paid", 25).Scan(&n)

var id int64; var amount float64
err = conn.QueryRow(ctx, "SELECT id, amount FROM orders WHERE id = $1", int64(100)).Scan(&id, &amount)

_, err = conn.Exec(ctx,
    "INSERT INTO orders (id, cust, status, amount, placed) VALUES ($1, $2, $3, $4, $5)",
    int64(50001), 3, "open", 12.5, time.Date(2024, 5, 6, 0, 0, 0, 0, time.UTC))

rows, err := conn.Query(ctx, "SELECT id FROM orders WHERE cust = $1 ORDER BY id LIMIT $2", 7, 3)

tag, err := conn.Exec(ctx, "UPDATE orders SET status = $1 WHERE id = $2", "open", int64(1))
// tag.String() == "UPDATE 1"

Errors arrive as *pgconn.PgError with the SQLSTATE in Code. A $n whose type nothing in the statement determines is 42P18; give it a cast, as in PostgreSQL. A prepared statement holds one statement; the gateway keeps up to 1,000 of them per session and binds each one again at every Bind with its parameter values.

10. Check the answers against DuckDB

The end-to-end test does not trust its own expectations. For every query it writes the generated rows to a CSV, loads them into DuckDB with the same column types, runs the same SQL, and compares the text of every row. For UPDATE and DELETE it runs the same statements in DuckDB first and compares the table afterwards. You can do the same by hand:

$ PGPASSWORD=$TOKEN_A psql -X -At -F #x27;\t' -h 127.0.0.1 -p 45432 -U app -d uf \
    -c "SELECT status, count(*), sum(amount) FROM orders GROUP BY status ORDER BY status" > uf.tsv

$ duckdb -noheader -separator #x27;\t' -list \
    -c "CREATE TABLE orders AS SELECT * FROM read_csv('orders.csv', header=true,
          columns={'id':'BIGINT','cust':'INTEGER','status':'VARCHAR','amount':'DECIMAL(12,2)','placed':'DATE'})" \
    -c "SELECT status, count(*), sum(amount) FROM orders GROUP BY status ORDER BY status" > duck.tsv

$ diff uf.tsv duck.tsv && echo same

The whole run, from building both binaries to the DuckDB comparison and a psql smoke test, is one script on the Linux host:

$ UF_RUN_USER=uf scripts/p3a-e2e          # builds ufd, ufgw and uf-test-token; starts both on 127.0.0.1:45432
$ UF_BENCH=1 UF_RUN_USER=uf scripts/p3a-e2e   # also runs pgbench: point lookups, an aggregate and inserts,
                                              # 1 and 8 clients, simple and prepared (indicative, shared host)

What it checks: tenant A sees its 10,000 rows and tenant B its 100, and neither sees the other's; the five queries above equal DuckDB; the joins add up; each error case returns its SQLSTATE; prepared statements return the same counts as the simple protocol; updates and deletes equal DuckDB after the same statements and leave the other tenant unchanged; a token signed with another key is refused with 28P01. The pgbench numbers from this script are on the performance page, labelled as what they are: one shared development host.

Where next

• SQL reference: every statement, type, operator, protocol detail and SQLSTATE.
• SQL and psql: the shorter overview and the planned order of the gaps.
• Security model: what the node guarantees about the rows you get back.