WebAuthnCredential.java uses non portable BLOB ColumnDefinition

[JesseNethack]

For passkey support, the user_credentials table is created, however some entity fields are explicitly mapped to the SQL type BLOB which doesn't exist in certain dialects.
e.g. PostgreSql:

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "
    create table public.user_credentials (
        credential_id varchar(512) not null,
        attestation_client_data_json BLOB,
        attestation_object BLOB,
        authenticator_transports varchar(1000),
        backup_eligible boolean not null,
        backup_state boolean not null,
        created timestamp(6) with time zone not null,
        label varchar(64) not null,
        last_used timestamp(6) with time zone,
        public_key BLOB not null,
        public_key_credential_type varchar(100),
        signature_count bigint not null,
        uv_initialized boolean not null,
        user_entity_user_id varchar(255) not null,
        primary key (credential_id)
    )" via JDBC [ERROR: type "blob" does not exist  Position: 127]

Issue seems to be due to the explicit ColumnDefinition in the entity model which bypasses the SQL dialect.

[devondragon]

Thanks for reporting this @JesseNethack!

The root cause is exactly as you identified — the three byte[] fields in WebAuthnCredential.java (publicKey, attestationObject, attestationClientDataJson) all have columnDefinition = "BLOB" hardcoded in their @Column annotations. This bypasses Hibernate's dialect translation, so PostgreSQL (and any other non-MySQL database) receives raw BLOB in the DDL, which it doesn't understand.

The fix is to simply remove the columnDefinition = "BLOB" from all three fields. The @Lob annotation already present on each field is the proper JPA way to indicate large binary data — Hibernate will then map it to the correct type for each dialect (BLOB for MySQL, bytea for PostgreSQL, etc.).

PR incoming shortly.

[devondragon]

Update: deeper investigation and revised fix

After testing the initial fix (removing columnDefinition = "BLOB" while keeping @Lob) against a real PostgreSQL 16 instance, we discovered that @Lob itself is also non-portable in Hibernate 7. Here's what we found:

The Hibernate/PostgreSQL pain point

This is a well-known issue in the Hibernate ecosystem. When you annotate a byte[] field with @Lob, Hibernate 6+/7 generates literal blob in the DDL — it does not go through dialect translation. PostgreSQL has no blob type (it uses bytea for binary data), so the DDL fails regardless of whether columnDefinition is present.

We tested several approaches against PostgreSQL 16:

Approach	DDL Generated	PostgreSQL?	MySQL?
@Lob @Column(columnDefinition = "BLOB")	BLOB	❌	✅
@Lob @Column(...) (no columnDefinition)	blob	❌	✅
@JdbcTypeCode(SqlTypes.BLOB)	blob	❌	✅
Plain byte[] with @Column(length = N)	bytea / varbinary(N)	✅	✅

Our solution

We removed both @Lob and columnDefinition = "BLOB", and use plain byte[] with explicit @Column(length = ...):

• publicKey: length = 2048 (COSE keys are typically 77–300 bytes, RSA up to ~1KB)
• attestationObject / attestationClientDataJson: length = 65536 (can be several KB)

This lets each Hibernate dialect choose the correct native type:

• PostgreSQL → bytea
• MySQL → varbinary(N) or mediumblob (depending on length)
• H2 → varbinary(N)

Verification

• ✅ DDL generated and executed successfully against PostgreSQL 16 (Docker)
• ✅ Full existing test suite passes on H2 with no regressions

PR #275 has been updated with this revised approach.

[devondragon]

Correction to the above analysis

After further testing, I need to correct my earlier comment. The @Lob failures we initially saw were caused by the test application hardcoding H2Dialect, not by @Lob being broken with PostgreSQLDialect. Once we properly overrode the dialect, here's what we actually observed:

Approach	PostgreSQL DDL	Type	Works?
@Lob @Column(columnDefinition = "BLOB")	BLOB	—	❌ columnDefinition bypasses dialect
@Lob @Column(...) (no columnDefinition)	oid	Large object reference	✅ but uses pg_largeobject table
Plain byte[] with @Column(length = N)	bytea	Inline binary	✅ simple inline storage

So @Lob does work on PostgreSQL with the correct dialect — it maps to oid (PostgreSQL's large object system). However, oid stores data in a separate pg_largeobject table with reference pointers, which adds complexity:

• Requires all access to happen within a transaction
• Orphaned large objects need separate cleanup (vacuumlo)
• Overkill for WebAuthn data that's typically < 64KB

Our fix uses plain byte[] with explicit @Column(length = ...) instead, which maps to bytea on PostgreSQL (inline binary storage) and varbinary/mediumblob on MySQL. This is simpler, fully portable, and a better fit for the data sizes involved.

The original columnDefinition = "BLOB" was the actual bug — it hardcodes a MySQL-specific type that bypasses Hibernate's dialect system entirely.

[devondragon]

This should be fixed in version 4.2.2, which was just released.

[JesseNethack]

Thanks for the quick turnaround. I can confirm 4.2.2 solved my issue.