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

pilou- · 2022-03-06T04:25:12Z

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

The text was updated successfully, but these errors were encountered:

singuliere · 2022-03-06T10:13:48Z

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

singuliere · 2022-03-06T10:14:09Z

@zeripath does this ring a bell?

pilou- · 2022-03-06T17:55:52Z

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-07T22:29:59Z

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- · 2022-03-08T00:54:49Z

@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 · 2022-03-08T07:28:53Z

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.

There is (yet) another problem with v210 in that Postgres will silently allow preset ID insertions ... but it will not update the sequence value. This PR simply adds a little step to the end of the v210 migration to update the sequence number. Users who have already migrated who find that they cannot insert new webauthn_credentials into the DB can either run: ```bash gitea doctor recreate-table webauthn_credential ``` or ```bash ./gitea doctor --run=check-db-consistency --fix ``` which will fix the bad sequence. Fix go-gitea#19012 Signed-off-by: Andrew Thornton <art27@cantab.net>

zeripath · 2022-03-10T08:18:36Z

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)

* Update the webauthn_credential_id_sequence in Postgres There is (yet) another problem with v210 in that Postgres will silently allow preset ID insertions ... but it will not update the sequence value. This PR simply adds a little step to the end of the v210 migration to update the sequence number. Users who have already migrated who find that they cannot insert new webauthn_credentials into the DB can either run: ```bash gitea doctor recreate-table webauthn_credential ``` or ```bash ./gitea doctor --run=check-db-consistency --fix ``` which will fix the bad sequence. Fix #19012 Signed-off-by: Andrew Thornton <art27@cantab.net>

Backport go-gitea#19048 There is (yet) another problem with v210 in that Postgres will silently allow preset ID insertions ... but it will not update the sequence value. This PR simply adds a little step to the end of the v210 migration to update the sequence number. Users who have already migrated who find that they cannot insert new webauthn_credentials into the DB can either run: ```bash gitea doctor recreate-table webauthn_credential ``` or ```bash SELECT setval('webauthn_credential_id_seq', COALESCE((SELECT MAX(id)+1 FROM `webauthn_credential`), 1), false) ``` which will fix the bad sequence. Fix go-gitea#19012 Signed-off-by: Andrew Thornton <art27@cantab.net>

Backport #19048 There is (yet) another problem with v210 in that Postgres will silently allow preset ID insertions ... but it will not update the sequence value. This PR simply adds a little step to the end of the v210 migration to update the sequence number. Users who have already migrated who find that they cannot insert new webauthn_credentials into the DB can either run: ```bash gitea doctor recreate-table webauthn_credential ``` or ```bash SELECT setval('webauthn_credential_id_seq', COALESCE((SELECT MAX(id)+1 FROM `webauthn_credential`), 1), false) ``` which will fix the bad sequence. Fix #19012 Signed-off-by: Andrew Thornton <art27@cantab.net> Co-authored-by: 6543 <6543@obermui.de>

pilou- · 2022-03-16T01:12:43Z

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)

Following the migration from 1.15.11 to 1.16.3 (after the gitea migrate had been executed):

I ran gitea doctor recreate-table webauthn_credential and I was still able to reproduce the issue:

2022-03-16 00:39:19.258 CET [3619346] giteadbadmin@testmigration ERREUR:  la valeur d'une clé dupliquée rompt la contrainte unique « webauthn_credential_pkey »
2022-03-16 00:39:19.258 CET [3619346] giteadbadmin@testmigration DÉTAIL:  La clé « (id)=(2) » existe déjà.
2022-03-16 00:39:19.258 CET [3619346] giteadbadmin@testmigration INSTRUCTION :  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

next I ran the SQL query, then I didn't encounter the error.

Following the migration from 1.15.11 to 1.16.4 and after the gitea migrate had been executed, I wasn't able to reproduce this issue :)

Thanks!

* Update the webauthn_credential_id_sequence in Postgres There is (yet) another problem with v210 in that Postgres will silently allow preset ID insertions ... but it will not update the sequence value. This PR simply adds a little step to the end of the v210 migration to update the sequence number. Users who have already migrated who find that they cannot insert new webauthn_credentials into the DB can either run: ```bash gitea doctor recreate-table webauthn_credential ``` or ```bash ./gitea doctor --run=check-db-consistency --fix ``` which will fix the bad sequence. Fix go-gitea#19012 Signed-off-by: Andrew Thornton <art27@cantab.net>

singuliere added the type/bug label Mar 6, 2022

singuliere added this to the 1.16.4 milestone Mar 6, 2022

singuliere added the issue/critical This issue should be fixed ASAP. If it is a PR, the PR should be merged ASAP label Mar 6, 2022

zeripath mentioned this issue Mar 10, 2022

Update the webauthn_credential_id_sequence in Postgres #19048

Merged

6543 closed this as completed in #19048 Mar 10, 2022

zeripath mentioned this issue Mar 11, 2022

Update the webauthn_credential_id_sequence in Postgres (#19048) #19060

Merged

go-gitea locked and limited conversation to collaborators Apr 28, 2022

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

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

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

pilou- commented Mar 6, 2022

singuliere commented Mar 6, 2022

singuliere commented Mar 6, 2022

pilou- commented Mar 6, 2022

zeripath commented Mar 7, 2022 •

edited

Loading

pilou- commented Mar 8, 2022

zeripath commented Mar 8, 2022

zeripath commented Mar 10, 2022 •

edited

Loading

pilou- commented Mar 16, 2022

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

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

Comments

pilou- commented Mar 6, 2022

Gitea Version

Git Version

Operating System

How are you running Gitea?

Database

Can you reproduce the bug on the Gitea demo site?

Log Gist

Description

Screenshots

singuliere commented Mar 6, 2022

singuliere commented Mar 6, 2022

pilou- commented Mar 6, 2022

zeripath commented Mar 7, 2022 • edited Loading

pilou- commented Mar 8, 2022

zeripath commented Mar 8, 2022

zeripath commented Mar 10, 2022 • edited Loading

pilou- commented Mar 16, 2022

zeripath commented Mar 7, 2022 •

edited

Loading

zeripath commented Mar 10, 2022 •

edited

Loading