SQL and psql

UltraFabric speaks the PostgreSQL protocol through a gateway, ufgw. You log in with a token, and every statement becomes a plan that the data node verifies before it runs.

How a connection works

1. The client connects to ufgw and sends a token as its password.
2. The gateway verifies the token: RS256 signature against a configured key set, issuer, audience ultrafabric, and expiry. A token signed with any other key is refused with 28P01.
3. The gateway opens a connection to the data node, ufd, over its local socket. The node establishes the requester's identity itself; it does not take the gateway's word for it.
4. Each statement is parsed by PostgreSQL's own parser, bound against the catalog and turned into a plan. The node's verifier refuses any plan that could read outside your tenant or your access policy.

Tokens travel in clear text inside the PostgreSQL password message, so the gateway accepts them only over TLS or from the same machine. Without a certificate it refuses to listen on anything but a loopback address. ufd itself never listens on the network.

A session

$ PGPASSWORD=$TOKEN 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);
uf=> INSERT INTO orders VALUES (1, 7, 'paid', 120.50, '2024-03-02');
uf=> SELECT status, count(*), sum(amount) FROM orders GROUP BY status ORDER BY status;
 status | count |     sum
--------+-------+-------------
 open   |  3310 | 16454322.57
 paid   |  3324 | 16582184.52
 void   |  3366 | 16970803.94
(3 rows)

Tenants

Every query reads only your tenant's rows; there is no tenant column to select, filter on or write. Primary keys are unique per tenant, so two tenants can both have an order 1. An UPDATE or DELETE changes only your tenant's rows.

The schema belongs to the application, not to a tenant: all tenants on a server share the same tables, and any logged-in tenant can see their names and columns, never their rows. Creating tables needs an operator credential, a separate kind of token for the application's operator. It can manage the schema but can never read or write tenant data, and an ordinary tenant token can never manage the schema.

What works today

The end-to-end test loads 10,000 rows for one tenant and 100 for another and checks that neither sees the other's rows. It compares five aggregate and filter queries with DuckDB on the same data, runs updates and deletes on one tenant and compares the result with DuckDB after the same statements, and repeats queries and inserts as prepared statements through the Go driver pgx. The answers are identical.

Not yet

Everything else fails at once with SQLSTATE 0A000 and names the missing feature. It is never silently approximated. The main gaps, in the order they are planned:

• BEGIN/COMMIT, RETURNING and ON CONFLICT.
• Subqueries, WITH, UNION, DISTINCT, HAVING, window functions, and SQL functions such as upper().
• The pg_catalog tables that database GUIs and ORMs read when they connect.
• Row-level policies. Today tenant isolation is the access boundary.
• Division of real and double precision values, varchar(n) length limits and timestamp(p) precision.
• Several statements in one query string when one of them writes, because there are no transactions yet to undo a half-applied string. Several reads in one string are fine.

Errors