Ask HN: How to encrypt data in a SQL database? | Hacker News Reader

Ask HN: How to encrypt data in a SQL database?

22 pointsdeidei27 comments

Hey HN,

I will need to store some sensitive data (API keys, Client Secrets) of my customers in my DB. I was wondering if anyone could help me with these questions.

1. What are the best practices for storing client secrets and API keys in a DB?

2. Should the encryption be handled in the application level (using a library built on top of Node Crypto) or the database level (with pgcrypto)? What are the pros/cons of each option?

3. What are some good encryption libraries in Node.js (I want to avoid rolling my own crypto)?

4. At what frequency should I rotate keys? What are some methods to handle key rotation?

Thanks a lot!

View on HN

Assuming you are using SQLite, an extension exists which will likely do the job: https://www.sqlite.org/see/doc/trunk/www/readme.wiki

Other databases will also have encrypt/decrypt APIs available.

Specific answers to your questions will depend on your application and use cases (local network? global website? credit card data? health data? etc...).

>Specific answers to your questions will depend on your application and use cases (local network? global website? credit card data? health data? etc...)

Thank you so much for the reply! My application is a SaaS that helps developers add integrations (with apps like Slack, Linear etc.) to their apps really quick. The kind of sensitive data is Client Secrets and Access Tokens.

Key management is the interesting part, although it's possible to screw up just in the crypto part as well. Encryption is just a way of substituting the trouble of keeping the key secret vs keeping the data secret.

You're going to match your key management to your threat model.

What are you going to do to keep the key safe in the scenarios of your threat model where an adversary can access the DB contents?

>What are you going to do to keep the key safe in the scenarios of your threat model where an adversary can access the DB contents?

One option that I have been thinking is - 1. When user signs up, generate an encryption key and ask the user to save it securely. (With the warning that in case this key is lost, the user would have to configure the Client Secrets again) 2. Whenever the user makes an API call that involves reading/writing sensitive data, require him to provide the encryption key as well.

Here I won't store any encryption key on the server side and only the user will be able to decrypt the data.

Look into something like HashiCorp's Vault for storing sensitive data.

Trying to roll your own is asking for trouble!

Thanks for the reply. I'm fully aware of the dangers of rolling my own and want to avoid it at all costs. Is using a battle-tested Node.js library or the database's own API for encryption also dangerous?

As I understand it, it's more scaling the company and managing the keys does become an issue if you handle those directly. Imagine explaining this stuff now to a junior dev lol. As for a database's own encryption, I would trust it as much as you trust the organization programming that database.

I manage keys with Azure since we are a Microsoft hybrid house.

That does make sense, thanks. Do you store all the secrets using the Key Vault?

Yep!

No, that's fine. I'd shy away from third party libs though, would probably use what Node.js provides out of the box. Or possibly something in the database, but I've never looked into that. Just don't roll your own crypto _library_, obviously.

This would be an atypical use case for Vault AFAIK. My understanding is it's intended for sensitive config-like data, not sensitive app data.

I was checking out the docs for that and a few other similar solutions too. This is very true. Most of the secret managers are primarily intended for config-like data.

https://www.microfocus.com/en-us/cyberres/data-privacy-prote...

You want to look at acra which intercepts your postgres calls and adds encryption.

https://github.com/cossacklabs/acra

This is exactly what I was looking for! Thank you so much for the help.

It depend on context, for which You asking.

If it to obey laws, this question of regulations of Your country;

for b2b market, each industry have their industry-wide typical use cases;

for internal use other consideration;

for box product other.

Hey, thanks for the reply. My application is a SaaS that helps developers add integrations (with apps like Slack, Linear etc.) to their apps really quick. What is the best way to securely store Client Secrets, Api Keys and Access tokens? Please let me know if you know of any external services too that can help with this.

If you’re using a cloud provider like AWS I’d recommend their dedicated solutions for this problem - for AWS it’s Secrets Manager

It would be better to store all the Client Secrets in such a Secrets Manager right? Or should I create an encryption key per user and store that in the Secrets Manager?

Best practice is to do envelope encryption where you encrypt the data with one key, then you encrypt that key with another (hence envelope). That allows you to routinely rotate the outer key without having to laboriously re-encrypt the actual data.

Whether you use one envelope key or one data key per client, one data key for all, or one envelope key for all is really a judgement call and depends on how paranoid you want to be vs. how much you want to worry about juggling keys.

Personally I don’t go per-user, but if you have the concept of a company/tenant/etc. I might do one for each of those. If you’re storing each tenant in a different database or region it lends itself more towards having a separate key for each as well.

Wouldn’t you have to re-encrypt if the envelope key changes?

Specifically I would think the decrypted data key is derived from the encryption key.

Which DB?

Some DBs have in built support while others require plugins. The answer will depend on your DB and usecases.

I'm using PostgreSQL and it has a built in encryption library (pgcrypto), so I'll probably use that.

Best practice is to NOT store them. At least not on server side.

Thanks for the help. So that two options that I was thinking about -

Option A 1. My user creates an secure (with API keys or some other method) API endpoint to provide the Client secrets when I need them.

2. When my app needs to access the client secrets, I maker an API call to the users endpoint to get the Client Secret.

Option B 1. 1. When user signs up, generate an encryption key and ask the user to save it securely. (With the warning that in case this key is lost, the user would have to configure the Client Secrets again)

2. Whenever the user makes an API call (over HTTPS ofc) that involves reading/writing sensitive data, require him to provide the encryption key as well.

Which one is better?

If I understand correctly, option B makes not much sense. If your user can provide an encryption key, he can as well provide the token or whatever directly.

When does your SaaS initiate the connection to the other services? Autonomous at arbitrary times? Or does the user initiate the connection? In the latter case, your app can store the secrets like Browsers do. In the former case, and when your user can provide an always on endpoint to provide the secrets, your option A seems the best way. If not, you must store the secrets server side, but then you definitely should consult an appropriate security guy to make this as secure as possible.

>If your user can provide an encryption key, he can as well provide the token or whatever directly.

Yeah that makes sense, thanks for pointing that out. I'm just brainstorming at this point and will consult a security person before going to prod. Thanks for your pointers!

Reply on news.ycombinator.com