I'm thinking of adding security to a database in the following way:

• Encrypt some of the more sensitive columns (password, e-mail, etc.) using blowfish, 3DES, etc. (not a hashing algorithm like MD5 because the data must be kept intact)
• Set a connection session variable that contains the private key
• For every request to write to and to read from an encrypted column, have PostgreSQL automatically recognize that the request is for encrypted data, and appropriate encrypt/decrypt the request/response using the previously set private key.

This way, the appropriate fields in the database are encrypted, so if the entire database is stolen or broken into, it's useless. Because the private key is set through a session variable and not actually stored anywhere in the database, the data is still effectively unreadable.

However, I don't know if that last bullet point is doable. Can PostgreSQL or other RDBMSs "autobox" data like that (convert data from one form to another without explicitly being told to)?

As a final example, this query:
...would implicitly update user ID 1's password to an encrypted form of 'foo' rather than the literal text 'foo.' Similarly, this query:
...would implicitly decrypt and return the password for user ID 1 rather than return his encrypted password.

So, possible?

Er... But what happens if the session expires? Where does the private key reside in permanence? (I know I'm not addressing your direct question, but I'm trying to grasp how you want to make this work.)

It's the database session, not a PHP session. What would happen:

1. Database exists with encrypted data and no private key stored.
2. My PHP application needs to run a query, so it connects to the database. A database connection session starts.
3. The application runs a query to set a variable for this connection session containing the private key.
4. The application runs whatever queries it needs for the page.
5. The connection closes, and implicitly in the process, the session variable is destroyed.

Therefore, the private key only exists during the life of the connection, so the only point of attack is through the application itself rather than directly through PostgreSQL.

So you're storing the private key alongside the application on the web server, then? If so, what makes you think that a compromised database server doesn't mean a compromised web server?

I can't really think of another way of storing the private key, which is the core problem.

Well, yeah, that's always the problem Public-key cryptography operates on the assumption that the private key is kept under lock and key at all times. I'm not so sure it's suited to this particular class of problem. It seems like in this case using encryption would be more of a discouragement than a preventative measure. Sure, the person who's just ripping along doing whatever won't be able to get the data, but the guy who really wants to will. And the guy who's just doing whatever can be discouraged just as easily by a simpler method... Maybe even as simple as ROT-13

Nonetheless, to address your original question -- I don't know of a way to do that, no. Though depending on Postgre's extensibility (I don't know what it is), maybe you could write a plugin or something?

Hash the password. There's no reason not to.

As for email... if you must encrypt it, why not just store the key as a constant in your code?

Maybe you should begin your security dilemma by eliminating the ability of people stealing your database.

Oh, btw, if this is in Java... just make an "Crypt" class that has static methods to encrypt and decrypt data... Crypt.encrypt() and Crypt.decrypt(). Put your key in there as a constant, compile it, and lose the source. Just don't forget the key!!!

It's PHP.

Obviously this is an extra layer of protection on top of other security. If I could 100% ensure that the database could never be accessed, I wouldn't even bother to encrypt things like passwords, but this is just extra insurance.

(and things like e-mails can't be hashed, unlike passwords)

Not to mention the fact that reverse-engineering compiled Java code and producing almost identical source-code is generally less-than-difficult. If someone's determined enough to go looking for a private key, then they're probably determined enough to reverse-engineer Java.

Nonetheless, if you were to take that approach, you could just as easily use it from PHP by running the external Java application to do the encrypting/decrypting.

I see why you'd do this -- especially with personal information like email. But I'm not sure that it will actually offer a significant security increase. It seems similar to how DRM in HDDVDs/Blu-Ray uses a private key that's already in the hands of the user in the form of a player for the relevant format. If the person can get to the key (and it's fair to assume that if they got to your database, they can probably get to the key)... That's it. It's really down to whether they're interested enough to decrypt the data, and if they aren't, then even simple encryption will likely discourage them. But then, I may be completely wrong.

Blu-Ray/HD-DVD was cracked because they managed to get a working key out of some playback software, not because (I believe) it can crack the actual encryption on the disk; rather, it is just using a known key to retrieve the video stream just like a player would. And normal DVDs are hackable because they use a short key.

This would use a 256-bit key combined with some strong encryption algorithm that would take years to brute force. The issue is just securing the private key in the first place, which as you mentioned, is a problem for any encryption situation.

But all that aside, is the main question of this thread--PostgreSQL autoboxing--possible?

Right. I was saying the encryption became worthless because the key was handed to the user, as is necessary in such cases, so the user didn't *have* to crack it, they could just extract the key (admittedly not the easiest process, but clearly easy enough) from their playback system. Once that's done once, you release it in the wild and... The rest is history.