Skip site navigation (1) Skip section navigation (2)

Re: [ADMIN] [PHP] Secure DB Systems - How to

From: Mitch Pirtle <mitchy(at)spacemonkeylabs(dot)com>
To: Daniel Struck <struck(dot)d(at)retrovirology(dot)lu>
Cc: Bruno Wolff III <bruno(at)wolff(dot)to>,Sarah Tanembaum <sarahtanembaum(at)yahoo(dot)com>, pgsql-php(at)postgresql(dot)org,pgsql-admin(at)postgresql(dot)org, pgsql-hackers-win32(at)postgresql(dot)org,pgadmin-support(at)postgresql(dot)org, pgsql-sql(at)postgresql(dot)org
Subject: Re: [ADMIN] [PHP] Secure DB Systems - How to
Date: 2004-07-13 14:06:02
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgadmin-supportpgsql-adminpgsql-hackers-win32pgsql-phppgsql-sql
Daniel Struck wrote:

>>If you decrypt the data on the database, the sysadmin can see it.
>Hm, you are right. If one does decrypt the data on the database you have to sent the password to postgresql and so a administrator of the database could easily grasb the password.
>So the only way to go, would be to perform en/decryption on the client side?

Exactly.  That is the only way to ensure that the data is never 
decrypted within the database or database server.

Now, the 'client' IMHO is the PHP application.  If the key for your 
encryption is stored in the user's session (on the webserver 
temporarily) then there is no log of that key or data (unless you store 
the session data in the database, then you got problems, see below).

I'm starting an article on doing just this for International PHP 
Magazine, and of course will use PostgreSQL as the back-end ;-)

>I wonder now; if somebody could achieve to get a snapshot of the database, they could also be able to get the log-file of postgresql.
>So one would also have to make attention that the information like sql statements don't leak that way.
>Are there other places where this kind of information could leak?

If you wanted the key based on each user, then you could use their 
password (which is typically stored in an MD5 hash) as the key for 
encryption/decryption, and put it in the session for use by the 
application while they are logged in.  This is the easiest (and most 
effective, IMHO) way to keep the encryption at the user level, and keep 
the data in the database encrypted at all times.  Basically every record 
would be encrypted with the key for the user associated with that 
record, and there's a lot of work for anyone with a snapshot who is 
working on brute forcing all that data row by row...  :)

The only time that data is not encrypted is on the webserver, and only 
during transmission of that data back to the client.  SSL would be the 
most common approach to solving this problem.

My absolute favourite DB layer for PHP is ADOdb, which also has a class 
that transparently stores your session data in the database (if 
desired).  This is crucial for sites that have multiple webservers and 
load balancers, as your session data needs to be accessible from the 
webserver that you are currently at.

The problem here is that the key for each user would also be stored in 
the database if this method were used, rendering your efforts pointless!

OTOH, using the default storage of session data would put all the user 
keys in temp files on the hard drives of the webservers.  Not only does 
this not scale well (as you have to tell pound or LocalDirector or 
whatever load balancer you use to stick each user to the primary 
server), but some would consider this absolutely not-acceptable, as any 
administrator on the servers could see that data.  So I suppose you have 
to pick your poison on this one.

I gotta figure this out so I can start writing ;^P

-- Mitch

In response to


pgsql-php by date

Next:From: Lynna LandstreetDate: 2004-07-14 19:27:49
Subject: Re: Resource id #12
Previous:From: Daniel StruckDate: 2004-07-13 13:18:38
Subject: Re: [PHP] Secure DB Systems - How to

pgsql-admin by date

Next:From: Tom LaneDate: 2004-07-13 14:24:36
Subject: Re: Bad dumps...
Previous:From: Bruno Wolff IIIDate: 2004-07-13 13:45:28
Subject: Re: Slony NG

pgadmin-support by date

Next:From: TaniaDate: 2004-07-13 18:05:22
Subject: Bug on postgresql
Previous:From: Daniel StruckDate: 2004-07-13 13:18:38
Subject: Re: [PHP] Secure DB Systems - How to

pgsql-hackers-win32 by date

Next:From: Alexander CohenDate: 2004-07-13 14:52:46
Subject: Re: libpq compiled not compiled with minGW
Previous:From: Merlin MoncureDate: 2004-07-13 13:56:43
Subject: Re: libpq compiled not compiled with minGW

pgsql-sql by date

Next:From: SZŰCS GáborDate: 2004-07-13 16:29:23
Subject: Re: Constraint->function dependency and dump in 7.3
Previous:From: Daniel StruckDate: 2004-07-13 13:18:38
Subject: Re: [PHP] Secure DB Systems - How to

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group