In the Dolt 1.49.0 release, we removed the ability to specify a username and an optional password when starting a sql-server. These arguments were originally the only way to control the user who could log into a running Dolt sql-server. A few years ago, we introduced full support for SQL user and privilege management, and we've decided it's time to consolidate user management to this single, standard SQL path. In this blog post, we discuss why we decided to remove the user and password arguments, the edge cases they created, and how you can manage users and privileges in Dolt using standard SQL syntax.

Background

When Dolt added initial support for running a sql-server, we didn't support SQL USER and GRANT syntax yet, but we still needed a way for customers to control the name and password of the user who could log into the Dolt sql-server. So, we introduced the --user and --password arguments to the dolt sql-server command and later added those same parameters to the dolt sql-server configuration file format. This allowed customers to easily specify a username and password for a superuser account that could log into the sql-server. Years later, when we added full support for SQL user and privilege management, we kept the --user and --password arguments around, but as is often the case when systems evolve multiple ways to do something, it introduced possibilities for confusion and surprising behavior.

On first glance, after we added full support for SQL user and privilege management, the ability to specify a superuser name and password when starting a sql-server seemed like a convenient feature. It saved customers a little time from having to run CREATE USER and GRANT statements to manually create the superuser. However, over time, some edge cases emerged that could surprise customers. We also noticed that customers sometimes seemed to think they always had to supply these arguments, which made running a sql-server slightly harder than we want it to be.

In most cases, specifying the --user parameter to dolt sql-server would create a temporary superuser with that name, and optional password, that would only exist for the lifetime of the sql-server process. In other words, it would never be persisted to the privileges database, where all other users and privileges are stored. However, if the specified username already existed in the privileges database, then Dolt would not overwrite it to give it superuser privileges or update it to match the specified password. In this case, specifying --user and --password was really a no-op, since the persisted privilege information took priority. This behavior was good for some use cases, but not what customers wanted in others.

Another edge case in this implementation is that the temporary user created by the --user argument was scoped to all hosts by default (i.e. %), and if another user record existed with the same username, but scoped to a more specific host (e.g. localhost), then customers could be surprised when they tried to log in from the more specific host address (e.g. localhost) and see that the user/password they requested on the command line didn't seem to be working. Again, this behavior made sense for some customer use cases, but it wasn't always what customers wanted or expected.

Fixing Edge Cases

At first, we looked into how to clean up these edge cases with the goal of making the behavior as intuitive, simple, and unsurprising as possible. We considered adding more arguments to let customers control the different behaviors, but that felt like it was just making things too complicated. We also considered having Dolt overwrite existing users if they existed, and then restoring them to the previous settings when the sql-server was stopped, but that also felt overly complicated and seemed destined to introduce new edge cases.

In the end, we decided that the most intuitive and least surprising behavior would be to consolidate user management to the standard SQL syntax and deprecate the --user and --password arguments for sql-server. We always hate to take away features or arguments that customers may be using, but we felt that the long-term benefits of simplifying the user management path and cleaning up these edge cases outweighed the inconvenience of removing these arguments.

Managing Users and Privileges with SQL

By default, when you start a Dolt sql-server without first manually creating any user accounts, Dolt will create a default root@localhost superuser account with no password (read more about Dolt's recent change to create a persistent root@localhost superuser account. When you're ready to create additional user accounts, you can use standard SQL syntax to create them, as described in the examples below.

This first example creates a new user named user1, which can only be accessed from localhost and does not have a password. It is granted all privileges on all databases, but not the ability to grant privileges to other users.

CREATE USER user1@localhost;
GRANT ALL ON *.* to user1@localhost;

The next example shows how to create a superuser account, named root with the password 'pa$$w0rd', and accessible from any host. Note that the end of the GRANT statement includes WITH GRANT OPTION, which is what allows this user to grant privileges to other users.

CREATE USER root@'%' IDENTIFIED BY 'pa$$w0rd';
GRANT ALL ON *.* to root@'%' WITH GRANT OPTION;

If you create the root@'%' superuser as above, you may want to also delete the root@localhost superuser that was created automatically for you. You can do that with the following statement:

DROP USER root@localhost;

Emergency Privileges Repair

A quick reminder that if you do ever lose the password for your superuser account, you can always launch dolt sql from the command line to get superuser access and repair privileges or reset passwords.

Alternatively, if you want to completely start fresh and remove all privilege information, you can delete the privileges database at .doltcfg/privileges.db. When you restart dolt sql-server the root@localhost superuser will be recreated, and you can manually recreate all users and grants.

Summary

We put a good amount of thought into ways to solve these edge cases with the --user and --password arguments to dolt sql-server. In the end we decided the best long-term decision was making this backwards incompatible change to remove these parameters from the dolt sql-server command line arguments and configuration. We apologize for any inconvenience it may cause for existing customers to migrate to this new behavior. Now that these arguments are not supported anymore, customers should use the standard SQL syntax to create users and grant privileges.

If you have any problems upgrading from this change, or if you just want to talk about user and privilege management, please come join us on the DoltHub Discord server! Our whole team hangs out on Discord and we're always happy to help with any issues people run into using Dolt.