[local-store] SQLite error when executing operations on read-only local store

Describe the bug

I am trying to use Nix in a sandboxed, incremental build environment where the Nix store needs to be built in a local path, and then consumed by downstream build targets, which are granted only read-only filesystem access to the pre-built Nix store.

When I try to execute a nix run action on the prebuilt (read-only) Nix store, even with the read-only store option set:

nix run --store "/path/to/local/store?read-only=true" path:/nix/store/...-my-in-store-flake#packageName

I get an error indicating that SQLite tried and failed to create an INSERT statement into the ValidPaths table, claiming the table does not exist:

error: creating statement 'insert into ValidPaths (path, hash, registrationTime, deriver, narSize, ultimate, sigs, ca) values (?, ?, ?, ?, ?, ?, ?, ?);': SQL logic error, no such table: ValidPaths (in '/path/to/local/store/nix/var/nix/db/db.sqlite')

However, as shown below, if I execute the SQLite command-line tool directly inside the sandbox environment, that table does exist, SQLite only thinks it doesn't because the database file is read-only.

Looking at the code, I notice that at https://github.com/NixOS/nix/blob/master/src/libstore/local-store.cc#L353, the SQL prepare statements are unconditionally run, even if config->readOnly is true. This may need to be changed so the statements that can modify the database are only prepared if !config->readOnly.

Steps To Reproduce

I was able to reproduce this without using a complex build environment using Podman:

1. Create a local Nix store with a prebuilt package.

$ nix build --store ./. nixpkgs#hello

1. Create a Podman container with the local Nix store mounted read-only.

$ podman run -it -v$PWD:/local_store:ro nixos/nix

1. In the container, try to run the binary we built.

$ nix \
    --extra-experimental-features nix-command \
    --extra-experimental-features read-only-local-store \
     run \
     --store "/local_store?read-only=true" \
     nixpkgs#hello

error: creating statement 'insert into ValidPaths (path, hash, registrationTime, deriver, narSize, ultimate, sigs, ca) values (?, ?, ?, ?, ?, ?, ?, ?);': SQL logic error, no such table: ValidPaths (in '/local_store/nix/var/nix/db/db.sqlite')

1. [Optional] Demonstrate that the ValidPaths table really does exist in the reported path.

$ nix-shell -p sqlite
$ sqlite3 /local_store/nix/var/nix/db/db.sqlite
sqlite> SELECT * FROM ValidPaths LIMIT 3;
1|/nix/store/hvj4i7y8y2ybjwdwmaj1v55j6n45rvck-source|sha256:3170fd8fffb4cd7fdc58fe21e03b8c292317561e7dc37742781c4be2016ca722|1789527006||223076736|||fixed:r:sha256:08m7dh0y4jqwg117ghvx3rb1f8r9ihxy08gyb3f7zkdlzy7zsw1i
2|/nix/store/l622p70vy8k5sh7y5wizi5f2mic6ynpg-source-stdenv.sh|sha256:5ed6e4a8ba460fe2cad21d3545eae24c6e8055fcfdf09071f813451a6f9e54cd|1789527006||216|||fixed:r:sha256:1kalkrpili8kz1qr1w7xziaq0vjcwbm4ad8xsb5f43s6palf9mjy
3|/nix/store/shkw4qm9qcw5sc5n1k5jznc83ny02r39-default-builder.sh|sha256:8388b2964cf255ccd6dfe853647927b7a44d3bd583175f932b9955c54d866239|1789527006||128|||fixed:r:sha256:0fb2hr6wamcr5f9my5w3slxlv95p4xwn8lz8vzbcqmgj9jbb5243

Expected behavior

Running the binary from inside the container succeeds.

Note: to keep the example simple, I used plain nixpkgs to reference the package, but in reality this would try to write the flake to the Nix store on every nix run invocation, which would fail on a read-only file system. This can be avoided by preloading the flake to the Nix store at build-time, i.e. with nix flake archive. Even without doing this, the SQLite error happens to occur first.

Metadata

nix-env (Nix) 2.34.7

Additional context

N/A

Checklist

• checked latest Nix manual (source)
• checked open bug issues and pull requests for possible duplicates
• reviewed the contributing guide

Add :+1: to issues you find important.