Re: Submission of Feature Request : RFC- for Implementing Transparent Data Encryption in P

sanjay sharma wrote:
> However there are certain fetures which are becoming key for putting postgres in areas where strong regulatory compliance is required.TDE is very helpful in storing data where there is strict privacy compliance requirement for example e.Government and e.Health. All columns of personal profile/health data do not need same level of security for all users and applications. Selective data encryption is very handy in an architecture where different applications are pulling data from a central data repository for processing and presenting to their users or where different users are changing different part of data set in central repository. These departmental applications may contain keys for decrypting and looking at only those columns needed by their users. Encrypting just needed column takes care of compliance requirement down the line in backups and archives.

You could implement that using views and contrib/pgcrypto. Create a view
on the underlying table that encrypts/decrypts the data on access.

I'm not sure who the encryption is supposed to protect from in this
scenario. From the superuser of the database server? It isn't really
suitable for that: the way you describe it, the encryption/decryption is
done in the server, so a malicious superuser that has full access to the
server can still capture the data before it's encrypted, and can also
recover the key from the running server, by crawling through system
memory or installing hacked software to print it out.

It's better than nothing, as it does protect from a casual non-malicious
observer, and it does protect the backups, but what I'd rather see is a
system where the database server never sees the data in plaintext. You
could do the encryption/decryption in the client, perhaps in the driver
so that it's transparent to the application.

I'm not familiar with the compliance requirements you refer to. What
exactly is required?

> Another area where I would like to put a RFC is Auditing. A flag at the database level (conf file) or in DDL which puts audit columns ( created_by, creation_date, last_updated_by, last_update_date) on tables and automatically populates them would be a very nice standard feature. Currently this needs code/trigger to be duplicated at each table which is a big grunt. At furthur higher level a way to audit data access/view for regulatory complinace like HIPPA is also needed.This should not be copy of Oracle FGA which has its own limitations.

This could be implemented fairly easily as an external tool that queries
the system catalogs, and adds the required columns and triggers.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com