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);

[james-d-elliott]

Example that can be used for advanced post-registration metadata validation:

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,
    legacy 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,
    attestation BYTEA NULL DEFAULT NULL,
    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);

[ayushs-2k4]

Example that can be used for advanced post-registration metadata validation:

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,
    legacy 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,
    attestation BYTEA NULL DEFAULT NULL,
    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);

I am still not able to implement this is postgres. I am retrieving values from DB but then my MacBook is not recognizing that a passkey is saved for "localhost", I tried to debug and I think it is due to Attestation props in red being null or 0, though I may be wrong. can you help me? like can you provide a code example how you implemented it using postgreSQL/ MySQL?

[ptman]

I seem to have started using the library before BackupEligible was exposed. 0.11.0 now fails if you don't store and restore the value.

[james-d-elliott]

I think you'd have to ask the spec authors on why it's not meant to change. I suspect the change in this flag would represent a flawed authenticator implementation but there is likely an additional security element associated.

I'd recommend to handle this that an additional value is stored to indicate the compatibility level of a credential and values are patched to reflect this. I've included an updated schema that I'm using which includes the attestation data as well as a legacy flag which could be leveraged to determine if the BE/BS flags (and legacy flag) are updated upon a login. I believe this would be possible using current methods if you parse and validate separately. If this wasn't the case then maybe we introduce tooling to allow for that.

For example adding an interface that indicates the BE/BS flags may not have been recorded at registration time:

type BackupFlagCredential interface {
    GetBackupFlagsRegistered() bool
    SetBackupFlagsRegistered()
}

[jgimenez]

I tracked down this conversation that essentially says it's to detect a flawed authenticator implementation, as you suggested: w3c/webauthn#1791

Therefore, I think it would be safe to:

• Allow the user of the library to disable checking: trivial implementation, but not so nice.
• Conditionally check the flag if stored during registration: I understand that's what you suggest. That would be spec compliant because the spec recommends but does not mandate the storage of this flag
• Trust on first check: since the BE flag is not supposed to change, we could skip validation if we don't know the value but store it for next times.

I believe this would be possible using current methods if you parse and validate separately. If this wasn't the case then maybe we introduce tooling to allow for that.

I think at the moment it's not possible to do that conditionally, because the check is an equality test:

if credential.Flags.BackupEligible != parsedResponse.Response.AuthenticatorData.Flags.HasBackupEligible() {
	return nil, protocol.ErrBadRequest.WithDetails("BackupEligible flag inconsistency detected during login validation")
}

so we could protect this check with something like:

if GetBackupFlagsRegistered() { ... }

[james-d-elliott]

Almost certain you can do something like this.

	parsedResponse, err := protocol.ParseCredentialRequestResponse(response)
	if err != nil {
		panic(err)
	}
	
	// Match the credential
	cred, err := DomainLogicGetCredentialFromUserFunc(parsedResponse, user)
	if err != nil {
		panic(err)
	}

        if cred.Legacy {
           cred.Flags.BackupEligible = parsedResponse.Response.AuthenticatorData.Flags.HasBackupEligible()
	   cred.Flags.BackupState = parsedResponse.Response.AuthenticatorData.Flags.HasBackupState()
	   cred.Legacy = false
        }	
        
        credential, err := ValidateLogin(user, session, parsedResponse)

[james-d-elliott]

That example is not strictly correct since you'd have to pre-modify the values stored in the user and have the user implementation build the []Credential slice from the known information. Essentially they'd be domain logic conerns. But the functions available currently make the concept of fixing lack of stored flags possible.

Regarding implementation I think adding some form of tooling to support something like this to migrate to proper storage is fine, the idea of baking something into the library to arbitrarily disable this check would be much more palatable for me if there were cases where proper credential storage did not specifically handle some case.

Examples of this would be an authenticator that is known to behave incorrectly particularly if that authenticator was also a certified authenticator.

[jgimenez]

That makes a lot of sense, thanks for the help!