What survives a pg_dump

Every managed database console has a checkbox called encryption at rest, and every compliance questionnaire asks whether it is ticked. It should be. It also defends against one specific event, someone walking off with the physical disk or a raw volume snapshot, and that is not how databases are breached. They are breached by someone who obtains the application's own connection string and runs pg_dump, which returns every row in plain text because, to the database, that person is the application. The useful question for each column is therefore not "is the disk encrypted" but "what does this column look like in that dump". I call it the dump test, and it sorts every column into one of three layers.

The credential is the attack surface

The reason the dump is the realistic threat is that application credentials leak constantly. GitGuardian's 2026 report counted 28.65 million new hardcoded secrets pushed to public GitHub in 2025, up 34 percent on the year, and the previous year's report found that the single most commonly leaked secret type in public repositories was a MongoDB connection string, at 18.8 percent, with 70 percent of the secrets leaked in 2022 still valid when checked. A connection string in a commit, a config file in a container image, an environment variable in a crash report: each is a full-access credential, and none of them involves a disk.

Three layers, three attackers

Encryption protects data against a particular attacker, and the layers line up against the attackers in a matrix that is worth drawing once. Volume encryption, the checkbox, defends against the stolen disk and nothing else: the database decrypts the volume on boot, so anyone who can talk to the database sees plaintext. Application-layer encryption, where the application encrypts a column's value before writing it and the key lives in a key management service outside the database, defends against the stolen disk and against the stolen credential, because the dump contains ciphertext and the key is not in the database. It does not defend against an attacker who has taken over the application server, because that server can ask the key service for the key. Only data that is never stored, or that is encrypted by the client before it reaches the server, survives that third attacker.

The matrix explains why "encrypted at rest" on a provider's console changes nothing in the middle column. It also explains the cost of the second row. Application-layer encryption means the database cannot index, sort or search the column, every read pays a round trip to the key service or a cached data key, and key rotation becomes the application's job. That cost is worth paying for some columns and absurd for others, and the dump test is how to tell which.

The dump test

Take the schema, imagine the output of pg_dump run with the application's connection string in a stranger's hands, and mark each column with one of three outcomes. Readable: the value is plaintext in the dump, and that is acceptable, because the value is not sensitive or because the application needs to query it in ways encryption forbids. Unreadable: the value must be ciphertext in the dump, encrypted in the application with a key the database never holds. Not there: the value should not be in the database at all, because a version of it that is useless when unreadable exists, a hash, a token from a payment processor, a reference to a store built for that data.

Run on a CRM schema of the kind I build, with pipelines, campaigns and service tickets in PostgreSQL, the test sorts most columns into the first box, and that is the point that makes the second box affordable. Names, companies, stages and amounts need to be searched, sorted and summed, so they stay readable and the disk checkbox is their only protection, which is honest: the dump reveals a customer list, and a customer list is what a CRM is. The columns that go into the second box are the ones where the dump would reveal something the customer list does not: the bodies of support tickets, free-text notes, call transcripts, and the refresh tokens for connected mailboxes, which are credentials to a different system and whose exposure is a second breach. Phone numbers sit at the boundary; they can go in the second box with a keyed hash alongside for exact-match lookup, which is a fair trade for losing prefix search.

The third box is the one people forget exists. Card numbers do not belong in the schema at all, because the payment processor will hold them and hand back a token that is useless in a dump. Passwords are stored as a slow hash, never encrypted, because a hash cannot be decrypted by anyone, including the application. Uploaded identity documents belong in an object store with their own access control, with the database holding only a reference. For each of these the question is not how to protect the column but why the column exists.

The dump is also the backup

The same command that models the attacker runs every night as the backup, and that is the second reason the test is the right lens. Whatever the dump test says a column looks like is also what it looks like in every backup, in the object storage bucket the backups are copied to, in the read replica, and in the nightly export to the analytics warehouse where the data science team has a broader set of credentials than the application does. Volume encryption on the primary says nothing about any of those copies. Application-layer encryption travels with the data: a column that is ciphertext in the table is ciphertext in the backup and in the warehouse, and the key service's access log is the one place that records who ever turned it back into text.

That property has a price that has to be paid up front. If the key is lost, every copy of the column is lost with it, including the backups that were supposed to be the safety net, so the key service's own durability and the procedure for escrowing its keys become part of the database's backup plan. And the warehouse loses the column entirely unless a deliberate decision is made to decrypt on the way in, which is a decision worth making in writing, because a warehouse that holds readable ticket bodies has the same dump test to pass as the database did and usually a wider audience.

Running it

The test takes an afternoon for a schema of a hundred tables, and its output is a list of columns for the second box, each with a note on which queries it breaks. That list is the actual work: adding an encryption step in the data access layer, choosing an envelope encryption scheme so that the key service signs data keys rather than every row, deciding how rotation happens, and replacing the broken queries with hashed lookups or application-side filtering. Everything not on the list stays as it is, and the disk checkbox stays ticked.

What the test refuses to do is let a console setting stand in for a decision. The setting answers the stolen-disk column of the matrix. The dump is the other column, it is the one the leaked-secret numbers describe, and the only thing that changes what it contains is deciding, column by column, what should be in it.