We're continuing to make progress on DoltgreSQL, which is a version of Dolt built to be a drop-in replacement for PostgreSQL. Dolt, is a MySQL compatible database is built from the ground up with Git-influenced version control features. This means that you can use branch, merge, diff, and more with your data and schema. Today, I'm excited to share that we've finally landed the initial support for users, authentication, and privileges!

Users, Authentication, and Privileges?

These are three pillars that govern most of the interaction that clients have with a database. The first pillar is users. Users are the backbone of many servers, as they uniquely identify which client is connected. In Postgres, all connections have a user associated with them, and therefore we've implemented that same expectation into Doltgres. It is possible for multiple clients to declare themselves as being the same user, which is often done for local databases.

So, you know which user you want to be, but you have to prove that you have access to that user within the database. That is where the second pillar, authentication, comes in. Currently, we only support password authentication via SCRAM-256, which is the default setting for modern Postgres databases. In the future, we plan to support other forms of authentication, such as certificates.

Authentication is important as it allows restriction to users with higher leverage in the permissions model. That permissions model is based on the third pillar, which are privileges (sometimes referred to as grants). Privileges allow for the fine-granted control of which statements a user is able to execute. For example, you may want to allow a user to view the data in a table, but you may want to disallow them from modifying that table's data. This is done through the GRANT statement, which gives privileges to a user, and the REVOKE statement to remove privileges from a user. Ownership is a related concept to privileges, as it essentially functions as a user having every privilege to database objects that they own.

Versioning with Users

With Dolt, we decided to store users, authentication, and privileges outside the commit graph in a centralized location. We believe that users as a concept only applies to a specific server, and shouldn't be a part of the data. This extends to versioning concepts such as cloning a repository, as the cloner is free to define their own set of users since they now "own" their copy of the data. It's also there for security reasons, as cloning a database doesn't allow a bad actor to interfere with an existing server of the same data, since they do not have any credentials that they could potentially brute-force locally. We've chosen to use this same model for Doltgres.

This does come with a few interesting challenges that normal databases do not need to worry about, as Doltgres supports use cases that are not possible in traditional databases. For example, let's say that there are two users: User1 and User2. There are also two branches: Branch1 and Branch2. User1 is on Branch1, and User2 is on Branch2. If User1 creates the table ExampleTable on Branch1, then they're considered the owner of the table. However, User2 can also create a table with the same name, ExampleTable, on their own Branch2. Although they're on different branches, our centralized user system will mark both of them as owning ExampleTable, meaning that they both have full control over those tables. Crucially, they are on different branches, so they don't actually interact with each other's tables, unless they merge to the same branch (generally the main branch). We think that this is okay in this scenario, and they both had the privileges to create a table with the given name, and they were both able to merge their changes into some target branch, so they should be able to modify the table on that target branch.

Another example deals with historical queries. In Doltgres, you are able to query information outside the HEAD of the current branch. For example, let's say that you have a commit with hash 'kfvpgcf8pkd6blnkvv8e0kle8j6lug7a', and you would like to query the table ExampleTable at the time the commit was created. You can do so using:

SELECT * FROM ExampleTable AS OF 'kfvpgcf8pkd6blnkvv8e0kle8j6lug7a';

This is only one way of referencing historical data, and we support many more. Let's say that User1 previously had access to ExampleTable when the commit was made, but since then, access has been restricted to ExampleTable. This restriction will also apply to the historic ExampleTable within the commit. This gives database administrators much more control over sensitive data.

These are only two examples out of many, and we are still investigating whether these are the best approaches. If your use case does not work with either of these examples, or you have questions on the many more that were not mentioned, then definitely reach out to us on Discord, as we would love to discuss it with you. Community feedback is very important for us.

Example

When starting a Doltgres server, we create the postgres user with a default password of 'password'. We strongly encourage everyone with a public-facing server to change this password, which you can do using the following:

ALTER USER postgres PASSWORD 'some_other_password';

In addition, the default database is doltgres, so ensure that you connect to that one for this example. You can use your favorite Postgres client to connect to Doltgres, as we are completely wire-compatible (after all, our goal is to be a drop-in replacement for existing Postgres server).

First, let's create two users using our default postgres account, and create a table. We'll set it so that user1 can view the table's data and modify it, while user2 may only view the data:

doltgres=> CREATE USER user1 PASSWORD 'user1pass';
CREATE ROLE
doltgres=> CREATE USER user2 PASSWORD 'user2pass';
CREATE ROLE
doltgres=> CREATE TABLE example(pk INT8 PRIMARY KEY);
CREATE TABLE
doltgres=> INSERT INTO example VALUES (1), (2), (3);
INSERT 0 3
doltgres=> GRANT SELECT, INSERT, UPDATE, DELETE ON example TO user1;
GRANT
doltgres=> GRANT SELECT ON example TO user2;
GRANT

Now, let's log in as user1 and try a few statements:

doltgres=> SELECT * FROM example;
 pk
----
  1
  2
  3
(3 rows)

doltgres=> UPDATE example SET pk=pk+3;
UPDATE 3
doltgres=> SELECT * FROM example;
 pk
----
  4
  5
  6
(3 rows)

doltgres=> TRUNCATE example;
ERROR:  permission denied for table example

user1 was able to use the SELECT and UPDATE statements just fine, however ran into an issue with TRUNCATE as they do not have the necessary privileges. Now, let's try using our other user, user2:

doltgres=> SELECT * FROM example;
 pk
----
  4
  5
  6
(3 rows)

doltgres=> UPDATE example SET pk=pk+10;
ERROR:  permission denied for table example
doltgres=> INSERT INTO example VALUES (100);
ERROR:  permission denied for table example
doltgres=> TRUNCATE example;
ERROR:  permission denied for table example

user2 cannot use the UPDATE statement, but they can still use the SELECT statement since they have the appropriate privilege. Lastly, let's remove the ability to SELECT from both users. For this, we'll use the postgres user again:

doltgres=> REVOKE SELECT ON example FROM user1;
REVOKE
doltgres=> REVOKE SELECT ON example FROM user2;
REVOKE

You'll notice that we only revoked SELECT, so we should still be able to INSERT on user1 right? Let's test it:

doltgres=> SELECT * FROM example;
ERROR:  permission denied for table example
doltgres=> INSERT INTO example VALUES (100);
INSERT 0 1

Although we can't see the data from user1, we can still modify the data. This is just an example of the kind of power and granularity that privileges can provide.

What's Missing?

• For now, only the SELECT, INSERT, UPDATE, DELETE, and TRUNCATE privileges are implemented, and only for tables.
• We track ownership for many database objects, but only check the ownership when querying tables.
• Authentication only supports SCRAM-256.
• We do not support role groups yet.
• Roles cannot be granted, only privileges.

We will continue to work on these missing features in the coming months, as well as greatly expanding the test suite.

Conclusion

DoltgreSQL is improving at a rapid pace, and we are excited with both the reception and its future. Users, authentication, and privileges enable DoltgreSQL to get one step closer to our goal of being a drop-in replacement for Postgres. If there are any features that are missing that are critical to your workflow, then let us know! You can find us on Twitter/X and chat with us on Discord. We also welcome all issue reports! Thank you for reading the blog post!