~/sqlite-authorizer-agent-sql-policythesshguy.com

SQLite authorizers are a policy layer, not a safety claim

published
reading time
8 min

I wanted an AI agent to answer analytics questions with raw SQL.

TL;DR: SQLite's authorizer callback is a policy layer, not a safety guarantee. On SQLite 3.53.4, which Node v24.22.1 still ships, a FULL JOIN USING query reads a column the authorizer denies.

peek is an analytics app I built for my own use, across all my sites. I didn't want to hardcode endpoints like getStats(dateRange, filter) because every new question would require more code changes. Instead, I thought exposing access to use raw SQL would be better. With raw SQL, the agent can answer a new question without a code change.

But raw access to the SQLite database is dangerous if the agent can write to it. So I used SQLite's authorizer as the read-only policy layer.

That doesn't make arbitrary model-generated SQL safe, but a keyword regex is worse, and the authorizer is a better policy input.

If you want to do this yourself, check the real engine and run hostile tests before you trust column rules.

The authorizer sees operations, not spelling#

Searching for DELETE in a string is a guess. You're trying to infer what a query does from how it's spelled.

The authorizer runs where SQLite compiles the operations. SQLite's C API documents a callback that receives an action code and the names involved. For each action, your policy can allow it, reject the whole statement, or choose the documented ignore behavior.

It's the same idea as an API Gateway resource policy: enforce access where the platform handles the request, instead of in your own code.

Here's how peek uses it. Its sql module installs setAuthorizer as a default-deny policy:

  • SQLITE_READ checks a table and column allowlist and denies anything unknown.
  • A final SQLITE_DENY covers everything else.

That policy only covers the ad hoc SQL endpoint. peek's main app database connection uses a separate @libsql/client driver.

The authorizer sees what SQLite is about to do, not what the query looks like.

It only runs when a statement is prepared#

The official reference says: "The authorizer callback is invoked only during sqlite3_prepare() or its variants."

What does that mean for you?

A schema change can make step re-prepare a statement, so the right authorizer has to stay installed. And there's only one slot: "Only a single authorizer can be in place on a database connection at a time." Replace or disable it, and you've changed the policy for that whole connection.

In other words, if you cache prepared statements or pool connections, check which authorizer is installed when each statement gets prepared.

I wouldn't casually switch policy on a shared connection and assume each caller's statements get reauthorized under the right identity.

DENY and IGNORE are not synonyms#

The official API says SQLITE_DENY rejects statement preparation. SQLITE_IGNORE does something different for each operation.

For a read, IGNORE puts NULL in place of the column. Sounds safe enough.

For a delete? "the DELETE operation proceeds but the truncate optimization is disabled and all rows are deleted individually."

The rows still go.

That's enough to reject a generic "return IGNORE for forbidden operations" recipe. A default-deny policy needs a deliberate mapping for the action codes your implementation actually receives, including null names and count-style reads with empty column names.

Check the SQLite you actually ship#

I found an official SQLite bug discussion (linked at the end) that documents a FULL JOIN USING column-authorization bypass and a follow-up correction. The maintainer wrote that "the fix will not appear in an official release until version 3.54.0," and that the problem did not "warrant back-porting it to the 3.53.x branch." The official release page dates 3.54.0 to October 9, 2026.

So which SQLite are you running?

peek's ad hoc SQL endpoint uses Node's bundled node:sqlite, so its SQLite is whatever Node ships. On Node v24.13.1, SELECT sqlite_version() on that connection returned 3.51.2. The production image was node:24-slim with no patch pin, so the engine moved with every Node patch.

So I pinned it. peek now runs Node 24.22.3, and the same query on the live connection returns 3.54.1, which has the fix. Its tests include FULL JOIN USING cases that fail on Node 24.13.1 and pass on 24.22.3.

The checks below ran on SQLite 3.53.4 inside Node v24.22.1, which is still behind the fix. Selected compile options include THREADSAFE=1 and MAX_ATTACHED=10.

Query the version from the actual connection you deploy. Even then, a version string alone doesn't prove the tool is safe.

Read-only isn't the only risk#

A query can return sensitive data without writing anything. It can also burn a lot of resources before it returns a single row.

The C API docs suggest "lowering resource limits using sqlite3_limit() and limiting database size using the max_page_count PRAGMA in addition to using an authorizer."

peek's sql module caps results at 60,000 characters of serialized JSON (JSON.stringify(row).length, so characters, not bytes). There's also a default 200-row cap, with a maximum of 1,000.

That limits how much comes back. It doesn't limit how long a query runs.

Execution is still synchronous (DatabaseSync and statement.iterate()), with no statement timeout, interrupt(), progress handler or busy_timeout.

Don't call a result cap a timeout. If the runtime can't enforce the execution limits you need, use a different isolation design.

What the checks showed#

These checks aren't peek's tool. They're a default-deny column policy through setAuthorizer, run on synthetic data on SQLite 3.53.4, the engine inside Node v24.22.1 node:sqlite.

The policy denies users.email, DELETE, PRAGMA, ATTACH and unknown codes. Here's what it returned:

node v24.22.1, SQLite 3.53.4 (the engine inside node:sqlite)
## mode = DENY for forbidden columns
allowed column                           -> ok [{"name":"ann"},{"name":"bo"}]
                                           callbacks: SELECT(null,null) READ(users,name)
denied column                            -> ERR_SQLITE_ERROR access to users.email is prohibited
                                           callbacks: SELECT(null,null) READ(users,email)
denied column only in WHERE              -> ERR_SQLITE_ERROR access to users.email is prohibited
                                           callbacks: SELECT(null,null) READ(users,name) FUNCTION(null,like) READ(users,email)
denied column via alias + CTE            -> ERR_SQLITE_ERROR access to users.email is prohibited
                                           callbacks: SELECT(null,null) READ(users,email)
count(*)                                 -> ok [{"n":2}]
                                           callbacks: SELECT(null,null) FUNCTION(null,count) READ(users,)
FULL JOIN USING(email)                   -> ok [{"name":"ann"},{"name":"bo"}]
                                           callbacks: SELECT(null,null) READ(users,name)
INNER JOIN USING(email), select email    -> ERR_SQLITE_ERROR access to users.email is prohibited
                                           callbacks: SELECT(null,null) READ(users,email)
FULL JOIN USING(email), select email     -> ok [{"email":"[email protected]"},{"email":"[email protected]"}]
                                           callbacks: SELECT(null,null)
DELETE                                   -> ERR_SQLITE_ERROR not authorized
                                           callbacks: DELETE(users,null)
PRAGMA                                   -> ERR_SQLITE_ERROR not authorized
                                           callbacks: PRAGMA(table_info,users)
ATTACH                                   -> ERR_SQLITE_ERROR not authorized
                                           callbacks: ATTACH(:memory:,null)
two statements                           -> ok [{"name":"ann"},{"name":"bo"}]
                                           callbacks: SELECT(null,null) READ(users,name)
                                           rows in users afterwards: 2
## mode = IGNORE for forbidden columns and DELETE
denied column under IGNORE               -> ok [{"name":"ann","email":null},{"name":"bo","email":null}]
                                           callbacks: SELECT(null,null) READ(users,name) READ(users,email)
DELETE under IGNORE                      -> changes=2; rows left: 0

Notice a few things:

  • DENY works where a regex wouldn't. It blocks email in a WHERE clause and behind a CTE alias.
  • count(*) calls READ with an empty column name, as documented. The policy has to allow that on purpose.
  • The FULL JOIN USING bypass is live on 3.53.4. SELECT email ... FULL JOIN ... USING(email) returned every email. The authorizer never saw a READ for it. The INNER JOIN version was blocked.
  • Two statements didn't fail. prepare compiled only the first one and dropped the DELETE. No rows changed. That's binding behavior to test, not a rule to rely on.
  • IGNORE on DELETE still deleted both rows.

The tests I'd require#

Use synthetic data, not secrets, and test at least:

  • Allowed direct reads and denied columns referenced in expressions or filters.
  • Aliases, CTEs, aggregate expressions and FULL JOIN USING shapes.
  • Writes, PRAGMA, ATTACH and any other unsupported action.
  • Multiple statements and the binding's exact trailing-input behavior.
  • Reused prepared statements, policy changes and schema re-preparation.
  • Large outputs and expensive computation that returns little output.
  • Unexpected callback arguments and unknown action codes.

The goal isn't "the happy query worked." It's evidence that the denied operations fail under the exact engine and wrapper you're deploying.

What I'd add next#

peek still has no execution time limit. A worker thread won't fix that, because it can't be terminated while it's blocked inside a synchronous node:sqlite call. The only thing that actually stops a runaway query is running it in a child process and killing it with SIGKILL. peek holds tens of events, and at a seeded 500k rows every realistic query finished in under a second, so I'll reach for that at around 1M rows or the first time a query hangs.

I'd also look at a statement time limit, or a progress-handler-style abort, if the driver supports one.

Don't reach for the timeout option in the Node.js node:sqlite docs, though. It's only a busy timeout for lock waits, not a cap on how long a statement can run.

The bottom line#

An authorizer is a useful policy layer, but only for the engine you tested. Pair it with minimized data exposure, restricted filesystem and process authority, bounded resources, and regression coverage.

The next time you give an agent a SQL tool, run the hostile tests above against the engine you actually deploy.

Official sources#

EOF

cd ~ (all posts)