Issue #8663: D1 database unable to execute queries. Error: not authorized: SQLITE_AUTH

Summary

Recommendation

CLOSE - User Error / Documentation Gap

Analysis

Root Cause Identified

The issue is caused by using unsupported PRAGMA statements in the user's SQL file. Specifically:

1. PRAGMA foreign_keys = ON - While D1 does support this PRAGMA per the documentation, D1 already enforces foreign keys by default. The documentation states: "By default, D1 enforces that foreign key constraints are valid within all queries and migrations. This is identical to the behaviour you would observe when setting PRAGMA foreign_keys = on in SQLite for every transaction." This means the PRAGMA may be redundant but should work.

2. PRAGMA triggers = OFF/ON - This is NOT a valid SQLite PRAGMA at all. The user mentioned in their comment that using PRAGMA triggers = OFF or PRAGMA triggers = ON causes the error. This is the likely culprit - PRAGMA triggers does not exist in SQLite's PRAGMA list.

Evidence from User's SQL File

The reproduction repository shows PRAGMA foreign_keys = ON; in create.sql. However, the user's comment explicitly states:

"after many tests it seems that using: PRAGMA triggers = OFF; or PRAGMA triggers = ON; hit this strange error"

D1 PRAGMA Support

D1 has a limited set of supported PRAGMAs documented at https://developers.cloudflare.com/d1/sql-api/sql-statements/. The supported PRAGMAs include:

• PRAGMA table_list, PRAGMA table_info, PRAGMA table_xinfo
• PRAGMA index_list, PRAGMA index_info, PRAGMA index_xinfo
• PRAGMA quick_check
• PRAGMA foreign_key_check, PRAGMA foreign_key_list
• PRAGMA foreign_keys = (on|off)
• PRAGMA defer_foreign_keys = (on|off)
• PRAGMA case_sensitive_like = (on|off)
• PRAGMA ignore_check_constraints = (on|off)
• PRAGMA legacy_alter_table = (on|off)
• PRAGMA recursive_triggers = (on|off)
• PRAGMA reverse_unordered_selects = (on|off)
• PRAGMA optimize

PRAGMA triggers is NOT supported because it doesn't exist in SQLite at all.

Similar Issues

• #5598: PRAGMA foreign_key_check authorization issue (still open)
• #8512: Local D1 migrations PRAGMA foreign_keys=OFF (closed - user error, advised to use defer_foreign_keys)

Resolution

This appears to be user error - the user is attempting to use a non-existent SQLite PRAGMA (PRAGMA triggers). The error message "not authorized: SQLITE_AUTH" is D1's way of rejecting unknown or unsupported PRAGMA statements.

Recommended Action

1. Close the issue with an explanation that PRAGMA triggers is not a valid SQLite command
2. Recommend the user use PRAGMA recursive_triggers = (on|off) if they want to control trigger recursion behavior
3. Suggest removing the unsupported PRAGMA from their SQL file

Changelog Search

No fix for issue #8663 found in the wrangler changelog.

PR Search

No PRs found mentioning issue #8663.