Unable to re-register a security key (webauthn_credential_pkey constraint)

[pilou-]

Gitea Version

1.16.3

Git Version

2.30.2

Operating System

Debian GNU/Linux

How are you running Gitea?

I am using the upstream binary from GitHub on amd64.

Database

PostgreSQL

Can you reproduce the bug on the Gitea demo site?

No

Log Gist

No response

Description

I just upgraded to 1.16.3:

/srv/gitea/bin/gitea-1.16.3-linux-amd64 --config /etc/gitea/gitea.ini migrate # the migration is successful
GITEA_WORK_DIR=/srv/gitea /srv/gitea/bin/gitea-1.16.3-linux-amd64 --config /etc/gitea/gitea.ini web

When I authenticated using my security key, this message appeared:

The key: 'securitykey' authenticates using the deprecated U2F process. You should re-register this key and remove the old registration.

then I removed my security key from my gitea account and I tried to re-register it. I was unable to re-register it:

Could not read your security key.
unknown error

The gitea log contains:

2022/03/06 04:49:08 ...els/auth/webauthn.go:138:getWebAuthnCredentialByName() [I] [SQL] SELECT "id", "name", "lower_name", "user_id", "credential_id", "public_key", "attestation_type", "aaguid", "sign_count", "clone_warning", "created_unix", "updated_unix" FROM "webauthn_credential" WHERE (user_id = $1 AND lower_name = $2) LIMIT 1 [312 solokey] - 1.934689ms
2022/03/06 04:49:08 models/db/context.go:127:Insert() [I] [SQL] INSERT INTO "webauthn_credential" ("name","lower_name","user_id","credential_id","public_key","attestation_type","aaguid","sign_count","clone_warning","created_unix","updated_unix") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11) RETURNING "id" [solokey solokey 312 [...] fido-u2f [0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0] 0 false 1646538548 1646538548] - 1.940643ms
2022/03/06 04:49:08 ...security/webauthn.go:103:WebauthnRegisterPost() [E] CreateCredential: pq: la valeur d'une clé dupliquée rompt la contrainte unique « webauthn_credential_pkey »

Once the following SQL commands has been executed, I was able to re-register my security key:

select nextval('webauthn_credential_id_seq'::regclass);

This issue seems related to #18881.

Screenshots

No response

[singuliere]

Could you please specify the Gitea version from which you upgraded? Was it 1.15.11?

[singuliere]

@zeripath does this ring a bell?

[pilou-]

Could you please specify the Gitea version from which you upgraded? Was it 1.15.11?

I mentioned it in the redmine issue but not here :-/ The current version is 1.15.11 indeed 👍

[zeripath]

2022/03/06 04:49:08 ...security/webauthn.go:103:WebauthnRegisterPost() [E] CreateCredential: pq: la valeur d'une clé dupliquée rompt la contrainte unique « webauthn_credential_pkey »

I'm not sure but it looks like your postgres primary key sequence is out of date. I guess this is related to the damned migration.

IIRC running gitea doctor recreate-table webauthn_credential should recreate the sequence and update it.

[pilou-]

@zeripath note that only Gitea 1.15.11 was connected to this postgres database. In order to test the migration from 1.15.11 to 1.16.2 then from 1.15.11 to 1.16.3, a copy of the database was used.

[zeripath]

That doesn't matter.

Have you run the requested command or use the doctor dbconsistency fixing command?

We'll need to fix v210 again to forcibly update the sequence counter for everyone but the suggested command should do that for you.

[zeripath]

Following migration to 1.16 could you please run:

gitea doctor recreate-table webauthn_credential

or in postgres:

SELECT setval('webauthn_credential_id_seq', COALESCE((SELECT MAX(id)+1 FROM `webauthn_credential`), 1), false)