How to Store WebAuthn Public Keys in PostgreSQL

Storing WebAuthn COSE-encoded public keys requires a narrow but precise set of PostgreSQL schema decisions. A single column-type mismatch produces a cascade of invalid byte sequence errors, broken signature verification, and silent sign-count drift — all of which are invisible until an authentication attempt fails in production. This page covers the exact error signatures, the four root causes, and the step-by-step resolution for each. For the broader lifecycle — key rotation, AAGUID-scoped trust, and revocation — see Handling Public Key Storage and Rotation, which is the parent topic for this implementation detail within the Backend Verification and Secure Credential Storage pillar.

---

COSE Key Storage: Schema Layout

The diagram below shows how a COSE public key travels from the authenticator’s attestationObject through the RP server and into PostgreSQL, and which column stores which field.

---

Error Signatures and Reference Table

Scan this table first to match the exact error your application is producing.

---

Root Cause Analysis

1. Column type mismatch: TEXT or VARCHAR instead of BYTEA

The credential public key returned by the authenticator is a CBOR map containing raw binary octets. CBOR uses a compact binary encoding; byte values in the range 0x80–0xBF, for example, are invalid UTF-8 sequences. PostgreSQL rejects any such byte when it encounters it in a TEXT column because the database’s client_encoding is UTF8. Declaring the column BYTEA bypasses UTF-8 validation entirely — PostgreSQL stores the octets verbatim.

2. Driver or ORM implicit string conversion

Node.js ORMs and some query builders call .toString() on Buffer objects before binding parameters. The pg library itself does not — if you pass a Buffer, it binds it as \x<hex> binary. But Sequelize, TypeORM with SQLite-first drivers, and raw string interpolation all silently convert the buffer to a UTF-8 string or hex string. The symptom is either the invalid byte sequence error (if the column is BYTEA but the value arrives as a malformed UTF-8 string) or silent hex-string storage (if the column is TEXT and the value is double-encoded as a hex string, later failing signature verification).

3. Converting COSE bytes to PEM/DER at rest

Some guides instruct developers to call crypto.createPublicKey() and store the PEM output. This strips the COSE encoding and the original CBOR algorithm map, requiring re-derivation of the algorithm at verification time. If the conversion logic ever diverges from the library version used at registration, the COSE algorithm ID (alg) becomes unreliable, and signature verification will fail for a fraction of authenticators whose algorithm maps are non-standard.

4. Missing or misplaced index on credential_id

The W3C WebAuthn spec (§7.2, step 3) requires the RP to look up the credential by credentialId on every authentication assertion. Without a UNIQUE index on the credential_id BYTEA column, every authentication request performs a sequential scan. On tables with more than a few thousand credentials this degrades P99 latency measurably, and under load it creates a denial-of-service vector.

---

Step-by-Step Resolution

Step 1 — Create the correct schema from scratch

If you are starting fresh, create the table with BYTEA columns and the algorithm ID column from the outset. Store transports as a TEXT[] array (the spec allows multiple transports per credential) and always initialise sign_count from the authenticator’s authData counter field.

CREATE TABLE webauthn_credentials (
  id            UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id       UUID        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  credential_id BYTEA       NOT NULL,
  public_key    BYTEA       NOT NULL,    -- raw COSE-encoded bytes; never convert to PEM
  alg           INTEGER     NOT NULL,    -- COSE algorithm ID extracted at registration
  transports    TEXT[],                  -- e.g. ARRAY['internal','hybrid']
  sign_count    BIGINT      NOT NULL DEFAULT 0,
  aaguid        UUID,                    -- from authData, for MDS3 attestation lookup
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  last_used_at  TIMESTAMPTZ,
  revoked_at    TIMESTAMPTZ,
  CONSTRAINT uq_credential_id UNIQUE (credential_id)
);

CREATE INDEX idx_webauthn_user   ON webauthn_credentials (user_id);
CREATE INDEX idx_webauthn_alg    ON webauthn_credentials (alg);

Adding the aaguid column now pays dividends when MDS3 metadata validation is added later — the AAGUID is parsed from authData at registration time and costs nothing extra to persist.

Step 2 — Migrate existing TEXT/VARCHAR columns to BYTEA

If credentials were previously stored incorrectly, migrate with a cast. Before migrating, audit whether the current column already contains hex-escaped strings (written by a broken ORM) or raw UTF-8 fragments — the cast behaviour differs.

-- Check current storage format: count rows where the value looks like a hex string
SELECT COUNT(*) FROM webauthn_credentials
WHERE public_key::text LIKE '\\x%';

-- If the column is TEXT and contains raw escaped bytes, cast directly:
ALTER TABLE webauthn_credentials
  ALTER COLUMN public_key     TYPE BYTEA USING public_key::bytea,
  ALTER COLUMN credential_id  TYPE BYTEA USING credential_id::bytea;

-- If the column contains a hex-encoded string (e.g. 'a1624964...'), decode first:
ALTER TABLE webauthn_credentials
  ALTER COLUMN public_key TYPE BYTEA USING decode(public_key, 'hex');

Run this inside a transaction and test on a staging environment. A mismatch between the stored encoding and the cast method will produce garbage bytes and break all existing credentials.

Step 3 — Bind Buffer objects directly in Node.js

The pg driver sends Buffer objects as PostgreSQL binary parameters without any encoding conversion. Pass the raw buffer from your CBOR parser directly:

import { Pool } from 'pg';
import { decodeFirst } from 'cbor'; // or cbor-x

const pool = new Pool();

interface StoredCredential {
  id: string;
}

async function storeCredential(params: {
  userId: string;
  credentialId: Buffer;
  cosePublicKey: Buffer;   // raw bytes from authData.credentialPublicKey
  alg: number;             // parsed from COSE map key 3 at registration
  aaguid: string | null;
  transports: string[];
  signCount: number;
}): Promise<StoredCredential> {
  const { userId, credentialId, cosePublicKey, alg, aaguid, transports, signCount } = params;

  // Do NOT call cosePublicKey.toString() — pass the Buffer directly.
  // The pg driver serialises Buffer as \x<hex> bytea literal automatically.
  const result = await pool.query<StoredCredential>(
    `INSERT INTO webauthn_credentials
       (user_id, credential_id, public_key, alg, aaguid, transports, sign_count)
     VALUES ($1, $2, $3, $4, $5, $6, $7)
     ON CONFLICT (credential_id) DO NOTHING
     RETURNING id`,
    [userId, credentialId, cosePublicKey, alg, aaguid, transports, signCount]
  );

  if (!result.rows[0]) {
    throw new Error('credential_id already registered — possible replay attack');
  }
  return result.rows[0];
}

Extract alg from the COSE map at registration time, before persisting, so the algorithm is always available for verification without decoding the stored bytes:

import { decode } from 'cbor-x';

function extractCoseAlg(cosePublicKey: Buffer): number {
  // COSE key map: key 3 is the algorithm identifier (RFC 8152 §13)
  const coseMap = decode(cosePublicKey) as Map<number, unknown>;
  const alg = coseMap.get(3);
  if (typeof alg !== 'number') {
    throw new TypeError('COSE key missing algorithm identifier (map key 3)');
  }
  return alg; // -7 = ES256, -8 = EdDSA, -257 = RS256
}

Step 4 — Add algorithm allowlist before storage

Reject unsupported COSE algorithm IDs at the boundary, before any database write. This prevents storing keys whose signatures your verification library cannot process:

// COSE algorithm IDs for FIDO2-required and commonly deployed algorithms
const FIDO2_ALLOWED_ALGS = new Set<number>([-7, -8, -257]);
// -7   ES256  (P-256 + SHA-256)  — FIDO2 REQUIRED
// -8   EdDSA  (Ed25519)          — widely supported, hardware security keys
// -257 RS256  (RSA + SHA-256)    — Windows Hello, platform attestation

function assertAllowedAlg(alg: number): void {
  if (!FIDO2_ALLOWED_ALGS.has(alg)) {
    throw new RangeError(
      `COSE algorithm ${alg} is not in the RP's supported list. ` +
      `Allowed: ${[...FIDO2_ALLOWED_ALGS].join(', ')}`
    );
  }
}

Combine the allowlist check with the lookup at authentication verification to prevent cross-algorithm signature substitution attacks.

---

Verification and Testing

After applying the fixes above, run these checks to confirm correctness before deploying to production.

Confirm column types:

SELECT column_name, data_type, udt_name
FROM information_schema.columns
WHERE table_name = 'webauthn_credentials'
  AND column_name IN ('credential_id', 'public_key', 'alg', 'sign_count');
-- Expected: credential_id=bytea, public_key=bytea, alg=integer, sign_count=bigint

Confirm binary storage integrity — byte lengths for COSE keys should be non-trivial:

SELECT
  credential_id,
  octet_length(credential_id) AS cred_id_bytes,
  octet_length(public_key)    AS cose_key_bytes,
  alg
FROM webauthn_credentials
LIMIT 10;
-- ES256 P-256 keys: ~77 bytes; RS256 RSA-2048 keys: ~270 bytes
-- Hex string stored incorrectly: byte count = 2× raw length

Confirm the unique index is being used for lookup:

EXPLAIN (ANALYZE, BUFFERS)
SELECT public_key, alg, sign_count
FROM webauthn_credentials
WHERE credential_id = '\x0102030405'::bytea
  AND revoked_at IS NULL;
-- Expected plan: "Index Scan using uq_credential_id on webauthn_credentials"
-- A "Seq Scan" indicates the index is missing or the planner is bypassing it

Unit test — round-trip a known P-256 key through INSERT and SELECT:

import { randomBytes } from 'crypto';
import assert from 'assert';

async function roundTripTest(pool: Pool): Promise<void> {
  // Minimal valid ES256 COSE key structure (78 bytes, simplified for testing)
  const fakeCredId  = randomBytes(16);
  const fakeCoseKey = Buffer.from(
    'a5010203262001215820' + 'a'.repeat(64) + '225820' + 'b'.repeat(64),
    'hex'
  );

  await pool.query(
    `INSERT INTO webauthn_credentials (user_id, credential_id, public_key, alg, sign_count)
     VALUES ($1::uuid, $2, $3, $4, $5)`,
    ['00000000-0000-0000-0000-000000000001', fakeCredId, fakeCoseKey, -7, 0]
  );

  const row = await pool.query<{ public_key: Buffer }>(
    'SELECT public_key FROM webauthn_credentials WHERE credential_id = $1',
    [fakeCredId]
  );

  assert.ok(row.rows[0].public_key.equals(fakeCoseKey), 'COSE key round-trip failed');
  console.log('Round-trip OK — bytes match exactly');
}

---

Pitfalls

Storing a derived PEM representation instead of raw COSE bytes. Calling crypto.createPublicKey(coseBuffer) and persisting .export({ type: 'spki', format: 'pem' }) loses the original CBOR encoding. If the authenticator returns an algorithm not supported by your Node.js version’s SubtleCrypto, the export will fail silently or produce an incompatible encoding. Always persist the original credentialPublicKey bytes from authData.

Using sign_count INTEGER instead of BIGINT. The CTAP2 spec defines the signature counter as a 32-bit unsigned integer (max value 4,294,967,295). A PostgreSQL INTEGER is a signed 32-bit type with a max of 2,147,483,647 — approximately half the counter range. High-use authenticators (hardware tokens used thousands of times per day) can overflow a signed INTEGER in under six years. Declare the column BIGINT from the start. See the signCount verification logic for the counter-drift detection procedure.

Neglecting revoked_at in lookup queries. Always filter WHERE revoked_at IS NULL in every authentication query. A credential flagged for revocation must never participate in signature verification, even if sign_count is valid. Omitting the filter is a silent security regression that survives all unit tests that do not explicitly test the revoked-credential path.

---

Related

• Handling Public Key Storage and Rotation — parent topic covering key rotation policies, AAGUID-scoped trust configuration, and automated revocation workflows
• Implementing Credential ID Lookup at Scale — BYTEA index tuning, partial indexes for active credentials, and connection-pool configuration for high-throughput signCount updates
• Handling WebAuthn Signature Verification in Node.js — using the stored COSE key bytes and alg column to execute ES256/RS256/EdDSA signature checks
• Validating Attestation Statements on the Server — AAGUID lookup against MDS3 metadata and attestation format verification before the credential row is committed