top of page

Spelunking In SQLite && macOS Keychains

The Apple Keychain is Apple's password management system. Keychain provides a built-in feature on macOS, iOS, and iPadOS devices that securely stores passwords, account numbers, and other confidential information - such as certificates and tokens to interact with various web services. Keychain is most commonly accessed using the Keychain Access app on a Mac, the Passwords System Settings pane on Mac, iPhone, or iPad, from the Passwords pane of the Settings in Safari, or using the security command line tool. The cloud synchronization option, iCloud Keychain, keeps passwords and other secure information updated across devices. All of this is encrypted using the TPM chip on a device, which Apple calls the Secure Enclave.

The Secure Enclave is a dedicated secure subsystem integrated into Apple's systems on chip (SoCs), found in devices like iPhones, iPads, Macs, Apple Watches, and HomePods. It's basically a tiny fortress within your device that's designed to keep your most sensitive data safe and secure, even if the rest of the system is compromised. The Secure Enclave is physically separated from the main processor and has its own dedicated memory. This means that even if malware gains access to the main operating system, it can't reach the Secure Enclave and the information it stores.

The Secure Enclave runs its own secure operating system that's designed to be resistant to attacks. It also uses strong encryption based on technologies like ECC keys to protect all of the data that it stores. Only a few trusted applications, such as Apple Pay and Face ID, are allowed to interact with the Secure Enclave. Third party developers can also use the encryption APIs from Apple to gain access to certain functions the Secure Enclave provides. This helps to ensure that only authorized programs can access sensitive information.

The Secure Enclave also protects other services. For example, secure boot keys are used to verify the authenticity of the operating system that your device is running. The Secure Enclave (and indeed TPM chips on non-Apple devices) are also used to protect passkeys and other biometric data. ECC keys are generated within the Secure Enclave itself, never leaving its protected environment. This prevents exposure to the main operating system or potential malware.

Those ECC keys are then used for signing and verification. The Secure Enclave can generate digital signatures using ECC private keys, ensuring data authenticity and integrity. It can also verify signatures created using the corresponding public key, validating their origin and trustworthiness. The Secure Enclave can also engage in Elliptic Curve Diffie-Hellman (ECDH) key exchange with other devices or services. This provides secure, shared secret keys over untrusted networks, enabling secure communication. This is made possible at the speed required for such large keys using a hardware component called the Public Key Accelerator (PKA), specifically designed for ECC operations with NIST P-256 elliptic curves.

Keychain secures passwords with these keys, but isn't just a password manager. Even if users don’t use the keychain as their password manager, the system uses keychain constantly. For example, most watch extensions use keychain to display data for watch complications. To oversimplify things (which is what we do when we present and write about technology), there are basically two types of keychains. The first is a file-based keychain, which exists on disk. By default, there’s a user keychain, ~/Library/Keychains/login.keychain-db and a system keychain, /Library/Keychains/System.keychain. Thre’s also a data protection keychain, which has more protection. Note that some say login and others say iCloud, as follows.

These are encrypted, with the ARM SoC public key encryption accelerators (described earlier) and/or hardware crypto kits. These files can be copied off-disk, though. To see the keychains for a computer, use the security command with the list—keychains verb, as follows:

security list-keychains

Or add the -d with the word user to refine the search to just user keychains. These are files. Underneath keychain, and most other password managers, is some kind of database. More often than not, that database is SQL - as is the case with keychain.


SQL stands for Structured Query Language. It's a language used to access, manipulate, and manage data stored in relational databases. Think of it as a way to talk to a database and tell it what  to do with the information inside. Developers often use SQL to INSERT data, so add data to a database. This could be as simple as the following:

INSERT INTO secret (domain, email, password)
VALUES (‘’, ‘', ‘my’supersecret password);

The INSERT INTO indicates  to add new data to a table. The secret portion indicates the name of the table to insert the data into. The list of objects in parenthesis indicates to insert the data into those columns of the database (the table could be considered similar to a worksheet or workbook in a spreadsheet and the columns are then similar to columns in a spreadsheet). The VALUES are what goes into the cell for this new row being inserted into the database. The order of the values must match the order of the columns listed earlier.

Other common tasks done with SQL include the following:

  • Retrieve data: Select specific data from tables in your database.

  • Update data: Modify existing data in your database.

  • Delete data: Remove data from your database.

  • Create and manage tables: Define the structure of your data and how it's organized.

  • Combine data from multiple tables: Join related data from different tables to get a more complete picture.

  • Perform complex calculations: Use SQL functions to analyze and summarize your data.

SQL is essential because it's become the standard language for relational databases. This means that SQL as a generalized language can work with most databases, regardless of the specific software used. The most common of these includes MySQL, PostgreSQL, Microsoft SQL Server, Oracle, and SQLite. Each has their own strengths and weaknesses as projects forked and grew and found weaknesses with previous implementations. They all sprang from groundbreaking work done decades ago, though.

The invention of SQL isn't solely credited to one person, but rather attributed to a combined effort from two key figures and the preceding groundwork laid by another. The first was Edgar F. Codd, who didn’t directly develop SQL itself, but laid the theoretical foundation for with his influential paper "A Relational Model of Data for Large Shared Data Banks" published in 1970. This paper introduced the relational database model, which became the basis for SQL and modern database systems. Codd is often referred to as the "father of relational databases" for his crucial role in shaping the way we store and manage data today.

Armed with Codd's relational model, Donald D. Chamberlin and Raymond F. Boyce, two researchers at IBM started the development of SQL. They designed and implemented the language within IBM in the early 1970s, initially calling it SEQUEL (Structured English Query Language). SEQUEL eventually evolved into SQL. It didn’t become the standard at IBM, due to competing projects, but given that it was published in academic works, it got picked up by others, like the founding team at Oracle, and eventually became the standardized language for accessing and manipulating data in relational databases.


D. Richard Hipp was a computer scientist known for his technical expertise and open-source contributions. He designed and implemented SQLite while working for General Dynamics on a contract with the United States Navy in 2000. The initial purpose was to create a lightweight, embedded database engine for a damage-control system aboard guided-missile destroyers. Hipp's vision was to develop a database library that was:

  • Small and efficient: Suitable for resource-constrained environments like embedded systems.

  • Simple and easy to use: Requiring minimal configuration and administration.

  • Reliable and stable: Ensuring data integrity and security.

  • Open-source and free: Accessible to everyone without licensing fees.

And so SQLite was born and relational databases could be embedded into operating systems, offering portable and efficient services that were relational. Relational means that data is organized in tables, columns, and rows, allowing for relationships between different pieces of information. The permissible open source license for SQLite allowed it to be used by nearly every embedded system imaginable. It’s simple, so developers can expect it to be present, compact, so developers don’t need to eat up memory to substantiate objects, and reliable given what are called ACID transactions. ACID stands for:

  • Atomicity: Each transaction is an indivisible unit. Either the entire transaction succeeds or fails completely, ensuring no partial updates occur.

  • Consistency: Transactions bring the database from one valid state to another, preventing inconsistencies in data integrity.

  • Isolation: Concurrent transactions are isolated from each other, preventing conflicts and ensuring reliable data reads and writes.

  • Durability: Once committed, successful transactions are permanently recorded in the database, even if the system crashes.

ACID in SQLite is then implemented as follows:

  • Autocommit: By default, SQLite automatically commits transactions after each statement. This simplifies usage but limits flexibility.

  • Explicit transactions: You can control transactions with commands like BEGIN, COMMIT, and ROLLBACK. This allows for complex operations and handling errors gracefully.

  • Journaling: SQLite uses a write-ahead logging mechanism called "journaling" to ensure durability. Changes are first written to a journal file, guaranteeing data recovery even if a crash occurs before the changes are written to the main database file.

SQLite then became the de facto standard for many mobile apps that needed to store small amounts of data and user settings, for local data in desktop apps, for web browsers to store browsing history and bookmarks, and for tons of embedded systems that store data logs and control data. But SQLite is meant for small amounts of data. It isn't suitable for large-scale, high-performance applications requiring complex data management. Its serverless nature also means it doesn't handle concurrent access from multiple users effectively. Oracle, MySQL, and PostgreSQL are typically better choices for those workflows.

Encrypted SQLite

SQL is then this awesome language to interact with databases and SQLite is this cross-platform implementation that’s simple, reliable, and compact. It’s also got security features through its extensibility. SQLite doesn't offer built-in encryption. However, it provides mechanisms for third-party extensions to encrypt databases. Here's how it typically works:

1. Extension Integration:

  • Choose an extension: Select a suitable SQLite encryption extension like SEE (SQLite Encryption Extension) or SQLCipher.

  • Link the extension: Integrate the extension's library with your application, enabling its encryption capabilities.

2. Encryption Initialization:

  • Set a password: Provide a strong password to protect the database during creation or opening.

  • Key derivation: The extension derives a secure encryption key from the password using algorithms like PBKDF2.

3. Data Encryption:

  • Page-level encryption: The extension encrypts individual pages (data blocks) of the database file using a strong algorithm like AES-256.

  • Ciphertext storage: The encrypted data is stored in the database file, making it unreadable without the password.

4. Access Control:

  • Password-based authentication: Accessing the database requires providing the correct password.

  • Key retrieval: Upon successful authentication, the encryption key is retrieved to decrypt the data for authorized use.

5. Decryption on Access:

  • Transparent decryption: The extension automatically decrypts pages as needed when data is read.

  • Secure access: Data is only accessible in its decrypted form within the application's memory.

6. Journal Encryption (optional):

  • Journal file protection: Some extensions also encrypt the journal file to safeguard transaction logs and prevent data recovery attempts.

So there’s no default encryption. The specific algorithms and features each developer uses depends on the chosen extension. Given that encryption can impact performance, the extensions often optimize for efficiency over key lengths. Developers also have to choose a well-maintained extension with a proven track record and update it when new versions of open source tools are released. And they still rely on a password. While encryption adds a layer of protection, careful handling of passwords and database access is crucial. Weak passwords or keys happen, so SQLite encryption  is a valuable tool, but it's not a substitute for other security practices - especially a layered security approach.

Breaking Into SQLite

SQLite databases offer convenience and portability, but their added-on encryption isn't impenetrable. A false sense of security can be alluring, especially for sensitive data. As mentioned, SQLite encryption relies on extensions like SQLCipher, adding a layer of protection to database files. Data is scrambled using algorithms like AES before being written to disk, rendering it unreadable without the correct decryption key. However, this security shield isn't flawless. The main ways attackers normally approach breaking into SQLite include the following:

  • Weak passwords: The Achilles' heel of any encryption system is the key itself. A weak password, easily guessed or brute-forced, provides swift access to the decrypted database. Further, when there’s a keypair and a password, there are now two vectors of attack.

  • Side-channel attacks: These exploit hardware or software leaks to glean information about the encryption process. Timing analysis of decryption operations, for example, can reveal key bits, gradually unlocking the database.

  • Vulnerable implementations: Not all encryption extensions are created equal. Bugs or flaws in the encryption code can be exploited to bypass the security altogether.

  • Social engineering: Tricking users into revealing the password or installing malware that steals it remains a potent avenue for attackers.

There are plenty of tools attackers use in the process. One is dictionary attacks, or pre-computed lists of common passwords and phrases can be tested against the extracted key hash. Another is rainbow tables, or pre-calculated tables that map plaintext passwords to their corresponding hashes, significantly speeding up the cracking process. Another more recent technique is GPU cracking, which leverages the immense parallel processing power of modern graphics cards to make brute-force attacks much faster.

Encryption is  then a powerful tool, but not a silver bullet. A layered approach to security, encompassing strong passwords, vigilant monitoring, and continuous updates, is crucial for keeping an encrypted SQLite databases safe from prying eyes. Those are a lot of assumptions to make about the back-end storage mechanism that all passwords are stored behind with practically every password manager on the market. Most will have a file that ends with .db or .sqlite. Some might even change the extension. There are also .db-shm for shared memory files and .db-wal for journalling.

WAL mode WAL mode, or Write-Ahead Logging mode, is a journaling mechanism in SQLite3 that enhances performance and concurrency, especially for write-intensive workloads. The actual -wal file is a Write-Ahead Log (WAL) file. Instead of directly modifying the main database file, changes are first written to a separate WAL file. This file acts as a checkpoint, tracking pending transactions and updates. Periodically, SQLite flushes the contents of the WAL file into the main database file. This process, called a checkpoint, ensures updates are eventually persisted to the main database. WAL files are another place where the encryption mechanism can be attacked out-of-band.

This offers faster writes, as writes to the WAL file are usually faster than direct writes to the database file, improving overall write performance. Multiple connections can read from the database concurrently while one or more connections are writing to the WAL, reducing contention. If a crash occurs, SQLite can replay the WAL file to recover any uncommitted transactions, minimizing data loss. WAL mode is enabled for SQLite3 with the PRAGMA journal_mode=WAL command. To then view all PRAGMA settings, simply run PRAGMA; while connected to a database using the sqlite3 command. Most password managers aren’t going to be write-heavy applications, nor multi-user, and so concerned about concurrency. So the reason, for example, that Apple’s Keychain use WALis to mitigate data loss in the case of a power failure or other interrupted process. Having said this, again, Keychain does a lot more than most people think.

Keychain: Beyond Encrypted SQLite3

I’ve often oversimplified statements I’ve made when I say something like "Keychain uses an encrypted SQLite3 database." Keychain isn’t just encrypted SQLite databases because the engineers have learned and read about attacks, like the brute force attacks mentioned earlier. Armed with the information thus far, let’s explore the actual Keychain database. There are a few, so let’s focus on /Library/Keychains/system-keychain-2.db for now. First, let's see if it is indeed SQLite3. To do so, let's just run a generic cat of the file:

cat /Library/Keychains/system-keychain-2.db

Like a good old shebang line, the first line makes the file type clear.

To open it from the sqlite3 command line, first sudo (or it will fail), and simply invoke sqlite3 with the database path as the operator, as follows:

sudo sqlite3 /Library/Keychains/system-keychain-2.db

Notice there’s a prompt for a password. As mentioned, the first layer of security is an encrypted database. But there’s more layers of security. Now run .tables, as follows:


This is a good idea when first looking at any database, as it provides a bit of the lay of the land. It’s also possible to go spelunking using a graphical interface SQL app, such as SQLiteStudio as here. But anyone looking will quickly find that information about secrets is not readily available. That will look like this.

There are plenty of tables to sort through. The syncKeys table, for example, is as it sounds, and the columns types, in the ckzone are mostly self-explanatory. However, there are a couple that aren’t entirely, including manatee for iCloud Keycahin and engram for ApplePay checkins back to Apple. One table that’s pretty insightful, though, is tlkshare, which includes the SHA256 of sender and receiver peers, as well as wrapped keys and wrapped signatures. But here’s the thing, in general, there’s interesting information that can provide a slightly deeper understanding, but it’s all encrypted on a per-row basis. Look into the inet table and note that there’s a sha1 column, which is how humpty dumpty gets put back together when you hit the button to show password. It’s not a bad system. But, it assumes that the actual password is strong and never leaked.  And the keychain files don’t store passkeys, which is the future of secret storage - those are stored in the data protection keychain mentioned earlier.

Oh, keychain has ACLs, so each entry can have a list of applications and what they can do, a null value (so any app can do stuff), an empty list (thus no app can do stuff, or an array of objects (thus a list of apps that can do stuff). 

But, as seen in the acct column of the database using a SQL browsing tool, that too isn’t easy to read. Again, encryption. But some data comes through just fine. To use sqlite3 from the command line, connect as with the previous example, and then look at the raw table inet data using the following select statement:


This still isn’t exactly human readable, because it isn’t meant to be. There are ways to get at this data, it’s just not easy. One way is using the security command. From the macOS command line, that’s as simple as the following:

security dump-keychain -a -d

In the above incantation, the dump-keychain verb tells the interpreter to dump the db, the -a modifier tells it to dump everything, and the -d tells it to decrypt when possible. If the keychain is unlocked it shouldn’t prompt for passwords a thousand times.

There were a couple of issues with that last statement for those looking at Keychain offensively. The first is that the password is hashed - that’s where chainbreaker comes into the picture. There are a few options here. One is, if the keychain is unlocked, or if there’s a password available, that’s another way to get at especially generic passwords. To download chainbreaker, check it out on github:

If the keychain password is available, it’s as simple as the following to run:

python3 -m chainbreaker -pa /Users/charlesedge/Library/Keychains/login.keychain-db

This produces a dump that contains each of the following assets on a device:

It's also available for creating some secret in an app and seeing if the count of objects enumerated goes up, thus getting pretty quick telemetry into operations running on a host:

If the keychain password isn't available, they will show as locked in the output, on a per-record basis.

When a keychain password isn't available, consider dumping the hash of the password and going after that:

python3 -m chainbreaker --dump-keychain-password-hash /Users/charlesedge/Library/Keychains/login.keychain-db

That can be time consuming, but also if there’s a password and a keychain db then there’s a pretty darn good chance that there will be valuable secrets still unchanged (and so unchained) once the thing is decrypted. There’s also plenty of great information in the metadata of keychain objects. Cody Thomas did a great talk at Objective By The Sea on this, looking at amongst other things how to go after Slack and Microsoft services or API keys/JWTs. That talk is available at and I feel like everyone should watch it. It’s really a fantastic talk, as are all the OBTS talks. Very inspire.

Thomas also goes into details on using APIs rather than CLI tools or attacking databases directly. He released a tool there called LockSmith, which everyone in technology should tinkerate with, available at: It’s worth mentioning that some keychain data classes behave differently when it comes to when they are locked or unlocked, as defined at

What’s Next

So here we are, where Apple is able to hide secrets better than practically anyone else in the industry, for tools that are free with the operating system. Security researchers can abuse APIs, go after various pieces of data, and subvert controls in ways that Apple is then able to quickly patch. There will always be holes found - that's just computing. But still far more secure than re-using passwords across websites.

In most cases, they don’t get access to the specific targets Apple has defined as high value (the $100k per flaw ones), as Cedric pointed out at a different OBTS talk. So $5k bounties aren’t a great reason to go after keychain when it can take a long time to find a new exploit. But at the end of the day, Apple built their tool on top of SQLite, as did most every third party password manager. This allows them to go deeper because they control the OS. That’s the way it’s always been, but these days they do a really, really good job at being not only intentional, but deep in their thinking. It’s like Thales of Miletus is a security architect out there. Also - very inspire!

Having said all that, it’s the job of security researchers to try and dig even deeper. To look at every angle that the engineers at Apple couldn’t have thought to look at - because diversity of thought is a thing. And diversity of experience is a thing. It’s like a great boxing match, where it’s Apple engineers vs the rest of the world - and someone eventually makes a George Forman grill. Which by the way, was like the best grill ever. Well, at least until the smart Traeger grills started to ship, like this one - (although they’re like 20-30x the cost of a George Forman, but getting off topic here).

So the next step in digging into keychain is to read Here, a perceptive reader will notice the inclusion of the word keybag. That hasn’t come up yet. To quote Apple: “A data structure used to store a collection of class keys. Each type (user, device, system, backup, escrow, or iCloud Backup) has the same format. A header containing: Version (set to four in iOS 12 or later), Type (system, backup, escrow, or iCloud Backup), Keybag UUID, an HMAC if the keybag is signed, and the method used for wrapping the class keys—tangling with the UID or PBKDF2, along with the salt and iteration count. A list of class keys: Key UUID, Class (which file or Keychain Data Protection class), wrapping type (UID-derived key only; UID-derived key and passcode-derived key), wrapped class key, and a public key for asymmetric classes.”

More importantly, mapping the objects in the inet table has somewhat already been done, and is available on github at To then see how the actual table entries match to attributes, check out

The earlier screenshots from the sqlite db might trigger a memory about keybags. But keep in mind that those are just reflecting the backup of keybags, and any CLI changes to accounts (e.g. through sysadminctl or Jamf’s LAPS would break the link between accounts and their keybags and so also break the ability to have an account login through a TPM (FaceID/TouchID interface). Ergo, not great for use with regular users. So let’s not focus on any of that. Those are changes better made via an invocation of /System/Library/PreferencePanes/Profiles.prefPane. But that’s aside from the point.

Now, the local keychains might be considered a low value target these days. As Thomas discussed in his talk, a stray key or JWT or other token might be found that can provide access to a specific app or online service. Notice that Chainbreaker is fairly adept at finding base64 encoded passwords and the such - even when a keychain is locked, as some vendors think of keychain metadata as a place to store objects that don't fit neatly into other places.

But the higher value target, and the place where there’s likely to be far more research in the future is the iCloud Keychain. Why’s that? Notice how more of those are stored in iCloud keychain now? Let’s talk about that. Run this command:

system_profiler SPHardwareDataType | grep 'Hardware UUID' | awk '{print $3}'

Now go to the ~/Library/Keychains folder and open the folder named off that key. This is where the WAL file described earlier lives. There are tools that can replay the journalled entries in the WAL - especially if the original database schema is known. One would be something like That database reconstructed from journal entries, it turns out, is encrypted as well. There are still plenty of places to poke around. For example, keychain access control, or at least the ACLs that can be used on a keychain item. Those are evaluated in the secure enclave but set using the agrp and pdmn columns in the SQLite database. Then there’s just straight trying to brute force things, which I initially posted a project to work on about 3 years ago: But it seems easier to just use a camera and copy the PIN someone uses on their phone than all that. Which is what was in the news and why new protections keep coming to keep not only the data, but the human who has the data, safe. In other words, after all these words - it’s still all about the password (and the occasional mis-use of APIs).

All of this is why Secret Chest chose to use keychain as a database. It turns out sqlite is great at storing json. It’s also fast and items can be marked as non-syncing. So we can put shards into the keychain database and build on top of the amazing security work done by Apple. We get a lot for free that way! And yet, given that data is sharded, even if a local keychain database is brute forced, the data in keycains on other devices would be needed as well - and not data synchronized through iCloud Keychain. Secret Chest looks a lot like Keychain, because Apple also provides solid APIs for autofill extensions, etc.

427 views0 comments

Recent Posts

See All


bottom of page