[jira] [Commented] (KNOX-3401) Switch OOTB JDBC-based token state service from Derby to something else

["Sandor Molnar (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/KNOX-3401?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18111189#comment-18111189 ] 

Sandor Molnar commented on KNOX-3401:
-------------------------------------

h2. Derby replacement — embedded JDBC backend for Knox token-state + KnoxIDF services

*Context:* Apache Derby (today's embedded, auto-provisioned OOTB backend) is retired and must be
replaced. This note captures the requirements, the candidates evaluated, and the evidence behind
the recommendation. Related refactor: delete the Java-side DDL splitter/idempotency guard once the
new engine executes scripts natively.
h3. Functional requirements
|| #  ||Requirement||Notes||
|FR1|Embedded, in-process engine (zero-config OOTB default, no separate server)|Derby's current role|
|FR2|File-based persistence under ${securityDir}/tokens, survives restart|Derby parity|
|FR3|Auto-provisioning: DB + schema created on first boot, no DBA setup|Derby parity|
|FR4|*Native multi-statement DDL* — run the full delegation-policy file (5 CREATE TABLEs) in one execute(); engine-native mechanisms (Oracle-style) acceptable|Hard requirement + DDL refactor|
|FR5|Self-idempotent DDL (CREATE TABLE IF NOT EXISTS) so tableExists/normalizeIdentifier guard can be deleted|DDL refactor|
|FR6|Backs 4 services (token-state, federated-identity, trusted-OIDC, delegation-policy); per-service DDL kept separate so token-state alone never creates KnoxIDF tables|KNOX-3436 design|
|FR7|Fits existing DatabaseType -> DataSourceProvider -> *DataSourceFactory abstraction|Codebase|
h3. Non-functional requirements
|| #  ||Requirement||Notes||
|NFR1|License ASF-compatible|Cat A ideal; Cat B OK as unmodified *binary* dep only|
|NFR2|Longevity / active maintenance|The explicit "not another Derby" criterion|
|NFR3|Community / docs / support| |
|NFR4|Security posture|Backs token store + IDF (sensitive)|
|NFR5|*Option* to enable encryption at rest|Derby's was never on, but the ability to enable it was a selection factor -> must be retained|
|NFR6|Java 17 compatible|Knox runtime|
|NFR7|Adequate concurrency for single-node token writes| |
|NFR8|Migration path from existing Derby data|Follow-up, not POC|

*Scope:* HSQL is test-only. Any embedded engine is single-node — production/HA is out of scope and
stays with external Postgres/MySQL/MariaDB (already supported).
h3. Candidates evaluated
||Candidate||FR4 native multi-statement||License||Longevity||Embedded / pure-Java||Encryption option||Verdict||
|*H2*|(/) verified — runs all, idempotent|(!) MPL-2.0 / EPL-1.0 (Cat B)|(/) verified — released 2026-08-31; annual since 2021|(/) pure Java|(/) built-in AES|*LEADING*|
|SQLite (Xerial)|(x) verified — runs stmt #1 only, silently|(/) Apache-2.0 driver / PD engine|(/) best (ubiquitous, long-term)|(!) native JNI lib|(x) needs willena crypt fork|Rejected on FR4|
|HSQLDB|not tested|(/) BSD (Cat A)|(!) slow (~2yr cadence)|(/) pure Java|partial|Dropped — test-only|
|MySQL/MariaDB embedded|—|—|—|(x) no pure-Java in-process (libmysqld removed 8.0; MariaDB4j = native process wrapper)|—|Rejected — not embeddable|
|Oracle / Postgres / MySQL / MariaDB (server)|n/a|—|—|(x) external server|—|Not for OOTB — production/HA tier|
h3. Evidence gathered (verified this session)
 * *SQLite fails FR4:* merged 3-table IF NOT EXISTS script -> Xerial execute() created only table #1, *no exception* (silent partial schema). Disqualifying for a 5-table delegation file.
 * *H2 meets FR4:* same script -> all tables created; idempotent on re-run; file auto-created; RUNSCRIPT FROM also works; handles leading – license headers.
 * *H2 encryption:* CIPHER=AES encrypts at rest; wrong password rejected. Matches Derby's "enable if needed" property natively (no fork).
 * *H2 facts:* dual MPL-2.0 / EPL-1.0 (POM); Java 11+ (class major 55); latest 2.5.250 (2026-08-31, GitHub) not yet on Maven Central -> pin *2.4.240* (Sep 2025) until it syncs.
 * *H2 longevity:* steady ~annual releases since the 2.0 rewrite (2021); one prior ~2yr gap (2019–2021). [Release|https://github.com/h2database/h2database/releases#release-version-2.5.250] 3 days before this analysis
 * *H2 security:* notable RCE CVEs are in the Console/JNDI surface, not headless-embedded use — mitigated by fixed jdbc:h2:file: URL, no console/server.

h3. License finding (ASF policy)

MPL-2.0 and EPL-1.0 are both *Category B* per apache.org/legal/resolved.html. Category B works are
permitted under the {*}Binary-only Inclusion Condition{*}:
{quote}"Any Category B licensed works may be included in binary-only form in Apache Software Foundation
convenience binaries. Do not include Category B licensed works in source releases."
{quote}
Applied to Knox + H2:
 * (/) Binary-only: H2 consumed as the unmodified Maven JAR, bundled in the binary distribution.
 * (/) Not in source releases: Knox source tarball carries only the pom dependency — {*}no H2 source vendored{*}.
 * (/) Appropriately labelled: add H2 + MPL-2.0/EPL-1.0 to binary-distro LICENSE/NOTICE.
 * (/) Unmodified: stock jar as-is.
Conclusion: *license is not a blocker* — permitted as a bundled binary dependency.

h3. Recommendation

Adopt *H2* as the embedded OOTB backend replacing Derby. It is the only embeddable pure-Java engine that satisfies FR4, and it preserves Derby's at-rest-encryption option (NFR5) natively.

The single Cat-B license question is resolved: binary-JAR-only inclusion is ASF-compliant.

> Switch OOTB JDBC-based token state service from Derby to something else
> -----------------------------------------------------------------------
>
>                 Key: KNOX-3401
>                 URL: https://issues.apache.org/jira/browse/KNOX-3401
>             Project: Apache Knox
>          Issue Type: Bug
>    Affects Versions: 3.0.0
>            Reporter: Sandor Molnar
>            Assignee: Sandeep More
>            Priority: Major
>         Attachments: image-2026-07-30-07-34-16-369.png, screenshot-1.png
>
>
> The community agreed to switch from alias-based TSS to JDBC-based TSS as the default implementation due to several reasons, details are found in [this e-mail thread|https://lists.apache.org/thread/fs9nkl6l45o330ttvgvqxj3jnxt63bcs].
> At that time (late 2023), Derby DB seemed a good choice given its wide range of configs and community support. However, the project os now retired, this is what's listed on their Apache page:
> h2. Derby Retired
> *On 2025-10-10, the Derby developers voted to retire the project into a read-only state. Derby development and bug-fixing have ended. No further releases will be published. The Derby website and wiki are frozen in place. Derby JIRA issues are now read-only and new bugs cannot be logged. The user and developer lists are disabled and can be accessed only through their archives.*
>  
> Therefore, we need to find another DB vendor that satisfies our needs and can be used as Knox's default token state backend.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)