[PB-5873] Secret for user id <uuid> already exists

[nelson-sean]

Secret for user id already exists

• Passbolt Version: docker image passbolt/passbolt:2.13.5-pro-debian
• Platform and Target:
  -- Browser: Chrome 88.0.4324.146 (Official Build) (64-bit)
  -- Web server: Unknown, whatever the docker image uses

What you did

While logged in, user clicked on the password column in the grid view in order to copy the secret.

What happened

Error message pops up saying "Secret for user id <uuid> already exists"

What you expected to happen

Secret is copied to user's clipboard

[nourcy]

Hi @nelson-sean,

It looks like you have some duplicates in your secrets table.
First of all, perform a backup of your database before doing any action on it.

You can identify the duplicates by executing the following SQL request on your database.

SELECT resource_id, user_id, count(*) FROM secrets GROUP BY resource_id, user_id HAVING COUNT(*)>1;

You will have a result like this:
+--------------------------------------+--------------------------------------+----------+
| resource_id | user_id | count(*) |
+--------------------------------------+--------------------------------------+----------+
| 2a08d0ad-cd50-5f06-a1b1-a2fa46e44d3f | f848277c-5398-58f8-a82a-72397af2d450 | 2 |
| 8e3874ae-4b40-590b-968a-418f704b9d9a | f848277c-5398-58f8-a82a-72397af2d450 | 2 |
+--------------------------------------+--------------------------------------+----------+

Here I have two resources for the same user that that have duplicate secrets.

To get the id of the duplicate here is the query to execute (based on my example):
SELECT id, resource_id, user_id,created,modified FROM secrets WHERE resource_id='2a08d0ad-cd50-5f06-a1b1-a2fa46e44d3f' AND user_id='f848277c-5398-58f8-a82a-72397af2d450';
You will get a result like
+----------------------+-------------------------+------------------------+---------------+----------------+
| id | resource_id | user_id | created | modified |
+----------------------+-------------------------+------------------------+---------------+----------------+
| 84934b7b-5a8f-5d38-a8f6-35757e8034d4 | 2a08d0ad-cd50-5f06-a1b1-a2fa46e44d3f | f848277c-5398-58f8-a82a-72397af2d450 | 2021-02-03 08:19:07 | 2021-02-03 08:19:07 |

| 84934b7b-5a8f-5d38-a8f6-35757e8034d5 | 2a08d0ad-cd50-5f06-a1b1-a2fa46e44d3f | f848277c-5398-58f8-a82a-72397af2d450 | 2021-02-03 08:19:07 | 2021-02-03 08:19:07 |
+----------------------+-------------------------+------------------------+---------------+----------------+
Then delete one of the duplicate (if one of the line got an older modified date, delete that one).
Like (again based on my example):
DELETE FROM secrets WHERE id='84934b7b-5a8f-5d38-a8f6-35757e8034d4';
And repeat the operation for as many duplicate per resource_id,user_id couple you have.

This is a dirty but quickest way to get back your passwords back.

An hotfix will be release very soon to avoid this error.

Thanks for pointing that out.

Max

[nelson-sean]

@nourcy Thanks for the quick response. You were correct, this user had a duplicate of every secret in the system (or at least the majority of them). I wrote a quick query to remove all the duplicates and the issue is no longer happening. Thank you!

[nourcy]

@nelson-sean Cool that you sorted it out.
But I am curious about this user, do you know how he created his secrets?
Normal password creation? Password shared to him only, via API? Quantum computer 😅 ?
This is the first time that we hear about such a behaviour.
Any info will be helpful to identify a potential bug.

Thanks in advance!

[nelson-sean]

Sure thing I can try to provide some context here,

I have a group called 'Everyone' that all users belong to. The large majority of the secrets in the system are shared with this 'Everyone' group. To be specific, 'Everyone' has the 'can update' permission on all but a couple of the folders we have set up.

So the user that experienced this error is our newest user. The account was set up a couple weeks ago. After he activated his account, I added him to the 'Everyone' group. As I understand it at that point he would have gotten his copy of all the shared secrets. Following that afaik he did not use his account at all until he reported this error to me. In the two weeks following his account creation I would estimate something like two dozen new passwords would have been added to a folder shared with 'Everyone'.

Lastly for some numbers:
This user had 599 duplicates in the secrets table out of 633 total unique resource_ids shared with them.

So with all that in mind my guess is that these duplicates were created at the moment I added this user to the 'Everyone' group.

[cedricalfonsi]

Internal ref: PB-5873

Note: it should not occur anymore as the API is more restrictive. However the ticket is kept open as long as no cleanup script is schedule on the API to handle duplicates or the client handles the issue better.