Re: Data Encryption

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