Generate GizmoSQL (JWT) Token - by GizmoData™

A utility for generating Bearer Authentication Tokens (Javascript Web Tokens - JWTs) for testing GizmoSQL (JWT) token authentication.

Setup (to run locally)

Install Python package

from PyPi

# Create the virtual environment
python3 -m venv .venv

# Activate the virtual environment
. .venv/bin/activate

# Upgrade pip
pip install --upgrade pip

# Install the package
pip install generate-gizmosql-token

from source - for development

git clone https://github.com/gizmodata/generate-gizmosql-token

cd generate-gizmosql-token

# Create the virtual environment
python3 -m venv .venv

# Activate the virtual environment
. .venv/bin/activate

# Upgrade pip, setuptools, and wheel
pip install --upgrade pip setuptools wheel

# Install the package (in editable mode)
pip install --editable .[dev]

Note

For the following commands - if you are running from source and using --editable mode (for development purposes) - you will need to set the PYTHONPATH environment variable as follows:

export PYTHONPATH=$(pwd)/src

Usage Example

generate-gizmosql-token \
  --issuer "GizmoData LLC" \
  --audience "GizmoSQL Server" \
  --subject "philip@gizmodata.com" \
  --role "admin" \
  --token-lifetime-seconds 86400 \
  --output-file-format "output/gizmosql_token_{issuer}_{audience}_{subject}_{role}.jwt" \
  --private-key-file keys/private_key.pem

Tip

If you use: --role "readonly" - you can generate a token that has read-only privileges in GizmoSQL (for the DuckDB backend only)

Catalog-Level Access Control

You can specify fine-grained catalog-level access controls using the --catalog-access option. This allows you to grant different access levels (none, read, write) to specific catalogs.

Important

Catalog-level access control requires GizmoSQL Enterprise Edition (version >= v1.15.0). This feature is not available in GizmoSQL Core. Contact sales@gizmodata.com for Enterprise licensing information.

generate-gizmosql-token \
  --issuer "GizmoData LLC" \
  --audience "GizmoSQL Server" \
  --subject "philip@gizmodata.com" \
  --role "user" \
  --catalog-access '[{"catalog": "production", "access": "read"}, {"catalog": "staging", "access": "write"}]' \
  --token-lifetime-seconds 86400 \
  --output-file-format "output/gizmosql_token_{issuer}_{audience}_{subject}_{role}.jwt" \
  --private-key-file keys/private_key.pem

Access Levels:

• none - No access to the catalog
• read - Read-only access (SELECT queries only)
• write - Full access (SELECT, INSERT, UPDATE, DELETE, DDL)

Rules:

• Rules are evaluated in order; first match wins
• Use "catalog": "*" as a wildcard to match all catalogs
• If no --catalog-access is specified, full access is granted to all catalogs (backward compatible)

Example configurations:

# Read-only access to everything
--catalog-access '[{"catalog": "*", "access": "read"}]'

# Write access to staging, read-only to everything else
--catalog-access '[{"catalog": "staging", "access": "write"}, {"catalog": "*", "access": "read"}]'

# Access only to specific catalogs, deny all others
--catalog-access '[{"catalog": "allowed_db", "access": "write"}, {"catalog": "*", "access": "none"}]'

Note

The _gizmosql_instr instrumentation database has special protection: only admin users can read it, and no one can write to it via client connections (it's system-managed). Token-based catalog_access rules do not override this protection

Using the generated token with GizmoSQL

Server setup

In order to use the JWT generated by this package, you must start the GizmoSQL server using the public certificate associated with the private key you signed the JWT with using this utility.

Below is an example of starting the GizmoSQL server with TLS and JWT authentication enabled.

Please note - you MUST use the issuer, audience, and the public certificate that matches the private key you used to sign the JWT using this utility in order for the token to be accepted by the server.

gizmosql_server                                         \
   --database-filename data/tpch.db                     \
   --username gizmosql_user                             \
   --print-queries                                      \
   --tls tls/cert0.pem tls/cert0.key                    \
   --token-allowed-issuer "GizmoData LLC"               \
   --token-allowed-audience "GizmoSQL Server"           \
   --token-signature-verify-cert-path tls/jwt.pem       \
   --log-format json                                    \
   --access-log off                                     \
   --log-level info

JDBC

You can use the generated token with GizmoSQL via JDBC by using "token" as the username, and putting the JWT in the password value:

jdbc:arrow-flight-sql://hostname:port?useEncryption=true&user=token&password=JWT_TOKEN_HERE&disableCertificateVerification=true

ADBC

You can use the generated token with GizmoSQL via ADBC using the adbc_driver_flightsql package as follows - if you have set the environment variable GIZMOSQL_TOKEN to the generated token:

import os

from adbc_driver_flightsql import dbapi as gizmosql, DatabaseOptions

with gizmosql.connect(uri="grpc+tls://localhost:31337",
                      db_kwargs={"username": "token",
                                 "password": os.getenv("GIZMOSQL_TOKEN", "BAD TOKEN!"),
                                 DatabaseOptions.TLS_SKIP_VERIFY.value: "true",
                                 },
                      autocommit=True
                      ) as conn:
    with conn.cursor() as cur:
        print(f"Catalog: {conn.adbc_current_catalog}")
        print(f"Schema: {conn.adbc_current_db_schema}")

        cur.execute("SELECT * FROM region")
        x = cur.fetch_arrow_table()
        print(x)

Handy development commands

Generate self-signed certificate and private key for testing purposes

There is a handy shell script (if you clone the repo) in scripts/gen-certs.sh that you can use to generate a self-signed certificate and private key for testing purposes.

scripts/gen-certs.sh

Version management

Bump the version of the application - (you must have installed from source with the [dev] extras)

bumpver update --patch