Dolt is the world's first fully-versioned SQL relational database. It allows you to track the full history of the data in every row and every column of all your tables. Although Dolt doesn't share any code with MySQL, it is MySQL-compatible – meaning you can drop in Dolt instead of MySQL for any application that uses MySQL. For example, a few weeks ago we showed how you can run WordPress on Dolt, instead of MySQL.

That said, there is a LOT of surface area for full MySQL-compatibility, so occasionally customers hit a snag with something not working correctly. We love learning about these gaps, and fixing them! Recently, a customer reported problems getting the XCA application to work with Dolt, so we dug in, fixed some missing functionality, and are happy to report that XCA now works with a Dolt database backend! 🎉 Let's dive in and see it in action...

XCA

XCA is a cross-platform application that makes it easy to work with private keys, certificates, requests, and certificate revocation lists (CRLs). It allows you to import and export in many formats (e.g. PEM, DER, PKCS#7, PKCS#12), create your own templates for certificates, and leverage x509v3 extensions. All those features come in a very easy to use UI that can even be customized to display the information you want to see.

XCA stores all of its data in a SQL database and allows you to configure the database connection so you can use your own database. XCA supports several database flavors, including MySQL, so we can start up a Dolt SQL server and point XCA to it.

Go ahead and download XCA, and get it running. The first time you open the app, you should see an empty screen like this:

Connecting to Dolt

Now we're ready to connect XCA to a dolt sql-server. If you haven't already installed dolt by now, what are you waiting for!? Go ahead and follow your preferred install instructions and get dolt onto your system.

Note: If you've just installed dolt, don't forget to configure your name and email before you run any dolt commands:

$ dolt config --global --add user.email YOU@DOMAIN.COM
$ dolt config --global --add user.name "YOUR NAME"

Dolt can operate as a CLI, a la Git, or a sql-server, a la MySQL. We're going to create our new Dolt database with the CLI, just like you'd create a new Git repository:

mkdir xca_data && cd xca_data;
dolt init 

Now we've got a Dolt database initialized and ready to use. Note that the name of the database comes from the directory it's in, so xca_data in this example. You can call it whatever you want, but know that if you use a hyphen in the name, you'll need to swap it with an underscore when you reference the database name.

Since we're dropping in Dolt for MySQL, we want to start up a dolt sql-server. There are a few options we want to set, notably, dolt_transaction_commit, which we'll talk about more later. Create a new file, dolt.config, in the xca_data directory you just created and put in these contents:

log_level: warning

behavior:
  dolt_transaction_commit: true

There are a lot more options you can set in that configuration file if you need to. For example, if you already have a MySQL server running on port 3306, you can set an alternate port for Dolt to run on, or you can add a user name and a password for a more secure login if you're going to be making your dolt sql-server available on the internet.

Now that we've created our config file, let's pass it to dolt sql-server and start up our local server:

dolt sql-server --config dolt.config

If the server starts up correctly, you should see some output like:

Starting server with Config HP="localhost:3306"|T="28800000"|R="false"|L="warning"|S="/tmp/mysql.sock"

With our dolt sql-server running, we can get XCA connected to it now. From the File menu, select Open Remote Database:

The next screen allows you to tell XCA how to connect to the dolt sql-server we just started. Make sure you select MySQL / MariaDB as the database type. If you don't specify a user when you start dolt sql-server, by default the user account will be named root. If you didn't specify a password when starting dolt sql-server, then you don't need to specify one here either. Database name comes from the directory you created for your Dolt database.

After you hit OK, since this is our first time using this new database, XCA will ask us for an application password to protect all of XCA's data. Note that this is NOT the database password. This is an additional password at the XCA level that enables you to keep your security credentials safe, even if someone gets access to your database. Go ahead and enter an application password for XCA that you'll remember.

Finally, if you want to use this same Dolt database with XCA in the future, you can set it as the default database for XCA to use each time you start up XCA. Just remember that you'll need to have the dolt sql-server process running whenever you want to use XCA!

Taking XCA for a Spin

Now that we've got XCA connected to Dolt, let's create some resources in XCA, starting with a private key:

After you hit Create, you should see a dialog confirming that your new private key was created successfully, and you should also see it listed in the private keys tab of XCA's main window. You can double click on your new private key in XCA to bring up another window with more information on your private key as well as change its name or add a comment.

Now that we've got a private key, let's try creating a certificate. Click on the Certificates tab in the main XCA window and hit the New Certificate button.

This first tab, Source, gives you some options for how to sign the certificate, and if you want to use a template for setting certificate options. Go ahead and select the TLS_Server template and we'll create a certificate that can be used by a process listening for TLS connections. After you select TLS_Server for the template, don't forget to hit the Apply extensions button! Hitting that button will fill in the Extensions tab for you.

The Subject tab allows you to specify metadata about the subject of your certificate. At minimum, you'll need to fill in an Internal Name for your certificate and a commonName. After that, you can hit OK to create your first certificate with XCA. You can double-click on it in the main XCA window to see more details about the certificate you just created:

Better Together: XCA and Dolt

Now that we've seen a bit of what XCA can do, let's go a little deeper and see how the data is being stored in our Dolt database. Let's start by connecting to our running dolt sql-server using the standard mysql client:

mysql --protocol TCP -uroot xca_data

Once we're logged in to our database, we can poke around and see all the data XCA has created:

mysql> show tables;
+--------------------+
| Tables_in_xca_data |
+--------------------+
| authority          |
| certs              |
| crls               |
| items              |
| private_keys       |
| public_keys        |
| requests           |
| revocations        |
| settings           |
| templates          |
| token_mechanism    |
| tokens             |
| view_certs         |
| view_crls          |
| view_private       |
| view_public_keys   |
| view_requests      |
| view_templates     |
| x509super          |
+--------------------+
19 rows in set (0.01 sec)

So, we can see that Dolt works as a MySQL compatible database engine for the XCA application, but why bother using Dolt instead of MySQL? Can Dolt make the XCA experience even better than when you're using MySQL? Let's check out how Dolt's data versioning features improve the experience with XCA...

Let's start by looking at the commit history for our data. Earlier, in the configuration file we created for our dolt sql-server, we specified the dolt_transaction_commit behavior setting. Turning this setting on will set the global @@dolt_transaction_commit system variable to true, which will automatically create a Dolt commit for every SQL commit. As a quick refresher, SQL commits are controlled through statements such as begin transaction and commit and control when changes in your SQL session are visible to other SQL sessions. Dolt commits are a layer on top of that – when you create a Dolt commit, you create a new node in the Dolt commit graph, just like when you use git commit. Normally, an application would do this explicitly by calling the dolt_commit() stored procedure. This is the recommended approach because it gives you full control over exactly what and when you commit, as well as setting a descriptive commit message. However, with third-party applications like XCA, where we aren't able to easily change the SQL statements it runs, you can enable the @@dolt_transaction_commit setting to make Dolt automatically create a Dolt commit for every SQL commit it processes. This is a really handy way to get a versioned commit history for applications that aren't explicitly aware of Dolt's commit model.

Since we turned on @@dolt_transaction_commit earlier, we can see a full history for all the changes XCA made while we were using the application earlier. The dolt_log system table shows us all the commits reachable from our current branch HEAD:

mysql> select * from dolt_log;
+----------------------------------+-----------+-------------------------+---------------------+----------------------------+
| commit_hash                      | committer | email                   | date                | message                    |
+----------------------------------+-----------+-------------------------+---------------------+----------------------------+
| 3b8galm4a705f29k7opeuff68717abkq | jfulghum  | jason.fulghum@gmail.com | 2023-08-21 17:20:30 | Transaction commit         |
| ktmh60dl1qkps1ftjqirntnrp7nboacj | jfulghum  | jason.fulghum@gmail.com | 2023-08-21 17:09:31 | Transaction commit         |
| 1ipll3qc7655i9en4fpi6t4cjscnodov | jfulghum  | jason.fulghum@gmail.com | 2023-08-21 17:09:31 | Transaction commit         |
...
+----------------------------------+-----------+-------------------------+---------------------+----------------------------+
13 rows in set (0.00 sec)

Alternatively, we can look in the dolt_diff system table to see a high-level summary of what tables those commits changed:

mysql> select * from dolt_diff;
+----------------------------------+-----------------+-----------+-------------------------+---------------------+--------------------+-------------+---------------+
| commit_hash                      | table_name      | committer | email                   | date                | message            | data_change | schema_change |
+----------------------------------+-----------------+-----------+-------------------------+---------------------+--------------------+-------------+---------------+
| 3b8galm4a705f29k7opeuff68717abkq | certs           | jfulghum  | jason.fulghum@gmail.com | 2023-08-21 17:20:30 | Transaction commit |           1 |             0 |
| 3b8galm4a705f29k7opeuff68717abkq | items           | jfulghum  | jason.fulghum@gmail.com | 2023-08-21 17:20:30 | Transaction commit |           1 |             0 |
| 3b8galm4a705f29k7opeuff68717abkq | x509super       | jfulghum  | jason.fulghum@gmail.com | 2023-08-21 17:20:30 | Transaction commit |           1 |             0 |
| ktmh60dl1qkps1ftjqirntnrp7nboacj | settings        | jfulghum  | jason.fulghum@gmail.com | 2023-08-21 17:09:31 | Transaction commit |           1 |             0 |
| 1ipll3qc7655i9en4fpi6t4cjscnodov | settings        | jfulghum  | jason.fulghum@gmail.com | 2023-08-21 17:09:31 | Transaction commit |           1 |             0 |
| sf1f8ho487v9emjhl5kqun70d9r6nr9i | settings        | jfulghum  | jason.fulghum@gmail.com | 2023-08-21 17:09:31 | Transaction commit |           1 |             0 |
| k18sq61bt2ug4gh3mjq7cmho05b6gumc | settings        | jfulghum  | jason.fulghum@gmail.com | 2023-08-21 17:09:31 | Transaction commit |           1 |             0 |
| atgvkgn0c7o5t4p2os2dfllto4jesck8 | settings        | jfulghum  | jason.fulghum@gmail.com | 2023-08-21 17:09:31 | Transaction commit |           1 |             0 |
| 5hotg4nnh8kda3pn24nklsebn6b8fkdo | settings        | jfulghum  | jason.fulghum@gmail.com | 2023-08-21 17:09:31 | Transaction commit |           1 |             0 |
...
+----------------------------------+-----------------+-----------+-------------------------+---------------------+--------------------+-------------+---------------+
29 rows in set (0.02 sec)

For any of those commits listed, we can dig deeper and see exactly how any rows in a table changed by using the dolt_diff() table function. For example, I see the most recent commit on the current branch (which is always aliased to HEAD) changed the certs, items, and x509super tables, so we can use dolt_diff() to see exactly how the data changed in those tables:

mysql> select * from dolt_diff('HEAD~', 'HEAD', 'certs') \G
*************************** 1. row ***************************
         to_item: 2
         to_hash: 849378307
     to_iss_hash: 1021136096
       to_serial: 67EB51B45327F24C
       to_issuer: 2
           to_ca: 0
         to_cert: MIIDVTCCAj2gAwIBAgIIZ+tRtFMn8kwwDQYJKoZIhvcNAQELBQAwGTEXMBUGA1UEAxMOZGV2Lm15dGVzdC5jb20wHhcNMjMwODIxMTcxOTAwWhcNMjQwODIwMTcxOTAwWjAZMRcwFQYDVQQDEw5kZXYubXl0ZXN0LmNvbTCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAK9w54vaZBTqKm8FSiWRfzDLGoC+aEe4NyBWmPEuVfctYxrNqB+7cB6MPdrSooNRYD3Vf2TYfkMFAdFALKxe6FWbp1mrjEmyqj2KtP3/k+KRFNb2ETt/Br8AFCtESYmfeonv+mU0aa14EM+Z8li/qkN2XJRomq9tuX4sUNR3EmzP0Bk54rcHwGprB1N+lVDXLxIjXe658uDB0JPMCH1HcT7eXZbYkxICASCEng6Sk6wvxQM2GcxpHH06T9jqj+hQ2U38zm+zn9g/K67kO5bf3r1WlyYSB/YNmcA7CI2yFRyZyRySHRLZYxQGa3bIE279mGKbIoZ2path/TLZt8+XPHECAwEAAaOBoDCBnTAMBgNVHRMBAf8EAjAAMB0GA1UdDgQWBBRUpj12k8I74piKEj2VkcnxYa61rTALBgNVHQ8EBAMCA+gwEwYDVR0lBAwwCgYIKwYBBQUHAwEwGQYDVR0RBBIwEIIOZGV2Lm15dGVzdC5jb20wEQYJYIZIAYb4QgEBBAQDAgZAMB4GCWCGSAGG+EIBDQQRFg94Y2EgY2VydGlmaWNhdGUwDQYJKoZIhvcNAQELBQADggEBAHx4qMvU0uk+x2OAzBmsGzdBf2cSW88tOUPw7QuQl5DERmOrdmD4HQB+gzvWJ8iYf38/pUK0XqYA2g1Gf62k17lfsBIi4tjBBLJKilloRSUcC+ztCxZIr9eVv65U9CpTJPSEoKJX9sOl+GotBkJiqs2Xutl23E9Wd4lRm9h0smWtiTSAbBuxJqJfXJppq8DfN9XSGCmAw2qqJV00GVqiBcqWZcxualmt8G2mRf46INWVjXONJOjijAme5yzTvuICiVdgfcWVPR7XtIrMG+o5HynMnkt0uVYzlidqVRarMEdEpm9UptQIEooG2UougXdWf/4/eqEVUgP8C73uvbNj++k=
       to_commit: HEAD
  to_commit_date: 2023-08-21 17:20:30.395
       from_item: NULL
       from_hash: NULL
   from_iss_hash: NULL
     from_serial: NULL
     from_issuer: NULL
         from_ca: NULL
       from_cert: NULL
     from_commit: HEAD~
from_commit_date: 2023-08-21 17:09:31.881
       diff_type: added
1 row in set (0.01 sec)

The ability to deeply audit your data and see exactly how it changed is incredibly powerful. Let's go back into the XCA application and simulate a real world mistake...

Go to the Private Keys tab in the main XCA window, select the private key we created earlier, and then hit the Delete button. Don't worry, Dolt will help us rescue it!

Without that private key, we aren't going to be able to validate signatures against the certificate that's using it. Mistakes like this aren't uncommon when managing lots of data, but if you have a versioned history of your data, then it's easy to recover from it.

First we need to find the change where we lost our data. We can go back to the dolt_log system table or the dolt_diff system table to quickly see what's been changing:

mysql> select * from dolt_diff;
+----------------------------------+-----------------+-----------+-------------------------+---------------------+--------------------+-------------+---------------+
| commit_hash                      | table_name      | committer | email                   | date                | message            | data_change | schema_change |
+----------------------------------+-----------------+-----------+-------------------------+---------------------+--------------------+-------------+---------------+
| bt8ukjlaga176rhger99rva19ulh26l7 | settings        | jfulghum  | jason.fulghum@gmail.com | 2023-08-21 18:13:16 | Transaction commit |           1 |             0 |
| erjccpggohpsc8dmimugf93a193ko0jj | items           | jfulghum  | jason.fulghum@gmail.com | 2023-08-21 18:12:11 | Transaction commit |           1 |             0 |
| erjccpggohpsc8dmimugf93a193ko0jj | private_keys    | jfulghum  | jason.fulghum@gmail.com | 2023-08-21 18:12:11 | Transaction commit |           1 |             0 |
| erjccpggohpsc8dmimugf93a193ko0jj | public_keys     | jfulghum  | jason.fulghum@gmail.com | 2023-08-21 18:12:11 | Transaction commit |           1 |             0 |
| erjccpggohpsc8dmimugf93a193ko0jj | x509super       | jfulghum  | jason.fulghum@gmail.com | 2023-08-21 18:12:11 | Transaction commit |           1 |             0 |
| 3b8galm4a705f29k7opeuff68717abkq | certs           | jfulghum  | jason.fulghum@gmail.com | 2023-08-21 17:20:30 | Transaction commit |           1 |             0 |
| 3b8galm4a705f29k7opeuff68717abkq | items           | jfulghum  | jason.fulghum@gmail.com | 2023-08-21 17:20:30 | Transaction commit |           1 |             0 |
| 3b8galm4a705f29k7opeuff68717abkq | x509super       | jfulghum  | jason.fulghum@gmail.com | 2023-08-21 17:20:30 | Transaction commit |           1 |             0 |
| ktmh60dl1qkps1ftjqirntnrp7nboacj | settings        | jfulghum  | jason.fulghum@gmail.com | 2023-08-21 17:09:31 | Transaction commit |           1 |             0 |
...
+----------------------------------+-----------------+-----------+-------------------------+---------------------+--------------------+-------------+---------------+
34 rows in set (0.02 sec)

Based on the tables that changed, we can guess that erjccpggohpsc8dmimugf93a193ko0jj is the offending commit. We can use the dolt_diff_stat() table function to get a summarized version of what that commit changed:

select * from dolt_diff_stat('erjccpggohpsc8dmimugf93a193ko0jj~', 'erjccpggohpsc8dmimugf93a193ko0jj');
+--------------+-----------------+------------+--------------+---------------+-------------+---------------+----------------+---------------+---------------+----------------+----------------+
| table_name   | rows_unmodified | rows_added | rows_deleted | rows_modified | cells_added | cells_deleted | cells_modified | old_row_count | new_row_count | old_cell_count | new_cell_count |
+--------------+-----------------+------------+--------------+---------------+-------------+---------------+----------------+---------------+---------------+----------------+----------------+
| items        |               1 |          0 |            0 |             1 |           0 |             0 |              2 |             2 |             2 |             16 |             16 |
| private_keys |            NULL |          0 |            1 |          NULL |        NULL |          NULL |           NULL |          NULL |          NULL |           NULL |           NULL |
| public_keys  |            NULL |          0 |            1 |          NULL |        NULL |          NULL |           NULL |          NULL |          NULL |           NULL |           NULL |
| x509super    |            NULL |          1 |            1 |          NULL |        NULL |          NULL |           NULL |          NULL |          NULL |           NULL |           NULL |
+--------------+-----------------+------------+--------------+---------------+-------------+---------------+----------------+---------------+---------------+----------------+----------------+
4 rows in set (0.00 sec)

Sure enough, we can see that commit deleted a row from private_keys. Let's use the dolt_revert() stored procedure to get it back!

mysql> call dolt_revert('erjccpggohpsc8dmimugf93a193ko0jj');
+--------+
| status |
+--------+
|      0 |
+--------+
1 row in set (0.03 sec)

Now head back over to the XCA app, quit it and restart it to force it to reload its data from the database, and we see that our private key is back!

Conclusion

If you're looking for a slick UI to manage certificates and private keys, definitely check out XCA! It's a solid tool with lots of nice features and is very easy to use. If you do... I hope you will plug in Dolt as the underlying database for your data!

We take MySQL compatibility seriously and are more than happy to jump on MySQL compatibility issues customers hit. If you have a MySQL compatible tool you want Dolt to work with, just let us know! You can send us an issue on GitHub, or just drop by the DoltHub Discord and let us know about any issues you hit or questions you have.