is blocked because of many connection
I am constantly getting abuse from azure, amazon, digital ocean cloud. With previous mysql versions I never got this message. I assume you have set this to a default. I don't want to have this limit on ip but on ip+database, how can I change this?
Apologies; somehow my reply did not go to the list address. On Monday 01 April 2024 at 11:29:17, Marc via discuss wrote:
I am constantly getting abuse from azure, amazon, digital ocean cloud.
That sounds most unfortunate, but what has that to do with MariaDB? What exactly do you mean by that statement?
With previous mysql versions I never got this message.
Which message?
I assume you have set this to a default.
Who is "you" and what is "this"?
I don't want to have this limit on ip but on ip+database, how can I change this?
Please explain in more detail (or more clearly) what your problem is and what you are trying to achieve. Antony. -- This email was created using 100% recycled electrons. Please reply to the list; please don't CC me.
Hi Antony,
I am constantly getting abuse from azure, amazon, digital ocean cloud.
That sounds most unfortunate, but what has that to do with MariaDB? What exactly do you mean by that statement?
With previous mysql versions I never got this message.
Which message?
x.x.x.x is blocked because of many connection and unblock with 'mysqladmin flush-hosts'
I assume you have set this to a default.
Who is "you" and what is "this"?
you: default settings introduced by mariadb ;) this: connection limit per ip
I don't want to have this limit on ip but on ip+database, how can I change this?
Please explain in more detail (or more clearly) what your problem is and what you are trying to achieve.
1. I don't want the connection limit to be on just an ip in a shared environment, but on the combination ip+accessed db. 2. or maybe this can be auto-flushed after some period?
On Monday 01 April 2024 at 12:57:43, Marc via discuss wrote:
x.x.x.x is blocked because of many connection and unblock with 'mysqladmin flush-hosts'
Have you tried setting max_connections to something higher than the default (on my machine this is 100) in /etc/mysql/mariadb.conf.d/50-server.cnf?
I don't want the connection limit to be on just an ip in a shared environment, but on the combination ip+accessed db.
I don't know whether that is even possible; maybe someone else here does. Antony. -- Why are they called "The Rocky Mountains"? What are other mountains made of? Please reply to the list; please *don't* CC me.
On 4/1/24 12:57, Marc via discuss wrote:
I don't want to have this limit on ip but on ip+database, how can I change this?
at the point at which connections are blocked the database the remote host wants to use is not even known yet, so this blocking mechanism can't take it into account. The related setting is max_connect_errors, but as far as I can tell it defaults to 100 for all MariaDB releases, and for MySQL, too, at least back to MySQL 5.7 https://mariadb.com/kb/en/server-system-variables/#max_connect_errors https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_... That's why I was asking for specific version information on a previous reply ... PS: your quoting style is confusing, the ">" indentation marker is supposed to mark quoted text, one ">" per level of quoting, not your actual reply. -- Hartmut Holzgraefe, Principal Support Engineer (EMEA) MariaDB Corporation | http://www.mariadb.com/
I don't want to have this limit on ip but on ip+database, how can I change this?
at the point at which connections are blocked the database the remote host wants to use is not even known yet, so this blocking mechanism can't take it into account.
afaik you have a connection requist with user/pass/db. So maybe it could be a good option to delay tcp blocking till after you get more info? I donk't think there is much overhead created using these variables that are being send. You don't query the backend looking up users in tables. I think this could be nice feature for design.
The related setting is max_connect_errors, but as far as I can tell it defaults to 100 for all MariaDB releases, and for MySQL, too, at least back to MySQL 5.7
https://mariadb.com/kb/en/server-system-variables/#max_connect_errors
https://dev.mysql.com/doc/refman/5.7/en/server-system- variables.html#sysvar_max_connect_errors
That's why I was asking for specific version information on a previous reply ...
I don't think it is that relevant currently. It takes me a bit of time to gather this. I can remember noticing starting to have these incidents after an upgrade.
PS: your quoting style is confusing, the ">" indentation marker is supposed to mark quoted text, one ">" per level of quoting, not your actual reply.
:) My reply is being quoted? I sort of thought this was a clear way of responding. I am usually annoyed by people not responding clearly, so I definitely want to prevent doing the same.
at the point at which connections are blocked the database the remote host wants to use is not even known yet, so this blocking mechanism can't take it into account.
The related setting is max_connect_errors, but as far as I can tell it defaults to 100 for all MariaDB releases, and for MySQL, too, at least back to MySQL 5.7
https://mariadb.com/kb/en/server-system-variables/#max_connect_errors
https://dev.mysql.com/doc/refman/5.7/en/server-system- variables.html#sysvar_max_connect_errors
That's why I was asking for specific version information on a previous reply ...
Is it possible to get this logged. Currently I only see restarts being logged (to a remote syslog)
Hi Antony,
I am constantly getting abuse from azure, amazon, digital ocean cloud.
x.x.x.x is blocked because of many connection and unblock with 'mysqladmin flush-hosts'
Do you mean you do not want ANY connections from those sources? You mention "abuse" which makes me think that these are not legitimate connection attempts and Maria is only blocking them after they have tried NN times and failed. That sounds like you have port 3306 open to the internet, which is generally considered a Very Bad Thing. (As it is for any port you don't need the random internet to connect to) I would suggest you close this port to the internet immediately. If you do need connections from the internet, then adjust your firewall to restrict it to accept connections from them explicitly. Apologies if I'm misinterpreting this, but if the above is true then I also suggest you review your basic computer security.
On Tuesday 02 April 2024 at 11:36:13, Simon Avery via discuss wrote:
Hi Antony,
Thanks, but I did not post the information; I responded to Marc, who asked the original question.
That sounds like you have port 3306 open to the internet, which is generally considered a Very Bad Thing. (As it is for any port you don't need the random internet to connect to)
I would suggest you close this port to the internet immediately. If you do need connections from the internet, then adjust your firewall to restrict it to accept connections from them explicitly.
I agree with the above advice, however it's not clear to me that the OP is complaining about connection attempts from unwanted sources, or simply large numbers of connections from legitimate clients.
Apologies if I'm misinterpreting this, but if the above is true then I also suggest you review your basic computer security.
Not a bad idea in general, for most people :) Antony. -- "Can you keep a secret?" "Well, I shouldn't really tell you this, but... no." Please reply to the list; please *don't* CC me.
Apologies if I'm misinterpreting this, but if the above is true then I also suggest you review your basic computer security.
Not a bad idea in general, for most people :)
That would exclude 70% or so of people doing business on the internet ;) I can remember years ago complaining to some developer of an s3 client that they were having default read everyone rights on uploads. Then it starts to make sense when you read about data breaches where files are being downloaded. I have the impression the cloud mostly facilitates people that are not a 'professional'.
I am constantly getting abuse from azure, amazon, digital ocean cloud.
x.x.x.x is blocked because of many connection and unblock with 'mysqladmin flush-hosts'
Do you mean you do not want ANY connections from those sources?
This is shared web server, so I need to allow what ever else is not being abused at that time.
You mention "abuse" which makes me think that these are not legitimate connection attempts and Maria is only blocking them after they have tried NN times and failed.
That sounds like you have port 3306 open to the internet, which is generally considered a Very Bad Thing. (As it is for any port you don't need the random internet to connect to)
I know, but 3306 is not exposed. It are the websites being abused, as in not following robots.txt standard etc. Some mild form of dos.
I would suggest you close this port to the internet immediately. If you do need connections from the internet, then adjust your firewall to restrict it to accept connections from them explicitly.
Apologies if I'm misinterpreting this, but if the above is true then I also suggest you review your basic computer security.
:) no worries. Unfortunately my security is of that level, that simple minded web developers have problems with it and take their hosting somewhere else.
On 4/2/24 14:35, Marc via discuss wrote:
This is shared web server, so I need to allow what ever else is not being abused at that time.
with that you should never end up with blocked hosts though, a host only gets blocked when terminating before the initial handshake is complete, e.g. by connecting to port 3306 and then immediately disconnecting again without sending any data. Hosts do not get blocked for e.g. repeatedly providing the wrong password, as for that the initial handshake gets completed. Completed with an "access denied" error, but completed nonetheless. Main problem here though is that you seem to want to provide as little information as possible only, so this turned into a big guessing game. And with that I'm out, I don't want to waste unpaid time on doing educated guesses with information only being reviled bit by bit instead of describing the full scope of the problem up front. -- Hartmut Holzgraefe, Principal Support Engineer (EMEA) MariaDB Corporation | http://www.mariadb.com/
Hosts do not get blocked for e.g. repeatedly providing the wrong password, as for that the initial handshake gets completed. Completed with an "access denied" error, but completed nonetheless.
I think the initial post with error message is clear " blocked because of many connections"
Main problem here though is that you seem to want to provide as little information as possible only, so this turned into a big guessing game.
And with that I'm out, I don't want to waste unpaid time on doing educated guesses with information only being reviled bit by bit instead of describing the full scope of the problem up front.
But my intial question was also if the blocking could be expanded to a match of ip+db and not just ip. I still think this could be an interesting change in design.
On 4/2/24 15:00, Marc wrote:
I think the initial post with error message is clear " blocked because of many connections"
so it was "too many connections" after all, not "too many connection errors"? in that case you had us all lead on the wrong track, and the solution would be simple: increase max_connections
But my intial question was also if the blocking could be expanded to a match of ip+db and not just ip. I still think this could be an interesting change in design.
that again hints towards "too many connection errors" All this mess could have been avoided by a clear copy+paste of the error message you are actually getting, instead of trying to be as vague as possible (e.g. not mentioning actual old and new version, even after repeated questions about that). So back to "too many connection errors": as described in my last reply this error is about misbehaved clients that have repeatedly connected to the server, but then aborted before completing the login handshake. And with that the IP is the only thing known at that point, user name and the optional up-front default database are only transferred later in the handshake. So it is an interesting feature request, but one impossible to add to that specific feature. PS: so you tricked me into replying once more, while again not really adding any useful information. Won't happen again though ... -- Hartmut Holzgraefe, Principal Support Engineer (EMEA) MariaDB Corporation | http://www.mariadb.com/
On Tuesday 02 April 2024 at 15:16:52, Hartmut Holzgraefe via discuss wrote:
so it was "too many connections" after all, not "too many connection errors"?
in that case you had us all lead on the wrong track, and the solution would be simple: increase max_connections
This has already been suggested :) Antony. -- She did not swoon, but she did get a look on her face that said 'This conversation is over', which Jack took as a sign he was going in the right direction. - Neal Stephenson, Quicksilver Please reply to the list; please *don't* CC me.
Hi, I think you're looking for these variables: MariaDB> show variables like '%max%connections%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | extra_max_connections | 1 | | max_connections | 5000 | | max_user_connections | 250 | +-----------------------+-------+ 3 rows in set (0.001 sec) So set max_connections high, and limit the per-user scope using max_user_connections. works well for us. Kind regards, Jaco On 2024/04/02 15:00, Marc via discuss wrote:
Hosts do not get blocked for e.g. repeatedly providing the wrong password, as for that the initial handshake gets completed. Completed with an "access denied" error, but completed nonetheless. I think the initial post with error message is clear " blocked because of many connections"
Main problem here though is that you seem to want to provide as little information as possible only, so this turned into a big guessing game.
And with that I'm out, I don't want to waste unpaid time on doing educated guesses with information only being reviled bit by bit instead of describing the full scope of the problem up front. But my intial question was also if the blocking could be expanded to a match of ip+db and not just ip. I still think this could be an interesting change in design.
discuss mailing list --discuss@lists.mariadb.org To unsubscribe send an email todiscuss-leave@lists.mariadb.org
MariaDB> show variables like '%max%connections%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | extra_max_connections | 1 | | max_connections | 5000 | | max_user_connections | 250 | +-----------------------+-------+ 3 rows in set (0.001 sec)
Ah yes, that is good to try, thanks!
MariaDB> show variables like '%max%connections%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | extra_max_connections | 1 | | max_connections | 5000 | | max_user_connections | 250 | +-----------------------+-------+ 3 rows in set (0.001 sec)
So set max_connections high, and limit the per-user scope using max_user_connections. works well for us.
/usr/sbin/mysqld --verbose --help shows me the value I have configured in server.cnf at [mysqld]. However the show variables like '%max%connections%' shows me a different value. I should be able to put this in server.cnf not?
Hi, On 2024/04/02 23:48, Marc wrote:
MariaDB> show variables like '%max%connections%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | extra_max_connections | 1 | | max_connections | 5000 | | max_user_connections | 250 | +-----------------------+-------+ 3 rows in set (0.001 sec)
So set max_connections high, and limit the per-user scope using max_user_connections. works well for us.
/usr/sbin/mysqld --verbose --help shows me the value I have configured in server.cnf at [mysqld]. However the show variables like '%max%connections%' shows me a different value. I should be able to put this in server.cnf not?
Did you restart mariadb after modifying the configuration? And yes, you should be able, specifically we have our "standard" configuration in /etc/mysql/mariadb.d/90uls.cnf, and our distro by default sets my.cnf up such that this folder is included using !includedir /etc/mysql/mariadb.d/ (and the references to /etc/mysql was done to maintain as far as possible compatibility for what it's worth nowadays, to make it easier to swap between mysql and mariadb). In 99custom.cnf we would then put something like this: [mysqld] max_user_connections = 250 max_connections = 5000 Along with some other settings that's server specific and deviates from the default standards (like setting innodb_buffer_pool_size based on the available RAM on the specific server). Kind regards, Jaco
Did you restart mariadb after modifying the configuration?
yes
[mysqld] max_user_connections = 250 max_connections = 5000
Yes I am putting it exactly there. If I change max_connections there, I see that change as expected. However if I put there max_user_connections=103, I see 10 in show statement, and 103 in the output of '/usr/sbin/mysqld --verbose --help | grep max| grep connect ' using v10.5.
On 4/3/24 12:05, Marc wrote:
[mysqld] max_user_connections = 250 max_connections = 5000
Yes I am putting it exactly there. If I change max_connections there, I see that change as expected. However if I put there max_user_connections=103, I see 10 in show statement, and 103 in the output of '/usr/sbin/mysqld --verbose --help | grep max| grep connect '
using v10.5.
with 10.5 you can use SELECT VARIABLE_NAME , GLOBAL_VALUE , GLOBAL_VALUE_ORIGIN , GLOBAL_VALUE_PATH FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'max%connections'; to see what value is in effect, and from where (esp.: what .cnf file) it was fetched. -- Hartmut Holzgraefe, Principal Support Engineer (EMEA) MariaDB Corporation | http://www.mariadb.com/
[mysqld] max_user_connections = 250 max_connections = 5000
Yes I am putting it exactly there. If I change max_connections there, I see that change as expected. However if I put there max_user_connections=103, I see 10 in show statement, and 103 in the output of '/usr/sbin/mysqld --verbose --help | grep max| grep connect '
using v10.5.
with 10.5 you can use
SELECT VARIABLE_NAME , GLOBAL_VALUE , GLOBAL_VALUE_ORIGIN , GLOBAL_VALUE_PATH FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'max%connections';
to see what value is in effect, and from where (esp.: what .cnf file) it was fetched.
Thanks!
Hi, On 2024/04/03 12:05, Marc wrote:
Did you restart mariadb after modifying the configuration? yes
[mysqld] max_user_connections = 250 max_connections = 5000 Yes I am putting it exactly there. If I change max_connections there, I see that change as expected. However if I put there max_user_connections=103, I see 10 in show statement, and 103 in the output of '/usr/sbin/mysqld --verbose --help | grep max| grep connect '
using v10.5.
character encoding for that 103? because 10 looks suspiciously like a prefix of 103 and given the default is 0 (unlimited) I can only think that you've got a space or some other invisible character between the 10 and the 3 that causes the parse to treat the string as 10 and not 103. Good luck. Kind regards, Jaco
[mysqld] max_user_connections = 250 max_connections = 5000 Yes I am putting it exactly there. If I change max_connections there, I see that change as expected. However if I put there max_user_connections=103, I see 10 in show statement, and 103 in the output of '/usr/sbin/mysqld --verbose --help | grep max| grep connect '
using v10.5.
character encoding for that 103? because 10 looks suspiciously like a prefix of 103 and given the default is 0 (unlimited) I can only think that you've got a space or some other invisible character between the 10 and the 3 that causes the parse to treat the string as 10 and not 103.
I have been testing with different numbers just to see if and how it would change, or if there was a relationship (maybe total amount of connections devided by something as a max for users). I think I also tested with 200. Maybe some update script did not execute fully/correctly. No idea where this 10 is coming from. I am going to check logs a bit and just wait for the next blocking ;)
Hi Jaco, I did not have any issues until now with these settings[1]. I had again abuse from Digital ocean and Microsoft. I thought these settings would solve my issues. However looking at the logs I have a lot of these entries to db: 'unconnected' user: 'unauthenticated' host: before these entries are logged: Aborted connection 3077 to db: xxxx user: 'aaaaaa' host: Can it be that the user is being blocked and when then the abuse continues, these 'unauthenticated' are triggering the global server blocking? [1] MariaDB [(none)]> show variables like '%max%connections%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | extra_max_connections | 1 | | max_connections | 1000 | | max_user_connections | 10 | +-----------------------+-------+ MariaDB [(none)]> SELECT VARIABLE_NAME , GLOBAL_VALUE , GLOBAL_VALUE_ORIGIN , GLOBAL_VALUE_PATH FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'max%connections'; +----------------------+--------------+---------------------+--------------------------+ | VARIABLE_NAME | GLOBAL_VALUE | GLOBAL_VALUE_ORIGIN | GLOBAL_VALUE_PATH | +----------------------+--------------+---------------------+--------------------------+ | MAX_USER_CONNECTIONS | 50 | CONFIG | /etc/my.cnf.d/server.cnf | | MAX_CONNECTIONS | 1000 | CONFIG | /etc/my.cnf.d/server.cnf | +----------------------+--------------+---------------------+--------------------------+
I think you're looking for these variables:
MariaDB> show variables like '%max%connections%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | extra_max_connections | 1 | | max_connections | 5000 | | max_user_connections | 250 | +-----------------------+-------+ 3 rows in set (0.001 sec)
So set max_connections high, and limit the per-user scope using max_user_connections. works well for us.
Kind regards, Jaco
I lowered the max_user_connections hoping it would keep other connections. Yet I am seeing the opposite. I have the impression that ' db: 'unconnected' user: 'unauthenticated' host:' is being counted, which they should not be.
-----Original Message----- From: Marc Sent: Monday, 8 July 2024 12:30 To: 'Jaco Kroon' <jaco@uls.co.za>; Hartmut Holzgraefe <hartmut@mariadb.com>; discuss@lists.mariadb.org Subject: RE: [MariaDB discuss] Re: is blocked because of many connection
Hi Jaco,
I did not have any issues until now with these settings[1]. I had again abuse from Digital ocean and Microsoft. I thought these settings would solve my issues. However looking at the logs I have a lot of these entries
to db: 'unconnected' user: 'unauthenticated' host:
before these entries are logged:
Aborted connection 3077 to db: xxxx user: 'aaaaaa' host:
Can it be that the user is being blocked and when then the abuse continues, these 'unauthenticated' are triggering the global server blocking?
[1] MariaDB [(none)]> show variables like '%max%connections%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | extra_max_connections | 1 | | max_connections | 1000 | | max_user_connections | 10 | +-----------------------+-------+
MariaDB [(none)]> SELECT VARIABLE_NAME , GLOBAL_VALUE , GLOBAL_VALUE_ORIGIN , GLOBAL_VALUE_PATH FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'max%connections'; +----------------------+--------------+---------------------+------------ --------------+ | VARIABLE_NAME | GLOBAL_VALUE | GLOBAL_VALUE_ORIGIN | GLOBAL_VALUE_PATH | +----------------------+--------------+---------------------+------------ --------------+ | MAX_USER_CONNECTIONS | 50 | CONFIG | /etc/my.cnf.d/server.cnf | | MAX_CONNECTIONS | 1000 | CONFIG | /etc/my.cnf.d/server.cnf | +----------------------+--------------+---------------------+------------ --------------+
I think you're looking for these variables:
MariaDB> show variables like '%max%connections%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | extra_max_connections | 1 | | max_connections | 5000 | | max_user_connections | 250 | +-----------------------+-------+ 3 rows in set (0.001 sec)
So set max_connections high, and limit the per-user scope using max_user_connections. works well for us.
Kind regards, Jaco
Is it possible that someone of mariadb checks what happens when the max_user_connections is exhausted, because to me it looks like further requests are handled as being 'max_connections' which defeats the purpose of max_user_connections
I lowered the max_user_connections hoping it would keep other connections. Yet I am seeing the opposite. I have the impression that ' db: 'unconnected' user: 'unauthenticated' host:' is being counted, which they should not be.
I did not have any issues until now with these settings[1]. I had again abuse from Digital ocean and Microsoft. I thought these settings would solve my issues. However looking at the logs I have a lot of these entries
to db: 'unconnected' user: 'unauthenticated' host:
before these entries are logged:
Aborted connection 3077 to db: xxxx user: 'aaaaaa' host:
Can it be that the user is being blocked and when then the abuse continues, these 'unauthenticated' are triggering the global server blocking?
[1] MariaDB [(none)]> show variables like '%max%connections%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | extra_max_connections | 1 | | max_connections | 1000 | | max_user_connections | 10 | +-----------------------+-------+
MariaDB [(none)]> SELECT VARIABLE_NAME , GLOBAL_VALUE , GLOBAL_VALUE_ORIGIN , GLOBAL_VALUE_PATH FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'max%connections'; +----------------------+--------------+---------------------+----------
--
--------------+ | VARIABLE_NAME | GLOBAL_VALUE | GLOBAL_VALUE_ORIGIN | GLOBAL_VALUE_PATH | +----------------------+--------------+---------------------+---------- -- --------------+ | MAX_USER_CONNECTIONS | 50 | CONFIG | /etc/my.cnf.d/server.cnf | | MAX_CONNECTIONS | 1000 | CONFIG | /etc/my.cnf.d/server.cnf | +----------------------+--------------+---------------------+---------- -- --------------+
I think you're looking for these variables:
MariaDB> show variables like '%max%connections%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | extra_max_connections | 1 | | max_connections | 5000 | | max_user_connections | 250 | +-----------------------+-------+ 3 rows in set (0.001 sec)
So set max_connections high, and limit the per-user scope using max_user_connections. works well for us.
Reference: https://mariadb.com/kb/en/server-system-variables/#max_user_connections Is the abuse of the nature where you are rejecting unwelcome connection attempts from random sources? If so, that does not relate to max_user_connections which only increments when a connection is successful and sustained to mariadb. The limit here is for simultaneous and successful connections - Maria doesn't keep count of unsuccessful accounts from specific users other than some global counter status such as those in %conn%. Preventing such random connections from the internet to mariadb is usually achieved using a firewall, rather than MariaDb - stop malicious activity as early in the transaction as you can to avoid risk and resource load. If your instance of Mariadb is open to the internet, it's not surprising the bots are hammering it. Apologies if I've misunderstood and for some reason you are allowing wanting each of these sources to connect up to 250 times and they do have accounts. (seems a lot to me and isn't what I'd call abuse - I know who's connecting and would ask them to reduce that number if they were causing problems, but okay). If so, then this is indeed the right setting - but check the link above and ensure those accounts do not have SUPER or CONNECTION ADMIN privileges as those privs intentionally disregard this value. S -----Original Message----- From: Marc via discuss <discuss@lists.mariadb.org> Sent: Friday, July 12, 2024 10:39 AM To: discuss@lists.mariadb.org Subject: [MariaDB discuss] Re: possible bug in dropping max connections Is it possible that someone of mariadb checks what happens when the max_user_connections is exhausted, because to me it looks like further requests are handled as being 'max_connections' which defeats the purpose of max_user_connections
I lowered the max_user_connections hoping it would keep other connections. Yet I am seeing the opposite. I have the impression that ' db: 'unconnected' user: 'unauthenticated' host:' is being counted, which they should not be.
I did not have any issues until now with these settings[1]. I had again abuse from Digital ocean and Microsoft. I thought these settings would solve my issues. However looking at the logs I have a lot of these entries
to db: 'unconnected' user: 'unauthenticated' host:
before these entries are logged:
Aborted connection 3077 to db: xxxx user: 'aaaaaa' host:
Can it be that the user is being blocked and when then the abuse continues, these 'unauthenticated' are triggering the global server blocking?
[1] MariaDB [(none)]> show variables like '%max%connections%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | extra_max_connections | 1 | | max_connections | 1000 | | max_user_connections | 10 | +-----------------------+-------+
MariaDB [(none)]> SELECT VARIABLE_NAME , GLOBAL_VALUE , GLOBAL_VALUE_ORIGIN , GLOBAL_VALUE_PATH FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'max%connections'; +----------------------+--------------+---------------------+------- +----------------------+--------------+---------------------+---
--
--------------+ | VARIABLE_NAME | GLOBAL_VALUE | GLOBAL_VALUE_ORIGIN | GLOBAL_VALUE_PATH | +----------------------+--------------+---------------------+------- +----------------------+--------------+---------------------+--- -- --------------+ | MAX_USER_CONNECTIONS | 50 | CONFIG | /etc/my.cnf.d/server.cnf | | MAX_CONNECTIONS | 1000 | CONFIG | /etc/my.cnf.d/server.cnf | +----------------------+--------------+---------------------+------- +----------------------+--------------+---------------------+--- -- --------------+
I think you're looking for these variables:
MariaDB> show variables like '%max%connections%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | extra_max_connections | 1 | | max_connections | 5000 | | max_user_connections | 250 | +-----------------------+-------+ 3 rows in set (0.001 sec)
So set max_connections high, and limit the per-user scope using max_user_connections. works well for us.
_______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org
Reference: https://mariadb.com/kb/en/server-system- variables/#max_user_connections
I sort of guessed this functionality from the config name.
Is the abuse of the nature where you are rejecting unwelcome connection attempts from random sources?
A website is getting lots of requests until it reaches the max_user_connections
If so, that does not relate to max_user_connections which only increments when a connection is successful and sustained to mariadb. The limit here is for simultaneous and successful connections - Maria doesn't keep count of unsuccessful accounts from specific users other than some global counter status such as those in %conn%.
Say we have db usera, userb, userc all on the same clienthost ip. I suspect that if usera exhausts its max_user_connections, continued requests are not being counted any more under usera but under clienthost. At some point max_connections will be reached resulting in userb and userc failure. (It looks like this is what I am experiencing currenlty) This can be mitigated by not counting these failed connections of usera to the global max_connections (or closing them more quickly?). This way you will probably safeguard the operation of userb and userc from the same clienthost ip.
Preventing such random connections from the internet to mariadb is usually achieved using a firewall, rather than MariaDb - stop malicious activity as early in the transaction as you can to avoid risk and resource load.
I agree.
If your instance of Mariadb is open to the internet, it's not surprising the bots are hammering it.
It is not. More indirectly via website.
Apologies if I've misunderstood and for some reason you are allowing wanting each of these sources to connect up to 250 times and they do have accounts. (seems a lot to me and isn't what I'd call abuse - I know who's connecting and would ask them to reduce that number if they were causing problems, but okay).
These 250 are from different a different user responding to this thread. I am testing with between 50 and 150. I 'noticed' that lowering this resulted in what seemed to have the clienthost ip being blocked. This fuels my suspection that continuos requests on blocked users is accumulating to the global 'ip' counter.
If so, then this is indeed the right setting - but check the link above and ensure those accounts do not have SUPER or CONNECTION ADMIN privileges as those privs intentionally disregard this value.
These are mostly read (select) only accounts.
Hi, Marc, On Jul 12, Marc via discuss wrote:
Is it possible that someone of mariadb checks what happens when the max_user_connections is exhausted, because to me it looks like further requests are handled as being 'max_connections' which defeats the purpose of max_user_connections
No, further requests are rejected with an error: https://github.com/MariaDB/server/blob/10.5/mysql-test/main/user_limits.resu... Unless the connecting user has the CONNECTION ADMIN privilege: https://mariadb.com/kb/en/grant/#connection-admin Regards, Sergei Chief Architect, MariaDB Server and security@mariadb.org
Hi Sergei,
Is it possible that someone of mariadb checks what happens when the max_user_connections is exhausted, because to me it looks like further requests are handled as being 'max_connections' which defeats the purpose of max_user_connections
No, further requests are rejected with an error:
https://github.com/MariaDB/server/blob/10.5/mysql- test/main/user_limits.result#L92
Say we have db usera, userb, userc all on the same clienthost ip. I suspect that if usera exhausts its max_user_connections, continued requests are not being counted any more under usera but under clienthost. At some point max_connections will be reached resulting in userb and userc failure. (It looks like this is what I am experiencing currenlty) This can be mitigated by not counting these failed connections of usera to the global max_connections (or closing them more quickly?). This way you will probably safeguard the operation of userb and userc from the same clienthost ip.
Unless the connecting user has the CONNECTION ADMIN privilege: https://mariadb.com/kb/en/grant/#connection-admin
These are mostly read only users. I check with 'SHOW GRANTS for ' and only get 'GRANT USAGE ON' and 'GRANT SELECT ON'
Hi, Marc, On Jul 12, Marc wrote:
Is it possible that someone of mariadb checks what happens when the max_user_connections is exhausted, because to me it looks like further requests are handled as being 'max_connections' which defeats the purpose of max_user_connections
No, further requests are rejected with an error:
https://github.com/MariaDB/server/blob/10.5/mysql-test/main/user_limits.resu...
Say we have db usera, userb, userc all on the same clienthost ip.
I suspect that if usera exhausts its max_user_connections, continued requests are not being counted any more under usera but under clienthost. At some point max_connections will be reached resulting in userb and userc failure. (It looks like this is what I am experiencing currenlty)
Not quite. max_user_connections limit is enforced after successful authentication - that's when the server knows the user name. max_connections is enforced as soon as the client connects. So yes, even when usera has reached max_user_connections limit, it can keep trying to connect and exhaust max_connections too, especially if it'll delay sending authentication packets.
This can be mitigated by not counting these failed connections of usera to the global max_connections (or closing them more quickly?). This way you will probably safeguard the operation of userb and userc from the same clienthost ip.
The server cannot close connection in the middle of authentication, it could be your valid userb or userc. Regards, Sergei Chief Architect, MariaDB Server and security@mariadb.org
Hi Sergei,
Not quite. max_user_connections limit is enforced after successful authentication - that's when the server knows the user name.
max_connections is enforced as soon as the client connects.
So yes, even when usera has reached max_user_connections limit, it can keep trying to connect and exhaust max_connections too, especially if it'll delay sending authentication packets.
So what about waiting a bit with dropping the connection of max_connections, so you can do - get the send user name - check if the username is in max_user_connections limit - if it is limited drop the connection, but don't add it to the max_connections counter. - if it is not limited add the connection, and add it to max_connections counter. disadvantage - is when you drop the connection for max_connections. You have to maybe postpone this a bit. - could this postponing be abused in a dos attack? advantage - is you allow other not blocked users from the same ip. - admins don't need to set max_connections high to prevent it from blocking other valid users - keep this max_connections functionality as it is meant for. I think this is a good enhancement. Depending on postponing this dropping. In sendmail you have a delayed_checks option. You could also add this to the configuration activating this behaviour. So people can choose to have this functionality or not. It all depends on the influence of postponing dropping a connection until you know what the user name is. Afaik is the current combination of max_user_connections and max_connections useles when on the same ip.
Hi, Marc, On Jul 22, Marc wrote:
Hi Sergei,
Not quite. max_user_connections limit is enforced after successful authentication - that's when the server knows the user name.
max_connections is enforced as soon as the client connects.
So yes, even when usera has reached max_user_connections limit, they can keep trying to connect and exhaust max_connections too, especially if they'll delay sending authentication packets.
So what about waiting a bit with dropping the connection of max_connections, so you can do
- get the send user name - check if the username is in max_user_connections limit - if it is limited drop the connection, but don't add it to the max_connections counter. - if it is not limited add the connection, and add it to max_connections counter.
disadvantage - is when you drop the connection for max_connections. You have to maybe postpone this a bit. - could this postponing be abused in a dos attack?
Yes, it could. Currently when usera has reached max_user_connections limit, they can keep trying to connect and exhaust max_connections too, especially if they'll delay sending authentication packets. If the server will delay enforcing of max_connections (that is, the server will not reject connections about max_connections at once), then this user in the above scenario will open all possible connections your OS can handle and the computer will become completely inaccessible. Currently only the MariaDB server will become inaccessible, but you can configure extra_port to always be able to access the server in such a case. Regards, Sergei Chief Architect, MariaDB Server and security@mariadb.org
Not quite. max_user_connections limit is enforced after successful authentication - that's when the server knows the user name.
max_connections is enforced as soon as the client connects.
So yes, even when usera has reached max_user_connections limit, they can keep trying to connect and exhaust max_connections too, especially if they'll delay sending authentication packets.
So what about waiting a bit with dropping the connection of max_connections, so you can do
- get the send user name - check if the username is in max_user_connections limit - if it is limited drop the connection, but don't add it to the max_connections counter. - if it is not limited add the connection, and add it to max_connections counter.
disadvantage - is when you drop the connection for max_connections. You have to maybe postpone this a bit. - could this postponing be abused in a dos attack?
Yes, it could. Currently when usera has reached max_user_connections limit, they can keep trying to connect and exhaust max_connections too, especially if they'll delay sending authentication packets.
But you already have timeouts for this not? I think that is a separate case.
If the server will delay enforcing of max_connections (that is, the server will not reject connections about max_connections at once), then this user in the above scenario will open all possible connections
Really? Mostly you need to delay until the n'th packet is received with the username. Besides you mostly are also processing requests from 'badly' operating clients. Who are not trying to dos the db server with some malformed tcp stream. These clients just send to many requests for whatever reason. So in ~90% of the cases you will just receive the username as with any 'normal' connection. What delay are we then talking about? I assume the delay between initiating a connection and receiving the username is in the low ms?
your OS can handle and the computer will become completely inaccessible.
Besides is the limits.conf or so from your os not limiting the ports. I don't think it is currently that easy for a process to cause a dos on the os.
Currently only the MariaDB server will become inaccessible, but you can configure extra_port to always be able to access the server in such a case.
Yes, I am now restarting it even. The idea about this change is to have a more useful and expected implementation of max_user_connections and max_connections. Currently I am using max_connections not for what it is supposed to be used, just because the max_user_connections is not doing as much as it 'should'.
Not quite. max_user_connections limit is enforced after successful authentication - that's when the server knows the user name.
max_connections is enforced as soon as the client connects.
So yes, even when usera has reached max_user_connections limit,
they
can keep trying to connect and exhaust max_connections too, especially if they'll delay sending authentication packets.
So what about waiting a bit with dropping the connection of max_connections, so you can do
- get the send user name - check if the username is in max_user_connections limit - if it is limited drop the connection, but don't add it to the max_connections counter. - if it is not limited add the connection, and add it to max_connections counter.
disadvantage - is when you drop the connection for max_connections. You have to maybe postpone this a bit. - could this postponing be abused in a dos attack?
Yes, it could. Currently when usera has reached max_user_connections limit, they can keep trying to connect and exhaust max_connections too, especially if they'll delay sending authentication packets.
But you already have timeouts for this not? I think that is a separate case.
If the server will delay enforcing of max_connections (that is, the server will not reject connections about max_connections at once), then this user in the above scenario will open all possible connections
Really? Mostly you need to delay until the n'th packet is received with the username. Besides you mostly are also processing requests from 'badly' operating clients. Who are not trying to dos the db server with some malformed tcp stream. These clients just send to many requests for whatever reason. So in ~90% of the cases you will just receive the username as with any 'normal' connection.
What delay are we then talking about? I assume the delay between initiating a connection and receiving the username is in the low ms?
your OS can handle and the computer will become completely inaccessible.
Besides is the limits.conf or so from your os not limiting the ports. I don't think it is currently that easy for a process to cause a dos on the os.
Currently only the MariaDB server will become inaccessible, but you can configure extra_port to always be able to access the server in such a case.
Yes, I am now restarting it even. The idea about this change is to have a more useful and expected implementation of max_user_connections and max_connections. Currently I am using max_connections not for what it is supposed to be used, just because the max_user_connections is not doing as much as it 'should'.
Hi Sergei, Is this something you are going to look in to? I am also curious about this delay between first package and package with the username. I can't imagine that being such a problem, to me this looks feasible currently.
Hi, Marc, On Aug 02, Marc wrote:
If the server will delay enforcing of max_connections (that is, the server will not reject connections about max_connections at once), then this user in the above scenario will open all possible connections your OS can handle and the computer will become completely inaccessible.
The idea about this change is to have a more useful and expected implementation of max_user_connections and max_connections. Currently I am using max_connections not for what it is supposed to be used, just because the max_user_connections is not doing as much as it 'should'.
Hi Sergei, Is this something you are going to look in to? I am also curious about this delay between first package and package with the username. I can't imagine that being such a problem, to me this looks feasible currently.
I'm afraid, I don't understand your use case. There are, basically, three limits now: max_user_connections, max_connections, OS limit. An ordinary user would connect many times, hit max_user_connections and stop. Or will keep connecting and get disconnects because of max_user_connections. A malicious user would connect and wouldn't authenticate, this will exhaust max_connections and nobody will be able to connect to the server anymore. max_user_connections won't help here. After your suggestion of delayed max_connections check - an ordinary user would still connect max_user_connections times, nohing would change for him. A malicious user, not stopped by max_connections anymore, would completely exhaust OS capability for opening new connections making the whole OS inaccessible. That's what I mean - I don't understand your use case. It doesn't change much if all users behave and it makes the situation much worse if a user is malicious. So, in what use case your change would be an improvement? Regards, Sergei Chief Architect, MariaDB Server and security@mariadb.org
If the server will delay enforcing of max_connections (that is, the server will not reject connections about max_connections at once), then this user in the above scenario will open all possible connections your OS can handle and the computer will become completely inaccessible.
The idea about this change is to have a more useful and expected implementation of max_user_connections and max_connections. Currently I am using max_connections not for what it is supposed to be used, just because the max_user_connections is not doing as much as it 'should'.
Hi Sergei, Is this something you are going to look in to? I am also curious about this delay between first package and package with the username. I can't imagine that being such a problem, to me this looks feasible currently.
I'm afraid, I don't understand your use case.
There are, basically, three limits now: max_user_connections, max_connections, OS limit.
An ordinary user would connect many times, hit max_user_connections and stop. Or will keep connecting and get disconnects because of max_user_connections.
and after exhausting max_user_connections it will exhaust max_connections (which I have a problem with)
A malicious user would connect and wouldn't authenticate, this will exhaust max_connections and nobody will be able to connect to the server anymore. max_user_connections won't help here.
This is not my use case
After your suggestion of delayed max_connections check - an ordinary user would still connect max_user_connections times, nohing would change for him.
Indeed. But we don't care about him, we care about the other users from the same ip address.
A malicious user, not stopped by max_connections anymore, would completely exhaust OS capability for opening new connections making the whole OS inaccessible.
This is not my use case (this would require direct access to the db server)
That's what I mean - I don't understand your use case. It doesn't change much if all users behave and it makes the situation much worse if a user is malicious.
I am not to sure about if this really much worse. I don't really know how long a connection of a blocked user stays 'open'. This is I guess similar to what happens after max_connections is exhausted.
So, in what use case your change would be an improvement?
allowing other users access from the same ip (while this 1 user is blocked from that ip)
Hi Sergei, On 2024/08/02 11:30, Sergei Golubchik via discuss wrote:
Hi, Marc,
On Aug 02, Marc wrote:
If the server will delay enforcing of max_connections (that is, the server will not reject connections about max_connections at once), then this user in the above scenario will open all possible connections your OS can handle and the computer will become completely inaccessible. The idea about this change is to have a more useful and expected implementation of max_user_connections and max_connections. Currently I am using max_connections not for what it is supposed to be used, just because the max_user_connections is not doing as much as it 'should'. Hi Sergei, Is this something you are going to look in to? I am also curious about this delay between first package and package with the username. I can't imagine that being such a problem, to me this looks feasible currently.
I'm afraid, I don't understand your use case.
There are, basically, three limits now: max_user_connections, max_connections, OS limit.
An ordinary user would connect many times, hit max_user_connections and stop. Or will keep connecting and get disconnects because of max_user_connections.
A malicious user would connect and wouldn't authenticate, this will exhaust max_connections and nobody will be able to connect to the server anymore. max_user_connections won't help here.
I think let me explain a different way, and doesn't directly to what I understand Marc's use-case to be, but relates, and what I reckon is not a bad compromise, because I get where Marc is coming from. We've had some interesting experiences with a remote party effectively DOS'ing themselves from connecting to one of our haproxy instances as well recently (https), so not directly related. TCP connection establishment is phase one. This is limited by operating system receive queues (yes, two of them, one for SYN_RECV, and one for ESTABLISHED but not yet accept()ed), but in the case of Linux the SYN_RECV queue can be exceeded if configured to use syn cookies. Bad idea? Possibly as it prevents tcp options from being used, but it does allow a connection to be established at all in case of SYN flood, so IMHO, switching to SYN cookies once SYN_RECV queue is full is a good idea since a degraded but working connection is significantly better than no connection at all. This isn't mariadb specific, nor does it relate to Marc's request but does give some level of background. It's the same issue under-lyingly, just at a different layer. Once MariaDB accept()s the connection I understand MariaDB counts it against max_connections. If max_connections is then exceeded the new connection is dropped. This can trivially deny service to legitimate well-behaved clients. This provides for a very, very simple DOS situation. Simply open a connection from a remote side, and never send anything. Eventually MariaDB will close this connection, not sure how long this would take, dropping the connection count again, and only now legitimate users can connect again. As I understand, this is what Marc is experiencing. There are many reasons why this could happen under *normal operations* but you're right, this is a "badly behaving client". You're also right that not limiting this pre-auth would just move the problem to operating system limits. Our use-cases are controlled in most cases, we have one case where unfortunately MariaDB needs to be world-exposed, and we've got no way around that, and this would apply to us here as well. Fortunately we have other mechanisms in place to rate limit how fast untrusted sources can connect, which helps to mitigate this. I think one could also front this with a tool like haproxy which can be configured to say if the client side doesn't send something within the first X ms of a connection, close the connection, which could be protection layer two. That said, I agree with Marc that the situation can be improved MariaDB side. He's worried about a mix of good and bad actors from the same IP address, our use-case is from different IPs, but the same underlying problem. MariaDB can (in my opinion) help in both cases. I would suggest have a separate max_unauthenticated_connections counter, and an authenticate_timeout variable (no more than 2s for most use-cases, and I can't imagine this should be higher than 5s in any situation). I would probably run with something like: max_connections = 5000 max_user_connections = 250 max_unauthenticated_connections = 500 Combining this with firewall rate-limits from untrusted sources one can then get a fairly protected setup, so we normally do burst 100, max 1/s connections by default, with a 5s timeout on mariadb side this permits a "bad player" maximum 100 connections initially, but over time max 5 connections to DOS. Per source IP. So even with my suggestion one can run into trouble, but at least it makes it harder. One could adjust relevant rate limits to something like 1/min with a burst of 500, or have small + large buckets and connections over time has to pass through both, but this gets complicated, and if someone wants to DOS you that desperately, there honestly isn't much you're going to do, but see below. Once max_unauthenticated_connections is reached, I can think of two possible strategies: 1. Drop the new connection. 2. Drop the connection we've been waiting for the longest to auth. Each with pro's and cons. Possibly a third option would be to drop the connection we've been waiting for longest from same source, else revert to 1 or 2. In this scenario, I don't think it matters significantly if unauthenticated connections counts towards max_connections or not, but my gut would go towards not. To further mitigate the multiple sources it would be great if we can get logs specifically for authentication results, ie, for each incoming connection log exactly one line indicating the source IP, and the auth result, eg: Connection auth result: user@a.b.c.d accepted. Connection auth result: a.b.c.d timed out. Connection auth result: user@a.b.c.d auth failed. Of course a.b.c.d could also be IPv6 dead::beef, as the case may be. One can then feed this into fail2ban or similar to mitigate further. There might be a way to log this already that I just haven't found yet, I've only spent a very superficial amount of time looking for this. Given this, we can adjust rate limits to higher limits once a successfully authenticated connection happens, say what our current defaults are, and run with even lower defaults than current (say burst 10, 1/min or something). Too many auth failed or timeouts in the absence of successful auth can be used to outright ban source IPs for some time.
After your suggestion of delayed max_connections check - an ordinary user would still connect max_user_connections times, nohing would change for him. A malicious user, not stopped by max_connections anymore, would completely exhaust OS capability for opening new connections making the whole OS inaccessible. Bingo. You're spot on.But the current mechanism does allow for a very effective and trivial denial of service on any remote server. That's what I mean - I don't understand your use case. It doesn't change much if all users behave and it makes the situation much worse if a user is malicious. So, in what use case your change would be an improvement?
I hope the above helped. Kind regards, Jaco
Can you put this finally on the agenda, it is becoming an annoying issue.
If the server will delay enforcing of max_connections (that is, the server will not reject connections about max_connections at once), then this user in the above scenario will open all possible connections your OS can handle and the computer will become completely inaccessible.
The idea about this change is to have a more useful and expected implementation of max_user_connections and max_connections. Currently I am using max_connections not for what it is supposed to be used, just because the max_user_connections is not doing as much as it 'should'.
Hi Sergei, Is this something you are going to look in to? I am also curious about this delay between first package and package with the username. I can't imagine that being such a problem, to me this looks feasible currently.
I'm afraid, I don't understand your use case.
There are, basically, three limits now: max_user_connections, max_connections, OS limit.
An ordinary user would connect many times, hit max_user_connections and stop. Or will keep connecting and get disconnects because of max_user_connections.
A malicious user would connect and wouldn't authenticate, this will exhaust max_connections and nobody will be able to connect to the server anymore. max_user_connections won't help here.
I think let me explain a different way, and doesn't directly to what I understand Marc's use-case to be, but relates, and what I reckon is not a bad compromise, because I get where Marc is coming from. We've had some interesting experiences with a remote party effectively DOS'ing themselves from connecting to one of our haproxy instances as well recently (https), so not directly related.
TCP connection establishment is phase one. This is limited by operating system receive queues (yes, two of them, one for SYN_RECV, and one for ESTABLISHED but not yet accept()ed), but in the case of Linux the SYN_RECV queue can be exceeded if configured to use syn cookies. Bad idea? Possibly as it prevents tcp options from being used, but it does allow a connection to be established at all in case of SYN flood, so IMHO, switching to SYN cookies once SYN_RECV queue is full is a good idea since a degraded but working connection is significantly better than no connection at all. This isn't mariadb specific, nor does it relate to Marc's request but does give some level of background. It's the same issue under-lyingly, just at a different layer.
Once MariaDB accept()s the connection I understand MariaDB counts it against max_connections. If max_connections is then exceeded the new connection is dropped. This can trivially deny service to legitimate well-behaved clients.
This provides for a very, very simple DOS situation. Simply open a connection from a remote side, and never send anything. Eventually MariaDB will close this connection, not sure how long this would take, dropping the connection count again, and only now legitimate users can connect again. As I understand, this is what Marc is experiencing. There are many reasons why this could happen under *normal operations* but you're right, this is a "badly behaving client". You're also right that not limiting this pre-auth would just move the problem to operating system limits.
Our use-cases are controlled in most cases, we have one case where unfortunately MariaDB needs to be world-exposed, and we've got no way around that, and this would apply to us here as well. Fortunately we have other mechanisms in place to rate limit how fast untrusted sources can connect, which helps to mitigate this. I think one could also front this with a tool like haproxy which can be configured to say if the client side doesn't send something within the first X ms of a connection, close the connection, which could be protection layer two.
That said, I agree with Marc that the situation can be improved MariaDB side. He's worried about a mix of good and bad actors from the same IP address, our use-case is from different IPs, but the same underlying problem. MariaDB can (in my opinion) help in both cases.
I would suggest have a separate max_unauthenticated_connections counter, and an authenticate_timeout variable (no more than 2s for most use- cases, and I can't imagine this should be higher than 5s in any situation).
I would probably run with something like:
max_connections = 5000 max_user_connections = 250 max_unauthenticated_connections = 500
Combining this with firewall rate-limits from untrusted sources one can then get a fairly protected setup, so we normally do burst 100, max 1/s connections by default, with a 5s timeout on mariadb side this permits a "bad player" maximum 100 connections initially, but over time max 5 connections to DOS. Per source IP. So even with my suggestion one can run into trouble, but at least it makes it harder. One could adjust relevant rate limits to something like 1/min with a burst of 500, or have small + large buckets and connections over time has to pass through both, but this gets complicated, and if someone wants to DOS you that desperately, there honestly isn't much you're going to do, but see below.
Once max_unauthenticated_connections is reached, I can think of two possible strategies:
1. Drop the new connection. 2. Drop the connection we've been waiting for the longest to auth.
Each with pro's and cons. Possibly a third option would be to drop the connection we've been waiting for longest from same source, else revert to 1 or 2.
In this scenario, I don't think it matters significantly if unauthenticated connections counts towards max_connections or not, but my gut would go towards not.
To further mitigate the multiple sources it would be great if we can get logs specifically for authentication results, ie, for each incoming connection log exactly one line indicating the source IP, and the auth result, eg:
Connection auth result: user@a.b.c.d <mailto:user@a.b.c.d> accepted. Connection auth result: a.b.c.d timed out. Connection auth result: user@a.b.c.d <mailto:user@a.b.c.d> auth failed.
Of course a.b.c.d could also be IPv6 dead::beef, as the case may be.
One can then feed this into fail2ban or similar to mitigate further. There might be a way to log this already that I just haven't found yet, I've only spent a very superficial amount of time looking for this. Given this, we can adjust rate limits to higher limits once a successfully authenticated connection happens, say what our current defaults are, and run with even lower defaults than current (say burst 10, 1/min or something). Too many auth failed or timeouts in the absence of successful auth can be used to outright ban source IPs for some time.
After your suggestion of delayed max_connections check - an ordinary user would still connect max_user_connections times, nohing would change for him. A malicious user, not stopped by max_connections anymore, would completely exhaust OS capability for opening new connections making the whole OS inaccessible.
Bingo. You're spot on. But the current mechanism does allow for a very effective and trivial denial of service on any remote server.
That's what I mean - I don't understand your use case. It doesn't change much if all users behave and it makes the situation much worse if a user is malicious. So, in what use case your change would be an improvement?
I hope the above helped.
Kind regards, Jaco
On 4/1/24 12:48, Antony Stone via discuss wrote:
With previous mysql versions I never got this message. Which message?
and which versions, specifically -- Hartmut Holzgraefe, Principal Support Engineer (EMEA) MariaDB Corporation | http://www.mariadb.com/
participants (6)
-
Antony Stone -
Hartmut Holzgraefe -
Jaco Kroon -
Marc -
Sergei Golubchik -
Simon Avery