Re: Encryption For Specific Column- Where to store the key - Mailing list pgsql-general

>From: "Pavel Stehule" <pavel.stehule@gmail.com>
>
>Hello
>
>try to use a security definer functions
>
>http://www.postgresql.org/docs/current/static/sql-createfunction.html
>
>inside this function you can access to resourcess thats are no
>available from outer for web user
>
>Regards
>
>Pavel Stehuke

I understand the idea behind it but it does not protect me when someone can dump the whole database.
He can simply change the user credentials and can access this function. But anyway thanks for the hint, it's useful to
improvesecurity! 


>From: "Merlin Moncure" <mmoncure@gmail.com>
>
>securing the backend from the dba is basically impossible.  you can
>make the client pretty secure, but the only way your encryption can be
>reasonably enforced is for both the encryption and decryption to
>happen on the client side -- the key cannot and should not be
>possessed by anyone who is not trustworthy.
>
>merlin

I can trust the dba. But while someone gain access, he can control everything and could easily extend his rights to
dba.
An other way with client side encryption/decryption should be possible with deployed certificates and keys, but so only
theuser has access to the data. 

The case is that I store private information like address, telephone, e-mail and maybe credit card information.
For billing or control of the customer data I need to access to the data, with a client en/decryption this is *not
possible.
(*I'm not sure but I read sometime ago something about a master key which could decrypt data encrypted with the child
keys,this would be a way to solve the problem. An other way is to save the decryption keys to a safe place and only
loadit when access is needed. Else the decryption is made through the client. (With Java Script this should be easily
possible))
Thanks for this hint, it seems a very elegant way to solve my problem! The deployment seems a problem while we are a
"opento register project" and I fear we get much support calls about this, but security is it worth. The only big
problemis when the user has lost his key, then it could be complicated. 

>From: "Craig Ringer" <craig@postnewspapers.com.au>
>Make sure you use a salt value and store the salt as well as the
>password. If not salted, your passwords will be easily cracked with a
>rainbow table if someone manages to dump them.

Password it salted and hashed multiple times. Simple salt + hash is no longer secure since there so much cpu power in
thecloud. 
I used "crypt('12345', gen_salt('bf', 16))" for it.
Some nice information could be found under http://codahale.com/how-to-safely-store-a-password/ if anyone is interested
inthis topic. 

>You won't have much luck with that. It only takes one "SELECT * FROM
>..." to bypass your query rate limiting. You could force everything
>through stored procedures, but that'll be slow and clumsy.
>
>Good intrusion detection and system monitoring so that you detect
>unusual events is probably a safter bet than trying to make your system
>fail when load spikes.

Yes, I know. This security measurements are being implemented.
I just try to protect from the worst case scenario, when intrusion detection fails.

>Yep. There you can do per-IP or per-range rate limiting, among other
>things, which will help. I'd probably want to do this using an intrusion
>detection/prevention system probably running as a reverse http proxy.

I need to read me deeper in this topic, thanks for the hint!

>Not much, no. The main benefit to encrypting some data in the database
>is to make it harder to use a stolen dump or extracted content. It can't
>make it impossible so long as your system can use the content too.
>
>I'd certainly avoid keeping the key in the database.
>
>If you don't need the capability for unattended web front-end re-start,
>you can have your web frontend store the key encrypted on disk and
>require interactive password entry to decrypt the key before it can
>start. It can then keep the key in RAM and forget the password. If your
>frontend is utterly compromised and someone has the time to do the
>analysis you're still busted, but it'll slow them down a bunch. If you
>want to be viewed as a frothing paranoid you can even store the
>encrypted key on the web frontend's HDD but on a separate SD card or
>thumb drive that requires physical insertion. I wouldn't.
>
>Personally, I'd probably just keep the key on the web frontend machine
>either unencrypted or encrypted with a passphrase coded into the
>frontend app. At least that way someone who manages to dump some of your
>tables or steal a database dump will need to steal - and know they need
>to steal - something completely different as well before they can use
>the stolen information.

I don't know, somehow I do not like the idea to save it on the frontend.
I think I try to implemented a Java Script based client en/decryption, with storing the information on creating to a
drivewhich could only be written but not read. 
(Or your idea mentioned some lines further down)

>It depends a lot on what the trade-off between convenience/performance
>and security is. How often is the data you want to encrypt accessed? Is
>it acceptable to require interactive authorization or input before
>encrypted data can be decrypted? Are the people who add sensitive data
>the same ones who need to be able to read it back out again, or can you
>restrict the group who can read it to a smaller group of users?

Data is only needed to the user who insert it. It's a simply database with personal information.
The person could only see his own data, editing should be possible.


>Use Stack Overflow instead ;-)
>

I did not post the question, found it while doing a google research. :)

>Anyway: Given the additional detail you provided in that post, where it
>becomes clear that you only need to be able to *read* the CCNs
>occasionally and only in batches, perhaps you should consider using
>public key crypto.
>
>Store the last 4 digits of the CCN unencrypted but not the CVV or expiry
>time. That way you can show a hint to the user about which card you're
>using without them (or anyone else) being able to extract the full details.
>
>Encrypt the full details using a public key when you store them in the
>database. The web front end only needs to know the public key to encrypt
>data. It doesn't need to know the private key, and without it it cannot
>*decrypt* the data again.
>
>Now your batch invoicing program can load the private key off a USB key
>or SD card - or just keep it on disk and decrypt it using a strong
>passphrase that is hand-entered by a user. When you're not doing
>invoicing and billing, your system doesn't have any access to the
>private key and cannot decrypt the stored data.
>
>If you switch to rolling billing later on, you'll need to adjust this
>process, but it still allows you to keep the part that can read the
>credit card numbers very separate from the part that interacts with
>untrusted users over the Internet.

It's not exactly my use case, but there are some nice ideas in your answer. (Sorry, should wrote it that the content of
theposted link is not from me) 
What I could do for the fields which needs protection is the store the first or last letter as plain text, so the user
knowswhat it is but an attacker who gains access not. 

While I need to control the data for billing or validity I can load the key from a usb stick or so like you mentioned
aboveand else there is no access to the data. This sounds very good! (While the user knows what data he provided, he
hasno problems to read "Main****** 34" as his address, but an attacker can't do much with this data.) 
Thanks a lot!


Kind Regards
Manuel Gysin