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

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.

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)

"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