Dolt's disappearing `root` user is confusing

[fulghum]

I had the chance to sit with a new Dolt customer recently and the behavior around the root user changing based on what you specify or don't specify when you start a sql-server and whether or not you create any additional users is a sharp edge that confused the customer and made them think there was something buggy about Dolt.

This behavior is very easy to trigger with common usage patterns. Users just start a sql-server with the default options:

❯ mkdir new-dir && cd new-dir
❯ dolt sql-server 

Then they connect and start creating users for their application:

❯ mysql -uroot --protocol TCP 
CREATE USER 'myapp'@'%' IDENTIFIED BY 'password';

The next time they need to restart their sql-server, the root account will have mysteriously disappeared:

❯ mysql -uroot --protocol TCP 
ERROR 1045 (28000): User not found 'root'

In the customer interaction I saw, we needed to ask them to restart the sql-server to turn up the loglevel, but the sql-server could need to be restarted for many reasons of course. It might even be weeks later when the user needs to restart the server and it would be even more mysterious why that root account suddenly stopped existing.

Dolt should match MySQL's behavior with the default admin user to make this less confusing/surprising for customers. MySQL has a default root admin user that users can delete or change as necessary, but MySQL never removes that user because customers created other users. Dolt should create the default root admin user account, and not delete it unless a customer explicitly deletes it.

[Hydrocharged]

To add to this, we chose this behavior as our default root user is not password-protected. In MySQL, the default root account has a password or certificate that is specified by the user. To ease adoption, if no users exist, then we accept connections that specify "root" as the username, as grant those connections full permissions. As soon as users are created, then we no longer accept those connections. At the time, the thought was that we shouldn't have an exposed root account for the world to connect so, as the user may forget to protect it or delete it since they never created it.

Also at the time, we identified the potential confusion that this behavior could cause, and since the workaround was to restart the server with -u root, we didn't do anything further. We did identify another route, which would be to force the user to input a password or certificate during dolt init.

[fulghum]

I think we should change to match MySQL's behavior: create the root super user (without a password) and add root@localhost to the known users listed in mysql.user. There are many cases where MySQL installers will create a password for root, but it doesn't always happen. Then, if/when new users are created, we don't automatically remove or change that root account. Customers can secure the root account in the same way as MySQL.

That root user will be scoped only to localhost, so I'm not concerned about sql-servers being unprotected. If someone is already on localhost, then they can of course just access all the data through the CLI anyway.

I like the intention behind trying to keep sql-servers safe, but I think the behavior difference from MySQL will continue to surprise and confuse people. If the root account is properly scoped to localhost then I think there's very minimal risk, and it'd be better to be explicit about management of the root account and match MySQL's behavior.

[fulghum]

We helped another customer bit by this last week and suspect other customers are probably hitting it without letting us know, too.