6 ms·
Ask HN: How to encrypt data in a SQL database?
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!
- ianpurton 4y agoYou want to look at acra which intercepts your postgres calls and adds encryption. https://github.com/cossacklabs/acra https://github.com/cossacklabs/acra
- deidei 4y agoThis is exactly what I was looking for! Thank you so much for the help.
- deleted 4y ago[deleted]
- comprev 4y agoLook into something like HashiCorp's Vault for storing sensitive data. Trying to roll your own is asking for trouble!
- deidei 4y agoThanks 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?
- AnEro 4y agoAs 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.
- NoThisIsMe 4y agoNo, 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.
- deleted 4y ago[deleted]
- NoThisIsMe 4y agoThis would be an atypical use case for Vault AFAIK. My understanding is it's intended for sensitive config-like data, not sensitive app data.
- deidei 4y agoI 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.
- ameminator 4y agoAssuming you are using SQLite, an extension exists which will likely do the job: https://www.sqlite.org/see/doc/trunk/www/readme.wiki 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...).
- deidei 4y ago>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.
- simne 4y agoIt 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.
- deidei 4y agoHey, 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.
- goralph 4y agoIf you’re using a cloud provider like AWS I’d recommend their dedicated solutions for this problem - for AWS it’s Secrets Manager
- deidei 4y agoIt 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?
- dev_0 4y ago[dead]
- chrismeller 4y agoBest 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.
- isuckatcoding 4y agoWouldn’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.
- danielmarkbruce 4y agohttps://www.microfocus.com/en-us/cyberres/data-privacy-protection https://www.microfocus.com/en-us/cyberres/data-privacy-prote...
- mharig 4y agoBest practice is to NOT store them. At least not on server side.
- deidei 4y agoThanks 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?
- mharig 4y agoIf 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.
- deidei 4y ago>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!
- _448 4y agoWhich DB? Some DBs have in built support while others require plugins. The answer will depend on your DB and usecases.
- deidei 4y agoI'm using PostgreSQL and it has a built in encryption library (pgcrypto), so I'll probably use that.
- fulafel 4y agoKey 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?
- deidei 4y ago>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.