Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

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

news.ycombinator.com

Post reply on HN
Print view View original

21–30 of 31 posts

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

#21

Post by isuckatcoding »

chrismeller wrote: post #15
deidei wrote: post #13

Earlier quoted context omitted.

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 w…

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.

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

#22

Post by fulafel »

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?

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

#24

Post by deidei »

fulafel wrote: post #22

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.

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

#25

Post by deidei »

mharig wrote: post #19

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?

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

#26

Post by dev_0 »

deidei wrote: post #13
goralph wrote: post #10

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?

[dead]

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

#27

Post by deidei »

NoThisIsMe wrote: post #17
comprev wrote: post #3

Look into something like HashiCorp's Vault for storing sensitive data. Trying to roll your own is asking for trouble!

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.

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

#28

Post by mharig »

deidei wrote: post #25
mharig wrote: post #19

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 secure…

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.

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

#29

Post by deidei »

mharig wrote: post #28
deidei wrote: post #25

Earlier quoted context omitted.

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 secure…

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…

>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!

Post reply on HN