# Connecting clients to DuckDB: JDBC, ADBC, ODBC and native ATTACH

> Connect JDBC, ADBC, ODBC, Python and DuckDB itself to a governed DuckDB gateway: connection targets, credentials and per-client recipes.

Queries never go through the REST API. The gateway serves them on two dedicated wires, and a client picks whichever one it already speaks:

| Wire | Port | Who speaks it |
|---|---|---|
| **Arrow Flight SQL** | `:31338` | The JDBC driver (DBeaver, Spark, any JDBC tool), ADBC (Python, Go, and others), a third-party Flight SQL ODBC driver, the `qod` CLI |
| **Native Quack** | `:9494` | DuckDB itself: the CLI, the Python package, an embedded DuckDB, with no driver in between |

Both wires resolve the same tenants and users, run the same per-statement ACL, column masking and row filters, and route through the same router, so the choice is about which client you hold, not which features you get.

From DuckDB, the whole connection is one statement, and the attached catalog joins your local tables:

```sql
ATTACH 'quack:host:9494' AS qod (TYPE quack, TOKEN 'tenant=acme&pool=bi&user=alice&password=...');
```

See [DuckDB (native Quack)](/qod/connecting/duckdb) for the token format, TLS, and what the client sees. The rest of this page covers the FlightSQL connection target and per-client recipes; see [Authenticating](/qod/connecting/authenticating) for credentials and TLS, and [Supported SQL](/qod/connecting/sql) for what you can run.

**AI agents** (Claude Code, Claude Desktop, Cursor) are the exception: they connect to the manager's embedded [MCP server](/qod/connecting/mcp) on the REST port.

## The FlightSQL connection target

A connection needs four things, because the edge applies **no defaults**: every client must fully address its target.

| What | Value |
|---|---|
| Endpoint | `host:31338` (the FlightSQL edge port) |
| Transport | `grpc+tls://` when edge TLS is on (the default), `grpc://` when off; JDBC uses `jdbc:arrow-flight-sql://` |
| `tenant` + `pool` | Required on every connection. The owning database (tenant-db) is resolved server-side. |
| User credential | A username/password (Basic) or a bearer JWT. |

How `tenant` and `pool` are carried depends on the driver, but the names are always plain `tenant` and `pool` (not `X-`-prefixed):

- **JDBC**: URL query parameters, e.g. `?tenant=acme&pool=bi`.
- **ADBC**: per-RPC call headers via the driver's call-header option.
- **Raw Flight**: gRPC headers `tenant` and `pool`.

### Superusers

Connections authenticated against the system realm (a row in `qodstate_user` with `tenant IS NULL`) add `superuser=true` **alongside** the existing `tenant` and `pool` parameters. The `tenant` and `pool` parameters still drive query routing; the `superuser=true` flag only picks which realm validates the credential and whether the per-statement ACL gate fires.

- **JDBC**: `?tenant=acme&pool=bi&superuser=true`.
- **ADBC**: send the call header `superuser` with value `true` alongside the existing `tenant` and `pool` call headers.
- **Raw Flight**: gRPC header `superuser: true` plus the regular `tenant` and `pool` headers.

A superuser can target any tenant's pool for routing while authenticating against the manager's global auth providers; the per-statement ACL gate is bypassed and the session can reach any catalog. See [Authentication](/qod/operating/authentication) for the realm split.

Because the edge ships with an auto-generated self-signed certificate, the recipes below skip certificate verification. To remove that, install a CA-signed cert as described on the [TLS](/qod/operating/tls) page.

## DBeaver and JDBC

For the click-by-click DBeaver walkthrough (registering the driver, the connection dialog, the OAuth token property), see the dedicated [DBeaver](/qod/connecting/dbeaver) page. This section is the generic JDBC reference any Flight SQL JDBC tool shares.

Install the Apache Arrow Flight SQL JDBC driver (`org.apache.arrow:flight-sql-jdbc-driver`, on Maven Central) and set the driver class to `org.apache.arrow.driver.jdbc.ArrowFlightJdbcDriver`. The entire connection is expressed in the URL:

```
jdbc:arrow-flight-sql://localhost:31338?useEncryption=true&disableCertificateVerification=true&user=alice&password=demo-alice&tenant=acme&pool=bi
```

- `useEncryption=true` matches edge TLS being on; `disableCertificateVerification=true` accepts the self-signed cert.
- `user` / `password` are the edge credential.
- `tenant` / `pool` route the connection.

The URL parameters QoD reads:

| Parameter | Required | Values | Default | Purpose |
|---|---|---|---|---|
| `user` | Yes (Basic) | string | none | Username for Basic auth. Omit when using a bearer JWT via the driver's token option. |
| `password` | Yes (Basic) | string | none | Password for Basic auth. |
| `tenant` | Yes | display name or surrogate id (`t-<8 hex>`) | none | Selects the tenant. Surrogate ids are normalized to the display name server-side. |
| `pool` | Yes | string | none | Pool name within the tenant. Pool names are unique per tenant; the owning tenant-db is resolved server-side. |
| `superuser` | No | `true` (case-insensitive); anything else is false | `false` | Picks the system realm instead of the tenant realm. `tenant` and `pool` still drive query routing. |
| `useEncryption` | No | `true` / `false` | `false` | JDBC driver flag. Set `true` when edge TLS is on (the default for QoD), `false` when off. |
| `disableCertificateVerification` | No | `true` / `false` | `false` | JDBC driver flag. `true` accepts the auto-generated self-signed cert; remove once a CA-signed cert is installed. |

Any other parameter is a driver-level option (see the Arrow Flight SQL JDBC driver docs) and is not interpreted by the edge.

A system-realm superuser adds `superuser=true` to the same URL (the `tenant` and `pool` params stay in place because they still drive query routing):

```
jdbc:arrow-flight-sql://localhost:31338?useEncryption=true&disableCertificateVerification=true&user=root&password=demo-root&tenant=acme&pool=bi&superuser=true
```

### OAuth with DBeaver

When the edge is wired to an OIDC provider (Keycloak / Azure AD / Google), there are three ways to authenticate, all with the same driver:

1. **Password (ROPC)** - put the user's IdP username/password in the `user` / `password` params. The edge exchanges them with the IdP (resource-owner-password grant). IdP side: a confidential client with *Direct access grants* enabled. No browser needed.

2. **Bearer token from the login page** - the manager serves a browser token page on its own port; no extra service and no Python on the client. Walk through:

   1. Open `https://<gateway>:20900/api/auth/sql-token/start` in a browser. The manager 302-redirects you to the provider's browser-facing login (the URL is resolved via OIDC discovery, so it is reachable from your host even when the manager reaches the IdP at a different in-cluster address).
   2. Log in (SSO / MFA). The provider redirects back to `/api/auth/sql-token/callback`, which renders the **"Quack on Demand token"** page.
   3. Click **Copy** and paste the token into DBeaver's **`token`** driver property (Driver Properties tab) - or append `&token=<jwt>` to the URL.

   The page mints an **id token** whose `aud` is the edge's own client id, which is exactly what the edge requires (it rejects access tokens whose audience is the provider's resource). The edge then validates the bearer against the provider's JWKS. Because the page logs in against the edge's own OIDC provider, the returned token already carries the audience and issuer the edge expects.

3. **Headless (no browser on the host)** - fetch a token directly from the IdP's device-code endpoint and use it as `token` (no Python required):

   ```bash
   RESP=$(curl -s -d "client_id=qod-flightsql" -d "scope=openid" \
     https://<idp>/realms/qod/protocol/openid-connect/auth/device)
   echo "$RESP"   # open verification_uri_complete in a browser to log in, then:
   DEVICE_CODE=$(echo "$RESP" | sed -n 's/.*"device_code":"\([^"]*\)".*/\1/p')
   curl -s -d "grant_type=urn:ietf:params:oauth:grant-type:device_code" \
     -d "device_code=$DEVICE_CODE" -d "client_id=qod-flightsql" \
     https://<idp>/realms/qod/protocol/openid-connect/token \
     | sed -n 's/.*"access_token":"\([^"]*\)".*/\1/p'
   ```

`tenant` / `pool` are still required as URL params in every case (the bearer carries identity, not routing). A token-based URL drops `user` / `password` and rides the JWT instead:

```
jdbc:arrow-flight-sql://localhost:31338?useEncryption=true&disableCertificateVerification=true&tenant=acme&pool=bi&token=<jwt>
```

The interactive browser flow that ODBC drivers do in-process is not available from the Arrow Flight SQL JDBC driver, which is why the login page exists.

Any JDBC-based tool uses the same driver and URL. For **Spark**, point the JDBC data source at this URL with the same driver class; each Spark task opens its own Flight SQL connection.

## Python (ADBC / PyArrow)

```bash
pip install adbc_driver_flightsql adbc_driver_manager
```

```python
import adbc_driver_flightsql.dbapi as flight_sql
from adbc_driver_flightsql import DatabaseOptions

hdr = DatabaseOptions.RPC_CALL_HEADER_PREFIX.value
conn = flight_sql.connect(
    uri="grpc+tls://localhost:31338",
    db_kwargs={
        "username": "alice",
        "password": "demo-alice",
        DatabaseOptions.TLS_SKIP_VERIFY.value: "true",  # self-signed cert
        hdr + "tenant": "acme",
        hdr + "pool": "bi",
    },
)
cur = conn.cursor()
cur.execute("SELECT count(*) FROM tpch1.customer")
print(cur.fetchall())
```

The `tenant` and `pool` are passed as per-RPC call headers through `RPC_CALL_HEADER_PREFIX`. A superuser keeps both headers in place and adds `hdr + "superuser": "true"`. The bundled `scripts/bench/tpch-load-test/tpch-load-test.py` is a ready-made ADBC client built exactly this way; pass `--superuser` (or `LT_SUPERUSER=true`) when running it as the bootstrap `admin` user so the same gRPC header is forwarded. The runner cycles a curated TPC-H mix by default (`--workload tpch`, schema `tpch1`); pass `--workload tpcds` (schema `tpcds1`) to drive the TPC-DS benchmark against the `globex_tpcds` tenant-db seeded by `scripts/load-tpcds-dbgen.sh`.

## ODBC

The project ships no ODBC driver, but any Arrow Flight SQL ODBC driver connects with the same parameters: the `grpc+tls://host:31338` endpoint, a username/password, the skip-verify option for the self-signed cert, and the `tenant` / `pool` call headers (plus `superuser=true` alongside `tenant` + `pool` for a system-realm login).

### Sample DSN

Below is a sample `odbc.ini` entry for the Dremio / Apache Arrow Flight SQL ODBC driver. Adjust the `Driver=` path and exact key names to match your driver's documentation; the *values* (host, port, encryption, call headers) are what QoD cares about.

```ini
[QoD]
Description       = Quack-on-Demand FlightSQL edge
Driver            = /opt/dremio/lib/libarrow-odbc.so   ; Windows: ArrowFlightSQLODBCDriver
HOST              = localhost
PORT              = 31338
useEncryption     = true                               ; edge TLS is on by default
disableCertificateVerification = true                  ; accepts the auto-generated self-signed cert
UID               = alice
PWD               = demo-alice
; tenant / pool / superuser are forwarded to the edge as gRPC call headers.
; Most Flight SQL ODBC drivers expose this as either a "RPCCallHeaders" /
; "CallHeaders" semicolon-separated list or as individual "header.<name>"
; keys. Pick whichever your driver supports:
RPCCallHeaders    = tenant=acme;pool=bi
; header.tenant   = acme
; header.pool     = bi
; header.superuser = true                              ; only for a system-realm login
```

And as a DSN-less connection string (same fields, one line):

```
Driver={Arrow Flight SQL ODBC Driver};HOST=localhost;PORT=31338;useEncryption=true;disableCertificateVerification=true;UID=alice;PWD=demo-alice;RPCCallHeaders=tenant=acme;pool=bi
```

Once a CA-signed cert is installed, drop `disableCertificateVerification=true`. For a bearer JWT instead of Basic, drop `UID`/`PWD` and use the driver's bearer-token option (the key varies; commonly `Token` or `BearerToken`).

## Next

- [Authenticating](/qod/connecting/authenticating) - Basic vs JWT, and TLS.
- [Supported SQL](/qod/connecting/sql) - the dialect, transactions, and querying federated catalogs.
- [MCP server](/qod/connecting/mcp) - connect AI agents with a personal access token.