[GH-ISSUE #7133] SQLite→PostgreSQL migration via pgloader: BIGINT/INTEGER type mismatch causes silent login failure for all users #30114

New Issue

GiteaMirror · 2026-06-17T09:50:51-05:00

GiteaMirror commented

2026-06-17 09:50:51 -05:00

Originally created by @jp1337 on GitHub (Apr 25, 2026).
Original GitHub issue: https://github.com/dani-garcia/vaultwarden/issues/7133

Summary

When migrating from SQLite to PostgreSQL using pgloader (as recommended in the Vaultwarden wiki), pgloader maps all SQLite INTEGER columns to PostgreSQL BIGINT (8 bytes). However, Vaultwarden's Diesel schema defines these columns as i32, which maps to PostgreSQL INTEGER (4 bytes).

This type mismatch causes Diesel to silently crash when deserializing any user row, which means:

Every login attempt fails with "Username or password is incorrect" — even with the correct password
The Admin Panel /admin/users/overview returns a 500 error
No obvious error is shown to the user or in default logs

The failure is completely silent at INFO log level and extremely difficult to diagnose.

Root Cause

pgloader converts SQLite INTEGER → PostgreSQL BIGINT by default. When Diesel reads a BIGINT (8 bytes) into a Rust i32 (4 bytes), it panics internally:

Received more than 4 bytes while decoding an i32. Was an BigInt expression accidentally marked as Integer?

This panic causes find_by_mail to return None, which Vaultwarden treats identically to "user not found" — producing the generic "Username or password is incorrect" message.

The panic is only visible at RUST_LOG=debug level. At the default log level, logins silently fail for every user.

Affected Columns (28 across 11 tables)

All of these are mapped as i32 in Diesel but pgloader creates them as BIGINT:

Table	Column
`users`	`login_verify_count`, `client_kdf_type`, `client_kdf_iter`, `password_iterations`, `client_kdf_memory`, `client_kdf_parallelism`
`attachments`	`file_size`
`auth_requests`	`device_type`
`ciphers`	`atype`, `reprompt`
`devices`	`atype`
`emergency_access`	`atype`, `status`, `wait_time_days`
`event`	`device_type`, `event_type`
`org_policies`	`atype`
`organization_api_key`	`atype`
`sends`	`access_count`, `atype`, `max_access_count`, `password_iter`
`twofactor`	`atype`, `last_used`
`twofactor_duo_ctx`	`exp`
`twofactor_incomplete`	`device_type`
`users_organizations`	`atype`, `status`

Fix

After running pgloader, apply this SQL to correct the column types:

-- users table (login breaks without this)
ALTER TABLE users
  ALTER COLUMN login_verify_count TYPE INTEGER USING login_verify_count::INTEGER,
  ALTER COLUMN client_kdf_type TYPE INTEGER USING client_kdf_type::INTEGER,
  ALTER COLUMN client_kdf_iter TYPE INTEGER USING client_kdf_iter::INTEGER,
  ALTER COLUMN password_iterations TYPE INTEGER USING password_iterations::INTEGER,
  ALTER COLUMN client_kdf_memory TYPE INTEGER USING client_kdf_memory::INTEGER,
  ALTER COLUMN client_kdf_parallelism TYPE INTEGER USING client_kdf_parallelism::INTEGER;

-- remaining tables
ALTER TABLE attachments ALTER COLUMN file_size TYPE INTEGER USING file_size::INTEGER;
ALTER TABLE auth_requests ALTER COLUMN device_type TYPE INTEGER USING device_type::INTEGER;
ALTER TABLE ciphers ALTER COLUMN atype TYPE INTEGER USING atype::INTEGER;
ALTER TABLE ciphers ALTER COLUMN reprompt TYPE INTEGER USING reprompt::INTEGER;
ALTER TABLE devices ALTER COLUMN atype TYPE INTEGER USING atype::INTEGER;
ALTER TABLE emergency_access ALTER COLUMN atype TYPE INTEGER USING atype::INTEGER;
ALTER TABLE emergency_access ALTER COLUMN status TYPE INTEGER USING status::INTEGER;
ALTER TABLE emergency_access ALTER COLUMN wait_time_days TYPE INTEGER USING wait_time_days::INTEGER;
ALTER TABLE event ALTER COLUMN device_type TYPE INTEGER USING device_type::INTEGER;
ALTER TABLE event ALTER COLUMN event_type TYPE INTEGER USING event_type::INTEGER;
ALTER TABLE org_policies ALTER COLUMN atype TYPE INTEGER USING atype::INTEGER;
ALTER TABLE organization_api_key ALTER COLUMN atype TYPE INTEGER USING atype::INTEGER;
ALTER TABLE sends ALTER COLUMN access_count TYPE INTEGER USING access_count::INTEGER;
ALTER TABLE sends ALTER COLUMN atype TYPE INTEGER USING atype::INTEGER;
ALTER TABLE sends ALTER COLUMN max_access_count TYPE INTEGER USING max_access_count::INTEGER;
ALTER TABLE sends ALTER COLUMN password_iter TYPE INTEGER USING password_iter::INTEGER;
ALTER TABLE twofactor ALTER COLUMN atype TYPE INTEGER USING atype::INTEGER;
ALTER TABLE twofactor ALTER COLUMN last_used TYPE INTEGER USING last_used::INTEGER;
ALTER TABLE twofactor_duo_ctx ALTER COLUMN exp TYPE INTEGER USING exp::INTEGER;
ALTER TABLE twofactor_incomplete ALTER COLUMN device_type TYPE INTEGER USING device_type::INTEGER;
ALTER TABLE users_organizations ALTER COLUMN atype TYPE INTEGER USING atype::INTEGER;
ALTER TABLE users_organizations ALTER COLUMN status TYPE INTEGER USING status::INTEGER;

Suggested Fixes (for maintainers)

There are two good approaches:

Option A — Fix the pgloader config in the wiki

Add CAST overrides to the pgloader command in the migration documentation:

CAST type bigint to integer drop typemod

This tells pgloader to use INTEGER instead of BIGINT for all integer columns.

Option B — Add a post-migration SQL script

Include the SQL above as a post-migration.sql script in the wiki or repository, to be run immediately after pgloader.

Option C — Make Diesel schema explicit

Add explicit CAST or migration guards so mismatched types are caught at startup rather than silently failing at query time.

Impact

All users migrating from SQLite → PostgreSQL via pgloader following the official wiki instructions will hit this
The failure mode (silent "wrong password" for every user) is devastating: the password manager becomes completely inaccessible
No data is lost — only the schema types are wrong — but recovery requires database access and knowledge of the root cause
This is especially dangerous in production/corporate environments where the admin might not have easy PostgreSQL access

Environment

Vaultwarden: 1.35.8
PostgreSQL: 16
pgloader: 3.6.x
Migration command: standard pgloader sqlite:///data/db.sqlite3 postgresql://... as documented in wiki

Originally created by @jp1337 on GitHub (Apr 25, 2026). Original GitHub issue: https://github.com/dani-garcia/vaultwarden/issues/7133 ## Summary When migrating from SQLite to PostgreSQL using `pgloader` (as recommended in the [Vaultwarden wiki](https://github.com/dani-garcia/vaultwarden/wiki/Using-the-PostgreSQL-Backend)), pgloader maps **all SQLite `INTEGER` columns to PostgreSQL `BIGINT` (8 bytes)**. However, Vaultwarden's Diesel schema defines these columns as `i32`, which maps to PostgreSQL `INTEGER` (4 bytes). This type mismatch causes **Diesel to silently crash** when deserializing any user row, which means: - **Every login attempt fails** with "Username or password is incorrect" — even with the correct password - The Admin Panel `/admin/users/overview` returns a 500 error - No obvious error is shown to the user or in default logs The failure is completely silent at `INFO` log level and extremely difficult to diagnose. --- ## Root Cause `pgloader` converts SQLite `INTEGER` → PostgreSQL `BIGINT` by default. When Diesel reads a `BIGINT` (8 bytes) into a Rust `i32` (4 bytes), it panics internally: ``` Received more than 4 bytes while decoding an i32. Was an BigInt expression accidentally marked as Integer? ``` This panic causes `find_by_mail` to return `None`, which Vaultwarden treats identically to "user not found" — producing the generic "Username or password is incorrect" message. The panic is only visible at `RUST_LOG=debug` level. At the default log level, logins silently fail for every user. --- ## Affected Columns (28 across 11 tables) All of these are mapped as `i32` in Diesel but pgloader creates them as `BIGINT`: | Table | Column | |-------|--------| | `users` | `login_verify_count`, `client_kdf_type`, `client_kdf_iter`, `password_iterations`, `client_kdf_memory`, `client_kdf_parallelism` | | `attachments` | `file_size` | | `auth_requests` | `device_type` | | `ciphers` | `atype`, `reprompt` | | `devices` | `atype` | | `emergency_access` | `atype`, `status`, `wait_time_days` | | `event` | `device_type`, `event_type` | | `org_policies` | `atype` | | `organization_api_key` | `atype` | | `sends` | `access_count`, `atype`, `max_access_count`, `password_iter` | | `twofactor` | `atype`, `last_used` | | `twofactor_duo_ctx` | `exp` | | `twofactor_incomplete` | `device_type` | | `users_organizations` | `atype`, `status` | --- ## Fix After running `pgloader`, apply this SQL to correct the column types: ```sql -- users table (login breaks without this) ALTER TABLE users ALTER COLUMN login_verify_count TYPE INTEGER USING login_verify_count::INTEGER, ALTER COLUMN client_kdf_type TYPE INTEGER USING client_kdf_type::INTEGER, ALTER COLUMN client_kdf_iter TYPE INTEGER USING client_kdf_iter::INTEGER, ALTER COLUMN password_iterations TYPE INTEGER USING password_iterations::INTEGER, ALTER COLUMN client_kdf_memory TYPE INTEGER USING client_kdf_memory::INTEGER, ALTER COLUMN client_kdf_parallelism TYPE INTEGER USING client_kdf_parallelism::INTEGER; -- remaining tables ALTER TABLE attachments ALTER COLUMN file_size TYPE INTEGER USING file_size::INTEGER; ALTER TABLE auth_requests ALTER COLUMN device_type TYPE INTEGER USING device_type::INTEGER; ALTER TABLE ciphers ALTER COLUMN atype TYPE INTEGER USING atype::INTEGER; ALTER TABLE ciphers ALTER COLUMN reprompt TYPE INTEGER USING reprompt::INTEGER; ALTER TABLE devices ALTER COLUMN atype TYPE INTEGER USING atype::INTEGER; ALTER TABLE emergency_access ALTER COLUMN atype TYPE INTEGER USING atype::INTEGER; ALTER TABLE emergency_access ALTER COLUMN status TYPE INTEGER USING status::INTEGER; ALTER TABLE emergency_access ALTER COLUMN wait_time_days TYPE INTEGER USING wait_time_days::INTEGER; ALTER TABLE event ALTER COLUMN device_type TYPE INTEGER USING device_type::INTEGER; ALTER TABLE event ALTER COLUMN event_type TYPE INTEGER USING event_type::INTEGER; ALTER TABLE org_policies ALTER COLUMN atype TYPE INTEGER USING atype::INTEGER; ALTER TABLE organization_api_key ALTER COLUMN atype TYPE INTEGER USING atype::INTEGER; ALTER TABLE sends ALTER COLUMN access_count TYPE INTEGER USING access_count::INTEGER; ALTER TABLE sends ALTER COLUMN atype TYPE INTEGER USING atype::INTEGER; ALTER TABLE sends ALTER COLUMN max_access_count TYPE INTEGER USING max_access_count::INTEGER; ALTER TABLE sends ALTER COLUMN password_iter TYPE INTEGER USING password_iter::INTEGER; ALTER TABLE twofactor ALTER COLUMN atype TYPE INTEGER USING atype::INTEGER; ALTER TABLE twofactor ALTER COLUMN last_used TYPE INTEGER USING last_used::INTEGER; ALTER TABLE twofactor_duo_ctx ALTER COLUMN exp TYPE INTEGER USING exp::INTEGER; ALTER TABLE twofactor_incomplete ALTER COLUMN device_type TYPE INTEGER USING device_type::INTEGER; ALTER TABLE users_organizations ALTER COLUMN atype TYPE INTEGER USING atype::INTEGER; ALTER TABLE users_organizations ALTER COLUMN status TYPE INTEGER USING status::INTEGER; ``` --- ## Suggested Fixes (for maintainers) There are two good approaches: **Option A — Fix the pgloader config in the wiki** Add `CAST` overrides to the pgloader command in the migration documentation: ``` CAST type bigint to integer drop typemod ``` This tells pgloader to use `INTEGER` instead of `BIGINT` for all integer columns. **Option B — Add a post-migration SQL script** Include the SQL above as a `post-migration.sql` script in the wiki or repository, to be run immediately after `pgloader`. **Option C — Make Diesel schema explicit** Add explicit `CAST` or migration guards so mismatched types are caught at startup rather than silently failing at query time. --- ## Impact - All users migrating from SQLite → PostgreSQL via `pgloader` following the official wiki instructions will hit this - The failure mode (silent "wrong password" for every user) is devastating: the password manager becomes completely inaccessible - No data is lost — only the schema types are wrong — but recovery requires database access and knowledge of the root cause - This is especially dangerous in production/corporate environments where the admin might not have easy PostgreSQL access ## Environment - Vaultwarden: 1.35.8 - PostgreSQL: 16 - pgloader: 3.6.x - Migration command: standard `pgloader sqlite:///data/db.sqlite3 postgresql://...` as documented in wiki

GiteaMirror closed this issue

2026-06-17 09:50:53 -05:00

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: github-starred/vaultwarden#30114