Re: [SQL] [PHP] Secure DB Systems - How to
On Thu, Jul 08, 2004 at 11:49:36 -0400, Sarah Tanembaum <[EMAIL PROTECTED]> 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
Re: [SQL] [PHP] Secure DB Systems - How to
> 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_contentBYTEA
);
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: [EMAIL PROTECTED]
---(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
Re: [SQL] [PHP] Secure DB Systems - How to
On Tue, Jul 13, 2004 at 11:35:57 +0200, Daniel Struck <[EMAIL PROTECTED]> 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. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] [PHP] Secure DB Systems - How to
> 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: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] [PHP] Secure DB Systems - How to
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" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > On Thu, Jul 08, 2004 at 11:49:36 -0400, > Sarah Tanembaum <[EMAIL PROTECTED]> 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 > ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
