Обсуждение: Data Encryption
hello list, i am new to postgres and am puzzled how to solve the untrusted admin problem. i am having very sensitive data (accounts and payroll) on postgresql server. the goal i want to achive is that even the sys. admin/database admin. should be able to read the data. i understand that the solution lies in encrypting the fields. the fields i would like to encrypt are numeric. according to my understanding SQL queirues in the application will fail due to this. is it possible to accomplish this task ? i am using postgres 8.1, its a perl based application running on apache. Sandeep
On Thu, Mar 29, 2007 at 01:56:34PM +0530, Sandeep Agarwal wrote: > i am new to postgres and am puzzled how to solve the untrusted admin > problem. > > i am having very sensitive data (accounts and payroll) on postgresql > server. the goal i want to achive is that even the sys. admin/database > admin. should be able to read the data. i understand that the solution > lies in encrypting the fields. Do you mean that "even the sys. admin/database admin. should NOT be able to read the data"? If so then you must perform encryption and decryption on a secure machine to which only trusted personnel have access. Assume that admins can read all memory, swap space, files, and network connections and can trace any process on the system. If your threat model includes untrusted admins then also consider what kind of responsibility the admins have over the data even if they can't read it. Do you trust them not to manipulate, destroy, or otherwise render the data unrecoverable, either intentionally or accidentally? Do you have a way of ensuring the safety, integrity, and availability of the backups those admins will be performing? > the fields i would like to encrypt are numeric. according to my > understanding SQL queirues in the application will fail due to this. > is it possible to accomplish this task ? The PostgreSQL documentation discusses encryption options: http://www.postgresql.org/docs/8.2/interactive/encryption-options.html As "Client-Side Encryption" says, if you don't trust the admins then you'll need to encrypt the data before it reaches the database. > i am using postgres 8.1, its a perl based application running on apache. If you're responsible for "very sensitive data" then consider hiring a security expert. Security is easy to get wrong; the strongest encryption algorithms are worthless if misused or if used properly in an unsecure environment. "The world is full of bad security systems designed by people who have read _Applied Cryptography_."[1] [1] Niels Ferguson and Bruce Schneier quoting an unnamed source in _Practical Cryptography_, p 382. -- Michael Fuhr
On 3/29/07, Michael Fuhr <mike@fuhr.org> wrote: > On Thu, Mar 29, 2007 at 01:56:34PM +0530, Sandeep Agarwal wrote: > > i am new to postgres and am puzzled how to solve the untrusted admin > > problem. > > > > i am having very sensitive data (accounts and payroll) on postgresql > > server. the goal i want to achive is that even the sys. admin/database > > admin. should be able to read the data. i understand that the solution > > lies in encrypting the fields. > > Do you mean that "even the sys. admin/database admin. should NOT > be able to read the data"? If so then you must perform encryption > and decryption on a secure machine to which only trusted personnel > have access. Assume that admins can read all memory, swap space, > files, and network connections and can trace any process on the > system. thanks for the suggestion > If your threat model includes untrusted admins then also consider > what kind of responsibility the admins have over the data even if > they can't read it. Do you trust them not to manipulate, destroy, > or otherwise render the data unrecoverable, either intentionally > or accidentally? Do you have a way of ensuring the safety, integrity, > and availability of the backups those admins will be performing? yes i can trust the admin that they will not alter / destroy the data. > > the fields i would like to encrypt are numeric. according to my > > understanding SQL queirues in the application will fail due to this. > > is it possible to accomplish this task ? > > The PostgreSQL documentation discusses encryption options: > > http://www.postgresql.org/docs/8.2/interactive/encryption-options.html > > As "Client-Side Encryption" says, if you don't trust the admins > then you'll need to encrypt the data before it reaches the database. > > > i am using postgres 8.1, its a perl based application running on apache. > > If you're responsible for "very sensitive data" then consider hiring > a security expert. Security is easy to get wrong; the strongest > encryption algorithms are worthless if misused or if used properly > in an unsecure environment. > > "The world is full of bad security systems designed by people who > have read _Applied Cryptography_."[1] > > [1] Niels Ferguson and Bruce Schneier quoting an unnamed source in > _Practical Cryptography_, p 382. > > -- > Michael Fuhr >
> On Thu, Mar 29, 2007 at 01:56:34PM +0530, Sandeep Agarwal wrote: > > i am new to postgres and am puzzled how to solve the untrusted > admin > > problem. > > > > i am having very sensitive data (accounts and payroll) on postgresql > > server. the goal i want to achive is that even the sys. > admin/database> admin. should be able to read the data. i > understand that the solution > > lies in encrypting the fields. Sandeep, Please do not take affront if I am reading way too much into your question, but if the following scenario is true: 1) It is a web application with the server colocated in the US 2) You are the developer AND admin and are not in the US If so, you obviously trust yourself -- the problem is that customers or prospective customers will possibly (likely) be hesitant to use your service, particularly when SSN's, names and employee addresses are involved. If my conjecture is true, then you may want to rephrase your question. Different suggestions may result. If I am 'off base', please disregard. Thanks, Mike Ellsworth
>> > i am new to postgres and am puzzled how to solve the untrusted >> admin >> > problem. >> > > Please do not take affront if I am reading way too much into your > question, but if the following scenario is true: > 1) It is a web application with the server colocated in the US > 2) You are the developer AND admin and are not in the US > > If so, you obviously trust yourself -- the problem is that customers or > prospective customers will possibly (likely) be hesitant to use your > service, particularly when SSN's, names and employee addresses are > involved. The original question was on-topic (basic technical question re postgresql) but the answer wasn't. I'm sorry to continue off-topic and will endeavour never to do it again but... The original question is a serious one that few companies seem to take seriously. I've had a number of freelance jobs as an Oracle DBA over the years and it has shocked me how careless big companies can be with their own and their client's data, some of it supposed to be confidential. As a DBA I've been able to access all sorts of sensitive data if I'd chosen to. I was trusted and have never betrayed that trust. But consider my and countless others' positions: - I might be there for a few months with no commitment as such to the company; - People trust me because I'm a nice guy and hey, DBAs are trustworthy, aren't they? - I tend to work for the same type of companies because having oil company 1 on my cv is attractive to oil company 2; - I have access to data that might be worth a lot of money to oil company 2. - And as a DBA I sometimes have to work out of hours when there's noone to watch me (not that anyone ever does anyway). Should I be trusted by these big companies who should be protecting the interests of their share holders? No way. But I always have been. And so have others I've known who I wouldn't trust with my credit card number. OK, I promise, with fingers firmly crossed, never to get involved in an off-topic discussion again! And I'm sorry but I can't answer the original question. Nick.
On 3/29/07, nhrcommu@rochester.rr.com <nhrcommu@rochester.rr.com> wrote: > > > On Thu, Mar 29, 2007 at 01:56:34PM +0530, Sandeep Agarwal wrote: > > > i am new to postgres and am puzzled how to solve the untrusted > > admin > > > problem. > > > > > > i am having very sensitive data (accounts and payroll) on postgresql > > > server. the goal i want to achive is that even the sys. > > admin/database> admin. should be able to read the data. i > > understand that the solution > > > lies in encrypting the fields. > > Sandeep, > Please do not take affront if I am reading way too much into your > question, but if the following scenario is true: > 1) It is a web application with the server colocated in the US > 2) You are the developer AND admin and are not in the US > > If so, you obviously trust yourself -- the problem is that customers or > prospective customers will possibly (likely) be hesitant to use your > service, particularly when SSN's, names and employee addresses are involved. > > If my conjecture is true, then you may want to rephrase your question. > Different suggestions may result. yes you are correct. the servers are colocated in the US and we are working from India. > If I am 'off base', please disregard. > > Thanks, > Mike Ellsworth >