Thread: Secure DB Systems - How to
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
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/ | +---------------------------------------------------+
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