How to store credentials in relational database (postgres / mariadb / mysql)

[uvulpos]

As a Software Engineer, I want to persist the created credential into my database, but currently I don't know what I have to persist exactly into the database and how to create the schema. Which columns are important? Is it also possible to prevent users from using the same YubiKey on different accounts, or is the browser smart enough to detect that?

[james-d-elliott]

I'll try to consolidate most of this information into a FAQ at some stage as a number of people have been asking.

1. All of the data in the webauthn.Session struct returned from various functions must be in the same state between the Begin and Finish/Validate function pairs. i.e. if you use BeginLogin the webauthn.Session it returns should be provided to the FinishLogin function with the same field values. How you achieve this is up to you.
2. The WebAuthnID implementation for the User interface should always return the same value. Generally speaking this should be an opaque value. This value is what I store in the webauthn_users table with the userid column. It should be noted there is a convenience option for this here which adapts the way this field is encoded here (the EncodeUserIDAsString field of the Config struct in the github.com/go-webauthn/webauthn/webauthn module).
3. All of the values within the webauthn.Credentials struct should realistically be saved and some should be updated in storage on each authentication. Roughly:
   1. The ID field which I store in the kid column.
   2. The PublicKey field which I store in the public_key column.
   3. The AttestationType field which I store in the attestation_type column.
   4. The Transport field which I store in the transport column.
   5. The Authenticator.AAGUID field which I store in the aaguid column.
   6. The Authenticator.SignCount field which I store in the sign_count column, this should also be updated on every successful FinishLogin/ValidateLogin.
   7. The Authenticator.CloneWraning field which should be updated on every sucessful FinishLogin/ValidateLogin.
   8. The Flags.UserPresent flag which I store in the present column.
   9. The Flags.UserVerified flag which I store in the verified column.
   10. The Flags.BackupEligible flag which I store in the backup_eligible column.
   11. The Flags.BackupState flag which should also be updated on every authentication if Flags.BackupEligible is true which I store in the backup_state column.

I also personally store the results from the credProps extensions specifically the rk value is stored in the discoverable column.

I have included a rough schema I've used for PostgreSQL below which covers 2 and 3. I personally encrypt the public_key locally in my application so that the value itself can't be tampered with unless you have the encryption key (that's optional).

CREATE TABLE IF NOT EXISTS webauthn_credentials (
    id SERIAL CONSTRAINT webauthn_credentials_pkey PRIMARY KEY,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_used_at TIMESTAMP WITH TIME ZONE NULL DEFAULT NULL,
    rpid VARCHAR(512) NOT NULL,
    username VARCHAR(100) NOT NULL,
    description VARCHAR(64) NOT NULL,
    kid VARCHAR(512) NOT NULL,
    aaguid CHAR(36) NULL,
    attestation_type VARCHAR(32),
    attachment VARCHAR(64) NOT NULL,
    transport VARCHAR(64) DEFAULT '',
    sign_count INTEGER DEFAULT 0,
    clone_warning BOOLEAN NOT NULL DEFAULT FALSE,
    discoverable BOOLEAN NOT NULL,
    present BOOLEAN NOT NULL DEFAULT FALSE,
    verified BOOLEAN NOT NULL DEFAULT FALSE,
    backup_eligible BOOLEAN NOT NULL DEFAULT FALSE,
    backup_state BOOLEAN NOT NULL DEFAULT FALSE,
    public_key BYTEA NOT NULL
);

CREATE UNIQUE INDEX webauthn_credentials_kid_key ON webauthn_credentials (kid);
CREATE UNIQUE INDEX webauthn_credentials_lookup_key ON webauthn_credentials (rpid, username, description);

CREATE TABLE IF NOT EXISTS webauthn_users (
    id SERIAL CONSTRAINT webauthn_users_pkey PRIMARY KEY,
    rpid VARCHAR(512) NOT NULL,
    username VARCHAR(100) NOT NULL,
    userid CHAR(64) NOT NULL
);

CREATE UNIQUE INDEX webauthn_users_lookup_key ON webauthn_users (rpid, username);