I need to store sensitive data in a particular column in my database (Postgres). I'm hosting with AWS, so I'm planning on using KMS to use envelope encryption. For example:

  1. To encrypt a secret:

    1. Generate a 256-bit plaintext data key.
    2. Generate a 256-bit random IV.
    3. Encrypt the secret with AES-256-CBC using the plaintext data key, and the IV.
    4. Using KMS, encrypt the data key.
    5. Throw away the plaintext data key.
    6. Encode the IV, the ciphertext secret, and the ciphertext data key in the column.
  2. To decrypt a payload from the column:

    1. Decode the IV, ciphertext secret, and the ciphertext data key from the column.
    2. Using KMS, decrypt the ciphertext data key.
    3. Decrypt the ciphertext secret with AES-256-CBC using the plaintext data key, and the IV.

With this in place, should I encrypt-then-MAC the message? I found this answer about using a MAC with a HSM, but I'm not sure if it still applies in the case of data stored in a database:

For checking data integrity, isn't this already covered by our database? For checking authenticity, we are the only ones who consume the ciphertext. For example, we do not exchange the ciphertext with a client where it could be modified.

Is applying a MAC to the encrypted data unnecessary overhead?

  • 2
    $\begingroup$ Authenticated encryption also defends against active attacks. The likelihood for that is rather low if the data comes from your system table, but why risk it: use AES-GCM or AESWrap. If you want to stick with CBC, then a HMAC on the ciphertext is the normal method. $\endgroup$
    – eckes
    Commented May 2, 2017 at 17:21
  • $\begingroup$ @eckes I'd prefer to use AES-GCM but the environment I'm using (.NET 4.5.1) does not have it built-in. Open source implementations exist, but I'm not sure if they've had independent code reviews done. Can you expand on what could happen in an "active attack"? Thanks! $\endgroup$ Commented May 2, 2017 at 17:28
  • $\begingroup$ Okay, there seems to be some add-ones to support it: blogs.msdn.microsoft.com/shawnfa/2009/03/17/… not sure if you can use that. But if not HMAC looks like a good option. You can even consider truncating the tag (like 12 bytes) as you are not on a very exposed interface. Alternatively you could use a trick like the MIC in pgp, it's basically an encrypted checksum. However I am not sure how much protection you can gain from that. $\endgroup$
    – eckes
    Commented May 2, 2017 at 17:43
  • $\begingroup$ @eckes Could you expand on what could happen in an "active attack" without a MAC? $\endgroup$ Commented May 2, 2017 at 18:17
  • $\begingroup$ The main problem is that attackers can learn from the reaction of your software to modified ciphertext about the keys used. $\endgroup$
    – eckes
    Commented May 2, 2017 at 18:29

2 Answers 2


Ideally yes, encrypted data in a database should be authenticated as well, along with the context in which the encrypted data appears as associated data—some subset of plaintext values like (not exhaustive):

  • A database instance identifier;
  • The database, schema, table and column names in which the encrypted data is stored;
  • The column names and values of unencrypted columns in the same row as the encrypted value.

The reason is that this protects against a privileged user—say, a malicious DBA—from cutting and pasting values around to change the interpretation that a client application will give to the data. E.g., Eve the malicious DBA could take encrypted credit card numbers in the database and swap them in order to cause the system to charge the wrong person for things.

If you don't authenticate the values, or even if you just authenticate them minimally, with no associated data, you expose yourself to such attacks. This is also true if you use too little associated data—for example, if you just use the row primary key as associated data, a malicious DBA might still be able to carry out the same attack by swapping everything else other than the ciphertext and primary key.

There are big downsides here, however, which is that the more thoroughly you authenticate the ciphertexts and their contexts, the harder you make it for honest users to work with the database unless you hand them the master key as well—which is of course very undesirable. For example:

  1. Suppose you include all the unencrypted columns in the same row as associated data for the ciphertext's authentication tag. Well, now any non-identity update to the row's plaintext columns will cause the decryption to fail unless it also decrypts, reencrypts and authenticates the encrypted columns anew. For insert-only databases this might not be a big deal, but if your application is heavy on updating existing rows then it can easily be a deal-breaker.
  2. A very common operation in relational database management is to create custom views (logical or physical) that present a subset of the data from one or many tables to some user or application. If some such views present them with encrypted data that they may want to decrypt, using associated data with the authentication can make things harder or impossible:
    • If table names are used as associated data, the decryptors must keep track of which column in the view came from which base table in order to decrypt it. This defeats the data abstraction that views are supposed to afford their users.
    • If the views available to some user or application allow them to see a ciphertext column but not all of the columns that provide its associated data, the user won't be able to reliably decrypt the ciphertext.

So these are very serious obstacles that make extensive use of associated data a tricky proposition for databases.

  • $\begingroup$ I agree with your points. So, as with most things, it's a balance between security and usability. I think that's a heck of a lot of extra overhead just to encrypt the data in the database. Maybe the question is: how much security do we need for these columns (what is "good enough")? And maybe only I can answer the question since it's very specific to the data we want to store (OAuth tokens). If we're not storing associated context because it's a bad experience trying to read the data, is it still worth it to have authentication at all? $\endgroup$ Commented May 2, 2017 at 22:44

Without HMAC an attacker could change your data and you wouldn't know it. For example, the attacker could swap the content of your encrypted column with that from another record - under your scheme the fake data will be perfectly valid. By adding another HMAC column that factors the record id (which I assume is unique) you will be able to tell if anyone fiddled with your data. If, however, you are not concerned of any adversary fiddling with your data then this will obviously be redundant.

  • $\begingroup$ If an attacker has write-access to our database, they could change all the data without us known (including unencrypted columns). So, that'd be much worse. Also, if they get access to KMS (e.g. via our web tier), they could encrypt+MAC their own values that would look "valid". So, I'm still not sure how a MAC helps in this situation. $\endgroup$ Commented May 2, 2017 at 18:15
  • 4
    $\begingroup$ If the KMS is compromised then an HMAC indeed won't help. But otherwise an HMAC will reveal unauthorized changes to the encrypted data. The recourse depends on your application - you could e.g. recover data from elsewhere. I'm not saying that this is something you should do, you're the one to tell if this attack vector is realistic. I only answered your question in sense of describing a situation where an HMAC can add protection that lacks in the scheme you described. Is this, as you put it, unnecessary overhead? You are the only one who can answer this question. $\endgroup$
    – stromboli
    Commented May 2, 2017 at 18:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.