Opening a DB with SQLITE_OPEN_EXCLUSIVE
(1) By HiddenWindshield on 2021-05-04 14:12:43 [link]
I would like to submit a bug report, and was told that the SQLite team prefer that they be submitted through the forum to keep the noise down on the actual bug tracker. So, here goes: When trying to create a new database, the sqlite3_open_v2() function silently filters out the SQLITE_OPEN_EXCLUSIVE flag. I've looked at the source code, read the documentation, and everything I can find says that that's the intended behavior. But doing it this way creates a [race condition](https://tldp.org/HOWTO/Secure-Programs-HOWTO/avoid-race.html). That is, after all, why POSIX added the O_EXCL flag to the open() call. Now, I realize that the odds of this actually being exploited are very, very slim. But that's not zero. So I'd like to request that SQLite properly handle the SQLITE_OPEN_EXCLUSIVE flag properly rather than just silently filtering it out. Thank you.
I suspect that you expect too much from this flag. According to the documentation <blockquote>The SQLITE_OPEN_EXCLUSIVE flag, when paired with the SQLITE_OPEN_CREATE, is used to indicate that file should always be created, and that it is an error if it already exists. It is not used to indicate the file should be opened for exclusive access.</blockquote> There's a (still) not documented Windows-only parameter when opening with SQLITE_OPEN_URI (starting version 3034000). This was mentioned here at the forum and implemented very quickly, but still not mentioned at the docs. You should format uri something like this <blockquote>file:///c:/mydb.db3?exclusive=1</blockquote>
(3.1) By HiddenWindshield on 2021-05-07 14:44:09 edited from 3.0 in reply to 2 [link]
"Failing if the file already exists" is exactly the behavior I want. I don't want "exclusive access". Would it help if they renamed it "SQLITE_FAIL_IF_EXISTS"? I mean, I know they named it after the POSIX "O_EXCL" flag, but I always thought *that* flag was weirdly named to begin with. I appreciate the workaround, but I'm developing on Linux for a program that's eventually going to be multi-platform, so a Windows-only solution wouldn't work for me. I did find a [better workaround](https://mijailovic.net/2017/08/27/sqlite-adventures/) (from back in 2017, so this bug has obviously been around for a while), but that involves modifying the VFS, and you have to maintain a separate state variable to change whether you want exclusive mode or not. It would be **so** much simpler if the developers would just stop filtering that particular flag out of the flags parameter.
If you are trying to guarantee you're creating a new database file, just open the database file readwrite and execute your "CREATE TABLE ..." SQL statement. If the file already existed and wasn't an SQLite database, the open will fail. If a prior invocation created the database first (i.e. race condition), the SQL will fail. If you have different applications using the same database filename, that may or may not be a problem.
(5) By HiddenWindshield on 2021-05-13 14:22:13 in reply to 4 [link]
I appreciate the tip, but I [already have a much better (if slightly more cumbersome) workaround as I linked above](https://mijailovic.net/2017/08/27/sqlite-adventures/). But my point is I shouldn't **need** a workaround at all. Filtering out the "exclusive" flag\* provides exactly zero benefit, while at the same time preventing the standard solution to the [aforementioned race condition](https://tldp.org/HOWTO/Secure-Programs-HOWTO/avoid-race.html). Which is why I would still like to politely request of the developers that this completely useless and counterproductive filter be removed from the production version of SQLite. Thank you. \*Putting "exclusive" in quotes to emphasize again that the flag in question doesn't actually open the file in any kind of "exclusive" mode and is badly named.
Hello, I'm a developer who uses SQLite in various projects. I ran into the exact same problem. I'd like to avoid any race conditions with creating database files. For this, the `O_EXCL` flag (and by extension, the `SQLITE_OPEN_EXCLUSIVE` flag) would be perfect. I do not understand why I cannot use it, as it prevents me from writing correct, robust code. All I want is that the `sqlite3_open_v2()` call fails when the database file already exists. What puzzles me is that the original poster is instructed to work around the issue without any explanation as to why the seemingly erroneous behaviour is kept in SQLite. Is this bug report officially rejected?
Why do you assume that a race condition exists (or can exist) that you need to (or can) mitigate in any way whatsoever? You are not opening a file, you are opening a database. If in the process of opening a database there is a race condition against the underlying file, that would be a bug in the SQLite3 library. Just because you *want* to your car to be yellow to avoid it being green, where the car manufacturer does not sell green cars, is not a valid reason for wanting a yellow car. No matter what you do, you will not get a green car, so why bother specifying a colour at all if all you want to do is avoid getting a green car, which the car manufacturer cannot sell to you anyway? It sounds like you expending extra cycles in what is commonly called "premature optimization". In other words, you are expending effort in avoidance of a problem which does not yet exist (and which cannot exist).
The SQLITE_OPEN_EXCLUSIVE flag is an internal-use-only flag in a SQLite. The SQLite core will sometimes send that flag down into the VFS in order to tell the VFS that it wants the open to fail if the file already exists. But it is not legal to pass the SQLITE_OPEN_EXCLUSIVE flag into sqlite3_open(). That flag is silently masked off [here]. This fact is not well documented. The only hint that you, the reader, have is that if you look at the [documentation page for open flags], you will see the "VFS only" comment after the SQLITE_OPEN_EXCLUSIVE definition. This is, admittedly, a feeble hint. That part of the documentation could use improvement. : src:/info/7a1b7017af16977b29?ln=3146 : https://www.sqlite.org/c3ref/c_open_autoproxy.html But the end analysis is that SQLite is currently performing as designed and what you are asking for is an enhancement. You want the ability to pass in the SQLITE_OPEN_EXCLUSIVE flag and have it work like O_EXCL. Probably this enhancement will also need a new extended return code: SQLITE_CANTOPEN_EXISTS. It seems like a reasonable request. But we need to move cautiously about these kinds of things. So all I can say for now is that we will look into it.
It is [documented](https://sqlite.org/c3ref/c_open_autoproxy.html) that `SQLITE_OPEN_EXCLUSIVE` is only allowed in the `xOpen` method of the VFS, not in `sqlite3_open_v2`. (That it is filtered out does not seem to be documented, but passing flags other than the allowed ones is presumably not really allowed anyways.) (So, I do not think that it is really a bug.) However, I agree that I would want to allow `SQLITE_OPEN_EXCLUSIVE` for `sqlite3_open_v2` too; I have wanted this in my programming at least once. (There might be other ways to do it, e.g. by a VFS shim, although this and other ways seem a bit messy to me and are not as ideal as allowing it in SQLite directly.) So, I repeat the top message's request; I also make the same request that `SQLITE_OPEN_EXCLUSIVE` should be allowed on `sqlite3_open_v2`.
What problem are you trying to avoid by adding O_EXCL to open() calls? Why do you need to ensure that no other process is jumping in and creating the database file ahead of you? Suppose your application is doing this: 1. Check to see if the SQLite database already exists 2. If it does not exist, create it. What harm would come about if another rogue process created the database in between steps 1 and 2? I can invent a contrived scenario where that might lead to trouble, but I'm having trouble coming up with a real-world use case where that might be harmful. Presumably you have such a use-case. Can you share it with us?
I'm the anonymous who wrote (6), I created an account now :) Yeah, it's not actually a bug, the documentation exhaustively lists the flags you can use and `SQLITE_OPEN_EXCLUSIVE` is simply not among them. > What harm would come about if another rogue process created the database in between steps 1 and 2? Unfortunately, a detailed response will inevitably lead to a contrived example, as you expected. Suppose you launch the program twice, let's call the instances A and B. 1. A checks if DB exists. It does not. 2. B checks if DB exists. It does not. 3. A creates DB file with `sqlite3_open_v2()` 4. A moves on to do initialisation jobs (set up tables, insert rows, etc.) 5. B opens existing DB file with `sqlite3_open_v2()`. B also thinks it created the file. 6. B moves on to do initialisation jobs (set up tables, insert rows, etc.) I now have to expect races in the initialisation jobs and guess whether there was a race in file creation. This complicates the logic. Of course I could just interpret the failure of CREATE TABLE with "already exists" as such a race condition and back out of the initialisation logic in this case. But now I'm already working around downstream consequences of not knowing whether it was me who created the file. With a working `SQLITE_OPEN_EXCLUSIVE`, I am reasonably confident that only A will ever run an initialisation job and it will not be possible for any other process to ever run this initialisation job on this database. Now, I admit that this has never happened to me. You may be correct if you point out that it will never happen to me. What bothers me is that I am forced to write code that suffers from a race condition, especially given the flag to solve it exists and is even used internally. In a similar vein, the `open(2)` implementers could say: "Do you really need `O_EXCL`? You could just check for file existence and then create it. A race condition is exceedingly rare. If another program already created it, why does that matter? After all, the file is now there for you to use, and you can handle any races between two processes inside that file."
Why are you checking if the database exists at all? You do not care if the database exists or not. You only care that you can open it. You should be opening it and then checking to see if it contains what you expect it to contain, and if it does not, THEN you initialize the database. If 700 copies of the application open the database at the SAME TIME, then all of them may notice (by testing) that the database does not contain anything (is empty) but ONLY ONE OF THEM will be able to begin a transaction to "initialize the database" and 699 of them will get SQLITE_BUSY when they try to start the transaction necessary to "initialize the database". Where is the race condition?
Open the DB with `SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE` from both A and B, then immediately begin a transaction containing the `CREATE TABLE/INDEX/VIEW … IF NOT EXISTS` calls that establish the desired schema. Only one of the two will create the DB if it’s not already present, and only one of the two will establish the schema. Both will then be free to use the DB in the normal fashion.
I'm not sure about the OP's issue. I'd like my initialization for a new database to set journal_mode=WAL, but if an existing database has another journal mode, I want to respect the owner's decision to change what I had initially picked (even if they made the change outside of my application, perhaps using sqlite3.exe). However, that means my "complete initialization" can't be done in a single transaction (setting journal_mode=WAL is effectively its own transaction). I suppose my application could: 1) Initialize the contents (other than journal mode) in single rollback-mode transaction, and containing a flag saying "still need to do first-time WAL". 2) Set WAL mode (if that flag is still set). 3) Clear that flag (in another transaction). That lets me respect a user's journal_mode setting, unless they managed to get it in before (3), which should be uncommon. In the "700 copies trying to initialize at the same time", I think there is a good chance that none of them manage to successfully set WAL mode and clear the flag. I don't want to do WAL-mode first (and then the first explicit transaction), because I don't want to modify a non-empty database at all if it has the wrong application_id (which I set in the first transaction for a new or empty file). Another approach would be to treat empty files as unusable, and do my initialization only for new files (initialize to a temporary file name, and then rename to the user-requested file name). That means I have to close the temporary file, rename it (assumes rename fails if the target exists), and then open it again. With this approach, exactly one of the 700 initializers succeed. That has its own set of problems (sqlite3.exe creates an empty file, and now that file-name is unusable).
If the database is "new" and "empty" the following conditions hold: `select count(*) from sqlite_master;` will be 0 `pragma journal_mode;` will be 'delete' `pragma application_id;` will be 0 `pragma user_version;` will be 0 `pragma page_count;` will be 0 If these conditions all hold then do: ``` pragma locking_mode=exclusive; begin immediate; pragma application_id=(some non-zero number) pragma user_version=(some non-zero number) commit; ``` If you get here, then you have exclusive access to the database so that you can do your initialization after which you do: ``` pragma locking_mode=normal; pragma journal_mode=wal; ``` and you are good to go. If any of the pre-conditions fail, then this is not a "new and empty database" and if the application_id/user_version are not what you want then you need to wait a bit and check again, eventually bailing because it is not a database you can work with.
(16) By Richard Damon (RichardDamon) on 2021-06-16 01:16:52 in reply to 11 [link]
The key answer to how to handle races is how databases normally handle races, using a Transaction. If the program places the check and the creation of the tables and such in a transaction, you won't have the race problem. First, you don't first check if the DB exists, you just open it and see if the needed tables/data does (inside a transaction). If you use a simple BEGIN, then the second one might see the tables not there when it checks but will get a BUSY return when it goes to create the tables, and that tells it it needs to back off and start over. If you use a BEGIN IMMEDIATE, then the second one will delay doing its test due to the busy wait until the first finishes. This makes the logic simpler, but if the database in use might have longish write transactions might get some delays on start that wouldn't be otherwise needed.
Although I am not the same writer as at first, I can comment. At least in my case, it is unlikely that any harm would come due to a rogue process in between steps 1 and 2 (although it is not certain). However, disallowing passing `SQLITE_OPEN_EXCLUSIVE` to `sqlite3_open_v2` results in the program being more messy than it ought to be. I fail to see why passing that flag shouldn't be allowed; I don't know if there is some internal condition in SQLite that somehow won't work as it is currently written. However, it could probably be corrected; if it needs to mask out the flag after it opens the database file at first (in case it needs to use it again for some reason that I don't know), then it might do so. I don't know what other considerations there might be (or even if this is one of them).
Thanks. I hadn't noticed that locking_mode provides a way to avoid unlocking the database between one transaction and a following pragma journal_mode. However, if I kill my process after commit; but before setting wal, I've still got a database that shows my application_id, but which has never been wal. Admittedly, that is a very small window for failure. To close that window it seems like my choices are 1) A looser definition of "empty" that ignores the current journal mode, and for an empty database I set wal first, and then do my first transaction. This means that by the time I set application_id, the database had already been set to wal (somebody else could unset wal in-between, but since they can do that at any time, anyway, I'm ok with that). 2) Do what you suggested, but have my initial transaction set a "WAL pending" flag that gets cleared after my app (this one, or a later instance) succeeds in setting wal mode.
Why not just open() the file with the desired flags, save the result, and then do the SQLite open? Of course, you still have to have the same transaction to initialize the database in the case of a new file to deal with the same ultimate race conditions.
You will note that `pragma locking_mode=exclusive` only works this way when the journal_mode is not wal. Setting journal_mode=wal and locking_mode=exclusive have an entirely different meaning. You might, for example, create a crc32 aggregate function. Then you can set the user_version to the result of `select crc32(sql) from (select sql from sqlite_master order by sql))` which will verify that the database schema is exactly what you expect it to be. The only reason for setting the journal_mode to wal after setting locking_mode normal is that after being in locking_mode exclusive in a non-wal journal_mode, the change does not "become apparent" until the next time the commit machinery runs (so you could `select count(*) from sqlite_master` or any other command in its place).
(21) By HiddenWindshield on 2021-06-24 16:13:13 in reply to 10 [link]
Originally, POSIX didn't specify the O_EXCL flag, using much the same reasoning you're using now. After all, what harm could come from some other process creating a file in between checking if it already exists and actually performing the open? Then, someone (and I can't find the link to the story right now, sorry) demonstrated that this race condition opened up a security hole. They could trick a legitimate program into overwriting a sensitive file that the attacker wouldn't normally have access to. After a lot of back-and-forth, the Gods of POSIX eventually relented and added the O_EXCL flag to the open(2) function. Now, I get that the odds of this actually opening an exploitable security hole are remote. But they aren't zero. And if you pay attention to computer security news, you'll see that "remote chance of a security hole" exploits crop up all the time. It's enough to make any developer (such as myself) just a little bit paranoid about my code. But, the thing is, there are absolutely no downsides to allowing exclusive open whatsoever. So, no matter how small the improvement may or may not be, if it is **an** improvement with no drawbacks, why **wouldn't** you implement it?
It look like this has now been added ([d091150ff80709a1](https://www.sqlite.org/src/info/d091150ff80709a1)), so hopefully the next version of SQLite will include this.
(23) By HiddenWindshield on 2021-06-29 19:55:34 in reply to 22 [link]
Thanks for pointing this out! Also, thanks to the SQLite developers!
I am happy this found its way into SQLite. Thank you Richard Hipp.
That check-in has been backed out. 1. The change only worked for unix, not Windows. Such asymmetry, while occasionally necessary, is unwelcomed. 2. There are corner cases where it does not work on unix either. And those corner cases would be difficult to fix. 3. We don't see a compelling need for this feature (there are better ways to accomplish the same thing) and so we do not want to risk the stability of the project in order to implement it.