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
complianceis required.TDE is very helpful in storing data where there is strict privacy compliance requirement for
examplee.Government and e.Health. All columns of personal profile/health data do not need same level of security for
allusers and applications. Selective data encryption is very handy in an architecture where different applications are
pullingdata from a central data repository for processing and presenting to their users or where different users are
changingdifferent part of data set in central repository. These departmental applications may contain keys for
decryptingand looking at only those columns needed by their users. Encrypting just needed column takes care of
compliancerequirement 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
putsaudit columns ( created_by, creation_date, last_updated_by, last_update_date) on tables and automatically populates
themwould be a very nice  standard feature. Currently this needs code/trigger to be duplicated at each table which is a
biggrunt. At furthur higher level a way to audit data access/view for regulatory complinace like HIPPA is also
needed.Thisshould 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