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.

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.

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