2.4 Database Setup

How to create and configure the JSONAir database.

JSONAir uses a MySQL-compatible database to store two things: API keys (used to authenticate agents) and configurations (the data agents retrieve). This page walks through creating the database, importing the schema, and adding your first API key.

---

1. Create the Database and User

Log into your MySQL or MariaDB server as an administrator and run the following, substituting your own values for the username and password:

CREATE DATABASE jsonair CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

CREATE USER 'jsonair'@'%' IDENTIFIED BY 'your-strong-password';

GRANT SELECT, INSERT, UPDATE, DELETE ON jsonair.* TO 'jsonair'@'%';

FLUSH PRIVILEGES;

Tip: Restrict the 'jsonair'@'%' host to the specific IP address of your JSONAir server in production (e.g., 'jsonair'@'10.0.0.5').

---

2. Import the Schema

The repository includes a ready-to-use schema file at sql/jsonair.sql. Import it into the database you just created:

mysql -u jsonair -p jsonair < sql/jsonair.sql

This creates two tables: keys and configurations.

---

3. Table Overview

keys

Stores API keys used by agents to authenticate with the JSONAir server.

Column	Type	Description
id	int	Auto-increment primary key
uuid	varchar(36)	Unique identifier for this key (UUID v4)
name	varchar(64)	Human-readable label for the key
token	varchar(255)	HMAC-SHA256 hash of the plain-text PAT
created	timestamp	When the key was created
last_login	timestamp	Last successful authentication

configurations

Stores the configuration data that agents retrieve.

Column	Type	Description
id	int	Auto-increment primary key
uuid	varchar(36)	The key UUID this configuration belongs to
type	varchar(128)	Configuration type (e.g. suricata, nginx)
name	varchar(127)	Configuration name (e.g. prod.yaml)
reload	varchar(255)	Reload trigger key/value
debug	varchar(128)	Debug level or flag
config_data	mediumtext	The configuration data, AES-256-GCM encrypted and Base64-encoded (up to 16 MB)
created	timestamp	When the record was created
updated	timestamp	When the record was last updated

---

4. Adding an API Key

JSONAir never stores plain-text tokens. The token column holds an HMAC-SHA256 hash of the plain-text PAT, computed using the server's TOKEN_HMAC_SECRET. This means even if the database is compromised, the actual tokens cannot be recovered.

Step 1 — Choose a plain-text PAT

This is the token value that will be placed in the agent's JSONAIR_PAT environment variable. It should be a long, random string. For example:

openssl rand -hex 32

Step 2 — Hash the token

Using the same TOKEN_HMAC_SECRET that your JSONAir server is configured with, compute the HMAC-SHA256 hash:

echo -n "YOURTOKEN" | openssl dgst -sha256 -hmac "YOUR_TOKEN_HMAC_SECRET"

Example output:

SHA2-256(stdin)= a3f1c2d4e5b6...

Take only the hex string after the = — that is the value you insert into the token column.

Step 3 — Generate a UUID

uuidgen | tr '[:upper:]' '[:lower:]'

Step 4 — Insert the key

INSERT INTO `keys` (`uuid`, `name`, `token`, `created`, `last_login`)
VALUES (
  'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx',
  'My Agent Key',
  'a3f1c2d4e5b6...',
  NOW(),
  NOW()
);

---

5. Adding a Configuration

Configuration data is encrypted at rest using AES-256-GCM. Before inserting a row, you must Base64-encode the raw configuration and then encrypt it using the jsonair-encrypt tool. The server will transparently decrypt it when an agent requests it.

See 2.7 Encrypting Configuration Data for full detail on the encryption tool and key management.

Step 1 — Base64-encode your configuration

base64 -i /path/to/your/config.yaml

Or inline for a small value:

echo -n '{"key":"value"}' | base64

Step 2 — Encrypt the Base64 output

Pipe the Base64 string into jsonair-encrypt with your CONFIG_ENCRYPT_SECRET set:

echo -n "base64encodedcontenthere" | CONFIG_ENCRYPT_SECRET=your-secret ./jsonair-encrypt

The output is the encrypted value ready for the database.

Step 3 — Insert the configuration

INSERT INTO `configurations` (`uuid`, `type`, `name`, `reload`, `debug`, `config_data`, `created`, `updated`)
VALUES (
  'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx',
  'myapp',
  'prod.yaml',
  'RELOADKEY',
  'INFO',
  '<output from jsonair-encrypt>',
  NOW(),
  NOW()
);

The uuid must match the uuid of the API key that should be allowed to retrieve this configuration.

---

6. Environment Variables

Once the database is ready, configure the JSONAir server with the following environment variables:

Variable	Description
MYSQL_USERNAME	Database user
MYSQL_PASSWORD	Database password
MYSQL_DATABASE	Database name
MYSQL_HOST	Database hostname or IP
MYSQL_PORT	Database port (typically 3306)
MYSQL_TLS	Set to true to enable TLS for the DB connection
MYSQL_TLS_SKIP_VERIFY	Set to true to disable certificate verification (not recommended for production)
TOKEN_HMAC_SECRET	The secret used to HMAC-SHA256 hash PATs — must match what was used when inserting keys