SQLx cannot connect to mysql/MariaDB without using a password

[distinctjuggle]

Bug Description

The syntax for connecting to a mysql/MariaDB server without a password is not handled properly. No combination of different syntax when specifying a database url allows for a connection to a server where no password is present.

When attempting to connect to a database URL where no password is present, either the username of the connection will be interpreted incorrectly, or the blank/empty/"null" password will.

Minimal Reproduction

1. Have a mysql/MariaDB database without a password
2. Specify database url and connect via SQLx
3. Connection will fail due to syntax handling of database URL string in one of two ways

I have attempted all possible ways of specifying the database URL that I can think of. Here are a few of the ones I have tried:

let database_url = "mysql://username:@localhost/database";
let database_url = "mysql://username:@localhost/database?password=";
let database_url = "mysql://username:''@localhost/database?password=";
let database_url = "mysql://'username':@localhost/database?password=";
let database_url = "mysql://username@localhost/database?user=username";
let database_url = "mysql://username:@localhost/database?user=username";

Take the first line as an example. The password should be recognized as nonexistent by SQLx, and treated as such. Instead, the user will be met with an error that the connection failed such as follows: Access denied for user ''@'localhost' to database xxx. Note the two single quotes before the @ character, indicating that the username is read as null as opposed to the password. Attempts to remedy this do not offer any resolution, as specifying any value at all for a password will result in a failed connection due to the password not matching the database's lack of one.

This seems similar to a previous bug found here: #203

Info

• SQLx version: 0.8.1
• SQLx features enabled: "runtime-async-std", "mysql"
• Database server and version: MariaDB Ver 15.1 Distrib 10.11.8-MariaDB
• Operating system: Rocky Linux
• rustc --version: rustc 1.79.0 (129f3b996 2024-06-10)

[distinctjuggle]

I found a temporary workaround from the image here: #1624

Basically just use the format! macro in Rust to specify the password, and there you can specify an empty password which is handled properly.

---

Example:

let database_url = format!("mysql://{user}:{pass}@{host}/{database}", user = "your username here", pass = "", host = "your host here", database = "your database here");

[abonander]

I honestly for the life of me don't know how MySQL expects us to tell it we're not supplying a password. It's not actually documented anywhere that I can find (or MariaDB's, for that matter).

We do set auth_response to None in the HandshakeResponse if password is None which seems like it should do it:

sqlx/sqlx-mysql/src/connection/establish.rs

Lines 121 to 125 in dc32f99

	let auth_response = if let (Some(plugin), Some(password)) = (plugin, &options.password) {
	Some(plugin.scramble(&mut stream, password, &nonce).await?)
	} else {
	None
	};

And we set password to None if Url::password() returns None:

sqlx/sqlx-mysql/src/options/parse.rs

Lines 31 to 37 in dc32f99

	if let Some(password) = url.password() {
	options = options.password(
	&percent_decode_str(password)
	.decode_utf8()
	.map_err(Error::config)?,
	);
	}

Which is what it returns for every single one of your examples except username:'' (password: None in the debug output): https://play.rust-lang.org/?version=stable&mode=debug&edition=2021&gist=a10f64482e1e1bb3cffacc66845f07fe

And your reply is just confusing me more because if you do

let database_url = format!("mysql://{user}:{pass}@{host}/{database}", user = "username", pass = "", host = "localhost", database = "database");

You just get mysql://username:@localhost/database, the same as your first example. But you say that works?

I have no idea what's going on here.

https://play.rust-lang.org/?version=stable&mode=debug&edition=2021&gist=c18a68d7d5772792dd646ae9a04b521d

[distinctjuggle]

Are you able to reproduce my results when connecting to a passwordless database? I can try that out later tonight with a second database (though it should really be almost identical in every way to the first, including OS, packages, and almost hardware).

I don't understand it either, but it seems to be fully repeatable on my current environment/setup.

[abonander]

I think I figured it out. It doesn't seem to be a SQLx bug. It's largely MySQL/MariaDB's fault for returning an unhelpfully vague error message. One of the many reasons I'm personally not really a fan of MySQL.

The thing is, if it was actually a password auth issue, the error would include (using password: [NO/YES]).

It's specifically an access-denied error for the database, which suggests an issue with privileges. I can reproduce the error if I create a user without granting it any privileges for the database.

Presumably when you created your passwordless user, you either didn't grant it any privileges, or you explicitly did REVOKE ALL.

It appears that, at minimum, you need to grant SELECT privileges on the database, e.g.:

GRANT SELECT ON database.* FOR 'username'@'%';

This works in my test: #3505

[abonander]

This manual page explains how the privilege checks work, which gives us some clues as to what's going on: https://dev.mysql.com/doc/refman/8.4/en/request-access.html

Privileges are, in reality, just a set of bitflags that are OR'd together from the global level to the requisite level of authorization. You can see these flags by querying the corresponding tables in the mysql schema: of importance for our purposes is user and db.

It seems that, in order for the server to allow a user account to connect to a database, it ORs together the privileges from the user table and the db table for the user and database in question; my guess is, it just requires at least one privilege to be granted, or else it denies the connection.

GRANT USAGE (the "default" privilege if none else is granted) is quite a misnomer, because it's not actually a privilege according to these tables. So an account that only has GRANT USAGE might as well have no privileges at all.

Although to be fair, you don't have to actually specify a database when connecting. When I tested this with both the mysql and mariadb command-line clients, they were able to connect when not specifying a database, even without any privileges granted. So an account with just GRANT USAGE can still connect, it just can't really do anything useful. You also wouldn't know that you have missing privileges for a database until you try to issue a command that needs them.

SQLx's MySQL driver also allows the database name to be optional, though I've personally never used it this way (I'm used to Postgres, which requires specifying a database).

[distinctjuggle]

Thank you so much!

I think you're right - I came to a similar but far less detailed conclusion last night. I was going to test it first, but I think you've proven it far more rigorously.

I believe in my particular case, I was connecting to the "localhost" host, and not the "127.0.0.1" host, which are apparently different on this setup. In 95% of contexts, they're the same, so it never crossed my mind that mysql/MariaDB might handle that differently. Not to mention, the error provided from mysql/MariaDB is extremely unhelpful as you said.

If I simply list users in MariaDB:

MariaDB [(none)]> SELECT host, user, password FROM mysql.user;
+-----------------------+---------------+----------+
| Host                  | User          | Password |
+-----------------------+---------------+----------+
| localhost             | mariadb.sys   |          |
| localhost             | root          | invalid  |
| localhost             | mysql         | invalid  |
|                       | PUBLIC        |          |
| localhost             |               |          |
| localhost.localdomain |               |          |
| 127.0.0.1             | some_username |          |
+-----------------------+---------------+----------+

We can see that localhost and 127.0.0.1 are distinct. I think I actually "fixed" my issue with the format! macro simply because I typed out 127.0.0.1 instead of localhost this time around. I didn't set this database up, it's setup from another piece of software I am using, and my goal is to make a tool to make a settings change far easier on this tool. Since I "fixed" it with the format! macro yesterday, I've actually already got the basic functionality of my tool completed. I might have figured this issue out sooner had I created the database myself, or noticed sooner that localhost and 127.0.0.1 entries aren't the same.

All of this seems to be in line with your detailed explanation. I'm no expert with SQL, and most of my past experience is with postgreSQL as well. Even without the SQL experience, I wouldn't have expected such a misleading error message from such a popular platform/tool. I thank you again for your time in figuring this out! I apologize the real culprit was so simple, haha.

[abonander]

Yeah, the hostname-specific authentication is another thing I'm not really a fan of. You can essentially create multiple, entirely separate accounts with the same username but different passwords, privileges, etc.

It clearly can be a huge footgun, and the manual even has a whole page dedicated to diagnosing issues with it: https://dev.mysql.com/doc/refman/8.4/en/problems-connecting.html

My recommendation would be to keep it simple and always use the wildcard host specifier when creating accounts:

CREATE USER 'foo'@'%' IDENTIFIED BY '<pass word>' ;

That way there's less opportunity for confusion.

Anyway, glad I was able to help.