Thread: "hide" values in a column
Background: The maniacs I work for want an e-suggestion box. The identity of the suggestor must not be known, but the suggestion box administrator must be able to respond privately back to that person so I can't just completely leave out the identity of a suggestor against his/her suggestion. The suggestor is meant to be have complete anonymity. The real question: How do I completely "hide"/encrypt the values in the emp_id column even from me (who would be using psql) without the easy possibility of decoding it? CREATE TABLE suggestions ( emp_id INTEGER, dtm TIMESTAMP, comment TEXT, private_response BOOL, response TEXT, );
On Wed, 2003-03-12 at 16:12, Valerie Goodman wrote: > Background: The maniacs I work for want an e-suggestion box. The identity > of the suggestor must not be known, but the suggestion box administrator > must be able to respond privately back to that person so I can't just > completely leave out the identity of a suggestor against his/her suggestion. > The suggestor is meant to be have complete anonymity. > > The real question: How do I completely "hide"/encrypt the values in the > emp_id column even from me (who would be using psql) without the easy > possibility of decoding it? Write something in C, load it as a function. Then call it when inserting data into that field. I suggest using GPG and encrypting via a public key. This ensures only the person with the private key can decrypt the information. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
Valerie Goodman wrote: > Background: The maniacs I work for want an e-suggestion box. The identity > of the suggestor must not be known, but the suggestion box administrator > must be able to respond privately back to that person so I can't just > completely leave out the identity of a suggestor against his/her suggestion. > The suggestor is meant to be have complete anonymity. > > The real question: How do I completely "hide"/encrypt the values in the > emp_id column even from me (who would be using psql) without the easy > possibility of decoding it? > > CREATE TABLE suggestions ( > emp_id INTEGER, > dtm TIMESTAMP, > comment TEXT, > private_response BOOL, > response TEXT, > ); The easiest way to hide something is to create a view without some columns and revoke privileges from a table. Another way to do this is to create a pl/pgsql function with creator access level. After this - revoke necessary privileges from all other users. Anyway - I think you can't hide anything from table creator. Maybe you need to answer not to suggestor, but to suggestion? You won't need identity of sugestor at all. It sounds strange, that you want to answer privately to someone who should have anonymity... Regards, Tomasz Myrta
Web based? Email based? Other? You have all sorts of problems since identities could be discerned from careful reading of web or email logs, looking at IP addresses, or even sniffing on the wire. The amount of effort you put in to ensuring anonamity will be dictated by budget and paranoia level. I suspect your best bet may be to make it web based and allow people to pick an anonymous handle and password (or several if they are paranoid). The administrator could reply to that handle and the person could log in to submit new suggestions or to check for replies. Other things that could be used: https to limit wire-sniffing attacks, no logging or sending any relevant logs to /dev/null, etc. I'm afraid that if you are using employee IDs in a column you are SOL. Even a trapdoor encryption will be of little use since you can easily use a dictionary attack (encrypt all the employee IDs with all allowed salt values and you will create a nice linking table of encrypted to unencrypted ids). With email you are really up a creek since you have little control over the servers through which the messages pass and you need a method of determining the correct email address in the first place. That method is so full of holes I wouldn't even try it. Cheers, Steve On Wednesday 12 March 2003 1:12 pm, Valerie Goodman wrote: > Background: The maniacs I work for want an e-suggestion box. The identity > of the suggestor must not be known, but the suggestion box administrator > must be able to respond privately back to that person so I can't just > completely leave out the identity of a suggestor against his/her > suggestion. The suggestor is meant to be have complete anonymity. > > The real question: How do I completely "hide"/encrypt the values in the > emp_id column even from me (who would be using psql) without the easy > possibility of decoding it? > > CREATE TABLE suggestions ( > emp_id INTEGER, > dtm TIMESTAMP, > comment TEXT, > private_response BOOL, > response TEXT, > ); > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
Even better than picking a handle... Create a random identifier for the suggestion (thread?) and disclose that identifier to the submittor after the suggestion is submitted. The respondor can respond to the suggestion with the random identifier. The person who made the suggestion will have to revisit the site, put in the random identifier and then the correspondance will be displayed. They can add to the dialog until the issue is resolved. This takes care of storing any personally identifiable info in the DB. The IP packet trail is another issue that is completely seperate from PostgreSQL... Suggestion, tho: use https for the web protocol. :) Take a look at "uniqueidentifier" on gborg. That might just be the ticket you're looking for. CG --- Steve Crawford <scrawford@pinpointresearch.com> wrote: > Web based? Email based? Other? You have all sorts of problems since > identities could be discerned from careful reading of web or email logs, > looking at IP addresses, or even sniffing on the wire. > > The amount of effort you put in to ensuring anonamity will be dictated by > budget and paranoia level. > > I suspect your best bet may be to make it web based and allow people to pick > an anonymous handle and password (or several if they are paranoid). The > administrator could reply to that handle and the person could log in to > submit new suggestions or to check for replies. > > Other things that could be used: https to limit wire-sniffing attacks, no > logging or sending any relevant logs to /dev/null, etc. > > I'm afraid that if you are using employee IDs in a column you are SOL. Even a > > trapdoor encryption will be of little use since you can easily use a > dictionary attack (encrypt all the employee IDs with all allowed salt values > and you will create a nice linking table of encrypted to unencrypted ids). > > With email you are really up a creek since you have little control over the > servers through which the messages pass and you need a method of determining > the correct email address in the first place. That method is so full of holes > > I wouldn't even try it. > > Cheers, > Steve > > > On Wednesday 12 March 2003 1:12 pm, Valerie Goodman wrote: > > Background: The maniacs I work for want an e-suggestion box. The identity > > of the suggestor must not be known, but the suggestion box administrator > > must be able to respond privately back to that person so I can't just > > completely leave out the identity of a suggestor against his/her > > suggestion. The suggestor is meant to be have complete anonymity. > > > > The real question: How do I completely "hide"/encrypt the values in the > > emp_id column even from me (who would be using psql) without the easy > > possibility of decoding it? > > > > CREATE TABLE suggestions ( > > emp_id INTEGER, > > dtm TIMESTAMP, > > comment TEXT, > > private_response BOOL, > > response TEXT, > > ); > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster __________________________________________________ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com