rqlite reserves control of SQLite journal settings

rqlite deliberately disables SQLite's automatic WAL checkpointing when it opens its write connection. The database source at 8.26.8 calls this control critical to rqlite. It also restricts the write pool to one open connection so a replacement connection does not re-enable checkpointing. Those choices make journal management part of the surrounding distributed database's implementation.

An application that changes the same SQLite controls through SQL can therefore interfere with assumptions rqlite established during initialization. rqlite 8.26.8, released on July 28, adds checks for several PRAGMA statements that would break those assumptions. The changelog carefully describes the release as attempting to block such statements. The implementation is a set of regular expressions over request text.

I value the ability to use SQL within rqlite's small deployment model. That does not make every engine control an appropriate application setting. The component responsible for database lifecycle needs to retain the settings its own code depends on. This patch makes that ownership more explicit at the request boundary.

The settings the filter recognises

Pull request 1838 adds patterns for assignments to journal_mode, wal_autocheckpoint and synchronous. It also adds a pattern for wal_checkpoint operations. These are specific controls, not a prohibition on the PRAGMA keyword as a whole. The tests retain a non-matching PRAGMA together with an ordinary SELECT as an accepted request.

The version-pinned state implementation shows the matching rules. They ignore case and tolerate whitespace in specified positions. Some patterns accept an optional schema prefix. For example, the tests include both PRAGMA journal_mode=WAL and a schema-qualified journal-mode assignment. They also include a schema-qualified WAL checkpoint operation.

Reading a setting and assigning it receive different treatment in the patterns. For journal-mode and synchronous settings, the expressions look for an equals sign after the setting name. The checkpoint pattern does not require an assignment because invoking a checkpoint is itself the operation of concern. A request's treatment therefore depends on the actual expression matched by the filter.

An example from the system tests is enough to show the new result. The request tries to change automatic checkpointing, and the server returns a request-level error.

PRAGMA wal_autocheckpoint = 1000;

{"results":[],"error":"disallowed pragma"}

This is an example of a rejected operation. It is useful when diagnosing a client that sends a standard SQLite initialization sequence to every database it connects to. A sequence that is appropriate when the application owns SQLite's connection setup may conflict with rqlite's own setup. The release gives that conflict an explicit response for the forms it recognises.

Checking the whole request first

The store adds a wrapper around its protocol request and a method that scans the statements. If any statement matches a disallowed pattern, the check returns an error. It runs at the start of the execute, query and unified request methods. Those entry points therefore share the same preliminary policy check.

The placement is significant for requests with several statements. The wrapper scans the request before the store proceeds with execution. A disallowed statement at the end is enough to reject the request, even when earlier statements would have been acceptable. The tests include both leading and trailing disallowed statements, so the check is not limited to the first SQL string.

Putting the check in the store also makes it independent of whether a caller chose the query or execution API. The system tests send a checkpoint-setting assignment through both forms and expect the same rejection. A client cannot assume the read-oriented endpoint turns an engine-control statement into a harmless inspection merely because of the endpoint's name.

The request-level error is intentionally terse. It reports a disallowed pragma without identifying the matching expression in the response. A caller dealing with a batch must therefore inspect the statements it sent to determine which one triggered the filter. That follows from the implementation returning immediately on a match with the same fixed error text.

A textual check has a specific scope

The filter calls regular-expression matching on each statement's SQL string. It does not parse a complete SQL syntax tree and classify the effect of every valid PRAGMA form. That limits the claim that can be made about the patch. It blocks the forms matched by its expressions, and the tests document a collection of expected matches.

The expressions also search text without first excluding string literals or comments through lexical analysis. As an inference from those matching rules, SQL containing matching text in another syntactic context can trigger the check. This is a consequence of choosing a textual filter. It is separate from whether SQLite itself would execute that text as an engine-control statement.

The patch therefore expresses an operating rule more clearly than it establishes a general SQL security mechanism. Applications should leave these controls to rqlite because its implementation relies on them. Treating the current patterns as a complete catalogue of every syntactic way to affect SQLite would go beyond the code and tests available in this release.

This division of responsibility also affects imported connection configuration. A library may contain a setup routine shared between embedded SQLite and a remote database adapter. In rqlite, that routine needs to respect the server's ownership of the write connection. Removing a rejected initialization statement is a change to that adapter's assumptions, not evidence that the application's tables or data require another representation. The error concerns database control before the store starts processing the requested statements.

My preference is to keep application SQL concerned with application data and supported database operations in the distributed service. rqlite's explicit disabling of automatic checkpoints shows why a lower-level tuning command can violate that separation. The new request check catches recognised attempts to change those controls before execution, while its regular-expression implementation defines the actual coverage of that protection.