SQLite trusted_schema controls indirect schema execution
An SQLite view or trigger can contain an expression that calls an application-defined function. If the database schema is untrusted, opening the database and querying that view may execute the function even though the application never intended to expose it. PRAGMA trusted_schema is the setting that determines whether unaudited functions and virtual tables may be used by schema objects.
The setting applies to functions and virtual tables inside views, triggers, CHECK constraints, DEFAULT clauses, generated columns, expression indexes, and partial indexes. It is per connection and is ON by default for backwards compatibility. The SQLite documentation recommends turning it off on every connection, while noting that some applications may be affected.
When trusted_schema is OFF, SQLite takes additional defensive steps: it prohibits unaudited functions inside schema objects unless they are tagged SQLITE_INNOCUOUS, and it restricts unaudited virtual tables in triggers and views. The SQLITE_DIRECTONLY flag can prevent application functions from being used inside schema at all. That distinction is useful because a function can be safe for a direct, parameterized query and still be unsafe when a schema author controls its arguments.
The authorizer is still required
trusted_schema is not a general SQL sandbox. SQLite's authorizer callback is the mechanism for deciding which SQL operations an application will permit. It can deny schema changes, privileged tables, or dangerous function calls. The documentation recommends combining the authorizer with resource limits and controls on database size when processing SQL from untrusted sources.
Applications also need to review extension loading and custom functions. A custom function that opens a file, runs a command, or returns privileged data must not become reachable through a view that a lower-trust component can query. Marking functions correctly is part of a defense-in-depth plan, not a substitute for keeping the function out of the connection.
Use a dedicated connection profile for untrusted databases. Set PRAGMA trusted_schema=OFF immediately after opening the connection, register only the functions that are needed, apply SQLITE_DIRECTONLY to sensitive ones, and install an authorizer for the operations the application supports. Verify that the connection settings remain in place after migrations or transactions.
Evidence to collect
Run PRAGMA trusted_schema and inspect the function list on a test connection. Create a view that calls a harmless application function and confirm that it fails with the restrictive setting when the function is not marked innocuous. Then mark a test-only function innocuous and confirm the intentional exception. Do this in a disposable database, not in the production file.
If the application can open a database supplied by a user, add a schema review step and reject unexpected triggers or views before querying them. Log the connection setting, database identity, and authorizer decisions. A setting that is enabled only in a forgotten migration connection is not a security boundary.
The safe design has two layers: the schema cannot invoke functions the application has not audited, and the authorizer refuses operations the application does not need. trusted_schema closes one indirect path. It does not replace the policy that controls the SQL statement itself.
Top comments (0)