Large number of sign-ups on public Gitea instance #14483

@lunny commented on GitHub (May 13, 2025):

You can enable both email verification and captcha.

@lunny commented on GitHub (May 13, 2025): You can enable both email verification and captcha.

GiteaMirror commented

@toloveru commented on GitHub (May 13, 2025):

Right. I believe I have enabled CAPTCHA before, and it would seem that I now also have email from using the docs at Email setup and the config cheat sheet. From the looks of it, that leaves unverified accounts in the appropriate state (activated=false). I suppose it won't stop the attacks on my instance, but at least it should allow the administrative jobs for it to delete anything that follows. As for the accounts that have been created so far, I guess that's something for future toloveru to deal with.

@toloveru commented on GitHub (May 13, 2025): Right. I believe I have enabled CAPTCHA before, and it would seem that I now also have email from using the docs at [Email setup](https://docs.gitea.com/administration/email-setup#using-smtp) and the [config cheat sheet](https://docs.gitea.com/administration/config-cheat-sheet#mailer-mailer). From the looks of it, that leaves unverified accounts in the appropriate state (activated=false). I suppose it won't stop the attacks on my instance, but at least it should allow the administrative jobs for it to delete anything that follows. As for the accounts that have been created so far, I guess that's something for future toloveru to deal with.

GiteaMirror commented

@toloveru commented on GitHub (May 13, 2025):

As things stand now, there are still various attempts at sign-up on regular bases. They are successful, but they no longer have activated or even unrestricted status. Currently the configuration is set to have new accounts private by default, require email activation, and CAPTCHA on sign-up.

However, this is not a threat vector I'd dismiss normally. Inquiry into their services revealed that they have a Sendmail-like mail server running that can receive confirmation emails. They also have a default-ish instance of NGINX, though it does not seem to be the NGINX software's own default page. The target that these probes were ran against can be shared privately, to the security list (I'll admit, at the time of creation, I was unsure whether this qualifies as bug or security, or whether this has significance for fellow public operators).

Anyway, the Sendmail instance strongly hints at account verification not being a silver bullet here. If they're already using headless browsers to sign up, there's little stopping them from parsing emails and acting on those too.

FWIW, the instances I probed on are hosted on Hetzner, which I am aware to have a strict abuse policy. They also had default PTR records.

@toloveru commented on GitHub (May 13, 2025): ![Image](https://github.com/user-attachments/assets/af30b393-da18-4e04-a1df-006b609221b2) As things stand now, there are still various attempts at sign-up on regular bases. They are successful, but they no longer have activated or even unrestricted status. Currently the configuration is set to have new accounts private by default, require email activation, and CAPTCHA on sign-up. However, this is not a threat vector I'd dismiss normally. Inquiry into their services revealed that they have a Sendmail-like mail server running that can receive confirmation emails. They also have a default-ish instance of NGINX, though it does not seem to be the NGINX software's own default page. The target that these probes were ran against can be shared privately, to the security list (I'll admit, at the time of creation, I was unsure whether this qualifies as bug or security, or whether this has significance for fellow public operators). Anyway, the Sendmail instance strongly hints at account verification _not_ being a silver bullet here. If they're already using headless browsers to sign up, there's little stopping them from parsing emails and acting on those too. FWIW, the instances I probed on are hosted on Hetzner, which I am aware to have a strict abuse policy. They also had default PTR records.

GiteaMirror commented

@toloveru commented on GitHub (May 14, 2025):

Possibly relevant SQL commands:

[~] root@gitea.lan
[#] /bin/bash sqlite3 /home/git/data/gitea.db

sqlite> .mode column
sqlite> .headers on
sqlite> .output sqlite.sql
sqlite> PRAGMA table_info(user);
sqlite> SELECT id, full_name, email FROM user;

^D to exit from the SQLite console.

[~] root@gitea.lan
[#] /bin/bash sqlite3 /home/git/data/gitea.db 'SELECT id, full_name, email FROM user;' | grep -vE 'glinxy.org|dravix.org|lyvix.org|ylixo.com|juxal.org|elyquin.org|oxilv.com|hivoltz.org|elyquin.com|ivolix.com|claxyn.com|blyxen.com|ophixy.com|claxyn.org|ivolix.org|astroaxis.online|skywardhub.online|tryhike.click|astroarch.online|astroaxis.site|dontstress.online|cosmiccircuit.store|lunapixelguru.site|fruitingbodymushrooms.online|buskonavvt.us|topcompanygroup.com|anonmails.de' | grep -vE '.site|.store' | wc -l
70
[0] Command completed on 2025-05-14 02:03 UTC.

Against the current list of about 2.6k accounts, this gets rid of just about everything I'd want to get rid of.

Affected domains:

glinxy.org
dravix.org
lyvix.org
ylixo.com
juxal.org
elyquin.org
oxilv.com
hivoltz.org
elyquin.com
ivolix.com
claxyn.com
blyxen.com
ophixy.com
claxyn.org
ivolix.org
astroaxis.online
skywardhub.online
tryhike.click
astroarch.online
astroaxis.site
dontstress.online
cosmiccircuit.store
lunapixelguru.site
fruitingbodymushrooms.online
buskonavvt.us
topcompanygroup.com
anonmails.de
.site
.store

Whether that is a good thing or not, I guess that's an exercise left to the observer.

@toloveru commented on GitHub (May 14, 2025): Possibly relevant SQL commands: ``` [~] root@gitea.lan [#] /bin/bash sqlite3 /home/git/data/gitea.db sqlite> .mode column sqlite> .headers on sqlite> .output sqlite.sql sqlite> PRAGMA table_info(user); sqlite> SELECT id, full_name, email FROM user; ``` ^D to exit from the SQLite console. ``` [~] root@gitea.lan [#] /bin/bash sqlite3 /home/git/data/gitea.db 'SELECT id, full_name, email FROM user;' | grep -vE 'glinxy.org|dravix.org|lyvix.org|ylixo.com|juxal.org|elyquin.org|oxilv.com|hivoltz.org|elyquin.com|ivolix.com|claxyn.com|blyxen.com|ophixy.com|claxyn.org|ivolix.org|astroaxis.online|skywardhub.online|tryhike.click|astroarch.online|astroaxis.site|dontstress.online|cosmiccircuit.store|lunapixelguru.site|fruitingbodymushrooms.online|buskonavvt.us|topcompanygroup.com|anonmails.de' | grep -vE '.site|.store' | wc -l 70 [0] Command completed on 2025-05-14 02:03 UTC. ``` Against the current list of about 2.6k accounts, this gets rid of just about everything I'd want to get rid of. Affected domains: - glinxy.org - dravix.org - lyvix.org - ylixo.com - juxal.org - elyquin.org - oxilv.com - hivoltz.org - elyquin.com - ivolix.com - claxyn.com - blyxen.com - ophixy.com - claxyn.org - ivolix.org - astroaxis.online - skywardhub.online - tryhike.click - astroarch.online - astroaxis.site - dontstress.online - cosmiccircuit.store - lunapixelguru.site - fruitingbodymushrooms.online - buskonavvt.us - topcompanygroup.com - anonmails.de - .site - .store Whether that is a good thing or not, I guess that's an exercise left to the observer.

GiteaMirror commented

@lunny commented on GitHub (May 14, 2025):

There is a cron task to remove all unactived accounts. You can run it at admin panel.

@lunny commented on GitHub (May 14, 2025): There is a cron task to remove all unactived accounts. You can run it at admin panel.

GiteaMirror commented

@toloveru commented on GitHub (May 14, 2025):

That is, indeed, the future job for future toloveru. Now having filtered all these names, I guess that's next? Regular expressions did their job, what's next? How do you add such incident response to your logs? /etc/motd entry?

I do still wonder how I should address that SQLite database though... Backup and hopefully all read-only queries until I get them right?

[~] root@gitea.lan
[#] /bin/bash sqlite3 /home/git/data/gitea.db 'SELECT email FROM user;' | grep -E 'glinxy.org|dravix.org|lyvix.org|ylixo.com|juxal.org|elyquin.org|oxilv.com|hivoltz.org|elyquin.com|ivolix.com|claxyn.com|blyxen.com|ophixy.com|claxyn.org|ivolix.org|astroaxis.online|skywardhub.online|tryhike.click|astroarch.online|astroaxis.site|dontstress.online|cosmiccircuit.store|lunapixelguru.site|fruitingbodymushrooms.online|buskonavvt.us|topcompanygroup.com|anonmails.de|.site|.store' | sort | wc -l
2593
[0] Command completed on 2025-05-14 02:34 UTC.
--- snip ---
[~] root@gitea.lan
[#] /bin/bash sqlite3 /home/git/data/gitea.db 'SELECT email FROM user;' | grep -vE 'glinxy.org|dravix.org|lyvix.org|ylixo.com|juxal.org|elyquin.org|oxilv.com|hivoltz.org|elyquin.com|ivolix.com|claxyn.com|blyxen.com|ophixy.com|claxyn.org|ivolix.org|astroaxis.online|skywardhub.online|tryhike.click|astroarch.online|astroaxis.site|dontstress.online|cosmiccircuit.store|lunapixelguru.site|fruitingbodymushrooms.online|buskonavvt.us|topcompanygroup.com|anonmails.de|.site|.store' | sort | wc -l
70
[0] Command completed on 2025-05-14 02:34 UTC.
--- snip ---
[~] root@gitea.lan
[#] /bin/bash sqlite3 /home/git/data/gitea.db 'SELECT email FROM user;' | wc -l
2663
[0] Command completed on 2025-05-14 02:36 UTC.

I want to run this service with those remaining 70 records.

GiteaMirror commented

@toloveru commented on GitHub (May 18, 2025):

I believe to have now addressed everything I had to in the database. What I did was to copy the data/gitea.db file from the Gitea server, into my laptop. I needed a copy before making changes to it anyway. Wouldn't want to work on the live database like this, would we... 😉

You may want to preface this copying with disabling sign-up, I did not. But this would prevent any genuine users from making their accounts until you're done with the adjustments about to be made.

Then I opened the SQLite database on my laptop using their DB Browser for SQLite program.
Once in there, I navigated to the user table. Seems like the table it opens by default is access, that's what you'll want to change.
Now that you have that table open, you can start filtering email addresses by domain. So I pasted every domain I listed in my previous comment into the email filter, and selected all the records by clicking onto the results and pressing Ctrl-A and Delete. Rinse and repeat for every domain in the list.

What I am now left with is a much cleaner database, that I then copy-pasted back into Gitea. I'll just put the command list in a screenshot below.

I have a systemd integration for my Gitea, but the idea is that the service should be stopped before inserting a new database. But only stop it once the file has been prepared to have the same properties as the original. Maybe this can be scripted to minimize downtime, I did not. Anyway, it seems to have restarted correctly and that's probably the end of it. When I went into the User Accounts section of Gitea's admin panel, the records seem to have correctly applied.

Granted, one thing I'm not sure about is whether I got everything in the database I should've. Are there any other tables that remnants of these deleted accounts could still reside in? I don't want Gitea to exert undefined behaviour over this external change.

Anyway, for the future I guess it's going to remain an issue until I prevent these punks from making new accounts altogether. It's kind of interesting to find by looking at the database results, that these spam operations may be operated by only a handful of people. One of them likes right-wing American terms, while another likes astrology terms. They are both responsible for a majority of the accounts deleted now, though the former is certainly a lot more aggressive.

My guess so far is that I may want to add these domains to my DNS RPZ zone. That way my mail servers can try to send the email, sure, but it will all result in failed deliveries. At least they should not be able to confirm their emails at all that way. Granted, it would keep bringing the user ID ticker up and I would have to regularly run that cron task to delete those accounts. I wonder if there's a better way still...

@toloveru commented on GitHub (May 18, 2025): I believe to have now addressed everything I had to in the database. What I did was to copy the `data/gitea.db` file from the Gitea server, into my laptop. I needed a copy before making changes to it anyway. Wouldn't want to work on the live database like this, would we... 😉 You may want to preface this copying with disabling sign-up, I did not. But this would prevent any genuine users from making their accounts until you're done with the adjustments about to be made. Then I opened the SQLite database on my laptop using their [DB Browser for SQLite program](https://sqlitebrowser.org/). Once in there, I navigated to the `user` table. Seems like the table it opens by default is `access`, that's what you'll want to change. Now that you have that table open, you can start filtering email addresses by domain. So I pasted every domain I listed in my previous comment into the email filter, and selected all the records by clicking onto the results and pressing Ctrl-A and Delete. Rinse and repeat for every domain in the list. What I am now left with is a much cleaner database, that I then copy-pasted back into Gitea. I'll just put the command list in a screenshot below. ![Image](https://github.com/user-attachments/assets/13e2401a-0d96-47a6-808a-0395c39af255) I have a systemd integration for my Gitea, but the idea is that the service should be stopped before inserting a new database. But only stop it once the file has been prepared to have the same properties as the original. Maybe this can be scripted to minimize downtime, I did not. Anyway, it seems to have restarted correctly and that's probably the end of it. When I went into the User Accounts section of Gitea's admin panel, the records seem to have correctly applied. Granted, one thing I'm not sure about is whether I got everything in the database I should've. Are there any other tables that remnants of these deleted accounts could still reside in? I don't want Gitea to exert undefined behaviour over this external change. Anyway, for the future I guess it's going to remain an issue until I prevent these punks from making new accounts altogether. It's kind of interesting to find by looking at the database results, that these spam operations may be operated by only a handful of people. One of them likes right-wing American terms, while another likes astrology terms. They are both responsible for a majority of the accounts deleted now, though the former is certainly a lot more aggressive. My guess so far is that I may want to add these domains to my DNS RPZ zone. That way my mail servers can try to send the email, sure, but it will all result in failed deliveries. At least they should not be able to confirm their emails at all that way. Granted, it would keep bringing the user ID ticker up and I would have to regularly run that cron task to delete those accounts. I wonder if there's a better way still...

GiteaMirror commented

@lunny commented on GitHub (May 18, 2025):

It may result unknown problems if you changed the database manually if you don't know the tables relationships.

@lunny commented on GitHub (May 18, 2025): It may result unknown problems if you changed the database manually if you don't know the tables relationships.

GiteaMirror commented

@toloveru commented on GitHub (May 18, 2025):

It may result unknown problems if you changed the database manually if you don't know the tables relationships.

Okay, so where do I find the tables' relationships? Or can you tell me what those relationships are? Or which developer on the project do I ask instead?

This problem was not going to be solved with just the administrative UI. The project is still too incomplete for that, and so was my own deployment of it. Many things still require outside shell access. I did not update my shell environment to be that close to my jumphost's environment for nothing. Many of my other services like DHCP, DNS, mail, ... don't have that, because I barely log into them. They don't need me to.

If you want to help, then actually help. You're by no means obligated to do so, but that doesn't change me still feeling very much alone in this. And alone, I use my own experience with the shell on a host I own, in a way that I am comfortable with. If that's something you don't like, then tell me how you would've addressed the problem - in its full scope - instead.

@toloveru commented on GitHub (May 18, 2025): > It may result unknown problems if you changed the database manually if you don't know the tables relationships. Okay, so where do I find the tables' relationships? Or can you tell me what those relationships are? Or which developer on the project do I ask instead? This problem was not going to be solved with just the administrative UI. The project is still too incomplete for that, and so was my own deployment of it. Many things still require outside shell access. I did not update my shell environment to be that close to my jumphost's environment for nothing. Many of my other services like DHCP, DNS, mail, ... don't have that, because I barely log into them. They don't need me to. If you want to help, then actually help. You're by no means obligated to do so, but that doesn't change me still feeling very much alone in this. And alone, I use my own experience with the shell on a host I own, in a way that I am comfortable with. If that's something you don't like, then tell me how you would've addressed the problem - in its full scope - instead.

GiteaMirror commented

@techknowlogick commented on GitHub (May 18, 2025):

@toloveru to clarify your situation, you are looking at bulk-removing accounts based on specific email domains? And those domains contain no legitimate users? If so, I have a bash script that I used for gitea.com that does that, and then before running it I add those domains to the email block list that I could share with you

@techknowlogick commented on GitHub (May 18, 2025): @toloveru to clarify your situation, you are looking at bulk-removing accounts based on specific email domains? And those domains contain no legitimate users? If so, I have a bash script that I used for gitea.com that does that, and then before running it I add those domains to the email block list that I could share with you

GiteaMirror commented

e2277d07ca/services/cron/tasks_extended.go (L23-L35)

@toloveru commented on GitHub (May 18, 2025):

@toloveru to clarify your situation, you are looking at bulk-removing accounts based on specific email domains? And those domains contain no legitimate users? If so, I have a bash script that I used for gitea.com that does that, and then before running it I add those domains to the email block list that I could share with you

Precisely! These domains are what I am trying to target for bulk removal, I have reasonable confidence that none of those users are legitimate. If you'd like to share what you've done for the gitea.com instance (which I'd imagine to receive even more of these), much appreciated! What I'd be particularly interested in is the domain list, database implementation (i.e. SQLite, MariaDB/MySQL, ...), and which parts of the database are being targeted for this purpose.

Also, how does their presence affect certain fields in the database? When I look at field id under user, it's at 3365 when I received the initial database. Is there a limit to that number, and what happens if/when it overflows? Or is the max value large enough to make it irrelevant?

Just now I also looked into the source code for Gitea, and came across the following bits that may be able to tell me more about how Gitea does it in the administrative UI. However, my experience with Go is limited and I couldn't seem to get to concrete database calls... All I managed to deduce so far is that my (admittedly somewhat chainsaw) approach did not consider existing repositories for these users, the Go code does seem to be doing that. A bash script meanwhile would be right up my alley!

d89eed998f/services/user/user.go (L280-L284)

@toloveru commented on GitHub (May 18, 2025): > [@toloveru](https://github.com/toloveru) to clarify your situation, you are looking at bulk-removing accounts based on specific email domains? And those domains contain no legitimate users? If so, I have a bash script that I used for gitea.com that does that, and then before running it I add those domains to the email block list that I could share with you Precisely! These domains are what I am trying to target for bulk removal, I have reasonable confidence that none of those users are legitimate. If you'd like to share what you've done for the gitea.com instance (which I'd imagine to receive even more of these), much appreciated! What I'd be particularly interested in is the domain list, database implementation (i.e. SQLite, MariaDB/MySQL, ...), and which parts of the database are being targeted for this purpose. Also, how does their presence affect certain fields in the database? When I look at field `id` under `user`, it's at 3365 when I received the initial database. Is there a limit to that number, and what happens if/when it overflows? Or is the max value large enough to make it irrelevant? Just now I also looked into the source code for Gitea, and came across the following bits that may be able to tell me more about how Gitea does it in the administrative UI. However, my experience with Go is limited and I couldn't seem to get to concrete database calls... All I managed to deduce so far is that my (admittedly somewhat chainsaw) approach did not consider existing repositories for these users, the Go code does seem to be doing that. A bash script meanwhile would be right up my alley! https://github.com/go-gitea/gitea/blob/e2277d07ca5112a797f8c86f825060027ff1c2da/services/cron/tasks_extended.go#L23-L35 https://github.com/go-gitea/gitea/blob/d89eed998f9e4dc84d0ef02451703590d7a8ef51/services/user/user.go#L280-L284

GiteaMirror commented

@techknowlogick commented on GitHub (May 18, 2025):

@toloveru here is the portion of the script I use for this purpose: https://gist.github.com/techknowlogick/c2f0a91620aad747a8e226f0b1b5a484

I lock the accounts and mark them as private first, and then go through them later on, but I added a delete function in case you don't want to deal with that intermediary step. You could remove the CLI prompt (asking for a Y for each account) to loop through everything without intervention instead.

@techknowlogick commented on GitHub (May 18, 2025): @toloveru here is the portion of the script I use for this purpose: https://gist.github.com/techknowlogick/c2f0a91620aad747a8e226f0b1b5a484 I lock the accounts and mark them as private first, and then go through them later on, but I added a delete function in case you don't want to deal with that intermediary step. You could remove the CLI prompt (asking for a `Y` for each account) to loop through everything without intervention instead.

GiteaMirror commented

@ktwrd commented on GitHub (May 19, 2025):

Also, how does their presence affect certain fields in the database? When I look at field id under user, it's at 3365 when I received the initial database. Is there a limit to that number, and what happens if/when it overflows? Or is the max value large enough to make it irrelevant?

@toloveru All the primary keys use int64 as the type, which is a signed(?) 64bit integer. The limit for a signed 64bit integer is 9,223,372,036,854,775,807.

@ktwrd commented on GitHub (May 19, 2025): > Also, how does their presence affect certain fields in the database? When I look at field `id` under `user`, it's at 3365 when I received the initial database. Is there a limit to that number, and what happens if/when it overflows? Or is the max value large enough to make it irrelevant? @toloveru All the primary keys use `int64` as the type, which is a signed(?) 64bit integer. The limit for a signed 64bit integer is `9,223,372,036,854,775,807`.

GiteaMirror commented