how to surgically remove a no-longer-existing user

[zzz...@gmail.com]

hiya list -

I had a situation on my gerrit 3.3.2 server where the same username got created with two different casing conventions (due to a mistake I made when trying to manually create the user), which then caused lots of problems when the actual person was trying to get in via oauth with github.   so I was actually able to solve that problem, not without some side effects, by both removing the entries for both usernames from All-Users refs/meta/external-ids as well as deleting the ref for the user in general in refs/users/XY/userid .

this then has the effect that the users are mostly gone, the more troublesome one 1000235 pictured below:

ssh -p 29418 username@gerritserver admin-console show-account 1000235
No accounts found for your query: "1000235" Tip: Try double-escaping spaces, for example: "show-account Last,\\ First"

although there is still a ghost of that number in the user list (where is it still coming from?):

ssh -p 29418 username@gerritserver admin-console ls-users | grep 1000235
1000235 | | | | inactive

but even though the number is still floating around, all the identifying usernames/emails were gone from it and the person was able to create a new account w/ the username (that matches what's on github) and their email address.

so initial problem solved, but this user id still floats around in the system.  After that, one of the groups still had the user id stuck in there, showing up as "Name of user not set", which I could not change or move in any way, because the user id pointed to a non-existent record.  Once again I was able to go into All-Users, get the refs for the group file, remove the user id from the group file manually, push and flush caches, and it worked.  Whew!

So what's left?  The user is still stuck as a reviewer on a single review.   This data does not seem to be in the notedb system, I would gather it's in /var/gerrit/db/account_patch_reviews.h2.db .

question one: why is this user id still showing up in ls-users ?   caches have been flushed and all that.  I have zapped this user id from everywhere I can find, gerrit tools can't work with the user id at all and mostly show it as deleted, but where is it still coming from ?

question two: is account_patch_reviews.h2.db  the file where the user ids of reviewers for reviews are?   i can't see anywhere else this information might be.

question three: i thought everything is supposed to be in notedb now, is there a way to migrate that?

question four: how do i work w/ that h2 file, I downloaded the h2 console tools (this thing: http://www.h2database.com/html/tutorial.html#shell_tool) and I can query it like a database (I'm very well versed in SQL and can get rid of this user id if I can see the tables), but i dont know the username/password, so when i log in as "blank user /password", I can only see the information_schema tables.  I dont see any tables that look like they are related to gerrit.  im not sure if this is because i didnt log in as a certain user or what. There is a "[database] password" in my secrets.config file, not sure if that's what's used here, but if it is, then what is the username?  all forms of "gerrit" etc. w that password I get user/password error.   Google tells me there is a "gsql" tool but it looks like this is a remnant of the 2.x days as there does not seem to be any such command now.

I understand that I wasn't supposed to be messing with my datafiles but the lack of a simple "please delete this user account completely" feature is really problematic as I got into a situation where someone could not create / log in with their github account (we use github oauth) at all due to the presence of two usernames that were the same except for casing.

thanks for anyone who has the resources to chime in.

[Björn Pedersen]

zzz...@gmail.com schrieb am Donnerstag, 21. Oktober 2021 um 06:29:37 UTC+2:

hiya list -

I had a situation on my gerrit 3.3.2 server where the same username got created with two different casing conventions (due to a mistake I made when trying to manually create the user), which then caused lots of problems when the actual person was trying to get in via oauth with github.   so I was actually able to solve that problem, not without some side effects, by both removing the entries for both usernames from All-Users refs/meta/external-ids as well as deleting the ref for the user in general in refs/users/XY/userid .

So what's left?  The user is still stuck as a reviewer on a single review.   This data does not seem to be in the notedb system, I would gather it's in /var/gerrit/db/account_patch_reviews.h2.db .

This should be in refs/meta/<something> of the project the change resides on if I am not mistaken.

In the h2 db only the information for the  "reviewed" flag shown on changed files is stored.

Björn 

[mike bayer]

On Thu, Oct 21, 2021 at 12:29 AM zzz...@gmail.com <zzz...@gmail.com> wrote:

question one: why is this user id still showing up in ls-users ?   caches have been flushed and all that.  I have zapped this user id from everywhere I can find, gerrit tools can't work with the user id at all and mostly show it as deleted, but where is it still coming from ?

answering my own question, it's in the indexes, have to rerun the indexes.  forgot about that.   fortunately the reindexing process did not get upset that i changed things (at least in a test run I did here, another brilliant idea i should have had before i started tinkering).

[mike bayer]

right, that is where I first looked, at the meta file below:

git show-ref | grep 3141

4729d56cf9df21189ac771ec09bd4a08ac53ec61 refs/changes/41/3141/1
a209013f957a9c9c122d959449b33ffa0d939e18 refs/changes/41/3141/meta

so looking again, I see the data is stored as comments in commits, and indeed gerrits docs on notedb indicate this: "The metadata is a notes branch. The commit messages on the branch hold modifications to global data of the change (votes, global comments). The inline comments are in a NoteMap, where the key is the commit SHA-1 of the patchset that the comment refers to, and the value is JSON data. The format of the JSON is in the RevisionNoteData which contains Comment entities."

the offending "note" looks like this:

Author: Gerrit User 1000000 <1000000@879abffb-0c31-43dd-957f-c447d2918210>

Date:   Wed Oct 13 14:25:25 2021 +0000

    Update patch set 1
   
    Patch-set: 1
    Reviewer: Gerrit User 1000235 <1000235@879abffb-0c31-43dd-957f-c447d2918210>
    Attention: {"person_ident":"Gerrit User 1000235 \u003c1000235@879abffb-0c31-43dd-957f-c447d2918210\u003e","operation":"ADD","reason":"mike bayer replied o
n the change"}

so I want to add another "note" to "REMOVE" that user.    Is there a way to do that with git update-ref ?   im not really sure how to work with that command.   another way I could try to rewrite the history of that ref to not include that one message but seems cleaner to add a new note to remove it.  the gerrit API etc. is not able to do this for me because user 1000235 no longer exists and it errors out.

In the h2 db only the information for the  "reviewed" flag shown on changed files is stored.

Björn 

--
--
To unsubscribe, email repo-discuss...@googlegroups.com
More info at http://groups.google.com/group/repo-discuss?hl=en

---
You received this message because you are subscribed to a topic in the Google Groups "Repo and Gerrit Discussion" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/repo-discuss/RcBwgfXZbJA/unsubscribe.
To unsubscribe from this group and all its topics, send an email to repo-discuss...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/repo-discuss/77f71a18-e543-4bdb-9e1a-31035ca7fe31n%40googlegroups.com.

[mike bayer]

I'm playing around with "git commit --allow-empty" and adding various commit messages and authors that look the way they should, however when I push that to the repo, gerrit then 500 server errors on that review with errors like "org.eclipse.jgit.errors.ConfigInvalidException: Change 3141: missing or multiple Patch-set: []" in the logs.   I force-push back to the previous commit and the error resolves, so at least I can undo what I'm playing with (in a sandbox environment).    But there's still more stuff attached to these commits I have to figure out what it is.

seems like if I can just rewrite the history to skip that one commit that would fix it also.

[mike bayer]

OK, I can literally just do a git rebase and whack that one commit out and that seems to work.

the ultimate knowledge of how to craft gerrit notes fully will continue to evade me until another day....

[mike bayer]

for those reading, so that this is useful:

git clone /path/to/gerrit/git/myproject.git/

cd myproject.git

# suppose the change is 3141

git fetch origin refs/changes/41/3141/meta:refs/changes/41/3141/meta

git checkout refs/changes/41/3141/meta

git log

# (now look at the changes, at the comments which say "Attention":.  find the one you don't like, and then find a commit that is earlier than that)

git rebase -i <sha of earlier commit>

 

# edit the rebase file to remove the undesired messages

# force push it back to the repo

git push -f origin HEAD:refs/changes/41/3141/meta

you can literally refresh the page in the UX without clearing any caches or anything and it seems to resolve it.

[mike bayer]

caveat, the subsequent comments get messed up when you do this.   not sure if it's because the dates were rewritten or the SHAs, i cant see how it would care about the SHAs.     mysteries continue but at this point we will try to move on and hope nothing crashes later on.

[Luca Milanesio]

On 21 Oct 2021, at 15:03, mike bayer <zzz...@gmail.com> wrote:

caveat, the subsequent comments get messed up when you do this.   not sure if it's because the dates were rewritten or the SHAs, i cant see how it would care about the SHAs.     mysteries continue but at this point we will try to move on and hope nothing crashes later on.

Apologies for my comment as I may have missed the beginning of the discussion: how come that a user disappeared from the Gerrit accounts? Gerrit doesn’t allow removing accounts.

Why don’t you just add the user (as disabled) again? Wouldn’t that be easier?

Luca.

You received this message because you are subscribed to the Google Groups "Repo and Gerrit Discussion" group.
To unsubscribe from this group and stop receiving emails from it, send an email to repo-discuss...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/repo-discuss/CAFyO5uVsdkL1E8vnCeb6yzUO5UBmgTQuJxiQMqZ1EkdXYbNjCg%40mail.gmail.com.

[mike bayer]

On Thu, Oct 21, 2021 at 2:54 PM Luca Milanesio <luca.mi...@gmail.com> wrote:

On 21 Oct 2021, at 15:03, mike bayer <zzz...@gmail.com> wrote:

caveat, the subsequent comments get messed up when you do this.   not sure if it's because the dates were rewritten or the SHAs, i cant see how it would care about the SHAs.     mysteries continue but at this point we will try to move on and hope nothing crashes later on.

Apologies for my comment as I may have missed the beginning of the discussion: how come that a user disappeared from the Gerrit accounts? Gerrit doesn’t allow removing accounts.

Why don’t you just add the user (as disabled) again? Wouldn’t that be easier?

two accounts were created, one named "Username", the other "username".  The second one was the "good" account that had the email address.  The first one had no email address.  However the person could not create an account, github oauth seemed to not be matching on either name and we had the following error:

Email us...@account.com is already assigned to account 1000235; cannot create external ID github-oauth:1434962 with the same email for account 1000236.

[HTTP GET /oauth?code=< oauth code > (N/A from xx.xxx.xx.xxx)] ERROR com.google.gerrit.httpd.auth.oauth.OAuthSession : Unable to authenticate user "com.google.gerrit.extensions.auth.oauth.OAuthUserInfo@3f4bd072"
com.google.gerrit.server.account.AccountException: Email 'us...@account.com' in use by another account

This account had the exact username that we would need from the github oauth, and it appeared the oauth flow was not honoring the existing account. 

There is no gerrit feature to either change the username of an account or to delete it, so i had to resort to stackoverflow recipes to resolve this.  you certainly can "delete" an account (and i know now I also could have just changed the username by itself), the deletion is done by deleting from external-ids and deleting the ref from All-Users.   It's not actually that hard except that this user id is present in other places.  However, if the user id has not been used anywhere, it works cleanly.  

I have a vague recollection of another message on this board where I think (but i may be mistaken) you stated that the lack of a "delete user" is not for technical reasons, but for legal /GPDR reasons?  If true, I dont really understand that rationale.  I'm an administrator of my own system and these were usernames that were not even attached to real people, and im in the US where such things as GPDR are nonexistent.  I can certainly "delete" them, hackily, yet I dont think anyone is coming to sue gerrit developers as a result, so I fail to see a "legal" reason why gerrit could not have this feature implemented.  I'll gladly sign a disclaimer in order to download an extension that provides this feature.