SQLite Forum

VACUUM fails on a database with enabled user authentication extension
Login

VACUUM fails on a database with enabled user authentication extension

(1.1) By Aloys (aloys) on 2021-04-16 21:01:41 edited from 1.0 [link] [source]

I compiled SQLite 3.35.4 and the SQLite shell with enabled user authentication extension. Then I used the SQLite shell to create a new database. I added a user with the shell command

.user add admin 12345 1

I inserted, updated and deleted data for a while, and then later on I entered the SQL command

VACUUM;

The command failed. With logging turned on I got the following error messages:

(1) no such table: vacuum_db.sqlite_user in "INSERT INTO vacuum_db.'sqlite_user' SELECT*FROM"main".'sqlite_user'"
(1) statement aborts at 1: [vacuum;] no such table: vacuum_db.sqlite_user
Error: no such table: vacuum_db.sqlite_user

Thereafter I tried to perform a user login before executing the VACUUM command:

.user login admin 12345
VACUUM;

The error messages look a bit different, but the VACUUM command still fails:

(1) no such table: vacuum_db.sqlite_user in "SELECT pw=sqlite_crypt(?1,pw), isAdmin FROM "vacuum_db".sqlite_user WHERE uname=?2"
(279) statement aborts at 4: [ATTACH '' AS vacuum_db] unable to open database:
(23) statement aborts at 1: [vacuum;] unable to open database:
Error: unable to open database:

I didn't find any hint in the documentation of the user authentication extension that this behaviour were to be expected.

Am I doing anything wrong? Or is there some sort of flaw in the user authentication extension?

Thanks in advance for any hint how to overcome this problem.

(2.1) By Larry Brasfield (larrybr) on 2021-04-17 22:12:28 edited from 2.0 in reply to 1.1 [link] [source]

I've spent enough time on this to have a preliminary conclusion. (I mention this now mainly to let you know this has not been utterly ignored.) First, some facts: 1. The extension is almost 7 years old, with not much development attention given to it between now and inception. 2. However, it does get exercised, to a degree, in the test suite. 3. That testing has led to some recent changes. 4. VACUUM functionality is not tested. 5. VACUUM's current implementation involves opening a temporary DB in which to collect data. It's schema.name is temp.vacuum_db . 6. There is no provision in the userauth extension for allowing temporary DBs to be opened absent sufficient authorization, which appears would be the same authorization required for the opened DB. 7. Hence, the VACUUM attempt fails.

My conclusion is that this extension either never worked with VACUUM, or that it once did but ceased as the SQLite library's VACUUM implementation evolved. (Also, you are not doing anything wrong that you have shown. I can replicate the difficulty, although with different symptoms.)

I will see about disabling authentication on temporary DBs. I have not worked out the security implications of doing this, so I may decide not to do it that way and then have to think of another approach that does not require changes in the SQLite library itself.

(3) By Aloys (aloys) on 2021-04-18 09:10:51 in reply to 2.1 [link] [source]

Thanks for your thorough analysis.

In the meantime I tested with an older SQLite version (version 3.8.7.4 December 2014). VACUUM doesn't work. So this supports your conclusion that VACUUM most likely never worked with enabled user authentication.

IMHO allowing to attach an empty temporary database for performing the VACUUM operation should do no harm, if an admin user was authorized before executing VACUUM.

However, this is probably not enough, because VACUUM has to create the table sqlite_user in the temporary database and later on to copy the content of this table. If I understand it correctly the table sqlite_user is read-only for admin users, so that inserting the values from the main database into the table in the temporary database probably will fail. I'm not sure whether this can be handled in the user authentication extension alone.

(4) By Aloys (aloys) on 2021-04-25 20:34:28 in reply to 2.1 [source]

In the meantime I inspected the code of the user authtentication extension a bit closer. I have the impression that there is a small flaw in the implementation of the internal function userAuthCheckLogin (line 84 ff in ext/userauth/userauth.c). According to the comment above the function the function should Check to see if database zDb has a "sqlite_user" table. However, actually the function checks whether the table sqlite_user exists in the "main" database - see lines 93 ff:

  if( !userTableExists(db, "main") ){
    *peAuth = UAUTH_Admin;  /* No sqlite_user table.  Everybody is admin. */
    return SQLITE_OK;
  }

Later on table sqlite_user in database zDb is queried for the user id and password. If the table sqlite_user does not exist, this query fails, of course.

Due to this flaw attaching an empty new persistent database fails, and I think that is wrong behaviour. One should be able to attach a new empty database in any case, because it is not (yet) under user authentication control.

IMHO lines 93 ff in ext/userauth/userauth.c should read:

  if( !userTableExists(db, zDb) ){
    *peAuth = UAUTH_Admin;  /* No sqlite_user table.  Everybody is admin. */
    return SQLITE_OK;
  }

With this change the VACUUM command works as expected.

(5) By Larry Brasfield (larrybr) on 2021-04-25 21:05:06 in reply to 4 [link] [source]

That is one of several flaws which I am correcting. Thanks.

Could you please state your use model for the authentication feature? (I can think of a few, but would like to get a better view of expectations.)

Are you also using the authorizer callback API?

(6) By Aloys (aloys) on 2021-04-25 21:40:14 in reply to 5 [link] [source]

Yes, I use the authorizer callback API. In principle, it allows me to perform fine-grained authorization of the database access.

However, I also need some sort of user management, where users authenticate themselves with a password. I could have used a multi-user database system like MySQL or PostgreSQL, but in fact the application typically runs on a single computer and is used by a small number of different persons. A SQLite databae is much simpler to administrate in this case than a full-blown multi-user database system.

The user authentication extension seemed to be a good starting point, although the concept of access rights is currently rather limited with only 2 levels of access rights (admin and user). At the moment I have an additional separate table that I use to manage a list of roles per user, and the list of assigned roles of the currently logged-in user is passed on to the authorizer callback.