Inconsistent error codes after SQLITE_DENY from authorization callback

(1) By Stephen Lombardo (sjlombardo) on 2022-03-17 20:18:49 [source]

There appears to be a small regression in the behavior of the authorization feature starting with 3.37.0. In SQLite 3.36.0, if an authorization callback is registered and returns SQLITE_DENY for a create table operation, the error code will always be (23) SQLITE_AUTH. Starting in 3.37.0 and through trunk, the error will be different depending on whether the database exists. In the case of a new database, (23) SQLITE_AUTH is returned. However, if the database already exists, then the error code is (17) SQLITE_SCHEMA. In addition, even though the error code is 17, sqlite3_errmsg() still reports the error message for (23) SQLITE_AUTH, "not authorized".

This is a very simple auth program that registers an authorizer that will always return SQLITE_DENY and then reports both the error code and error message from the API.

#include <stdio.h>
#include "sqlite3.h"

int deny_authorizer(
  void* pUserData, int action, const char* table, const char* column, 
  const char* zDbSName, const char* zAuthContext 
#ifdef SQLITE_USER_AUTHENTICATION
  ,db->auth.zAuthUser
#endif
) {
  return SQLITE_DENY;
}

int main(int argc, char **argv) {
  sqlite3 *db = NULL;
  sqlite3_stmt *stmt = NULL;
  int rc = 0;
 
  printf("SQLite Version=%s\n", sqlite3_libversion());
  if((rc = sqlite3_open("auth.db", &db)) != SQLITE_OK) goto error;
  if((rc = sqlite3_set_authorizer(db, deny_authorizer, db)) != SQLITE_OK) goto error;
  if((rc = sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS t1(a,b);", NULL, 0, NULL)) != SQLITE_OK) goto error;
  goto cleanup;

error:
  printf("error %d: %s\n", rc, sqlite3_errmsg(db));

cleanup:
  if(stmt != NULL) sqlite3_finalize(stmt);
  if(db != NULL) sqlite3_close(db);
  return rc;
}

The following example runs demonstrate the difference.

3.36.0:

$ ./auth 
SQLite Version=3.36.0
error 23: not authorized
$ ./sqlite3 auth.db
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
sqlite> create table t1(a);
sqlite> .q
$ ./auth 
SQLite Version=3.36.0
error 23: not authorized

trunk:

$ ./auth 
SQLite Version=3.39.0
error 23: not authorized
$ ./sqlite3 auth.db
SQLite version 3.39.0 2022-03-17 11:23:13
Enter ".help" for usage hints.
sqlite> create table t1(a);
sqlite> .q
$ ./auth 
SQLite Version=3.39.0
error 17: not authorized

(2) By Simon Slavin (slavin) on 2022-03-18 10:45:02 in reply to 1 [link] [source]

Result 23 is SQLITE_AUTH which is, of course the right result.

Result code 17 is SQLITE_SCHEMA which indicates that the schema changed. Given that you went to the effort of writing a special test program to demonstrate your issue, I don't understand why you should be receiving that code.

There is one possibility I thought of, but the documentation says it can't occur. Since the CREATE TABLE command is your first operation on the new database, there may not yet be a cache of the schema yet. Therefore sqlite3_prepare() might get SQLITE_SCHEMA on its first attempt.

However you are calling sqlite3_exec(). The documentation says that this calls sqlite3_prepare_v2(). And the documentation for that says that it retries SQLITE_MAX_SCHEMA_RETRY times (default 50) before returning that error.

If none of the things I mentioned suggests anything you want to test or fiddle with, I hope the developer team investigates.

(3) By Stephen Lombardo (sjlombardo) on 2022-03-21 15:30:03 in reply to 2 [link] [source]

Thanks for taking a look at this. I completely agree that SQLITE_AUTH should be the correct result, and that SQLITE_SCHEMA appears inconsistent with the sqlite3_set_authorizer documentation. In addition, even if SQLITE_SCHEMA was the expected result, sqlite3_errstr should produce an error message that matches that error code.

Bisecting shows this problem was introduced in by check-in 91bcb9621529b58d. Prior to that the library would return (23) SQLITE_AUTH for any denied CREATE TABLE operation, regardless of whether the database file existed already. Starting with that commit, sqlite3StartTable sets pParse->checkSchema = 1; in the begin_table_error case, even if the error is an authorization failure. This in turn causes the (17) SQLITE_SCHEMA when the subsequent schema check fails.

This issue could legitimately affect any application using a simple authorizer to control table creation. The impact is probably minor because the DDL operation is still blocked, but if the application uses the resulting error code for flow control, error handling, or even user messaging it would be a problem.