2.4 Database Setup

How to create and configure the JSONAir database.

JSONAir uses a MySQL-compatible database to store three things: API keys (used to authenticate agents), configurations (the data agents retrieve), and, if you run the optional write-only API, write keys (used to authenticate services that change configurations). 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').

Least privilege (recommended for production)

The grant above is the simplest setup. Because each JSONAir program does a different job, each can use its own database user with only the access it needs. The read API (jsonair) never writes configuration data, so its user can be limited to:

CREATE USER 'jsonair_read'@'10.0.0.5' IDENTIFIED BY 'a-long-password';

GRANT SELECT ON jsonair.keys TO 'jsonair_read'@'10.0.0.5';
GRANT SELECT ON jsonair.configurations TO 'jsonair_read'@'10.0.0.5';
GRANT UPDATE (last_login) ON jsonair.keys TO 'jsonair_read'@'10.0.0.5';

jsonair-admin needs SELECT, INSERT, UPDATE, DELETE on configurations and SELECT on keys. jsonair-write has its own, narrower grants; see 2.9 The Write API.

---

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 three tables: keys, configurations and write_keys.

Warning: sql/jsonair.sql is written for a new database. It drops and recreates keys and configurations, which deletes their data. To upgrade an existing database, see Upgrading an Existing Database below.

---

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

The combination of uuid, type and name is unique (the idx_uuid_type_name key). There can only ever be one configuration for a given key, type and name. Trying to insert a second one fails with a duplicate-entry error (MySQL error 1062).

write_keys

Stores the keys used by the optional write-only API (jsonair-write). These are completely separate from keys: a read key is never accepted by the write API, and a write key is never accepted by the read API. See 2.9 The Write API.

Column	Type	Description
id	int	Auto-increment primary key
uuid	varchar(36)	The uuid of the configurations this key may write
name	varchar(64)	Human-readable label for the key
token	varchar(255)	HMAC-SHA256 hash of the plain-text PAT, using WRITE_TOKEN_HMAC_SECRET
allowed_types	varchar(512)	Comma separated list of type values this key may write (* for any)
allowed_names	varchar(512)	Comma separated list of name values this key may write (* for any)
created	timestamp	When the key was created
last_login	timestamp	Last successful authentication

---

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.

Other ways to add configurations: jsonair-admin (2.8) does the base64 and encryption steps for you from a web form, and jsonair-write (2.9) does the same for other services over an API. You only need to run jsonair-encrypt and INSERT by hand if you are not using either of them.

---

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

MYSQL_PORT is used to build the connection address, so a non-default port works as expected. (Older versions ignored MYSQL_PORT in jsonair; a port written directly into MYSQL_HOST, such as db.internal:3307, is still honored and takes precedence.)

---

7. Upgrading an Existing Database

Do not re-import sql/jsonair.sql into a database that already holds data. Apply only what you need:

Add the unique key on configurations (required by jsonair-write, and it prevents duplicate configurations). First look for existing duplicates:

SELECT `uuid`,`type`,`name`,COUNT(*) AS n FROM `configurations`
  GROUP BY `uuid`,`type`,`name` HAVING n > 1;

Remove or rename any rows it returns (the read API returns whichever duplicate MySQL finds first), then:

ALTER TABLE `configurations`
  ADD UNIQUE KEY `idx_uuid_type_name` (`uuid`,`type`,`name`);

Add the write_keys table (only if you will run jsonair-write): run the CREATE TABLE IF NOT EXISTS write_keys`` statement from sql/jsonair.sql. It does not touch existing tables.