Skip to content

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

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
pilou- opened this issue Mar 6, 2022 · 8 comments · Fixed by #19048
Closed

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

pilou- opened this issue Mar 6, 2022 · 8 comments · Fixed by #19048
Labels
issue/critical This issue should be fixed ASAP. If it is a PR, the PR should be merged ASAP type/bug
Milestone
1.16.4

Comments

@pilou-
Copy link
Contributor

pilou- commented Mar 6, 2022

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 singuliere added this to the 1.16.4 milestone Mar 6, 2022
@singuliere 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
@singuliere
Copy link
Contributor

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

@singuliere
Copy link
Contributor

@zeripath does this ring a bell?

@pilou-
Copy link
Contributor Author

pilou- commented Mar 6, 2022

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
Copy link
Contributor

zeripath commented Mar 7, 2022 •
edited
Loading

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-
Copy link
Contributor Author

pilou- commented Mar 8, 2022

@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
Copy link
Contributor

zeripath commented Mar 8, 2022

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 added a commit to zeripath/gitea that referenced this issue Mar 10, 2022
@zeripath
Update the webauthn_credential_id_sequence in Postgres
ff48df3 
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 zeripath mentioned this issue Mar 10, 2022
Merged
@zeripath
Copy link
Contributor

zeripath commented Mar 10, 2022 •
edited
Loading

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)

6543 pushed a commit that referenced this issue Mar 10, 2022
@zeripath
Update the webauthn_credential_id_sequence in Postgres (#19048)
886b1de 
* 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>
zeripath added a commit to zeripath/gitea that referenced this issue Mar 11, 2022
@zeripath
Update the webauthn_credential_id_sequence in Postgres (go-gitea#19048)
2bbd830 
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>
@zeripath zeripath mentioned this issue Mar 11, 2022
Merged
lunny pushed a commit that referenced this issue Mar 13, 2022
@zeripath @6543
Update the webauthn_credential_id_sequence in Postgres (#19048) (#19060)
d285905 
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-
Copy link
Contributor Author

pilou- commented Mar 16, 2022

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

    1. 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
    1. 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!

Chianina pushed a commit to Chianina/gitea that referenced this issue Mar 28, 2022
@zeripath
Update the webauthn_credential_id_sequence in Postgres (go-gitea#19048)
57812d9 
* 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>
@go-gitea go-gitea locked and limited conversation to collaborators Apr 28, 2022
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees
No one assigned
Labels
issue/critical This issue should be fixed ASAP. If it is a PR, the PR should be merged ASAP type/bug
Projects
None yet
Milestone
1.16.4
Development

Successfully merging a pull request may close this issue.

Update the webauthn_credential_id_sequence in Postgres zeripath/gitea
3 participants
@pilou- @zeripath @singuliere