pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSecure DB Systems - How to
17+ messages / 7 participants
[nested] [flat]
* Secure DB Systems - How to
@ 2004-07-08 15:49 Sarah Tanembaum <sarahtanembaum@yahoo.com>
0 siblings, 2 replies; 17+ messages in thread
From: Sarah Tanembaum @ 2004-07-08 15:49 UTC (permalink / raw)
To: pgadmin-support@postgresql.org; +Cc: pgsql-admin@postgresql.org; pgsql-hackers-win32@postgresql.org; pgsql-php@postgresql.org; pgsql-sql
I was wondering if it is possible to create a secure database system
usingPostgreSQL/PHP combination?
I have the following in mind:
I wanted to store all my( and my brothers and sisters) important document
information such as birth certificate, SSN, passport number, travel
documents, insurance(car, home, etc) document, and other important documents
imagined in the database.
The data will be entered either manually and/or scanned(with OCR). I need to
be able to search on all the fields in the database.
We have 10 computers(5bros, 4sisters, and myself) plus 1 server with I
maintained. The data should be synchronize/replicate between those
computers.
Well, so far it is easy, isn't it?
Here's my question:
a) How can I make sure that it secure so only authorized person can
modify/add/delete the information? Beside transaction logs, are there any
other method to trace any transaction(kind of paper trail)?
Assuming there are 3 step process to one enter the info e.g:
- One who enter the info (me)
- One who verify the info(the owner of info)
- One who verify and then commit the change!
How can I implement such a process in PostgreSQL and/or PHP or any other web
language?
b) How can I make sure that no one can tap the info while we are entering
the data in the computer? (our family are scattered within US and Canada)
c) Is it possible to securely synchronize/replicate between our computers
using VPN? Does PostgreSQL has this functionality by default?
d) Other secure method that I have not yet mentioned.
Anyone has good ideas on how to implement such a systems?
Thanks
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [PHP] Secure DB Systems - How to
@ 2004-07-09 15:27 Bruno Wolff III <bruno@wolff.to>
parent: Sarah Tanembaum <sarahtanembaum@yahoo.com>
1 sibling, 2 replies; 17+ messages in thread
From: Bruno Wolff III @ 2004-07-09 15:27 UTC (permalink / raw)
To: Sarah Tanembaum <sarahtanembaum@yahoo.com>; +Cc: pgsql-php@postgresql.org; pgsql-admin@postgresql.org; pgsql-hackers-win32@postgresql.org; pgadmin-support@postgresql.org; pgsql-sql
On Thu, Jul 08, 2004 at 11:49:36 -0400,
Sarah Tanembaum <sarahtanembaum@yahoo.com> wrote:
> I was wondering if it is possible to create a secure database system
> usingPostgreSQL/PHP combination?
>
> I have the following in mind:
>
> I wanted to store all my( and my brothers and sisters) important document
> information such as birth certificate, SSN, passport number, travel
> documents, insurance(car, home, etc) document, and other important documents
> imagined in the database.
>
> The data will be entered either manually and/or scanned(with OCR). I need to
> be able to search on all the fields in the database.
>
> We have 10 computers(5bros, 4sisters, and myself) plus 1 server with I
> maintained. The data should be synchronize/replicate between those
> computers.
>
> Well, so far it is easy, isn't it?
>
> Here's my question:
>
> a) How can I make sure that it secure so only authorized person can
> modify/add/delete the information? Beside transaction logs, are there any
> other method to trace any transaction(kind of paper trail)?
Keeping the system administrator from seeing the data while making it
searchable is difficult. To do this you need to encrypt the data on
the client side using a key the client has (and this key has to be
protected from loss) and the only searches you can do are equality
searches using a hash or encrypted value.
The system administrator can always delete the data.
If you store which user has access to a row in the row, you can use views
to control access to the rows for people other than the system administrator.
> b) How can I make sure that no one can tap the info while we are entering
> the data in the computer? (our family are scattered within US and Canada)
Use SSL.
> c) Is it possible to securely synchronize/replicate between our computers
> using VPN? Does PostgreSQL has this functionality by default?
Probably the best thing here is to run one live server and make backups
of the system that you store at your relatives along with instructions
for recovering them if something happens to you. Probably the backups
should be encrypted with either the keys in your safe deposit box or
using a system where something like 3 out of 5 keys can be used to recover
the backup files. Be sure to test the backup recovery.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Secure DB Systems - How to
@ 2004-07-12 14:07 Rajesh Kumar Mallah <mallah@trade-india.com>
parent: Sarah Tanembaum <sarahtanembaum@yahoo.com>
1 sibling, 2 replies; 17+ messages in thread
From: Rajesh Kumar Mallah @ 2004-07-12 14:07 UTC (permalink / raw)
To: Sarah Tanembaum <sarahtanembaum@yahoo.com>; +Cc: pgsql-sql
Sarah Tanembaum wrote:
>I was wondering if it is possible to create a secure database system
>usingPostgreSQL/PHP combination?
>
>I have the following in mind:
>
>I wanted to store all my( and my brothers and sisters) important document
>information such as birth certificate, SSN, passport number, travel
>documents, insurance(car, home, etc) document, and other important documents
>imagined in the database.
>
>The data will be entered either manually and/or scanned(with OCR). I need to
>be able to search on all the fields in the database.
>
>We have 10 computers(5bros, 4sisters, and myself) plus 1 server with I
>maintained. The data should be synchronize/replicate between those
>computers.
>
>Well, so far it is easy, isn't it?
>
>Here's my question:
>
>a) How can I make sure that it secure so only authorized person can
>modify/add/delete the information? Beside transaction logs, are there any
>other method to trace any transaction(kind of paper trail)?
>
>
There can be multiple solutions to your problem.
The security and logging may be implemented either at
database level or application level. That is a call you have to
take.
If you consider the database to take care of security and logging
you could do the following.
1. create a database user for each of your family members
2. ask the memebers to login to your application using their own id.
3. Use that id for connecting to the database using php.
the security at table level can be managed by various GRANT commands.
the security at row level can be done using a mechanism methods
describe in the -general mailling list (search: "row level grants").
For logging changes to your tables you can create audit trail of all
the tables in question by using triggers or enbale logging of sql
statements (with current user display) in postgresql server.
u may consider:
http://gborg.postgresql.org/project/audittrail/projdisplay.php
although i have not used it myself.
>Assuming there are 3 step process to one enter the info e.g:
>- One who enter the info (me)
>- One who verify the info(the owner of info)
>- One who verify and then commit the change!
>How can I implement such a process in PostgreSQL and/or PHP or any other web
>language?
>
>
I think such a moderation should be implemented at application
level.
>b) How can I make sure that no one can tap the info while we are entering
>the data in the computer? (our family are scattered within US and Canada)
>
>
you may run yor web application using https:// rather than http://
and you may enable ssl in postgresql for securing the communication
between application and database.
>c) Is it possible to securely synchronize/replicate between our computers
>using VPN? Does PostgreSQL has this functionality by default?
>
>
Slony and many other replication solution exists for asyncronous
replication.
Hope it helps a bit.
Regds
Mallah.
>d) Other secure method that I have not yet mentioned.
>
>Anyone has good ideas on how to implement such a systems?
>
>Thanks
>
>
>
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
>
>
>
--
regds
Mallah.
Rajesh Kumar Mallah
+---------------------------------------------------+
| Tradeindia.com (3,11,246) Registered Users |
| Indias' Leading B2B eMarketPlace |
| http://www.tradeindia.com/ |
+---------------------------------------------------+
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [PHP] Secure DB Systems - How to
@ 2004-07-12 21:48 Sarah Tanembaum <sarahtanembaum@yahoo.com>
parent: Bruno Wolff III <bruno@wolff.to>
1 sibling, 0 replies; 17+ messages in thread
From: Sarah Tanembaum @ 2004-07-12 21:48 UTC (permalink / raw)
To: pgsql-sql; +Cc: pgsql-php@postgresql.org; pgsql-admin@postgresql.org; pgsql-hackers-win32@postgresql.org; pgadmin-support@postgresql.org
Thanks Bruno.
I see the potential challenge. If the field value is encrypted, how can
search be done for certain value? Do I have to seach for the encrypted
value? If so, what password or key should I use?
Thanks
"Bruno Wolff III" <bruno@wolff.to> wrote in message
news:20040709152722.GA20935@wolff.to...
> On Thu, Jul 08, 2004 at 11:49:36 -0400,
> Sarah Tanembaum <sarahtanembaum@yahoo.com> wrote:
> > I was wondering if it is possible to create a secure database system
> > usingPostgreSQL/PHP combination?
> >
> > I have the following in mind:
> >
> > I wanted to store all my( and my brothers and sisters) important
document
> > information such as birth certificate, SSN, passport number, travel
> > documents, insurance(car, home, etc) document, and other important
documents
> > imagined in the database.
> >
> > The data will be entered either manually and/or scanned(with OCR). I
need to
> > be able to search on all the fields in the database.
> >
> > We have 10 computers(5bros, 4sisters, and myself) plus 1 server with I
> > maintained. The data should be synchronize/replicate between those
> > computers.
> >
> > Well, so far it is easy, isn't it?
> >
> > Here's my question:
> >
> > a) How can I make sure that it secure so only authorized person can
> > modify/add/delete the information? Beside transaction logs, are there
any
> > other method to trace any transaction(kind of paper trail)?
>
> Keeping the system administrator from seeing the data while making it
> searchable is difficult. To do this you need to encrypt the data on
> the client side using a key the client has (and this key has to be
> protected from loss) and the only searches you can do are equality
> searches using a hash or encrypted value.
>
> The system administrator can always delete the data.
>
> If you store which user has access to a row in the row, you can use views
> to control access to the rows for people other than the system
administrator.
>
> > b) How can I make sure that no one can tap the info while we are
entering
> > the data in the computer? (our family are scattered within US and
Canada)
>
> Use SSL.
>
> > c) Is it possible to securely synchronize/replicate between our
computers
> > using VPN? Does PostgreSQL has this functionality by default?
>
> Probably the best thing here is to run one live server and make backups
> of the system that you store at your relatives along with instructions
> for recovering them if something happens to you. Probably the backups
> should be encrypted with either the keys in your safe deposit box or
> using a system where something like 3 out of 5 keys can be used to recover
> the backup files. Be sure to test the backup recovery.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Secure DB Systems - How to
@ 2004-07-12 22:09 Sarah Tanembaum <sarahtanembaum@yahoo.com>
parent: Rajesh Kumar Mallah <mallah@trade-india.com>
1 sibling, 0 replies; 17+ messages in thread
From: Sarah Tanembaum @ 2004-07-12 22:09 UTC (permalink / raw)
To: pgsql-admin@postgresql.org; +Cc: pgsql-hackers-win32@postgresql.org; pgadmin-support@postgresql.org; pgsql-php@postgresql.org; pgsql-sql
Thanks Rajesh.
What you propose is good for making each record private but does not protect
from other with dba access from viewing the sensitive fields, e.g:
socialsecuritynumber.
Suppose that you have the following field:
name
address1
address2
city
state
zip
socialsecuritynumber
phonenumber
passportnumber
bankaccountnumber
bankdepositboxnumber
Individually, please correct me if I'm wrong, the field does not mean
anything to anyone EXCEPT to the owner of those information. BUT, the
RELATION of those fields are very telling about the individual.
How do one protect those relation so no non-authorized user able to view the
sensitive information.
Okay, let me think out loud.
Suppose that we store all those fields in text format, but withouth
RELATIONSHIP information, that is, anyone can view all the SSN, phone,
bankaccountnumber etc, but only the owner and a few authorized
users(controlled with secure password or key) able to view those relation.
Does this make any sense?
Thanks
"Rajesh Kumar Mallah" <mallah@trade-india.com> wrote in message
news:40F29B19.2020407@trade-india.com...
> Sarah Tanembaum wrote:
>
> >I was wondering if it is possible to create a secure database system
> >usingPostgreSQL/PHP combination?
> >
> >I have the following in mind:
> >
> >I wanted to store all my( and my brothers and sisters) important document
> >information such as birth certificate, SSN, passport number, travel
> >documents, insurance(car, home, etc) document, and other important
documents
> >imagined in the database.
> >
> >The data will be entered either manually and/or scanned(with OCR). I need
to
> >be able to search on all the fields in the database.
> >
> >We have 10 computers(5bros, 4sisters, and myself) plus 1 server with I
> >maintained. The data should be synchronize/replicate between those
> >computers.
> >
> >Well, so far it is easy, isn't it?
> >
> >Here's my question:
> >
> >a) How can I make sure that it secure so only authorized person can
> >modify/add/delete the information? Beside transaction logs, are there any
> >other method to trace any transaction(kind of paper trail)?
> >
> >
> There can be multiple solutions to your problem.
>
> The security and logging may be implemented either at
> database level or application level. That is a call you have to
> take.
>
> If you consider the database to take care of security and logging
> you could do the following.
>
> 1. create a database user for each of your family members
> 2. ask the memebers to login to your application using their own id.
> 3. Use that id for connecting to the database using php.
>
> the security at table level can be managed by various GRANT commands.
>
> the security at row level can be done using a mechanism methods
> describe in the -general mailling list (search: "row level grants").
>
> For logging changes to your tables you can create audit trail of all
> the tables in question by using triggers or enbale logging of sql
> statements (with current user display) in postgresql server.
>
> u may consider:
> http://gborg.postgresql.org/project/audittrail/projdisplay.php
> although i have not used it myself.
>
>
>
>
>
>
>
>
>
>
> >Assuming there are 3 step process to one enter the info e.g:
> >- One who enter the info (me)
> >- One who verify the info(the owner of info)
> >- One who verify and then commit the change!
> >How can I implement such a process in PostgreSQL and/or PHP or any other
web
> >language?
> >
> >
>
> I think such a moderation should be implemented at application
> level.
>
> >b) How can I make sure that no one can tap the info while we are entering
> >the data in the computer? (our family are scattered within US and Canada)
> >
> >
>
> you may run yor web application using https:// rather than http://
> and you may enable ssl in postgresql for securing the communication
> between application and database.
>
> >c) Is it possible to securely synchronize/replicate between our computers
> >using VPN? Does PostgreSQL has this functionality by default?
> >
> >
>
> Slony and many other replication solution exists for asyncronous
> replication.
>
> Hope it helps a bit.
>
> Regds
> Mallah.
>
> >d) Other secure method that I have not yet mentioned.
> >
> >Anyone has good ideas on how to implement such a systems?
> >
> >Thanks
> >
> >
> >
> >
> >
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 8: explain analyze is your friend
> >
> >
> >
>
>
> --
>
> regds
> Mallah.
>
> Rajesh Kumar Mallah
> +---------------------------------------------------+
> | Tradeindia.com (3,11,246) Registered Users |
> | Indias' Leading B2B eMarketPlace |
> | http://www.tradeindia.com/ |
> +---------------------------------------------------+
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [PHP] Secure DB Systems - How to
@ 2004-07-13 09:35 Daniel Struck <struck.d@retrovirology.lu>
parent: Bruno Wolff III <bruno@wolff.to>
1 sibling, 1 reply; 17+ messages in thread
From: Daniel Struck @ 2004-07-13 09:35 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: Sarah Tanembaum <sarahtanembaum@yahoo.com>; pgsql-php@postgresql.org; pgsql-admin@postgresql.org; pgsql-hackers-win32@postgresql.org; pgadmin-support@postgresql.org; pgsql-sql
> Keeping the system administrator from seeing the data while making it
> searchable is difficult. To do this you need to encrypt the data on
> the client side using a key the client has (and this key has to be
> protected from loss) and the only searches you can do are equality
> searches using a hash or encrypted value.
You can also perform regex searches.
Here is an example to get you started:
CREATE TABLE crypto (
id SERIAL PRIMARY KEY,
title VARCHAR(50),
crypted_content BYTEA
);
INSERT INTO crypto VALUES (1,'test1',encrypt_iv('daniel','fooz',decode('9MlPeZtpuxKo5m4O4+pd4g==','base64'),'aes'));
INSERT INTO crypto VALUES (2,'test2',encrypt_iv('test','fooz',decode('9MlPeZtpuxKo5m4O4+pd4g==','base64'),'aes'));
INSERT INTO crypto VALUES (3,'test3',encrypt_iv('struck','fooz',decode('9MlPeZtpuxKo5m4O4+pd4g==','base64'),'aes'));
SELECT *,decrypt_iv(crypted_content, 'fooz',decode('9MlPeZtpuxKo5m4O4+pd4g==','base64'), 'aes') FROM crypto;
-- equality search
SELECT *,decrypt_iv(crypted_content,'fooz',decode('9MlPeZtpuxKo5m4O4+pd4g==','base64'),'aes')
FROM crypto WHERE decrypt_iv(crypted_content, 'fooz',decode('9MlPeZtpuxKo5m4O4+pd4g==','base64'), 'aes')='struck';
-- regex search
SELECT *,decrypt_iv(crypted_content,'fooz',decode('9MlPeZtpuxKo5m4O4+pd4g==','base64'),'aes')
FROM crypto WHERE encode(decrypt_iv(crypted_content, 'fooz',decode('9MlPeZtpuxKo5m4O4+pd4g==','base64'), 'aes'),'escape')
~* 'daniel';
"fooz" is the password and "9MlPeZtpuxKo5m4O4+pd4g==" is the IV (initialization vector) stored in base64 format. I choose base64 because it is more convenient to create queries with it.
In the real database I do use a different IV for every row, so I do also store the IV with the row.
In my case I do generate the IV by PHP with /dev/urandom as a random source.
Greetings,
Daniel Struck
--
Retrovirology Laboratory Luxembourg
Centre Hospitalier de Luxembourg
4, rue E. Barblé
L-1210 Luxembourg
phone: +352-44116105
fax: +352-44116113
web: http://www.retrovirology.lu
e-mail: struck.d@retrovirology.lu
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [PHP] Secure DB Systems - How to
@ 2004-07-13 12:38 Bruno Wolff III <bruno@wolff.to>
parent: Daniel Struck <struck.d@retrovirology.lu>
0 siblings, 1 reply; 17+ messages in thread
From: Bruno Wolff III @ 2004-07-13 12:38 UTC (permalink / raw)
To: Daniel Struck <struck.d@retrovirology.lu>; +Cc: Sarah Tanembaum <sarahtanembaum@yahoo.com>; pgsql-php@postgresql.org; pgsql-admin@postgresql.org; pgsql-hackers-win32@postgresql.org; pgadmin-support@postgresql.org; pgsql-sql
On Tue, Jul 13, 2004 at 11:35:57 +0200,
Daniel Struck <struck.d@retrovirology.lu> wrote:
> > Keeping the system administrator from seeing the data while making it
> > searchable is difficult. To do this you need to encrypt the data on
> > the client side using a key the client has (and this key has to be
> > protected from loss) and the only searches you can do are equality
> > searches using a hash or encrypted value.
>
> You can also perform regex searches.
If you decrypt the data on the database, the sysadmin can see it.
If you are willing to take that chance (e.g. if you primary concern is
some third party getting a snapshot of the DB), then you can do lots of
things.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [PHP] Secure DB Systems - How to
@ 2004-07-13 13:18 Daniel Struck <struck.d@retrovirology.lu>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 17+ messages in thread
From: Daniel Struck @ 2004-07-13 13:18 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: Sarah Tanembaum <sarahtanembaum@yahoo.com>; pgsql-php@postgresql.org; pgsql-admin@postgresql.org; pgsql-hackers-win32@postgresql.org; pgadmin-support@postgresql.org; pgsql-sql
> 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?
> If you are willing to take that chance (e.g. if you primary concern is
> some third party getting a snapshot of the DB), then you can do lots of
> things.
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?
Greetings,
Daniel Struck
--
Retrovirology Laboratory Luxembourg
Centre Hospitalier de Luxembourg
4, rue E. Barblé
L-1210 Luxembourg
phone: +352-44116105
fax: +352-44116113
web: http://www.retrovirology.lu
e-mail: struck.d@retrovirology.lu
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [ADMIN] [PHP] Secure DB Systems - How to
@ 2004-07-13 14:06 Mitch Pirtle <mitchy@spacemonkeylabs.com>
parent: Daniel Struck <struck.d@retrovirology.lu>
0 siblings, 1 reply; 17+ messages in thread
From: Mitch Pirtle @ 2004-07-13 14:06 UTC (permalink / raw)
To: Daniel Struck <struck.d@retrovirology.lu>; +Cc: Bruno Wolff III <bruno@wolff.to>; Sarah Tanembaum <sarahtanembaum@yahoo.com>; pgsql-php@postgresql.org; pgsql-admin@postgresql.org; pgsql-hackers-win32@postgresql.org; pgadmin-support@postgresql.org; pgsql-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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Secure DB Systems - How to
@ 2004-07-15 02:44 Christopher Browne <cbbrowne@acm.org>
parent: Rajesh Kumar Mallah <mallah@trade-india.com>
1 sibling, 0 replies; 17+ messages in thread
From: Christopher Browne @ 2004-07-15 02:44 UTC (permalink / raw)
To: pgsql-sql
In the last exciting episode, mallah@trade-india.com (Rajesh Kumar Mallah) wrote:
> Sarah Tanembaum wrote:
>
>>I was wondering if it is possible to create a secure database system
>>usingPostgreSQL/PHP combination?
>>
>>I have the following in mind:
>>
>>I wanted to store all my( and my brothers and sisters) important document
>>information such as birth certificate, SSN, passport number, travel
>>documents, insurance(car, home, etc) document, and other important documents
>>imagined in the database.
>>
>>The data will be entered either manually and/or scanned(with OCR). I need to
>>be able to search on all the fields in the database.
>>
>>We have 10 computers(5bros, 4sisters, and myself) plus 1 server with I
>>maintained. The data should be synchronize/replicate between those
>>computers.
>>
>>Well, so far it is easy, isn't it?
>>
>>Here's my question:
>>
>>a) How can I make sure that it secure so only authorized person can
>>modify/add/delete the information? Beside transaction logs, are there any
>>other method to trace any transaction(kind of paper trail)?
>>
>>
> There can be multiple solutions to your problem.
>
> The security and logging may be implemented either at
> database level or application level. That is a call you have to
> take.
Doing it at the database level means having to trust anyone that has
administrative access to the database system.
The only way for this to NOT require trusting the administrators is to
store data in some sort of encrypted form, where the data is NOT
visible except when someone decrypts it within the client application.
The main work published on the subject is _Translucent Databases_, by
Peter Wayner. Here are a bunch of links that give a pretty good idea
of what it's about.
http://www.oreillynet.com/pub/a/network/2002/08/02/simson.html
http://www.wayner.org/books/td/
http://www.wayner.org/books/td/faq.php
http://www.linux-mag.com/2003-12/databases_01.html
They discuss it from the perspective of using Java as the "client
application" layer; presumably PHP offers some cryptographic tools to
allow doing similar things...
http://hotwired.lycos.com/webmonkey/programming/php/tutorials/tutorial1.html
--
output = ("cbbrowne" "@" "ntlug.org")
http://cbbrowne.com/info/languages.html
"To do is to be." -- Aristotle
"To be is to do." -- Socrates
"Do be do be do." -- Sinatra
"Do be a do bee." -- Miss Sally of Romper Room fame.
"Yabba dabba do." -- Fred Flintstone
"DO...BEGIN..END" -- Niklaus Wirth
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [ADMIN] Secure DB Systems - How to
@ 2004-07-27 11:41 Daniel Struck <struck.d@retrovirology.lu>
parent: Mitch Pirtle <mitchy@spacemonkeylabs.com>
0 siblings, 1 reply; 17+ messages in thread
From: Daniel Struck @ 2004-07-27 11:41 UTC (permalink / raw)
To: Mitch Pirtle <mitchy@spacemonkeylabs.com>; +Cc: pgsql-php@postgresql.org
> Exactly. That is the only way to ensure that the data is never
> decrypted within the database or database server.
But then one will also lose the ability to make sql-queries with the encrypted columns . Something I wouldn't want to miss, or else it doesn't make sense to store this data in a database if one can't work with it.
I do send queries with the password within it, so I disabled the logging of sql-statements in postgresql to prevent the leakage of the password.
But the problem exists that the password is the available at several places.
As I am the only admin of the machine this shouldn't be such a big problem.
> 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 simply store a sha1 hash of the password in a configuration file, to verify that the user did enter the correct password, or else he would only see garbage ;-)
> I'm starting an article on doing just this for International PHP
> Magazine, and of course will use PostgreSQL as the back-end ;-)
Interesting, good that one can also order this editions online ;-)
> 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... :)
I have a different scenario; a couple of common sensitive information several users must have access to. So I have to go with only one password.
(At last their is a second layer of protection if someone steals the server or hard drives: I will encrypt the postgres files with dm_crypt. Right now I am testing it, and it seems to work fine so far.)
> during transmission of that data back to the client. SSL would be the
> most common approach to solving this problem.
I have tested two-way ssl authentication. The certificates for the clients are stored on smartcards accessible to the mozilla browser.
(I have written a small howto, you can find it on http://www.opensc.org/files/doc/apache-client-authentication(v0.5.1).pdf, but I haven't yet tested it with the latest release of opensc)
> the database if this method were used, rendering your efforts pointless!
True, if you find the solution I hope to find it in your article ;-)
Daniel
--
Retrovirology Laboratory Luxembourg
Centre Hospitalier de Luxembourg
4, rue E. Barblé
L-1210 Luxembourg
phone: +352-44116105
fax: +352-44116113
web: http://www.retrovirology.lu
e-mail: struck.d@retrovirology.lu
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [ADMIN] Secure DB Systems - How to
@ 2004-07-27 19:49 Bruno Wolff III <bruno@wolff.to>
parent: Daniel Struck <struck.d@retrovirology.lu>
0 siblings, 1 reply; 17+ messages in thread
From: Bruno Wolff III @ 2004-07-27 19:49 UTC (permalink / raw)
To: Daniel Struck <struck.d@retrovirology.lu>; +Cc: Mitch Pirtle <mitchy@spacemonkeylabs.com>; pgsql-php@postgresql.org
On Tue, Jul 27, 2004 at 13:41:33 +0200,
Daniel Struck <struck.d@retrovirology.lu> wrote:
> > Exactly. That is the only way to ensure that the data is never
> > decrypted within the database or database server.
>
> But then one will also lose the ability to make sql-queries with the encrypted columns . Something I wouldn't want to miss, or else it doesn't make sense to store this data in a database if one can't work with it.
That depends on the kind of queries. Searching for exact matches should
work fine. Some other things can be done in special cases.
If you are interested in this topic you may want to pick up a copy of
Translucent Databases.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [ADMIN] Secure DB Systems - How to
@ 2004-07-28 20:16 Greg Stark <gsstark@mit.edu>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 2 replies; 17+ messages in thread
From: Greg Stark @ 2004-07-28 20:16 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: Daniel Struck <struck.d@retrovirology.lu>; Mitch Pirtle <mitchy@spacemonkeylabs.com>; pgsql-php@postgresql.org
Bruno Wolff III <bruno@wolff.to> writes:
> That depends on the kind of queries. Searching for exact matches should work
> fine. Some other things can be done in special cases.
If searching for exact matches works then you're using a naive encryption
system. The problem is that it also means your database is vulnerable to
dictionary attacks. Good encryption systems will include random padding to
ensure that you can't attack it by merely guessing many possible plaintexts
and verifying to see if any match.
--
greg
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [ADMIN] Secure DB Systems - How to
@ 2004-07-29 09:16 Daniel Struck <struck.d@retrovirology.lu>
parent: Greg Stark <gsstark@mit.edu>
1 sibling, 0 replies; 17+ messages in thread
From: Daniel Struck @ 2004-07-29 09:16 UTC (permalink / raw)
To: Greg Stark <gsstark@mit.edu>; +Cc: Bruno Wolff III <bruno@wolff.to>; Mitch Pirtle <mitchy@spacemonkeylabs.com>; pgsql-php@postgresql.org
> If searching for exact matches works then you're using a naive encryption
> system. The problem is that it also means your database is vulnerable to
> dictionary attacks. Good encryption systems will include random padding to
> ensure that you can't attack it by merely guessing many possible plaintexts
> and verifying to see if any match.
To prevent this, I use in my implementation for every encrypted value a corresponding IV to prevent that the same value will give the same crypted text.
This is a reason, why I must do pattern searches in postgresql itself, because I do need to include the IV-column in the sql statement.
Daniel
--
Retrovirology Laboratory Luxembourg
Centre Hospitalier de Luxembourg
4, rue E. Barblé
L-1210 Luxembourg
phone: +352-44116105
fax: +352-44116113
web: http://www.retrovirology.lu
e-mail: struck.d@retrovirology.lu
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [ADMIN] Secure DB Systems - How to
@ 2004-07-29 18:00 Bruno Wolff III <bruno@wolff.to>
parent: Greg Stark <gsstark@mit.edu>
1 sibling, 1 reply; 17+ messages in thread
From: Bruno Wolff III @ 2004-07-29 18:00 UTC (permalink / raw)
To: Greg Stark <gsstark@mit.edu>; +Cc: Daniel Struck <struck.d@retrovirology.lu>; Mitch Pirtle <mitchy@spacemonkeylabs.com>; pgsql-php@postgresql.org
On Wed, Jul 28, 2004 at 16:16:10 -0400,
Greg Stark <gsstark@mit.edu> wrote:
>
> Bruno Wolff III <bruno@wolff.to> writes:
>
> > That depends on the kind of queries. Searching for exact matches should work
> > fine. Some other things can be done in special cases.
>
> If searching for exact matches works then you're using a naive encryption
> system. The problem is that it also means your database is vulnerable to
> dictionary attacks. Good encryption systems will include random padding to
> ensure that you can't attack it by merely guessing many possible plaintexts
> and verifying to see if any match.
IVs act to make the key appear longer. This is especially useful when humans
are picking passphrases that are used to generate the key. If you control
what the actual keys are, then you can make dictionary attacks impractical.
However, there still would be the problem that identical items in the
database would be identical. Which, depnding on your application, might
be a problem because of information leakage.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [ADMIN] Secure DB Systems - How to
@ 2004-07-30 09:43 Daniel Struck <struck.d@retrovirology.lu>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 17+ messages in thread
From: Daniel Struck @ 2004-07-30 09:43 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: Greg Stark <gsstark@mit.edu>; Mitch Pirtle <mitchy@spacemonkeylabs.com>; pgsql-php@postgresql.org
> IVs act to make the key appear longer. This is especially useful when humans
> are picking passphrases that are used to generate the key. If you control
> what the actual keys are, then you can make dictionary attacks impractical.
> However, there still would be the problem that identical items in the
> database would be identical. Which, depnding on your application, might
> be a problem because of information leakage.
I don't think this is true for CBC mode. Here the first block of plaintext is XORed with the IV then encrypted with a block ciper like AES for example. The next plaintext block is than XORed with the previous cyphertext and so on.
So IV in CBC is used to make the encrypted cyphertext unique.
You can find some info in chapter 9.3 of "Appplied Cryptopgraphy" from Bruce Schneier.
In my application I do use a different IV for every encrypted plaintext:
$td = mcrypt_module_open('rijndael-128', '', 'cbc', '');
$iv = base64_encode(mcrypt_create_iv(mcrypt_enc_get_iv_size($td), MCRYPT_DEV_URANDOM));
I do use base64 as it is easier to store it in the database.
The plaintext is then encrypted using:
$ciphertext = "encrypt_iv('" . $plaintext . "','" . $password] . "',decode('$iv','base64'),'aes')";
The IV is then stored together with the ciphertext in the database.
Daniel
--
Retrovirology Laboratory Luxembourg
Centre Hospitalier de Luxembourg
4, rue E. Barblé
L-1210 Luxembourg
phone: +352-44116105
fax: +352-44116113
web: http://www.retrovirology.lu
e-mail: struck.d@retrovirology.lu
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [ADMIN] Secure DB Systems - How to
@ 2004-07-30 15:30 Bruno Wolff III <bruno@wolff.to>
parent: Daniel Struck <struck.d@retrovirology.lu>
0 siblings, 0 replies; 17+ messages in thread
From: Bruno Wolff III @ 2004-07-30 15:30 UTC (permalink / raw)
To: Daniel Struck <struck.d@retrovirology.lu>; +Cc: Greg Stark <gsstark@mit.edu>; Mitch Pirtle <mitchy@spacemonkeylabs.com>; pgsql-php@postgresql.org
On Fri, Jul 30, 2004 at 11:43:31 +0200,
Daniel Struck <struck.d@retrovirology.lu> wrote:
> > IVs act to make the key appear longer. This is especially useful when humans
> > are picking passphrases that are used to generate the key. If you control
> > what the actual keys are, then you can make dictionary attacks impractical.
> > However, there still would be the problem that identical items in the
> > database would be identical. Which, depnding on your application, might
> > be a problem because of information leakage.
I should have stated the above a bit differently. IVs are probably more used
to prevent the information leakage from have the same plain text encode
to the same cipher text when using the same, then for protecting users from
using poorly chosen keys.
> I don't think this is true for CBC mode. Here the first block of plaintext is XORed with the IV then encrypted with a block ciper like AES for example. The next plaintext block is than XORed with the previous cyphertext and so on.
In the context of records in a database you aren't (at least not normally)
going to treat multiple records as part of the same cipher stream, so that
using various types of block chaining over say a whole table isn't going to
happen.
> In my application I do use a different IV for every encrypted plaintext:
That is certainly a reasonable approach, though there may be cases where
it is useful to trade some information leakage for the ability to use
indexes while doing all decryption on the client.
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2004-07-30 15:30 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-07-08 15:49 Secure DB Systems - How to Sarah Tanembaum <sarahtanembaum@yahoo.com>
2004-07-09 15:27 ` Bruno Wolff III <bruno@wolff.to>
2004-07-12 21:48 ` Sarah Tanembaum <sarahtanembaum@yahoo.com>
2004-07-13 09:35 ` Daniel Struck <struck.d@retrovirology.lu>
2004-07-13 12:38 ` Bruno Wolff III <bruno@wolff.to>
2004-07-13 13:18 ` Daniel Struck <struck.d@retrovirology.lu>
2004-07-13 14:06 ` Mitch Pirtle <mitchy@spacemonkeylabs.com>
2004-07-27 11:41 ` Daniel Struck <struck.d@retrovirology.lu>
2004-07-27 19:49 ` Bruno Wolff III <bruno@wolff.to>
2004-07-28 20:16 ` Greg Stark <gsstark@mit.edu>
2004-07-29 09:16 ` Daniel Struck <struck.d@retrovirology.lu>
2004-07-29 18:00 ` Bruno Wolff III <bruno@wolff.to>
2004-07-30 09:43 ` Daniel Struck <struck.d@retrovirology.lu>
2004-07-30 15:30 ` Bruno Wolff III <bruno@wolff.to>
2004-07-12 14:07 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2004-07-12 22:09 ` Sarah Tanembaum <sarahtanembaum@yahoo.com>
2004-07-15 02:44 ` Christopher Browne <cbbrowne@acm.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox