JDBC Connector V2 — Quick Start, Configuration, Build, Run, Test

This project is a Spring Boot based JDBC connector that exposes REST endpoints for managing Accounts and Entitlements in any relational backend accessible via JDBC (e.g., Postgres, MySQL, CSV via JDBC). It mirrors the structure of our LDAP Connector V2 guide, adapted for JDBC.

By default, when required IDHub settings are not supplied, the security config logs "Bypassing security for endpoint" and permits all requests. See the Security section for enabling JWT.

---

Quick Start (Local Dev)

1. Ensure you are in the project directory and using the Gradle wrapper (no local Gradle install required).

2. Export essential runtime variables (see Environment Setup below).

3. Build and run in dev mode:

   ./gradlew bootRun
   

4. Open API docs in your browser (after the app starts):

   • Swagger UI: http://localhost:9090/jdbc-connector/swagger-ui/index.html
   • OpenAPI YAML (served to Swagger UI): hhttp://localhost:9090/jdbc-connector/jdbc_openapi.yml

5. Health check:

   curl http://localhost:9090/jdbc-connector/actuator/health
   

---

Configuration

Runtime configuration is provided primarily via environment variables that map to application.yml properties.

Important: The connector decrypts encrypted credentials using PASSPHRASE and SALT. Provide Base64 Salted__ OpenSSL values for DB user and password.

IDHub / Tenant settings (idhub.config.*)

Environment variable	Maps to
IDHUB_HOST	idhub.config.host-name
TENANT_REALM	idhub.config.tenant-realm
IDHUB_CLIENT_ID	idhub.config.service-account-username
IDHUB_CLIENT_SECRET	idhub.config.service-account-secret-ENC
CONNECTOR_SLUG	idhub.config.connector-slug
TENANT	idhub.config.tenant-name

Static values in application.yml:

• idhub-realm-client-id: Connector
• tenant-realm-client-id: public

Connector / JDBC settings (connector.config.*)

Environment variable	Maps to	Notes
DB_URL	connector.config.db-URL	e.g., jdbc:postgresql://localhost:5432/mydb or jdbc:mysql://localhost:3306/mydb
DB_USER_NAME_ENC	connector.config.db-user-name-ENC	Base64 OpenSSL Salted__ string
DB_PASSWORD_ENC	connector.config.db-password-ENC	Base64 OpenSSL Salted__ string
DB_DRIVER_CLASS	connector.config.db-driver-class	e.g., org.postgresql.Driver, com.mysql.cj.jdbc.Driver, cdata.jdbc.csv.CSVDriver
DB_DRIVER_LOCATION	connector.config.db-driver-location	Absolute path to JDBC driver JAR
BUSINESS_OWNER	connector.config.business-owner	Metadata only
IT_OWNER	connector.config.it-owner	Metadata only
ACCOUNT_SCHEMA_PATH	connector.config.account-schema-file	Absolute path or use defaults (see below)
ENTITLEMENT_SCHEMA_PATH	connector.config.entitlement-schema-file	Absolute path or use defaults

Schema defaults are not hardcoded; sample files are provided under default-configs/<flavor>/.

General

Environment variable	Default
CONFIG_PATH	(none) — recommend pointing to default-configs or subfolder
PASSPHRASE	test-passphrase-2026
SALT	8f623a9d4b1c7e0f
OLD_PASSPHRASE	test-passphrase-2026

Server settings (can be overridden)

• server.port: 9090
• server.servlet.context-path: /jdbc-connector

See:

• src/main/resources/application.yml

---

Environment Setup Examples

Below are minimal environment sets for common backends. Adjust paths to your local checkout.

Replace /abs/path/to/project with your machine’s absolute path to this repo root.

Postgres example

export DB_URL="jdbc:postgresql://localhost:5432/idhub"
export DB_DRIVER_CLASS="org.postgresql.Driver"
export DB_DRIVER_LOCATION="/abs/path/to/project/default-configs/postgres/lib/postgresql-42.7.13.jar"

# Symmetric crypto parameters
export PASSPHRASE="test-passphrase-2026"
export SALT="8f623a9d4b1c7e0f"

# Encrypt DB user/password with OpenSSL (example) and export Base64 values
# echo -n "dbuser" | openssl enc -aes-256-cbc -md sha256 -pbkdf2 -iter 10000 -pass env:PASSPHRASE -S ${SALT} | base64
# echo -n "dbpass" | openssl enc -aes-256-cbc -md sha256 -pbkdf2 -iter 10000 -pass env:PASSPHRASE -S ${SALT} | base64
export DB_USER_NAME_ENC="<base64-username>"
export DB_PASSWORD_ENC="<base64-password>"

# Schema and config
export ACCOUNT_SCHEMA_PATH="/abs/path/to/project/default-configs/postgres/Account.json"
export ENTITLEMENT_SCHEMA_PATH="/abs/path/to/project/default-configs/postgres/Entitlement.json"
export CONFIG_PATH="/abs/path/to/project/default-configs"

# Optional IDHub (leave empty to bypass security locally)
export IDHUB_HOST=""
export TENANT_REALM=""

./gradlew bootRun

MySQL example

export DB_URL="jdbc:mysql://localhost:3306/idhub?useSSL=false&allowPublicKeyRetrieval=true"
export DB_DRIVER_CLASS="com.mysql.cj.jdbc.Driver"
export DB_DRIVER_LOCATION="/abs/path/to/project/default-configs/mysql/lib/mysql-connector-j-9.2.0.jar"

export PASSPHRASE="test-passphrase-2026"
export SALT="8f623a9d4b1c7e0f"
export DB_USER_NAME_ENC="<base64-username>"
export DB_PASSWORD_ENC="<base64-password>"
export ACCOUNT_SCHEMA_PATH="/abs/path/to/project/default-configs/mysql/Account.json"
export ENTITLEMENT_SCHEMA_PATH="/abs/path/to/project/default-configs/mysql/Entitlement.json"
export CONFIG_PATH="/abs/path/to/project/default-configs"

./gradlew bootRun

CSV (via JDBC driver) example

export DB_URL="jdbc:csv:"
export DB_DRIVER_CLASS="cdata.jdbc.csv.CSVDriver"
export DB_DRIVER_LOCATION="/abs/path/to/project/default-configs/csv/lib/cdata.jdbc.csv.jar"

export PASSPHRASE="test-passphrase-2026"
export SALT="8f623a9d4b1c7e0f"
export DB_USER_NAME_ENC="<base64-username-or-empty>"
export DB_PASSWORD_ENC="<base64-password-or-empty>"
export ACCOUNT_SCHEMA_PATH="/abs/path/to/project/default-configs/csv/Account.json"
export ENTITLEMENT_SCHEMA_PATH="/abs/path/to/project/default-configs/csv/Entitlement.json"
export CONFIG_PATH="/abs/path/to/project/default-configs"

./gradlew bootRun

---

Build

Requested build flow (compile first, then package while skipping tests):

1. Clean and compile the code:

./gradlew clean compileJava

2. Package into a runnable jar (skip tests):

./gradlew build -x test

The jar is created under build/libs/ (e.g., build/libs/jdbc-connector-v2-0.0.1-SNAPSHOT.jar).

Other useful build commands:

• Full build with tests:

./gradlew clean build

• Produce an executable jar directly:

./gradlew bootJar

---

Run

You can run via Gradle (dev) or from the built jar (prod-like).

Dev mode (auto compile on change):

./gradlew bootRun

Run the jar (after building):

java -jar build/libs/*.jar \
  --server.port=9090 \
  --server.servlet.context-path=/jdbc-connector

---

Security

Security is conditionally enforced based on IDHub settings.

• If either IDHUB_HOST or TENANT_REALM is set, JWT auth is enabled for:

  • /actuator/**, /resources/**, /config/**, /metadata/**
  • Issuers are derived as:
    • Admin: ${IDHUB_HOST}/auth/realms/IDHub
    • Tenant: ${IDHUB_HOST}/auth/realms/${TENANT_REALM}
  • Provide Authorization: Bearer <jwt> and Tenant: <tenantName> headers when calling protected endpoints.

• If both are empty, the app logs "Bypassing security for endpoint" and permits all requests (useful for local dev).

CORS is open by default for development (all origins, common headers/methods).

---

API Endpoints (base path: /jdbc-connector)

• Account

  • POST /resources/account — Create account
  • GET /resources/account/{id} — Get account by ID
  • PATCH /resources/account/{id} — Patch account by ID
  • GET /resources/account?filter=...&size=...&page=...&token=... — Search accounts
  • POST /resources/account/AsyncMethodCall — Asynchronous search callback initiation

• Entitlement

  • GET /resources/entitlement/{id} — Get entitlement by ID
  • GET /resources/entitlement?filter=...&size=...&page=...&token=... — Search entitlements
  • POST /resources/entitlement/AsyncMethodCall — Asynchronous search callback initiation

• Metadata

  • GET /metadata/schema — List schemas
  • GET /metadata/schema/account — Account schema
  • GET /metadata/schema/entitlement — Entitlement schema
  • GET /metadata/resourceTypes — List resource types
  • GET /metadata/resourceTypes/account — Account resource type
  • GET /metadata/resourceTypes/entitlement — Entitlement resource type

• Health/Actuator

  • GET /actuator/health

Swagger UI: http://localhost:9090/jdbc-connector/swagger-ui/index.html

---

Example Requests

Assuming security is enforced and you have a valid IDHub JWT.

Create account:

curl --location 'http://localhost:9090/jdbc-connector/resources/account' \
  --header 'Authorization: Bearer <redacted-jwt>' \
  --header 'Content-Type: application/json' \
  --header 'Tenant: <tenant-realm>' \
  --data-raw '{
    "mail": "user@example.com",
    "sAMAccountName": "user1",
    "displayName": "User One",
    "givenName": "User",
    "sn": "One",
    "department": "Engineering"
  }'

Get account:

curl --location --request GET 'http://localhost:9090/jdbc-connector/resources/account/123' \
  --header 'Accept: application/json' \
  --header 'Authorization: Bearer <redacted-jwt>' \
  --header 'Tenant: <tenant-realm>'

Search entitlements:

curl --location 'http://localhost:9090/jdbc-connector/resources/entitlement?filter=name%20co%20"admin"&size=50&page=1' \
  --header 'Authorization: Bearer <redacted-jwt>' \
  --header 'Tenant: <tenant-realm>'

---

Test

1. Optional: override defaults for tests if needed via environment variables.

Recommended (align with crypto params used in Java decryption):

export PASSPHRASE="test-passphrase-2026"
export SALT="8f623a9d4b1c7e0f"

2. Run all unit tests:

./gradlew clean test

Tips:

• Run a single test class:

./gradlew test --tests "com.sath.idhub.jdbc.test.AccountControllerTest"

• Test report: build/reports/tests/test/index.html

---

Troubleshooting

• ClassNotFoundException for JDBC driver

  • Ensure DB_DRIVER_CLASS matches your driver and DB_DRIVER_LOCATION points to a readable JAR.
  • Sample drivers are included:
    • Postgres: default-configs/postgres/lib/postgresql-42.7.13.jar
    • MySQL: default-configs/mysql/lib/mysql-connector-j-9.2.0.jar
    • CSV: default-configs/csv/lib/cdata.jdbc.csv.jar (+ license file as needed)

• Decryption fails for DB credentials

  • Verify PASSPHRASE and SALT match those used when creating your DB_USER_NAME_ENC and DB_PASSWORD_ENC values.
  • Use the OpenSSL command shown above to regenerate Base64 Salted__ ciphertexts.

• 401 Unauthorized

  • Ensure IDHUB_HOST and TENANT_REALM are correctly set (to enable JWT).
  • Provide Authorization: Bearer <jwt> and Tenant: <tenant> headers for protected endpoints.

• Swagger UI not loading spec

  • The UI loads /jdbc_openapi.yml. Verify it is present at the app’s context path: /jdbc-connector/jdbc_openapi.yml.

---

Repository Layout

• default-configs/<flavor>/Account.json, Entitlement.json — Sample schema files per backend (csv, mysql, postgres)
• default-configs/<flavor>/lib/ — Place (or use provided) JDBC driver JARs
• src/main/resources/application.yml — Base configuration and property mappings
• src/main/java/com/sath/idhub/jdbc/controller/* — REST controllers
• src/test/java/com/sath/idhub/jdbc/test/* — Tests

---

Notes on SQL Safety

The service executes SQL via PreparedStatement. Query builder components still interpolate values into SQL strings before preparation; migrating them to parameterized placeholders with bound variables would further harden security.