Home » Articles » Server Design » Adding Transparent Data Encryption (TDE) to SQlite

Adding Transparent Data Encryption (TDE) to SQlite

By Mark Joseph - September 2, 2022 @ 3:17 pm

This document was updated on 4 November 2022

SQlite stores a database in a single file on disk. This single file is broken up into pages of around 4k bytes (but the size can be changed). Each page has 3 sections: header, data, and footer. The data section contains a database’s information. SQlite source code already has hooks, in the pager.c code file, to support page encryption (i.e., to encrypt the data section of each page). We took the SQlite design and extended it to use AES GCM (256 bit key) and ChaCha20Poly1305 authenticated encryption ciphers thus handling encryption and signing with a single key. P6R has packaged this work into a new standalone product
SQLiteTDE.

Page encryption is transparent to an application (i.e., decryption occurs as the page is read from file, and encryption is performed as the page is written to disk), and encrypts all data in the database. In addition to page encryption an application can also add field level encryption of selected columns in a defined schema.

P6R has implemented page encryption as follows. Each time a page is to be authenticated encrypted we generate a new Initialization Vector (IV). We take the IV and page number concatenated as the input to a cipher’s associated data in generating an authentication tag. Next we store the IV and authentication tag in a page’s non-encrypted footer. On decryption, we use the IV and authentication tag from the page’s footer to perform the authenticated decryption. The use of the ChaCha20Poly1305 cipher requires the use of OpenSSL 1.1.x or greater.

Some other designs that have implemented page encryption in SQlite use two keys: one for encryption and an HMAC for signing a page’s contents. Our approach is simpler (and more secure) as it only requires a single key and a single cryptographic operation per page.

P6R uses SQlite to implement our Keystore component which is used in several of our products. For example, we have incorporated a Keystore with TDE into our KMIP Client SDK for a managed object cache (e.g., keys). This provides our customers with 2 levels of encryption: field level encryption of key material implemented in the Keystore component, and page encryption in the SQlite database used to implement the Keystore.

Another difference between the P6R design and other extensions to SQlite is that our implementation does not generate nor store the key used for authenticated encryption. Instead, we have added a new API function where an application can set the key which it has generated and maintains outside of the database (e.g., on a key server, an external drive)

P6R’s SQLite Extended API

Below is the list of P6R added API calls, which provide page encryption functionality, to the standard SQLite code base.

[1] Set the page encryption key each time a database is opened. 
This function should be called first thing after the call to sqlite3_open_v2().

int sqlite3_p6r_setkeys(
  sqlite3 *db,                   /* Database to be encrypted */
  const char *pDbName,           /* Name of the database */
  const unsigned char *pKey,     /* Encryption Key bytes */
  unsigned int nKeySize,         /* key size in bytes, must be 32 */
  unsigned int encryptFlags      /* mostly control logging: trace, error */
);


[2] Re-key the entire database with a new key and possibly a different cipher.
This function should be called first thing after the call to sqlite3_open_v2()
and then sqlite3_close() afterwards.  All clear data appears only in memory.
Nothing is written unencrypted to the disk.

int sqlite3_p6r_rekeydb(
  sqlite3 *db,
  const char *pDbName,           /* Name of the database */
  const unsigned char *pOldKey,  /* Encryption Key bytes currently in use */
  unsigned int nOldSize,         /* old key size in bytes, must be 32 */
  unsigned int oldCipher,        /* One of the P6RCIPHER constants */
  const unsigned char *pNewKey,  /* Encryption Key bytes to rekey with */
  unsigned int nNewSize,         /* new key size in bytes, must be 32 */
  unsigned int newCipher,        /* One of the P6RCIPHER constants */
  unsigned int encryptFlags      /* mostly control logging: trace, error */
);


[3] Decrypt a previously SQLite3 P6R encrypted database.
This function should be called first thing after the call to sqlite3_open_v2()
and then sqlite3_close() afterwards.

int sqlite3_p6r_decryptdb(
  sqlite3 *db,                   /* Database to be decrypted */
  const char *pDbName,           /* Name of the database */
  const unsigned char *pKey,     /* Encryption Key bytes */
  unsigned int nKeySize,         /* key size in bytes, must be 32 */
  unsigned int encryptFlags      /* mostly control logging: trace, error */
);


[4] Re-encrypt a database that was previously decrypted by a call to 
sqlite3_p6r_decryptdb().  This function should be called first thing after the 
call to sqlite3_open_v2() and then sqlite3_close() afterwards.

int sqlite3_p6r_encryptdb(
  sqlite3 *db,                   /* Database to be encrypted */
  const char *pDbName,           /* Name of the database */
  const unsigned char *pKey,     /* Encryption Key bytes */
  unsigned int nKeySize,         /* key size in bytes, must be 32 */
  unsigned int encryptFlags      /* mostly control logging: trace, error */
);

"Adding Transparent Data Encryption (TDE) to SQlite" was published on September 2nd, 2022 and is listed in Server Design.

Follow comments via the RSS Feed | Leave a comment | Trackback URL


Leave Your Comment