Client-server protocol improvement proposal: Connection redirection
Hi! I would like to propose a new feature in the MariaDB client-server protocolhttps://mariadb.com/kb/en/clientserver-protocol: application-layer redirection of client connections. We want the MariaDB server to be able to tell clients connecting to it, “Sorry, this server is unavailable. Connect to an alternate server instead.” This mechanism is inspired by HTTP 302 (“temporary redirect”) mechanism familiar to all developers of web applications, and is intended to have similar semantics and security properties, since these have now been widely deployed and tested for decades. I have submitted a minimal but viable implementation of this at: * https://github.com/MariaDB/server/pull/2681, server-side implementation * https://github.com/mariadb-corporation/mariadb-connector-c/pull/226, MariaDB Connector/C implementation I am seeking advice and opinions on these, and will proceed to finalize this in the following 1-2 months. In its current form, this implementation allows the MariaDB server administrator to set two variables: * SET GLOBAL SERVER_REDIRECT_MODE={ON,OFF} (the default is OFF) * SET GLOBAL SERVER_REDIRECT_TARGET='my-new-server.example.com' (or 192.168.0.123:3307, or new-server.com:3308, etc) When SERVER_REDIRECT_MODE is set to on, the server will stop accepting new TCP-based connections (unless they are to the extra porthttps://mariadb.com/kb/en/thread-pool-in-mariadb/#configuring-the-extra-port, which is intended for emergency administrative use), and it will instead respond to all clients with an error packethttps://mariadb.com/kb/en/err_packet/: $ mariadb --host my-server.example.com ERROR 4189 (HY000): |Server is redirecting clients to 'my-new-server.example.com:3307'|my-new-server.example.com:3307 When an appropriately-updated client receives this error packet (error code 4196, message formatted as |Human readable message|<value of SERVER_REDIRECT_TARGET>), it will parse the redirection target out from the error message and attempt a new connection: $ updated-mariadb --host my-server.example.com -e 'select @@hostname' Got server redirect to 'my-new-server.example.com' (port 3307) +---------------------------+ | @@hostname | +---------------------------+ | my-new-server.example.com | +---------------------------+ The feature is gracefully backwards-compatible. Old clients that do not support redirection will fail and show the message in a form from which humans can deduce what happened. New clients that do support it will follow it to the new server. The feature is also designed to be extensible in a forwards-compatible way. The SERVER_REDIRECT_MODE system variable could be expanded to support additional redirection modes. For example, it could be augmented to redirect new connections only from specific IP subnets, or to direct the client to interpret the SERVER_REDIRECT_TARGET variable in a new form (such as ahttps://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-jdbc-url-... JDBC-compatible mysql:// URLhttps://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-jdbc-url-... as proposed in MDEV-15935https://jira.mariadb.org/browse/MDEV-15935). While researching and designing this connection redirection mechanism, I discovered or rediscovered a couple of major TLS-related security vulnerabilities in the MariaDB Connector/C client [1, 2]. These vulnerabilities affect all MariaDB users who care about the confidentiality and authenticity of their client/server connections, and they become even more severe if a connection redirection mechanism exists — because this entails the client trusting the server to send it instructions for creating a new network connection. Unless these are resolved, any connection redirection mechanism will be susceptible to hijacking and redirection to an attacker-controlled server even if the client thinks it is using TLS/SSL. What are the use cases for this connection redirection feature? Even in this simple, initial form, the feature is already useful for database administrators who want to gracefully shut down a MariaDB server (for example, to upgrade it), and to redirect new client connections to a replica or alternate server that can accept the traffic. Server-side connection redirection can be combined with existing MariaDB server features, such as SET GLOBAL READ_ONLY=ON and KILLing of idle or long-running connections, in order to build a full switchover solution for shunting traffic from one server to another as gracefully as possible. Currently, MariaDB database administrators tend to use proxies and wrappers (such as HikariCP) to redirect client connections from one MariaDB server to another. Supporting this redirection mechanism directly in the server, and in the client-server protocol, will allow many simple use cases of redirection without extra software, making MariaDB easier and simpler to manage for minimal downtime situations. Please post on this mailing list what you think about this suggestion. I am looking forward to polishing this feature and submitting it in a form that meets the quality requirements for new features from MariaDB core developers. Thank you! Daniel Lenski Amazon RDS MySQL/MariaDB engine team --- Notes on TLS vulnerabilities in MariaDB Connector/C: [1] CONC-648https://jira.mariadb.org/browse/CONC-648, reported by me on 5 June: when a Connector/C-based client is using connections secured by TLS/SSL, Connector/C will nevertheless receive plain-text, unencrypted error packets prior to the TLS handshake, and present them to the client in a way that’s indistinguishable from TLS-protected error packets sent by the server after the TLS handshake. This longstanding vulnerability is a consequence of the tangled and unsystematic way that the Connector/C codebase interleaves the MariaDB application-layer authentication protocol with the transport-layer security protocol. My standalone mariadb-connector-c PR#223https://github.com/mariadb-corporation/mariadb-connector-c/pull/223 fixes the most straightforward manifestation of this vulnerability. Until this issue is resolved in Connector/C, it will be trivial for MITM attackers to inject plaintext connection redirection packets into MariaDB client/server connections, and thereby redirect clients to attacker-controlled servers despite those clients’ intention to use only TLS-secured connections. [2] MDEV-28634https://jira.mariadb.org/browse/MDEV-28634, reported almost 3 years ago by Geoff Montee: Connector/C clients which specify --ssl without --ssl-verify-server-cert will allow their connections to be silently downgraded to unencrypted, plaintext connections. This vulnerability can be used by on-path attackers to trivially downgrade most MariaDB client-server connections from TLS-encrypted to plaintext. My standalone mariadb-connector-c PR#224https://github.com/mariadb-corporation/mariadb-connector-c/pull/224 fixes this. It is a backwards-incompatible change in that clients which specify --ssl (and thus clearly expect TLS-secured connections) will no longer succeed in connecting to servers which don’t support TLS. This should be considered a good thing: MariaDB Connector/C-based clients have been misleading their users about whether their connections are guaranteed to be encrypted, and will no longer do so. Essentially all software that purports to secure network connections with TLS have implemented similar changes, most of it about a decade ago when the pervasiveness of such attackshttps://en.wikipedia.org/wiki/Global_surveillance_disclosures_(2013%E2%80%93... (by ISPs, censors, intelligenceagencies, etc) became very widely-known.
Hi, Daniel, On Jun 28, Lenski, Daniel via developers wrote:
Hi!
I would like to propose a new feature in the MariaDB client-server protocolhttps://mariadb.com/kb/en/clientserver-protocol: application-layer redirection of client connections.
We want the MariaDB server to be able to tell clients connecting to it, “Sorry, this server is unavailable. Connect to an alternate server instead.” This mechanism is inspired by HTTP 302 (“temporary redirect”) mechanism familiar to all developers of web applications, and is intended to have similar semantics and security properties, since these have now been widely deployed and tested for decades.
I have submitted a minimal but viable implementation of this at:
* https://github.com/MariaDB/server/pull/2681, server-side implementation * https://github.com/mariadb-corporation/mariadb-connector-c/pull/226, MariaDB Connector/C implementation
I'm afraid MDEV-15935 is a bit confusing. There are lots of discussions and ideas in the comments, like, using a specially modified OK packet, using the error message, etc. But the final consensus was to use session tracking feature, it allows for most flexibility and user control over how redirect is happening. To quote here: We'll add a new global/session variable, say, redirect_url. The value should be an empty string or a connection string in the conventional format (in the style of a connection url of C/J or C/NodeJS or C/C or FederatedX). This variable is appended to the default value of session_track_system_variables variable. When this variable is changed, the existing SESSION_TRACK_SYSTEM_VARIABLES implementation will send its new value to the client as a part of the OK packet. It's up to the client or to the connector to use this value or to ignore it. Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
Hi Sergei, On Thu, 2023-06-29 at 15:58 +0200, Sergei Golubchik wrote:
On Jun 28, Lenski, Daniel via developers wrote:
I would like to propose a new feature in the MariaDB client-server protocolhttps://mariadb.com/kb/en/clientserver-protocol: application-layer redirection of client connections.
We want the MariaDB server to be able to tell clients connecting to it, “Sorry, this server is unavailable. Connect to an alternate server instead.” This mechanism is inspired by HTTP 302 (“temporary redirect”) mechanism familiar to all developers of web applications, and is intended to have similar semantics and security properties, since these have now been widely deployed and tested for decades.
I have submitted a minimal but viable implementation of this at:
* https://github.com/MariaDB/server/pull/2681, server-side implementation * https://github.com/mariadb-corporation/mariadb-connector-c/pull/226, MariaDB Connector/C implementation
I'm afraid MDEV-15935 is a bit confusing. There are lots of discussions and ideas in the comments, like, using a specially modified OK packet, using the error message, etc.
Right! I got the sense from the Jira discussion (https://jira.mariadb.org/browse/MDEV-15935) that there's a large and varied "wish lish" of different capabilities for this feature — not all of which are practical or compatible. It has also been open for over 5 years without an accepted implementation. This is why I thought it would make a lot of sense to implement redirection in a very MINIMAL and SIMPLE form — but one that would already be useful in its initial form, and with a design that's amentable to future extensibility. (Many thanks to Otto for encouraging and guiding this implementation!)
But the final consensus was to use session tracking feature, it allows for most flexibility and user control over how redirect is happening. To quote here:
We'll add a new global/session variable, say, redirect_url. The value should be an empty string or a connection string in the conventional format (in the style of a connection url of C/J or C/NodeJS or C/C or FederatedX).
This variable is appended to the default value of session_track_system_variables variable.
When this variable is changed, the existing SESSION_TRACK_SYSTEM_VARIABLES implementation will send its new value to the client as a part of the OK packet. It's up to the client or to the connector to use this value or to ignore it.
I read through this proposal as well, but I didn't realize that it had been in any way chosen as the desired implementation. I am surprised, because there seems to be 2 key problems with it… (1) Redirection targets in "URL" format will be extremely difficult to support in consistent and secure ways across client implementations. The Connector/C library currently has no ability to parse connection "URLs" in either the Connector/J (https://mariadb.com/kb/en/about-mariadb-connector-j) or the MySQL formats ( https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-jdbc-url-...), which are partially but fully compatible with each other, and to my knowledge there is no formal definition of either. As far as I know, Connector/C is the basis for most higher-level languages’ connector libraries (e.g. Connector/Python). If the redirection target can contain all the different kinds of connection information that MariaDB Connector/J URLs support (very long list of optional parameters! https://mariadb.com/kb/en/about-mariadb-connector-j/#optional-url-parameters), it will be very difficult to ensure that all languages’ Connector/X libraries handle all of these parameters in a consistent and secure way when redirecting. For example, if a *server* sends a redirect URL containing the optional `keyStorePassword` field, should a *client* library trust or attempt to use this value? I think not. Or what if Connector/J observes the `timezone` parameter when redirecting to a new server, but Connector/C ignores it? This would be a source of inconsistent behavior and could frustrate users of the redirection feature. Precisely in order to avoid this level of complexity, my proposal includes a *much simpler* format for the redirection target: `host[:port]`. This should be simple enough for any and all Connector/X client libraries to implement in a uniform fashion. At the same time, it is designed to be extensible; if there are clear use cases for it, it could be extended in the future to include more complex formats for the redirection target, such as Connector/J-style and/or MySQL-style connection URLs. (2) Using OK packets and session-tracking, the server would have no way to enforce redirection. As you wrote, "It's up to the client or to the connector to use this value [of redirect_url] or to ignore it." In this form, redirection instructions sent by the server would be merely "advisory", and the server would have no way to ensure that clients stop connecting to it. - Clients using OLD Connector libraries will continue to be oblivious to this value, and to ignore it. As all of us have experienced, old/forgotten/abandoned/unpatched client software is often used for DECADES beyond its originally-intended lifespan. - Clients using NEW Connector libraries can be configured to ignore the redirection value. Quite likely many of them WILL do this, since connecting to a new server is more difficult and more error- prone than just continuing to send queries to the already-connected server. This "advisory" redirection would seem to be greatly limited in the kind of use cases it supports. If servers sending redirection information can't RELY ON clients disconnecting from them, then they can't use redirection as a prelude to shutting down the server. This is why my proposal instead includes the redirection information in an ERROR packet: supporting clients will be able to follow the redirection (or simply disconnect if they don't want to follow redirects, e.g. `mariadb --no-follow-redirects`), while older clients will be disconnected with a comprehensible error message. In this way, a MariaDB server will be able to RELY ON clients disconnecting from it, which will simplify and clarify the usage of redirection for server shutdown. Note that my initial proposal only involves sending ER_SERVER_REDIRECT error packets to *newly- connecting* clients; in the future, it could also be extended to send similar packets to *already-existing* clients, for example for load-balancing purposes. Thanks, Daniel Lenski Amazon RDS MySQL/MariaDB engine team
Hi, I think that the two implementations can coexist and my suggestion in MDEV-15935 https://jira.mariadb.org/browse/MDEV-15935 about making the error packet version a "forceful" redirection would allow that. I believe that a reasonable way of doing a very graceful redirection would be as follows: * Turn on the redirection to another server (SET GLOBAL server_redirect_mode=ON). This then causes an advisory redirection to be added to all OK packets as well as EOF packets if the deprecate EOF protocol is in use. Newer client libraries would use this to migrate over to another server once their transactions are over and older clients that either don't implement the deprecate EOF protocol or don't know about redirection would remain oblivious to this. * After some grace period, the forceful redirection would be turned on (SET GLOBAL server_redirect_mode=FORCE). This would turn the advisory redirection into the currently proposed forceful redirection that sends an error packet. This amounts to the same as lowering max_connections to prevent new connections from taking place except that you get a different error message. * Once forceful redirection is in effect, you would wait for open transactions to complete, possibly after turn on read_only, and then shut down the server. As for the redirection information, a URL would be a more flexible way of connecting and would allow things like UNIX domain sockets and named pipes to be used as redirection targets. I definitely agree that a simple host and port approach (correctly formatted to support IPv6 of course) must be accepted as a redirection by all client libraries but some form of standardization for the redirection format string is needed or at least how client libraries should interpret unknown values. The forceful redirection has one minor problem that the advisory redirection does not have: only one redirection target can be given in the current form. The reason for multi-host redirections would be to prevent sudden spikes of new connections from happening on the server where the redirection is being done. The advisory redirection, as mentioned in MDEV-15935, could use SQL to set a session-level value which in turn opens up limitless possibilities of using it for load balanced redirection. This is probably a very minor in the grand scheme of things and having one host to redirect to is probably OK in most cases if the advisory redirection is implemented on top of the forceful one: the former would shift most of the connections away from the server and the latter would only be the final step that prevents new ones from being created. For things like JDBC connection pools that support multiple hosts, redirections are technically already implemented: I believe they just pick another server when they get a Too many connections error. The benefits of a forceful redirection are thus minimal for these use-cases and the advisory one would be much more effective in avoiding new work from being placed on servers that are about to shut down. However even in this case the redirection error could be interpreted as an instruction to avoid using the given server for some period of time. Markus On 6/29/23 22:30, Lenski, Daniel via developers wrote:
Hi Sergei,
On Thu, 2023-06-29 at 15:58 +0200, Sergei Golubchik wrote:
On Jun 28, Lenski, Daniel via developers wrote:
I would like to propose a new feature in the MariaDB client-server protocolhttps://mariadb.com/kb/en/clientserver-protocol: application-layer redirection of client connections.
We want the MariaDB server to be able to tell clients connecting to it, “Sorry, this server is unavailable. Connect to an alternate server instead.” This mechanism is inspired by HTTP 302 (“temporary redirect”) mechanism familiar to all developers of web applications, and is intended to have similar semantics and security properties, since these have now been widely deployed and tested for decades.
I have submitted a minimal but viable implementation of this at:
*https://github.com/MariaDB/server/pull/2681, server-side implementation *https://github.com/mariadb-corporation/mariadb-connector-c/pull/226, MariaDB Connector/C implementation I'm afraid MDEV-15935 is a bit confusing. There are lots of discussions and ideas in the comments, like, using a specially modified OK packet, using the error message, etc. Right! I got the sense from the Jira discussion (https://jira.mariadb.org/browse/MDEV-15935) that there's a large and varied "wish lish" of different capabilities for this feature — not all of which are practical or compatible.
It has also been open for over 5 years without an accepted implementation.
This is why I thought it would make a lot of sense to implement redirection in a very MINIMAL and SIMPLE form — but one that would already be useful in its initial form, and with a design that's amentable to future extensibility. (Many thanks to Otto for encouraging and guiding this implementation!)
But the final consensus was to use session tracking feature, it allows for most flexibility and user control over how redirect is happening. To quote here:
We'll add a new global/session variable, say, redirect_url. The value should be an empty string or a connection string in the conventional format (in the style of a connection url of C/J or C/NodeJS or C/C or FederatedX).
This variable is appended to the default value of session_track_system_variables variable.
When this variable is changed, the existing SESSION_TRACK_SYSTEM_VARIABLES implementation will send its new value to the client as a part of the OK packet. It's up to the client or to the connector to use this value or to ignore it. I read through this proposal as well, but I didn't realize that it had been in any way chosen as the desired implementation. I am surprised, because there seems to be 2 key problems with it…
(1) Redirection targets in "URL" format will be extremely difficult to support in consistent and secure ways across client implementations.
The Connector/C library currently has no ability to parse connection "URLs" in either the Connector/J (https://mariadb.com/kb/en/about-mariadb-connector-j) or the MySQL formats ( https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-jdbc-url-...), which are partially but fully compatible with each other, and to my knowledge there is no formal definition of either. As far as I know, Connector/C is the basis for most higher-level languages’ connector libraries (e.g. Connector/Python).
If the redirection target can contain all the different kinds of connection information that MariaDB Connector/J URLs support (very long list of optional parameters! https://mariadb.com/kb/en/about-mariadb-connector-j/#optional-url-parameters), it will be very difficult to ensure that all languages’ Connector/X libraries handle all of these parameters in a consistent and secure way when redirecting.
For example, if a *server* sends a redirect URL containing the optional `keyStorePassword` field, should a *client* library trust or attempt to use this value? I think not. Or what if Connector/J observes the `timezone` parameter when redirecting to a new server, but Connector/C ignores it? This would be a source of inconsistent behavior and could frustrate users of the redirection feature.
Precisely in order to avoid this level of complexity, my proposal includes a *much simpler* format for the redirection target: `host[:port]`. This should be simple enough for any and all Connector/X client libraries to implement in a uniform fashion. At the same time, it is designed to be extensible; if there are clear use cases for it, it could be extended in the future to include more complex formats for the redirection target, such as Connector/J-style and/or MySQL-style connection URLs.
(2) Using OK packets and session-tracking, the server would have no way to enforce redirection.
As you wrote, "It's up to the client or to the connector to use this value [of redirect_url] or to ignore it."
In this form, redirection instructions sent by the server would be merely "advisory", and the server would have no way to ensure that clients stop connecting to it.
- Clients using OLD Connector libraries will continue to be oblivious to this value, and to ignore it. As all of us have experienced, old/forgotten/abandoned/unpatched client software is often used for DECADES beyond its originally-intended lifespan. - Clients using NEW Connector libraries can be configured to ignore the redirection value. Quite likely many of them WILL do this, since connecting to a new server is more difficult and more error- prone than just continuing to send queries to the already-connected server.
This "advisory" redirection would seem to be greatly limited in the kind of use cases it supports. If servers sending redirection information can't RELY ON clients disconnecting from them, then they can't use redirection as a prelude to shutting down the server.
This is why my proposal instead includes the redirection information in an ERROR packet: supporting clients will be able to follow the redirection (or simply disconnect if they don't want to follow redirects, e.g. `mariadb --no-follow-redirects`), while older clients will be disconnected with a comprehensible error message. In this way, a MariaDB server will be able to RELY ON clients disconnecting from it, which will simplify and clarify the usage of redirection for server shutdown. Note that my initial proposal only involves sending ER_SERVER_REDIRECT error packets to *newly- connecting* clients; in the future, it could also be extended to send similar packets to *already-existing* clients, for example for load-balancing purposes.
Thanks,
Daniel Lenski Amazon RDS MySQL/MariaDB engine team _______________________________________________ developers mailing list --developers@lists.mariadb.org To unsubscribe send an email todevelopers-leave@lists.mariadb.org
-- Markus Mäkelä, Senior Software Engineer MariaDB Corporation
Hi, Daniel, On Jun 29, Lenski, Daniel wrote:
Hi Sergei,
I'm afraid MDEV-15935 is a bit confusing. There are lots of discussions and ideas in the comments, like, using a specially modified OK packet, using the error message, etc.
Right! I got the sense from the Jira discussion (https://jira.mariadb.org/browse/MDEV-15935) that there's a large and varied "wish lish" of different capabilities for this feature — not all of which are practical or compatible.
It has also been open for over 5 years without an accepted implementation.
There was an original pull request from ~5 years ago, but then the contributor disappeared and I didn't want to do anything until they'd at least confirm that the new suggested approach works for them. We waited until we got new users requesting this very feature.
This is why I thought it would make a lot of sense to implement redirection in a very MINIMAL and SIMPLE form — but one that would already be useful in its initial form, and with a design that's amentable to future extensibility. (Many thanks to Otto for encouraging and guiding this implementation!)
But the final consensus was to use session tracking feature, it allows for most flexibility and user control over how redirect is happening. To quote here:
We'll add a new global/session variable, say, redirect_url. The value should be an empty string or a connection string in the conventional format (in the style of a connection url of C/J or C/NodeJS or C/C or FederatedX).
This variable is appended to the default value of session_track_system_variables variable.
When this variable is changed, the existing SESSION_TRACK_SYSTEM_VARIABLES implementation will send its new value to the client as a part of the OK packet. It's up to the client or to the connector to use this value or to ignore it.
I read through this proposal as well, but I didn't realize that it had been in any way chosen as the desired implementation. I am surprised, because there seems to be 2 key problems with it…
(1) Redirection targets in "URL" format will be extremely difficult to support in consistent and secure ways across client implementations.
MDEV-15935 itself (the server part) does not specify what the URL should look like, it's only about passing basically an arbitrary string to the client. Although the original PR used URLs, Federated uses URLs, Connector/J uses URLs - it's de facto very common already. It might be good to standardize the URL format and use it everywhere.
The Connector/C library currently has no ability to parse connection "URLs" in either the Connector/J (https://mariadb.com/kb/en/about-mariadb-connector-j) or the MySQL formats ( https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-jdbc-url-...), which are partially but fully compatible with each other, and to my knowledge there is no formal definition of either. As far as I know, Connector/C is the basis for most higher-level languages’ connector libraries (e.g. Connector/Python).
Not quite. Connector/C can parse both DSN format (key=value pairs, semicolon-separated) and URI format, See https://github.com/mariadb-corporation/mariadb-connector-c/blob/5af90f00ff/l... and https://github.com/mariadb-corporation/mariadb-connector-c/blob/5af90f00ff/l... although the latter cannot be used for normal connections yet, which is good, becayse without standard URI syntax it would've definitely been incompatible with everything.
If the redirection target can contain all the different kinds of connection information that MariaDB Connector/J URLs support (very long list of optional parameters! https://mariadb.com/kb/en/about-mariadb-connector-j/#optional-url-parameters), it will be very difficult to ensure that all languages’ Connector/X libraries handle all of these parameters in a consistent and secure way when redirecting.
Right, that's why I moved URI topic out of MDEV-15935, making the latter only about "how to pass <arbitrary string> to the client" There are two common approaches to a connection string - let's call them "JDBC way" (that's URI) and "ODBC way" (semicolon separated key=value pairs). mysqljs uses URI, Perl DBI uses DSN, PHP PDO uses DSN, Federated engine uses URI, etc. I'd really rather avoid introducing a new connection string format.
Precisely in order to avoid this level of complexity, my proposal includes a *much simpler* format for the redirection target: `host[:port]`. This should be simple enough for any and all Connector/X client libraries to implement in a uniform fashion. At the same time, it is designed to be extensible; if there are clear use cases for it, it could be extended in the future to include more complex formats for the redirection target, such as Connector/J-style and/or MySQL-style connection URLs.
MDEV-15935 says that we "might" optionally implement URI validation in the server. Without it one can literally send an arbitrary string. This might be error-prone. With the server side URI validation we can limit redirection URIs to "mysql://host[:port]" which basically is what you wanted and postpones all the URI complexity till we have some standard specs for it.
(2) Using OK packets and session-tracking, the server would have no way to enforce redirection. As you wrote, "It's up to the client or to the connector to use this value [of redirect_url] or to ignore it." In this form, redirection instructions sent by the server would be merely "advisory", and the server would have no way to ensure that clients stop connecting to it.
Correct. This was the point.
This "advisory" redirection would seem to be greatly limited in the kind of use cases it supports. If servers sending redirection information can't RELY ON clients disconnecting from them, then they can't use redirection as a prelude to shutting down the server.
Of course, they can. The idea is to tell clients to connect to another server and to disconnect them after that. If redirection is forced, then the server will send a "redirection error" and disconnect. If redirection is advisory, the server will send a redirection info and disconnect few seconds later when it shuts down. Anyway, in the original proposal (from ~5 years ago) it was advisory, apparently it's what the user needed. Currently the main user requesting it is Markus, and he's apparently fine with advisory too. When we'll get someone requesting forceful redirection *for his own use case* - then, I'm sure, we can extend this approach to redirect and disconnect. The problem is - I don't see a logical way of extending suggested in MDEV-15935 approach to be forceful. I'll add a comment there about your error message based implementation, just to have all implementation details in one place, in Jira. Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
On Fri, 2023-06-30 at 13:29 +0200, Sergei Golubchik wrote:
There was an original pull request from ~5 years ago, but then the contributor disappeared and I didn't want to do anything until they'd at least confirm that the new suggested approach works for them Are you referring to this Connector/J PR, which attempted to add client-side parsing of a redirection string in a URL-ish format? https://github.com/mariadb-corporation/mariadb-connector-j/pull/134/files#di...
(1) Redirection targets in "URL" format will be extremely difficult to support in consistent and secure ways across client implementations.
MDEV-15935 itself (the server part) does not specify what the URL should look like, it's only about passing basically an arbitrary string to the client.
Giving the server the ability to passing "an arbitrary string" to the client will create many more problems than it will solve, if there is no agreement for how the client software should interpret that string. If the server can send a redirect string that looks something like "mysql://UserB@other-server.company.com:3306/my_database?ttl=10&user=UserA" (as proposed in the stale Connector/J PR), but Connector/C ignores the query parameter fields including the username, and Connector/J misinterprets UserB as part of the hostname… that’s chaos, not a solution.
Although the original PR used URLs, Federated uses URLs, Connector/J uses URLs - it's de facto very common already. It might be good to standardize the URL format and use it everywhere.
Agreed. However, this is a complex undertaking, and older/non-standard URL formats WILL continue to proliferate if existing clients and servers continue to accept them or silently ignore them.
The Connector/C library currently has no ability to parse connection "URLs" in either the Connector/J (https://mariadb.com/kb/en/about-mariadb-connector-j) or the MySQL formats ( https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-jdbc-url-...), which are partially but fully compatible with each other, and to my knowledge there is no formal definition of either. As far as I know, Connector/C is the basis for most higher-level languages’ connector libraries (e.g. Connector/Python).
Not quite. Connector/C can parse both DSN format (key=value pairs, semicolon-separated) and URI format, See https://github.com/mariadb-corporation/mariadb-connector-c/blob/5af90f00ff/l... and https://github.com/mariadb-corporation/mariadb-connector-c/blob/5af90f00ff/l...
although the latter cannot be used for normal connections yet, which is good, becayse without standard URI syntax it would've definitely been incompatible with everything.
All that the second snippet appears to show is that Connector/C can look for "://" in a string; as you say, it "cannot be used for normal connections yet." This does not mean that it can parse a string like "jdbc:mysql://mariadb.server.com:3306/defaultDB?characterEncoding=latin1" and set all of its relevant internal state in a way that's equivalent to what Connector/J does. Because of this, it would be inadvisably premature to ALLOW servers to SEND a JDBC URL like that as a redirection target, since we cannot expect various client software/libraries to be able handle that URL in a consistent way. Compare this with HTTP 3xx redirection, which has very well-defined semantics in terms of caching, HTTP methods, etc (summarized in https://en.wikipedia.org/wiki/URL_redirection#HTTP_status_codes_3xx) . Because of that servers can generally rely on clients to follow redirection instructions in a consistent way.
Right, that's why I moved URI topic out of MDEV-15935, making the latter only about "how to pass <arbitrary string> to the client"
There are two common approaches to a connection string - let's call them "JDBC way" (that's URI) and "ODBC way" (semicolon separated key=value pairs). mysqljs uses URI, Perl DBI uses DSN, PHP PDO uses DSN, Federated engine uses URI, etc.
I'd really rather avoid introducing a new connection string format.
Agreed, not introducing a new format makes sense.
MDEV-15935 says that we "might" optionally implement URI validation in the server. Without it one can literally send an arbitrary string. This might be error-prone. With the server side URI validation we can limit redirection URIs to "mysql://host[:port]" which basically is what you wanted and postpones all the URI complexity till we have some standard specs for it.
I'd be happy to modify my PRs to only allow SERVER_REDIRECT_TARGET of the form "mysql://host[:port]", to avoid introducing a new format.
This "advisory" redirection would seem to be greatly limited in the kind of use cases it supports. If servers sending redirection information can't RELY ON clients disconnecting from them, then they can't use redirection as a prelude to shutting down the server.
Of course, they can. The idea is to tell clients to connect to another server and to disconnect them after that. If redirection is forced, then the server will send a "redirection error" and disconnect. If redirection is advisory, the server will send a redirection info and disconnect few seconds later when it shuts down.
That would be substantially more complex to implement. If the server can simply send an error packet that says "Go somewhere else" and then disconnect the client, it doesn't need to track any further information on the connection. If the server needs to send an advisory packet that says "Please go somewhere else or I'll disconnect you in 15 seconds", and then continue handling that connection as normal, and then interrupt and disconnect it 15 seconds later… that requires a lot more state tracking. (What happens if the client starts a long-running query, or does some non-transactional DDL, or does anything with a non- transactional, non-crash-safe engine?)
Anyway, in the original proposal (from ~5 years ago) it was advisory, apparently it's what the user needed.
But WHY did the user need that? What are the use cases for "advisory-ONLY" redirection? Does it make sense to design and implement this feature be based on what one user thought would be useful 5 years ago, rather than basing it on a stateless redirection model that has been successfully used in HTTP for 25+ years? Thanks, Daniel Lenski Amazon RDS MySQL/MariaDB engine team
Hi, Lenski,, On Jun 30, Lenski, Daniel wrote:
On Fri, 2023-06-30 at 13:29 +0200, Sergei Golubchik wrote:
There was an original pull request from ~5 years ago, but then the contributor disappeared and I didn't want to do anything until they'd at least confirm that the new suggested approach works for them Are you referring to this Connector/J PR, which attempted to add client-side parsing of a redirection string in a URL-ish format? https://github.com/mariadb-corporation/mariadb-connector-j/pull/134/files#di...
Yes
(1) Redirection targets in "URL" format will be extremely difficult to support in consistent and secure ways across client implementations.
MDEV-15935 itself (the server part) does not specify what the URL should look like, it's only about passing basically an arbitrary string to the client.
Giving the server the ability to passing "an arbitrary string" to the client will create many more problems than it will solve, if there is no agreement for how the client software should interpret that string.
Agree. I just meant that there're two questions. What to send. And how to send. And that MDEV-15935 was mainly about "how". But it'd be better if it would implement a simple answer to "what" too, like that very limited URI of the form mysql://host[:port]
Although the original PR used URLs, Federated uses URLs, Connector/J uses URLs - it's de facto very common already. It might be good to standardize the URL format and use it everywhere.
Agreed. However, this is a complex undertaking, and older/non-standard URL formats WILL continue to proliferate if existing clients and servers continue to accept them or silently ignore them.
That's why it's not part of MDEV-15935 :)
Not quite. Connector/C can parse both DSN format (key=value pairs, semicolon-separated) and URI format, See https://github.com/mariadb-corporation/mariadb-connector-c/blob/5af90f00ff/l... and https://github.com/mariadb-corporation/mariadb-connector-c/blob/5af90f00ff/l...
although the latter cannot be used for normal connections yet, which is good, becayse without standard URI syntax it would've definitely been incompatible with everything.
All that the second snippet appears to show is that Connector/C can look for "://" in a string; as you say, it "cannot be used for normal connections yet."
It calls a "connection plugin" for that. And at the first glance it looks like the only connection plugin is replication, it I'd assume that Connector/C can understand URI in the form of replication://..... Anyway, Connector/C has the support for URIs and adding a "mysql" connection plugin is not only possible, it'll fit in nicely into the existing code, there's place for it already.
Because of this, it would be inadvisably premature to ALLOW servers to SEND a JDBC URL like that as a redirection target, since we cannot expect various client software/libraries to be able handle that URL in a consistent way.
Yes. Also because FederatedX and mysqljs use somewhat different set of parameters, so why should it follow JDBC URI specs? We need to do this "complex undertaking" first and write some specs for the URI format. For now it seems we both agree that it's better to keep it minimal.
This "advisory" redirection would seem to be greatly limited in the kind of use cases it supports. If servers sending redirection information can't RELY ON clients disconnecting from them, then they can't use redirection as a prelude to shutting down the server.
Of course, they can. The idea is to tell clients to connect to another server and to disconnect them after that. If redirection is forced, then the server will send a "redirection error" and disconnect. If redirection is advisory, the server will send a redirection info and disconnect few seconds later when it shuts down.
That would be substantially more complex to implement.
If the server needs to send an advisory packet that says "Please go somewhere else or I'll disconnect you in 15 seconds", and then continue handling that connection as normal, and then interrupt and disconnect it 15 seconds later… that requires a lot more state tracking.
Oh, I'm sorry, I didn't mean that. I didn't mean that the server needs to implement or track anything at all. What I was saying - if the server will send this advisory redirection message and then continue its normal shutdown process, it'll inevitably disconnect every client anyway. That is, either way the client gets a redirection message and then gets disconnected.
Anyway, in the original proposal (from ~5 years ago) it was advisory, apparently it's what the user needed.
But WHY did the user need that? What are the use cases for "advisory-ONLY" redirection?
I don't really know. Judging from the profile of the first contributor it seems like something they need in Azure. But I know nothing about Azure internals, so cannot say why.
Does it make sense to design and implement this feature be based on what one user thought would be useful 5 years ago, rather than basing it on a stateless redirection model that has been successfully used in HTTP for 25+ years?
well, I just don't think I know more than users about their application. If someone would explain why they need advisory redirection, I might be able to argue that they actually need forceful redirection. But as long as I cannot, I have to assume that advisory redirection is what users need. Instead of trying to invent a solution for a problem that I don't know. Ideally we'd have both, of course. Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
Hi Georg!
As the maintainer of MariaDB Connector C, what do you think about
https://github.com/mariadb-corporation/mariadb-connector-c/pull/226/commits/...
Do you agree it makes sense to have a new error that can tell clients
to go to another server (similar to how HTTP 301 redirects work)?
I would very much like to hear your opinion on this one. If you are in
agreement, Daniel can spend more time polishing the feature and also
taking care of implementation details such as preventing redirect
loops and other corner cases.
Seems Markus and Sergei are already supportive of continuing the
efforts on this. We also plan to seek input from others (e.g. MySQL,
Percona, ProxySQL) to get widespread agreement on this new protocol
feature but we wanted to get this in MariaDB first as a collaboration
with MariaDB developers.
- Otto
On Wed, 28 Jun 2023 at 15:24, Lenski, Daniel via developers
Hi!
I would like to propose a new feature in the MariaDB client-server protocol: application-layer redirection of client connections.
We want the MariaDB server to be able to tell clients connecting to it, “Sorry, this server is unavailable. Connect to an alternate server instead.” This mechanism is inspired by HTTP 302 (“temporary redirect”) mechanism familiar to all developers of web applications, and is intended to have similar semantics and security properties, since these have now been widely deployed and tested for decades.
I have submitted a minimal but viable implementation of this at:
https://github.com/MariaDB/server/pull/2681, server-side implementation
https://github.com/mariadb-corporation/mariadb-connector-c/pull/226, MariaDB Connector/C implementation
I am seeking advice and opinions on these, and will proceed to finalize this in the following 1-2 months.
In its current form, this implementation allows the MariaDB server administrator to set two variables:
SET GLOBAL SERVER_REDIRECT_MODE={ON,OFF} (the default is OFF)
SET GLOBAL SERVER_REDIRECT_TARGET='my-new-server.example.com' (or 192.168.0.123:3307, or new-server.com:3308, etc)
When SERVER_REDIRECT_MODE is set to on, the server will stop accepting new TCP-based connections (unless they are to the extra port, which is intended for emergency administrative use), and it will instead respond to all clients with an error packet:
$ mariadb --host my-server.example.com
ERROR 4189 (HY000): |Server is redirecting clients to 'my-new-server.example.com:3307'|my-new-server.example.com:3307
When an appropriately-updated client receives this error packet (error code 4196, message formatted as |Human readable message|<value of SERVER_REDIRECT_TARGET>), it will parse the redirection target out from the error message and attempt a new connection:
$ updated-mariadb --host my-server.example.com -e 'select @@hostname'
Got server redirect to 'my-new-server.example.com' (port 3307)
+---------------------------+
| @@hostname |
+---------------------------+
| my-new-server.example.com |
+---------------------------+
The feature is gracefully backwards-compatible. Old clients that do not support redirection will fail and show the message in a form from which humans can deduce what happened. New clients that do support it will follow it to the new server.
The feature is also designed to be extensible in a forwards-compatible way. The SERVER_REDIRECT_MODE system variable could be expanded to support additional redirection modes. For example, it could be augmented to redirect new connections only from specific IP subnets, or to direct the client to interpret the SERVER_REDIRECT_TARGET variable in a new form (such as a JDBC-compatible mysql:// URL as proposed in MDEV-15935).
While researching and designing this connection redirection mechanism, I discovered or rediscovered a couple of major TLS-related security vulnerabilities in the MariaDB Connector/C client [1, 2]. These vulnerabilities affect all MariaDB users who care about the confidentiality and authenticity of their client/server connections, and they become even more severe if a connection redirection mechanism exists — because this entails the client trusting the server to send it instructions for creating a new network connection. Unless these are resolved, any connection redirection mechanism will be susceptible to hijacking and redirection to an attacker-controlled server even if the client thinks it is using TLS/SSL.
What are the use cases for this connection redirection feature?
Even in this simple, initial form, the feature is already useful for database administrators who want to gracefully shut down a MariaDB server (for example, to upgrade it), and to redirect new client connections to a replica or alternate server that can accept the traffic. Server-side connection redirection can be combined with existing MariaDB server features, such as SET GLOBAL READ_ONLY=ON and KILLing of idle or long-running connections, in order to build a full switchover solution for shunting traffic from one server to another as gracefully as possible.
Currently, MariaDB database administrators tend to use proxies and wrappers (such as HikariCP) to redirect client connections from one MariaDB server to another. Supporting this redirection mechanism directly in the server, and in the client-server protocol, will allow many simple use cases of redirection without extra software, making MariaDB easier and simpler to manage for minimal downtime situations.
Please post on this mailing list what you think about this suggestion. I am looking forward to polishing this feature and submitting it in a form that meets the quality requirements for new features from MariaDB core developers.
Thank you!
Daniel Lenski
Amazon RDS MySQL/MariaDB engine team
---
Notes on TLS vulnerabilities in MariaDB Connector/C:
[1] CONC-648, reported by me on 5 June: when a Connector/C-based client is using connections secured by TLS/SSL, Connector/C will nevertheless receive plain-text, unencrypted error packets prior to the TLS handshake, and present them to the client in a way that’s indistinguishable from TLS-protected error packets sent by the server after the TLS handshake.
This longstanding vulnerability is a consequence of the tangled and unsystematic way that the Connector/C codebase interleaves the MariaDB application-layer authentication protocol with the transport-layer security protocol. My standalone mariadb-connector-c PR#223 fixes the most straightforward manifestation of this vulnerability.
Until this issue is resolved in Connector/C, it will be trivial for MITM attackers to inject plaintext connection redirection packets into MariaDB client/server connections, and thereby redirect clients to attacker-controlled servers despite those clients’ intention to use only TLS-secured connections.
[2] MDEV-28634, reported almost 3 years ago by Geoff Montee: Connector/C clients which specify --ssl without --ssl-verify-server-cert will allow their connections to be silently downgraded to unencrypted, plaintext connections. This vulnerability can be used by on-path attackers to trivially downgrade most MariaDB client-server connections from TLS-encrypted to plaintext. My standalone mariadb-connector-c PR#224 fixes this. It is a backwards-incompatible change in that clients which specify --ssl (and thus clearly expect TLS-secured connections) will no longer succeed in connecting to servers which don’t support TLS. This should be considered a good thing: MariaDB Connector/C-based clients have been misleading their users about whether their connections are guaranteed to be encrypted, and will no longer do so. Essentially all software that purports to secure network connections with TLS have implemented similar changes, most of it about a decade ago when the pervasiveness of such attacks (by ISPs, censors, intelligenceagencies, etc) became very widely-known.
_______________________________________________ developers mailing list -- developers@lists.mariadb.org To unsubscribe send an email to developers-leave@lists.mariadb.org
Hi Otto!
Do you agree it makes sense to have a new error that can tell clients to go to another server (similar to how HTTP 301 redirects work)?
I would very much like to hear your opinion on this one. If you are in agreement, Daniel can spend more time polishing the feature and also taking care of implementation details such as preventing redirect loops and other corner cases.
If we want to implement redirection via error message, then the client needs an option to enable redirecting, it should be disabled by default. The current PR introduces a new vulnerability, since the client redirects automatically under the hood. /Georg -- Georg Richter, Staff Software Engineer Client Connectivity MariaDB Corporation Ab
Hi Georg, On Wed, 2023-07-05 at 20:15 +0200, Georg Richter wrote: Do you agree it makes sense to have a new error that can tell clients to go to another server (similar to how HTTP 301 redirects work)? I would very much like to hear your opinion on this one. If you are in agreement, Daniel can spend more time polishing the feature and also taking care of implementation details such as preventing redirect loops and other corner cases. If we want to implement redirection via error message, then the client needs an option… Done. 1. Added to the Connector/C PR in https://github.com/mariadb-corporation/mariadb-connector-c/pull/226/commits/... 2. Added to the mariadb CLI as part of the MariaDB/Server PR, in https://github.com/MariaDB/server/pull/2681/commits/b43f5909605c8b31cef22156... … an option to enable redirecting, it should be disabled by default. Why does it make sense for it to be disabled by default? Again, I'm trying to follow the semantics of HTTP 3xx redirects here. Most HTTP(S) clients (e.g. web browsers) do follow redirections by default, and only disable them in specific cases where the server configuration is broken. For example, if the client encounters a redirect loop, or gets redirected to a server with an invalid certificate; the latter case has nothing to do with redirection per se. The current PR introduces a new vulnerability, since the client redirects automatically under the hood. Why should automatic redirection be considered a vulnerability? If a client, C1, trusts a MariaDB database server, S1, to handle its queries correctly, then presumably it should also trust that server to tell it to go redirect to another server, S2, and issues its queries to that alternate server instead. This is precisely the logic behind automatic redirection in HTTPS. If I trust "https://www.search-engine.com/?q=search+terms", then I shouldn't mind if the server tells me "go issue this same query to our secondary server at https://www2.search-engine.com/?q=search+terms". If redirection offered an opportunity for untrusted third parties to redirect to attacker-controlled servers, that would be a vulnerability. And that is precisely why it is crucial to plug the pre-existing https://jira.mariadb.org/browse/CONC-648 vulnerability while implementing redirection. Thanks, Daniel
In my opinion, disabled by default would be the correct choice except where using x509 certificate and requires server cert verification where it would be okay to enable redirection because an untrusted server should fail the verification. On Wed, 5 Jul 2023, 2:02 pm Lenski, Daniel via developers, < developers@lists.mariadb.org> wrote:
Hi Georg,
On Wed, 2023-07-05 at 20:15 +0200, Georg Richter wrote:
Do you agree it makes sense to have a new error that can tell clients to go to another server (similar to how HTTP 301 redirects work)?
I would very much like to hear your opinion on this one. If you are in agreement, Daniel can spend more time polishing the feature and also taking care of implementation details such as preventing redirect loops and other corner cases.
If we want to implement redirection via error message, then the client needs an option…
Done.
1. Added to the Connector/C PR in https://github.com/mariadb-corporation/mariadb-connector-c/pull/226/commits/... 2. Added to the mariadb CLI as part of the MariaDB/Server PR, in https://github.com/MariaDB/server/pull/2681/commits/b43f5909605c8b31cef22156...
… an option to enable redirecting, it should be disabled by default.
Why does it make sense for it to be *disabled by default*?
Again, I'm trying to follow the semantics of HTTP 3xx redirects here. Most HTTP(S) clients (e.g. web browsers) do follow redirections by default, and only disable them in specific cases where the server configuration is broken. For example, if the client encounters a redirect loop, or gets redirected to a server with an invalid certificate; the latter case has nothing to do with redirection *per se*.
The current PR introduces a new vulnerability, since the client redirects automatically under the hood.
Why should automatic redirection be considered a vulnerability?
If a client, C1, trusts a MariaDB database server, S1, to handle its queries correctly, then presumably it should *also *trust that server to tell it to go redirect to another server, S2, and issues its queries to that alternate server instead.
This is precisely the logic behind automatic redirection in HTTPS. If I trust "https://www.search-engine.com/?q=search+terms", then I shouldn't mind if the server tells me "go issue this same query to our secondary server at https://www*2*.search-engine.com/?q=search+terms".
If redirection offered an opportunity for untrusted third parties to redirect to attacker-controlled servers, that *would be a vulnerability*. And that is precisely why it is crucial to plug the pre-existing https://jira.mariadb.org/browse/CONC-648 vulnerability while implementing redirection.
Thanks, Daniel _______________________________________________ developers mailing list -- developers@lists.mariadb.org To unsubscribe send an email to developers-leave@lists.mariadb.org
On Sat, 2023-07-08 at 12:42 -0700, Antony T Curtis wrote:
In my opinion, disabled by default would be the correct choice except where using x509 certificate and requires server cert verification where it would be okay to enable redirection because an untrusted server should fail the verification.
Okay. A connection without TLS [1] or with TLS but without server cert verification [2] is already technically easy to MITM or silently downgrade to plaintext [3], but it is true that the risk is magnified by redirection, because a server that's on a trusted/local network may redirect to a server outside this local network, thus magnifying the practical concern. I will modify the Connector/C PR (https://github.com/mariadb-corporation/mariadb-connector-c/commit/54886ac29d...) such that… * If, and only if, the `ssl-verify-server-cert` or `ssl-fp-list` options are enabled, then Connector/C will enable `follow-server-redirects` by default. * Otherwise, `follow-server-redirects` will be disabled by default (but it will still be possible to enable it by explicitly specifying this option). Thanks, Daniel Lenski Amazon RDS MySQL/MariaDB engine team [1] `--ssl=OFF` [2] e.g. `--ssl` but not `--ssl-verify-server-cert` for the command-line client [3] Due to https://jira.mariadb.org/browse/MDEV-28634
Hi Daniel, Have you considered using AUTH_SWITCH_REQUEST for that purpose? That would allow redirect to happen after switch to TLS and client/server certificate validation. Server: server hello packet Client: switches to tls, sends client hello packet Server: sends authentication switch request, plugin name: "client_connect_redirect_plugin", plugin data: a string with new connection url Server closes connection ( or: client sends redirect confirmation auth plugin response message, server sends err packet and closes connection ) Clients that do not support redirect would show error message similar to "server wants client_connect_redirect_plugin authentication that this client does not allow". Andrey
Hi, Andrey, On Jul 19, andrey.sidorov--- via developers wrote:
Hi Daniel,
Have you considered using AUTH_SWITCH_REQUEST for that purpose? That would allow redirect to happen after switch to TLS and client/server certificate validation.
One of the main use cases at the moment is to redirect clients to another server when this current server is being shut down. For that we need to be able to send the redirect information in the middle of the session, not only when a connection is being established. Both session tracking and error message approach allow that. Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
But even err packet with redirect information would require some sort of server? The "redirect" server/proxy might handle ssl + auth switch and client_connect_redirect_plugin just as well
Have you considered using AUTH_SWITCH_REQUEST for that purpose? That would allow redirect to happen after switch to TLS and client/server certificate validation.
One of the main use cases at the moment is to redirect clients to another server when this current server is being shut down.
For that we need to be able to send the redirect information in the middle of the session, not only when a connection is being established. Both session tracking and error message approach allow that.
Not strictly so - we don't *need* to send the redirect information in the middle of the session. The session can just be dropped, and when clients automatically reconnect they will get the error message (if old client software) or do an automatic redirect to the new server (if https://github.com/MariaDB/server/pull/2681 is merged). This is similar to how HTTP 301/302 redirects work, and pretty sweet as it allows to gracefully drain the old server and for clients to fall back on tried-and-tested semantics on what is done on connection interrupts. Consider this scenario: 1. Server A is getting 1 new connection per second, has 100 existing active connections, which of 10 are actually doing a query and 2 of them are long-running transactions. 2. Admin prepares server B to replicate server A data, and once replica lag is low runs on server A: SET GLOBAL SERVER_REDIRECT_TARGET=sever-b.example.com 3. Server A redirects all new connections, server B gets 1 new connection per second but does not yet serve traffic, clients re-try connections/queries 4. Server A drops 98 connections, 2 continue with long-running transaction 5. After defined delay, server A also drops the long-running transaction connections to avoid being stuck on them 6. All traffic is hitting server B, which catching up in replication, clients keep re-connecting 7. Server B has caught up, get's promoted to primary, and starts accepting connections 8. Server B serves at this point all traffic, and clients/applications continue to work without errors as long as the total switchover delay was shorter than the configured re-try delay of clients 9. Server A serves no queries, all clients connecting to it get redirected to server B - after some delay server A can be completely shut down 10. If any of the clients had an old version of the MariaDB connector, they will stop working but error logs will clearly show that a redirect happened. No extra proxy software is necessary nor pre-distributed configuration. This redirect can be done even in an emergency without prior preparations if server A for example is stalled on a deadlock or if server A was overloaded and a failover to a new server with better hardware is needed.
On Sun, Jul 23, 2023 at 9:59 PM Otto Kekäläinen via developers < developers@lists.mariadb.org> wrote:
Not strictly so - we don't *need* to send the redirect information in the middle of the session. The session can just be dropped, and when clients automatically reconnect they will get the error message (if old client software) or do an automatic redirect to the new server (if https://github.com/MariaDB/server/pull/2681 is merged).
Hi Otto, Automatic reconnect is disabled by default, so applications would just stop unless the reconnect option was enabled or they execute reconnect. (MySQL 8.1 already deprecated automatic reconnect). /Georg -- Georg Richter, Staff Software Engineer Client Connectivity MariaDB Corporation Ab
Hi!
Not strictly so - we don't *need* to send the redirect information in the middle of the session. The session can just be dropped, and when clients automatically reconnect they will get the error message (if old client software) or do an automatic redirect to the new server (if https://github.com/MariaDB/server/pull/2681 is merged).
Automatic reconnect is disabled by default, so applications would just stop unless the reconnect option was enabled or they execute reconnect. (MySQL 8.1 already deprecated automatic reconnect).
All apps that run 24/7 have automatic reconnect, and will always have. It would not make sense for an app to stop running just because a database connection once got dropped for whatever reason. MySQL 8.1 is indeed removing the transparent automatic reconnect in their connector C [1], and it has been deprecated already in latest 8.0.34, but that isn't because the pattern of automatic reconnect goes away, they just want to make it always visible to the layer above managing the db connection (typically some ORM or programming language MySQL library, connection pool etc). The point still stands that changing the server from saying "sorry, can't do your query, try again" to "sorry, can't take your query, try again at this address" is nicely backwards compatible and has known behavior implications on all apps and connectors using the database, and that this would be a clean and robust way to do redirects and achieve e.g. near zero second failover. [1] https://dev.mysql.com/doc/c-api/8.0/en/c-api-auto-reconnect.html
Hi, Otto, On Jul 23, Otto Kekäläinen wrote:
Have you considered using AUTH_SWITCH_REQUEST for that purpose? That would allow redirect to happen after switch to TLS and client/server certificate validation.
One of the main use cases at the moment is to redirect clients to another server when this current server is being shut down.
For that we need to be able to send the redirect information in the middle of the session, not only when a connection is being established. Both session tracking and error message approach allow that.
Not strictly so - we don't *need* to send the redirect information in the middle of the session. The session can just be dropped, and when clients automatically reconnect they will get the error message (if old client software) or do an automatic redirect to the new server (if https://github.com/MariaDB/server/pull/2681 is merged).
First, this requires the client to perform one extra connection attempt, just to get the redirection information. This sounds like a waste of time. And also this is not guaranteed to work - if the server shuts down, the socket might be already closed, so the client simply won't be able to connect again. Second, it aborts the session in the middle where the client might not expect it. For example, in the middle of the transaction. Some clients might be able to replay it, others might not. That's why I suggest to let the client decide when it can reconnect.
This is similar to how HTTP 301/302 redirects work, and pretty sweet as it allows to gracefully drain the old server and for clients to fall back on tried-and-tested semantics on what is done on connection interrupts.
HTTP is stateless, what it does cannot always be blindly applied to the very much stateful MariaDB client-server protocol. And, really, I don't understand how phrases "session can just be dropped" and "gracefully drain the old server" can be used in adjacent statements. There's nothing graceful about forcefully dropping the connection.
Consider this scenario: 1. Server A is getting 1 new connection per second, has 100 existing active connections, which of 10 are actually doing a query and 2 of them are long-running transactions. 2. Admin prepares server B to replicate server A data, and once replica lag is low runs on server A: SET GLOBAL SERVER_REDIRECT_TARGET=sever-b.example.com 3. Server A redirects all new connections, server B gets 1 new connection per second but does not yet serve traffic, clients re-try connections/queries 4. Server A drops 98 connections, 2 continue with long-running transaction 5. After defined delay, server A also drops the long-running transaction connections to avoid being stuck on them 6. All traffic is hitting server B, which catching up in replication, clients keep re-connecting 7. Server B has caught up, get's promoted to primary, and starts accepting connections 8. Server B serves at this point all traffic, and clients/applications continue to work without errors as long as the total switchover delay was shorter than the configured re-try delay of clients 9. Server A serves no queries, all clients connecting to it get redirected to server B - after some delay server A can be completely shut down 10. If any of the clients had an old version of the MariaDB connector, they will stop working but error logs will clearly show that a redirect happened.
Yes, sure. It's a fine scenario and either solution can do it. With redirects-in-the-middle there will be 98 connection attempts less and _may be_ long running transactions will redirect right away too (depends on the client implementations). Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
Hi,
On Jul 23, Otto Kekäläinen wrote:
Have you considered using AUTH_SWITCH_REQUEST for that purpose? That would allow redirect to happen after switch to TLS and client/server certificate validation.
One of the main use cases at the moment is to redirect clients to another server when this current server is being shut down.
For that we need to be able to send the redirect information in the middle of the session, not only when a connection is being established. Both session tracking and error message approach allow that.
Not strictly so - we don't *need* to send the redirect information in the middle of the session. The session can just be dropped, and when clients automatically reconnect they will get the error message (if old client software) or do an automatic redirect to the new server (if https://github.com/MariaDB/server/pull/2681 is merged).
First, this requires the client to perform one extra connection attempt, just to get the redirection information. This sounds like a waste of time. And also this is not guaranteed to work - if the server shuts down, the socket might be already closed, so the client simply won't be able to connect again.
It is not a waste of time as the time it takes to attempt a connection and get an error+redirect reply is minimal. The actual delay of how much time is "wasted" in a switchover situation comes from how quickly the server A is able to drain all connections and server B to sync up and get promoted to primary, and that is the time the design should focus to optimize. It is guaranteed to work as long as the DBA keeps the server running and redirecting - just like with HTTP 301 redirects.
Second, it aborts the session in the middle where the client might not expect it. For example, in the middle of the transaction. Some clients might be able to replay it, others might not. That's why I suggest to let the client decide when it can reconnect.
The switchover is initiated and decided by the DBA controlling the server. You cannot possibly expect it to be controlled by the client - and in particular to be decided by the *slowest* client. The server can allow some time for the draining of existing connections to happen, but eventually it needs to tell old client connections to stop, and only after the redirect has started for new connections + all existing connections have been dropped can the actual switchover proceed by promoting a new primary.
This is similar to how HTTP 301/302 redirects work, and pretty sweet as it allows to gracefully drain the old server and for clients to fall back on tried-and-tested semantics on what is done on connection interrupts.
HTTP is stateless, what it does cannot always be blindly applied to the very much stateful MariaDB client-server protocol.
And, really, I don't understand how phrases "session can just be dropped" and "gracefully drain the old server" can be used in adjacent statements. There's nothing graceful about forcefully dropping the connection.
Perhaps not directly graceful, but at least all production grade clients have some mechanisms to cope with network interruptions and such. If you introduce a 'recommendation' based direct, it will lead clients to implement completely novel logic which in worst case leads to clients competing on which one is the last to use the old server to minimize per-client downtime and making system level total downtime in switchover worse for all other clients. Thus introducing a redirection that is based on server denying new connections will nicely align with existing error handling logic in clients.
Hi, Otto, On Jul 29, Otto Kekäläinen wrote:
The switchover is initiated and decided by the DBA controlling the server. You cannot possibly expect it to be controlled by the client - and in particular to be decided by the *slowest* client. The server can allow some time for the draining of existing connections to happen, but eventually it needs to tell old client connections to stop, and only after the redirect has started for new connections + all existing connections have been dropped can the actual switchover proceed by promoting a new primary.
Yes, exactly. In the model I was talking about, the server tells all clients to redirect, waits for a certain period of time and then kills all connections or shuts down. And during that period of time, while the server is waiting, the client can decide what is the best moment to disconnect. But if client doesn't disconnect - the server will kill the connection anyway eventually, it won't wait forever. Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
Hello Sergei and Yuchen! I was looking at the code Yuchen wrote this week [1]. Thinking back about practical scenarios on how the redirect would be used to gracefully decommission server A and have all existing and new connections user server B instead (as laid out in this thread at end of July [2]) I fail to see how it would work. Consider this scenario with session track variable redirect_url: 1. Server A is getting 1 new connection per second, has 100 existing active connections, of which 10 are actually doing a query and 2 of them are long-running transactions. 2. Admin prepares server B to replicate server A data, and once replica lag is low runs on server A: SET GLOBAL redirect_url=mariadb://server-b.example.com 3. Server B gets 100 new connections immediately from clients that follow the session tracked variable. Server A continues to authenticate and accept all new connections, which leads to server B getting one *new* per second but does not yet serve traffic, clients re-try connections/queries. 4. Server A goes into READ_ONLY mode and any client that did not follow the redirect will get errors on INSERT/UPDATE/DELETE 5. All clients are reaching server B, which is catching up on replication, clients keep re-connecting in attempt to get queries fulfilled 6. Server B has caught up, gets promoted to primary, and starts accepting connections 7. Server B serves at this point all traffic, and clients/applications continue to work without errors as long as the total switchover delay was shorter than the configured re-try delay of clients 8. If at this point there are any clients that don't support the session tracked redirect_url, or even it they support it, but do not obey it, they will be server stale data from server A 9. Server A shuts down, servers no redirects anymore nor stale data 9. If any of the clients had an old version of the MariaDB connector, they will stop working and error logs will simply say that connection to server A failed I find it a bit concerning that in this model old clients, instead of getting an error with a human readable explanation of what happened, continue to read stale data. And only way to prevent that is to shut down server A quickly, whereafter the old clients only get an error about server not being reachable and nothing about the old client not supporting redirects. Hence, I still feel that Daniel's submission [3,4] would be a better design, being more robust and safer. An error+redirect is easier to reason about regarding integrity of data and error modes than the voluntary maybe-redirect that is now in the works by Yuchen. [1] https://github.com/MariaDB/server/commit/04b99a30a3f.patch). [2] https://lists.mariadb.org/hyperkitty/list/developers@lists.mariadb.org/threa... [3] https://github.com/MariaDB/server/pull/2681 [4] https://github.com/mariadb-corporation/mariadb-connector-c/pull/22
Hi Otto, all,
On Wed, Aug 16, 2023 at 4:00 PM Otto Kekäläinen via developers
I find it a bit concerning that in this model old clients, instead of getting an error with a human readable explanation of what happened, continue to read stale data. And only way to prevent that is to shut down server A quickly, whereafter the old clients only get an error about server not being reachable and nothing about the old client not supporting redirects.
This reminds me of a recent true story of a DBaaS provider that did not give adequate warning to users when decommissioning a server: https://community.influxdata.com/t/getting-weird-results-from-gcp-europe-wes... https://news.ycombinator.com/item?id=36657829 It was not anything MySQL or MariaDB compatible, and they made matters worse by not only taking the server offline, but also deleting the data immediately. For some users, the only notification was via some web based dashboard, which they might never have accessed. I think that what might be useful is something analogous to the *nix /etc/motd, which is typically displayed to users when they log in. In the case of the MySQL or MariaDB client protocol, that would be some "out of band" data. It would not necessarily have to prevent new connections (at first), merely cause newer clients to output the "warning" message to some log. An example would be: "This server will be decommissioned on 2023-12-13. Please contact https://sales.example.com." Closer to the deadline, attempts to connect would lead to a hard error, along with the message.
Hence, I still feel that Daniel's submission [3,4] would be a better design, being more robust and safer. An error+redirect is easier to reason about regarding integrity of data and error modes than the voluntary maybe-redirect that is now in the works by Yuchen.
[1] https://github.com/MariaDB/server/commit/04b99a30a3f.patch). [2] https://lists.mariadb.org/hyperkitty/list/developers@lists.mariadb.org/threa... [3] https://github.com/MariaDB/server/pull/2681 [4] https://github.com/mariadb-corporation/mariadb-connector-c/pull/22
Could any of these approaches be extended to support a soft or hard "this database will be shut down" notice? Marko -- Marko Mäkelä, Lead Developer InnoDB MariaDB plc
Hi, I wrote down an idea for out-of-band warning messages in MDEV-31839 https://jira.mariadb.org/browse/MDEV-31839 where by reserving one "system variable" for warnings, we can inject them to the initial OK packet that the client receives. This way clients that understand the server variable changes are able to display them to the users. The MariaDB Connector/C is already able to extract the system variable change information even if it's in the first OK packet sent after authentication. I found this sort of accidentally when I was testing some code for MDEV-31609 https://jira.mariadb.org/browse/MDEV-31609. I would not be surprised if after MDEV-31609 this could be implemented by just adding a dummy server_motd global variable that when set is sent to the clients. Markus On 8/17/23 14:13, Marko Mäkelä via developers wrote:
Hi Otto, all,
On Wed, Aug 16, 2023 at 4:00 PM Otto Kekäläinen via developers
wrote: I find it a bit concerning that in this model old clients, instead of getting an error with a human readable explanation of what happened, continue to read stale data. And only way to prevent that is to shut down server A quickly, whereafter the old clients only get an error about server not being reachable and nothing about the old client not supporting redirects. This reminds me of a recent true story of a DBaaS provider that did not give adequate warning to users when decommissioning a server:
https://community.influxdata.com/t/getting-weird-results-from-gcp-europe-wes... https://news.ycombinator.com/item?id=36657829
It was not anything MySQL or MariaDB compatible, and they made matters worse by not only taking the server offline, but also deleting the data immediately.
For some users, the only notification was via some web based dashboard, which they might never have accessed.
I think that what might be useful is something analogous to the *nix /etc/motd, which is typically displayed to users when they log in. In the case of the MySQL or MariaDB client protocol, that would be some "out of band" data. It would not necessarily have to prevent new connections (at first), merely cause newer clients to output the "warning" message to some log. An example would be: "This server will be decommissioned on 2023-12-13. Please contact https://sales.example.com." Closer to the deadline, attempts to connect would lead to a hard error, along with the message.
Hence, I still feel that Daniel's submission [3,4] would be a better design, being more robust and safer. An error+redirect is easier to reason about regarding integrity of data and error modes than the voluntary maybe-redirect that is now in the works by Yuchen.
[1]https://github.com/MariaDB/server/commit/04b99a30a3f.patch). [2]https://lists.mariadb.org/hyperkitty/list/developers@lists.mariadb.org/threa... [3]https://github.com/MariaDB/server/pull/2681 [4]https://github.com/mariadb-corporation/mariadb-connector-c/pull/22 Could any of these approaches be extended to support a soft or hard "this database will be shut down" notice?
Marko
-- Markus Mäkelä, Senior Software Engineer MariaDB Corporation
Hi Markus!
I wrote down an idea for out-of-band warning messages in MDEV-31839 where by reserving one "system variable" for warnings, we can inject them to the initial OK packet that the client receives. This way clients that understand the server variable changes are able to display them to the users. The MariaDB Connector/C is already able to extract the system variable change information even if it's in the first OK packet sent after authentication. I found this sort of accidentally when I was testing some code for MDEV-31609.
I would not be surprised if after MDEV-31609 this could be implemented by just adding a dummy server_motd global variable that when set is sent to the clients.
The server supports a COMPILATION_COMMENT[1], which is shown in the SQL console for connecting clients. This is already used in the wild to e.g. inform users that an old version of MariaDB is out of support and should no longer be used. If that field could be changed or extended at run-time it could do the job you envision above - which would be useful indeed as a generic way to communicate things to users. [1] https://github.com/MariaDB/server/blob/485c9b1fb320958b13253a49d4480ee9decf9...
Hi, I think that the system variable tracker code has a lot of potential to improve both the user experience for command line clients (automatic out-of-band warnings) as well as improve the startup speed of connections created from many connectors by providing "early metadata" to them (MDEV-31609). This isn't strictly related to the connection redirection so I'll refrain from going on too big a tangent. Repurposing @@version_comment would probably work and since it's been a "part of the version" for quite some time, a lot of clients probably print it as a part of the version like the mysql/mariadb command line clients do, however it might look a little weird in GUI software. Here's a quick proof-of-concept that I hacked by replacing the SELECT @@version_comment query the client does with one that reads data from a table. It at least shows that it works with the command line version: $ mysql -S /tmp/rws.sock Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 9 Server version: 10.11.3-MariaDB-1:10.11.3+maria~ubu2204-log mariadb.org binary distribution Hello, the time is 2023-08-18 10:18:23. The weather looks good today. Here's an AI generated poem: MariaDB, a digital sea, Tables weave a web, endlessly free. Data's dance in rows and spaces, A symphony of ones and zeros embraces. Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> Making version_comment modifiable at runtime would probably be a nice first step that opens up more possibilities for these sorts of tricks. I still think that it would be better to reserve a variable name for passing out-of-band messages to clients, something like @@server_comment. It would already be very useful both for the MotD behavior demonstrated above but also for passing human-readable messages (possibly mid-query https://mariadb.com/kb/en/progress-reporting/) without requiring any interaction from the client. This could then be used to pass the information intended for humans along with the information intended to be machine readable. Markus On 8/18/23 12:26, Otto Kekäläinen wrote:
Hi Markus!
I wrote down an idea for out-of-band warning messages in MDEV-31839 where by reserving one "system variable" for warnings, we can inject them to the initial OK packet that the client receives. This way clients that understand the server variable changes are able to display them to the users. The MariaDB Connector/C is already able to extract the system variable change information even if it's in the first OK packet sent after authentication. I found this sort of accidentally when I was testing some code for MDEV-31609.
I would not be surprised if after MDEV-31609 this could be implemented by just adding a dummy server_motd global variable that when set is sent to the clients. The server supports a COMPILATION_COMMENT[1], which is shown in the SQL console for connecting clients. This is already used in the wild to e.g. inform users that an old version of MariaDB is out of support and should no longer be used. If that field could be changed or extended at run-time it could do the job you envision above - which would be useful indeed as a generic way to communicate things to users.
[1]https://github.com/MariaDB/server/blob/485c9b1fb320958b13253a49d4480ee9decf9...
-- Markus Mäkelä, Senior Software Engineer MariaDB Corporation
Hi, Otto, On Aug 16, Otto Kekäläinen wrote:
Hello Sergei and Yuchen!
I was looking at the code Yuchen wrote this week [1]. Thinking back about practical scenarios on how the redirect would be used to gracefully decommission server A and have all existing and new connections user server B instead (as laid out in this thread at end of July [2]) I fail to see how it would work.
<...cut...>
I find it a bit concerning that in this model old clients, instead of getting an error with a human readable explanation of what happened, continue to read stale data. And only way to prevent that is to shut down server A quickly, whereafter the old clients only get an error about server not being reachable and nothing about the old client not supporting redirects.
Yes, if the client doesn't support redirects, it'll work as before - that is, the client will be kicked out when the server is shut down and then the client will connect to a new server because, of course, if there's failover, there must be some way of telling clients what server to connect to. The error will be ER_SERVER_SHUTDOWN which is what old clients were always getting in this case. So, basically, for old clients there will be no changes whatsoever.
Hence, I still feel that Daniel's submission [3,4] would be a better design, being more robust and safer. An error+redirect is easier to reason about regarding integrity of data and error modes than the voluntary maybe-redirect that is now in the works by Yuchen.
Hm. Technically, the server can check whether redirect_url is set, and send ER_SERVER_REDIRECT instead of ER_SERVER_SHUTDOWN. I'm not sure it'll be much of an improvement, but it's doable. But even in this case, the error message will only inform the user, the actual machine-readable actionable url will be in the session tracking data. Forcing the client to parse and extract actionable data from the error message that is designed to be user readable, this looks like a rather unsightly hack to me.
[1] https://github.com/MariaDB/server/commit/04b99a30a3f.patch). [2] https://lists.mariadb.org/hyperkitty/list/developers@lists.mariadb.org/threa... [3] https://github.com/MariaDB/server/pull/2681 [4] https://github.com/mariadb-corporation/mariadb-connector-c/pull/22
Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
Hi!
Yes, if the client doesn't support redirects, it'll work as before - that is, the client will be kicked out when the server is shut down and then the client will connect to a new server because, of course, if there's failover, there must be some way of telling clients what server to connect to. The error will be ER_SERVER_SHUTDOWN which is what old clients were always getting in this case. So, basically, for old clients there will be no changes whatsoever.
..which is not ideal. Some backwards compatibility would make the user experience much better.
Hence, I still feel that Daniel's submission [3,4] would be a better design, being more robust and safer. An error+redirect is easier to reason about regarding integrity of data and error modes than the voluntary maybe-redirect that is now in the works by Yuchen.
Hm. Technically, the server can check whether redirect_url is set, and send ER_SERVER_REDIRECT instead of ER_SERVER_SHUTDOWN. I'm not sure it'll be much of an improvement, but it's doable.
That would make sense from the onset to ensure the rollout of the redirect feature is successful and users (and connector authors) can discover what happened.
Hi, Otto, On Aug 18, Otto Kekäläinen wrote:
Yes, if the client doesn't support redirects, it'll work as before - that is, the client will be kicked out when the server is shut down and then the client will connect to a new server because, of course, if there's failover, there must be some way of telling clients what server to connect to. The error will be ER_SERVER_SHUTDOWN which is what old clients were always getting in this case. So, basically, for old clients there will be no changes whatsoever.
..which is not ideal. Some backwards compatibility would make the user experience much better.
I'm sorry, I don't understand that. I wrote that for old clients _nothing changes whatsoever_. It's a perfect backward compatibility, as if the new feature didn't exist at all, an ideal case, which is rarely even possible. How can it be more backward compatible than that? :) Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
participants (8)
-
andrey.sidorov@gmail.com
-
Antony T Curtis
-
Georg Richter
-
Lenski, Daniel
-
Marko Mäkelä
-
Markus Mäkelä
-
Otto Kekäläinen
-
Sergei Golubchik