Handling of temporary SQLite files regarding encryption

[utelle]

Recently there was a discussion about encrypting temporary SQLite files (see issue #148). Currently, SQLite3 Multiple Ciphers uses the compile time option SQLITE_TEMP_STORE to select that temporary data are always stored in memory, because storing unencrypted temporary data in files creates a security risk for encrypted databases.

However, in principle this option can be overwritten by the user, for example if an application has memory restrictions which make holding temporary data in memory unfeasible. In such use cases, encrypting temporary data could be mandatory to prevent breaching secret data.

The encryption extension based on the officially unsupported and undocumented SQLITE_HAS_CODEC compile-time option (which was used until its removal in February 2020) did not support to encrypt temporary data. However, the new implementation of the encryption extension would allow to reconsider the encryption of temporary data.

So far, users of SQLite3 Multiple Ciphers have not explicitly asked for an option to encrypt temporary data, but adding support for encrypting temporary data could still be a useful enhancement.

Please take the time to vote for the option most suitable for your own use cases, and share your opinion about the topic in general.

[Willena]

Hello,

What would happen for unencrypted database in case of option 2 ("Encrypt temporary files unconditionally") ? If temporary files are always encrypted, what will be the key and algorithm in case of plain database ?

[utelle]

What would happen for unencrypted database in case of option 2 ("Encrypt temporary files unconditionally") ? If temporary files are always encrypted, what will be the key and algorithm in case of plain database ?

Since temporary files usually exist only for a limited, often short period of time (they are deleted on closing) a random key can be used. The default cipher scheme would be used for the purpose, but this could be made configurable.

In this scenario temporary files would be encrypted independently of whether the main database is encrypted or not.

[Willena]

Thanks for the clarification.
If I understand correctly, it means with options 2, we will still be able to choose to store temporary data in an external file or in-memory (keeping SQLITE_TEMP_STORE actual behavior). The only difference being that those temporary data will always be encrypted independently of the actual storage method/backend.

[utelle]

If I understand correctly, it means with options 2, we will still be able to choose to store temporary data in an external file or in-memory (keeping SQLITE_TEMP_STORE actual behavior).

Yes. The current behaviour (keeping temporary data in memory) will not change - unless the symbol SQLITE_TEMP_STORE is set to 0 or 1, or PRAGMA temp_store=FILE; is used.

The only difference being that those temporary data will always be encrypted independently of the actual storage method/backend.

If temporary data are written to disk and the sqlite3mc VFS shim is used, those data will be encrypted for option 2 (independently of whether encryption for the main database is enabled or not).

IF I add the feature to encrypt temporary data on disk, I intend to make it configurable, so that it can be turned on or off, depending on actual needs.

However, seeing not a single vote for any option yet, gives me the impression that supporting encryption for temporary data on disk is not an important issue for most users.

[Willena]

It's clear now. To be honest, this is not an important feature for me. I was just trying to anticipate possible breaking changes.

[utelle]

To be honest, this is not an important feature for me.

Neither for me, but it may be different for other users/developers.

I was just trying to anticipate possible breaking changes.

I will do my very best to avoid breaking changes.