Thread: Encryption For Specific Column- Where to store the key
Hello I'm currently designing a database layout where some columns are encrypted. Some tables contains sensitive user data which needs a special protection. I used http://www.postgresql.org/docs/8.1/static/encryption-options.html as a guide. - For the password field I just used a hash algorithm with some loops to protect the passwords ("Password Storage Encryption"with bcrypt). - For the sensitive columns I used "Encryption For Specific Columns", here I have later some questions. - For general data encryption I used luks (crypsetup) "Data Partition Encryption" - Connection is secured like desc. in "Encrypting Data Across A Network" with "SSL Host Authentication" Much attack use cases are covered with this but I see one problem: 1. There is a frontend (webserver) and a backend (database) - backend must be configured to not allow to much queries in a given time, else there is a possibility to get around thewhole security stuff - frontend needs too some protection against brute force 2. When encrypting some columns I need to save somewhere the key. - Frontend (very bad idea, first point of failure) - Backend (when someone can dump the database, he got the key too, encryption is in this use case useless) - Remote database (when someone can hack to the first db, it's not far away to the second db I think, but there is moretime to register an attack and force shutdown everything) - Write an dedicated application (when someone hacked this server, it's only a matter of time before he can find out wherethe key is stored in the RAM) So it seems there is no protection when someone gained access to the database server. Or is there a way? I can't see any. I'm not fit enough in attack a database server, but I think when someone has access to the database, he can simply dump thewhole tables, while the key is stored in the table, he has full access to everything in the database. At the end the questionis, where and how I should store the key to decrypt the columns? A discussion about this topic can be found under http://www.experts-exchange.com/Database/PostgreSQL/Q_21934798.html (answersare not all the time displayed...) But there were no final solution at all. Kind regards Manuel Gysin
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 2011/6/15 Manuel Gysin <manuel.gysin@quantum-bytes.com>: > Hello > > > I'm currently designing a database layout where some columns are encrypted. > Some tables contains sensitive user data which needs a special protection. > > I used http://www.postgresql.org/docs/8.1/static/encryption-options.html as a guide. > > - For the password field I just used a hash algorithm with some loops to protect the passwords ("Password Storage Encryption"with bcrypt). > - For the sensitive columns I used "Encryption For Specific Columns", here I have later some questions. > - For general data encryption I used luks (crypsetup) "Data Partition Encryption" > - Connection is secured like desc. in "Encrypting Data Across A Network" with "SSL Host Authentication" > > Much attack use cases are covered with this but I see one problem: > > 1. There is a frontend (webserver) and a backend (database) > - backend must be configured to not allow to much queries in a given time, else there is a possibility to get aroundthe whole security stuff > - frontend needs too some protection against brute force > 2. When encrypting some columns I need to save somewhere the key. > - Frontend (very bad idea, first point of failure) > - Backend (when someone can dump the database, he got the key too, encryption is in this use case useless) > - Remote database (when someone can hack to the first db, it's not far away to the second db I think, but there is moretime to register an attack and force shutdown everything) > - Write an dedicated application (when someone hacked this server, it's only a matter of time before he can find outwhere the key is stored in the RAM) > > So it seems there is no protection when someone gained access to the database server. Or is there a way? I can't see any. > I'm not fit enough in attack a database server, but I think when someone has access to the database, he can simply dumpthe whole tables, while the key is stored in the table, he has full access to everything in the database. At the endthe question is, where and how I should store the key to decrypt the columns? > > A discussion about this topic can be found under http://www.experts-exchange.com/Database/PostgreSQL/Q_21934798.html (answersare not all the time displayed...) > But there were no final solution at all. > > > Kind regards > Manuel Gysin > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
On Wed, Jun 15, 2011 at 1:07 AM, Manuel Gysin <manuel.gysin@quantum-bytes.com> wrote: > Hello > I'm currently designing a database layout where some columns are encrypted. > Some tables contains sensitive user data which needs a special protection. > > I used http://www.postgresql.org/docs/8.1/static/encryption-options.html as a guide. > > - For the password field I just used a hash algorithm with some loops to protect the passwords ("Password Storage Encryption"with bcrypt). > - For the sensitive columns I used "Encryption For Specific Columns", here I have later some questions. > - For general data encryption I used luks (crypsetup) "Data Partition Encryption" > - Connection is secured like desc. in "Encrypting Data Across A Network" with "SSL Host Authentication" > > Much attack use cases are covered with this but I see one problem: > > 1. There is a frontend (webserver) and a backend (database) > - backend must be configured to not allow to much queries in a given time, else there is a possibility to get aroundthe whole security stuff > - frontend needs too some protection against brute force > 2. When encrypting some columns I need to save somewhere the key. > - Frontend (very bad idea, first point of failure) > - Backend (when someone can dump the database, he got the key too, encryption is in this use case useless) > - Remote database (when someone can hack to the first db, it's not far away to the second db I think, but there is moretime to register an attack and force shutdown everything) > - Write an dedicated application (when someone hacked this server, it's only a matter of time before he can find outwhere the key is stored in the RAM) > > So it seems there is no protection when someone gained access to the database server. Or is there a way? I can't see any. > I'm not fit enough in attack a database server, but I think when someone has access to the database, he can simply dumpthe whole tables, while the key is stored in the table, he has full access to everything in the database. At the endthe question is, where and how I should store the key to decrypt the columns? > > A discussion about this topic can be found under http://www.experts-exchange.com/Database/PostgreSQL/Q_21934798.html (answersare not all the time displayed...) > But there were no final solution at all. 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
On 15/06/11 14:07, Manuel Gysin wrote:
> - For the password field I just used a hash algorithm with some loops to protect the passwords ("Password Storage
Encryption"with bcrypt).
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.
> 1. There is a frontend (webserver) and a backend (database)
> - backend must be configured to not allow to much queries in a given time, else there is a possibility to get
aroundthe whole security stuff
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.
> - frontend needs too some protection against brute force
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.
> 2. When encrypting some columns I need to save somewhere the key.
> - Frontend (very bad idea, first point of failure)
> - Backend (when someone can dump the database, he got the key too, encryption is in this use case useless)
> - Remote database (when someone can hack to the first db, it's not far away to the second db I think, but there is
moretime to register an attack and force shutdown everything)
> - Write an dedicated application (when someone hacked this server, it's only a matter of time before he can find
outwhere the key is stored in the RAM)
>
> So it seems there is no protection when someone gained access to the database server. Or is there a way? I can't see
any.
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'm not fit enough in attack a database server, but I think when someone has access to the database, he can simply
dumpthe whole tables, while the key is stored in the table, he has full access to everything in the database. At the
endthe question is, where and how I should store the key to decrypt the columns?
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?
--
Craig Ringer
On 15/06/11 14:07, Manuel Gysin wrote: > A discussion about this topic can be found under http://www.experts-exchange.com/Database/PostgreSQL/Q_21934798.html Use Stack Overflow instead ;-) 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. -- Craig Ringer
2011/6/16 Manuel Gysin <manuel.gysin@quantum-bytes.com>: >>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 toimprove security! > if attacker can dump a database, then any protection is terrible hard or impossible :( if you store some very good salted hash instead password to database, then access to dump isn't helpful for attacker. Regards Pavel Stehule p.s. any security protections are thin without full control over server.
On Thu, Jun 16, 2011 at 3:35 AM, Manuel Gysin <manuel.gysin@quantum-bytes.com> wrote: > 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 onlythe user has access to the data. With all due respect, I don't think you can trust the dba. People are always the weakest link in any security chain. Don't think of the specific person, but more the role involved and what data that person should be allowed to see. Imagine explaining to your customers that certain of your personnel will have access to credit card numbers or other highly sensitive information. Imagine explaining it to an auditor. OTOH, maybe your security expectations are a little too high -- it's always a tradeoff between security and usability (this is why we don't encrypt addresses). If you are talking about the specific case of credit card number processing, there are strict protocols that have to be followed in order to be able to do that. You must submit to an audit in order to be able to do that (google pci compliance). Password hashing is another thing -- randomly chosen defenses (like repeat hashing or combining algorithms) are, IMNSHO, useless. Better to use salt and, if necessary, do not do the hashing or store the salt on the server. merlin
>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
Hi, I am running a test setup for verifying the warm standby feature in Postgres 9.0 release. Both my Master (Win 7 Professional, 64 bit) & Standby (Win 7 Professional, 64 bit) machines are having Postgres 9.0 (postgresql-9.0.4-1-windows) installed & both are in our LAN environment. After taking a base backup of the master database using pg_start_backup() & copy of the data folder to Standby machine followed by pg_stop_backup() in Master, the Standby server is started. The Standby server logs show the following error and the standby Postgres server shuts down. After this it never starts 2011-06-18 13:56:32 IST LOG: entering standby mode 2011-06-18 13:56:32 IST LOG: streaming replication successfully connected to primary 2011-06-18 13:56:32 IST FATAL: the database system is starting up 2011-06-18 13:56:33 IST LOG: redo starts at 0/3000020 2011-06-18 13:56:33 IST FATAL: the database system is starting up 2011-06-18 13:56:34 IST LOG: consistent recovery state reached at 0/5000000 2011-06-18 13:56:34 IST FATAL: the database system is starting up 2011-06-18 13:56:35 IST FATAL: the database system is starting up 2011-06-18 13:56:36 IST FATAL: the database system is starting up 2011-06-18 13:56:37 IST FATAL: the database system is starting up 2011-06-18 13:56:38 IST FATAL: the database system is starting up 2011-06-18 13:56:39 IST FATAL: the database system is starting up . . . .and same message continues.. I have attached the configuration files of standby server. Please help me if anybody have any such experience or know the exact cause of the issue. Regards, Sanjay
Attachment
On Sat, Jun 18, 2011 at 10:57, Sanjay Rao <srao@noida.interrasystems.com> wrote: > Hi, > > I am running a test setup for verifying the warm standby feature in Postgres > 9.0 release. Both my Master (Win 7 Professional, 64 bit) & Standby (Win 7 > Professional, 64 bit) machines are having Postgres 9.0 > (postgresql-9.0.4-1-windows) installed & both are in our LAN environment. > > After taking a base backup of the master database using pg_start_backup() & > copy of the data folder to Standby machine followed by pg_stop_backup() in > Master, the Standby server is started. The Standby server logs show the > following error and the standby Postgres server shuts down. After this it > never starts > > 2011-06-18 13:56:32 IST LOG: entering standby mode > 2011-06-18 13:56:32 IST LOG: streaming replication successfully connected > to primary > 2011-06-18 13:56:32 IST FATAL: the database system is starting up > 2011-06-18 13:56:33 IST LOG: redo starts at 0/3000020 > 2011-06-18 13:56:33 IST FATAL: the database system is starting up > 2011-06-18 13:56:34 IST LOG: consistent recovery state reached at 0/5000000 > 2011-06-18 13:56:34 IST FATAL: the database system is starting up > 2011-06-18 13:56:35 IST FATAL: the database system is starting up > 2011-06-18 13:56:36 IST FATAL: the database system is starting up > 2011-06-18 13:56:37 IST FATAL: the database system is starting up > 2011-06-18 13:56:38 IST FATAL: the database system is starting up > 2011-06-18 13:56:39 IST FATAL: the database system is starting up > . > . > . > .and same message continues.. > > I have attached the configuration files of standby server. Please help me if > anybody have any such experience or know the exact cause of the issue. That seems perfectly normal. There's nothing in those logs indicating that the standby shut down - if it did, there should be some other messages when that happens. If you want to be able to actually connect to the slave, you need to run in hot standby. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/