Thread: Submission of Feature Request : RFC- for Implementing Transparent Data Encryption in Postgres

Hello All,<br />  <br /> I would like to submit following feature request for Postgres:<br />  <br /> 1. Transparent
DataEncryption: The column which needs to be stored in encrypted form can be specified through DDL. The encryption key
canbe stored in a  secure file accessible through a pass phrase. That particular column would apper in encrypted form
forall users except the users specified through a grant to see the data in decrypted form.<br />  <br /> I would like
tohook-up with people who are working in the postgres security area to refine the feature detail and work on its
implementation.<br/>  <br /> Please guide me how to go about it.<br />  <br /> Sanjay Sharma<br /> Victoria, Canada<br
/> <br /><br /><hr />Exclusive Marriage Proposals! Find UR life partner at Shaadi.com <a
href="http://ss1.richmedia.in/recurl.asp?pid=430"target="_new">Try it!</a> 

sanjay sharma <sanksh@hotmail.com> writes:
> 1. Transparent Data Encryption: The column which needs to be stored in encrypted form can be specified through DDL.
Theencryption key can be stored in a  secure file accessible through a pass phrase. That particular column would apper
inencrypted form for all users except the users specified through a grant to see the data in decrypted form.
 

Exactly what threat do you see this protecting against, that wouldn't be
better solved by SQL-standard features like column-level access
permissions?
        regards, tom lane

On Sun, Mar 30, 2008 at 4:36 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> sanjay sharma <sanksh@hotmail.com> writes:
>  > 1. Transparent Data Encryption: The column which needs to be stored in encrypted form can be specified through
DDL.The encryption key can be stored in a  secure file accessible through a pass phrase. That particular column would
apperin encrypted form for all users except the users specified through a grant to see the data in decrypted form.
 
>
>  Exactly what threat do you see this protecting against, that wouldn't be
>  better solved by SQL-standard features like column-level access
>  permissions?

Yes.  And if you're concerned about people getting access to the raw
data files, put $PGDATA on an encrypted partition.

-Doug

On Sun, Mar 30, 2008 at 2:52 PM, sanjay sharma <sanksh@hotmail.com> wrote:
>  1. Transparent Data Encryption: The column which needs to be stored in
> encrypted form can be specified through DDL.

Hey Sanjay.  Based on your wording, you've probably used Oracle's TDE
and want to implement it in PG.  Unfortunately, nine times out of ten,
cool Oracle features aren't seen as cool in this crowd.  Looking at
your responses, there's an obvious misunderstanding in regard to
security (column-level access != encryption), and of performance
(encrypt the whole thing and pay a heavy price on *all* accesses
instead of only granular accesses to only the column(s) you're
encrypting).

Regardless, if you want to get a feature into PG, you need to first
come up with a good reason for it, get people behind the idea, and
then come up with a plan to implement it.

-- 
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
Edison, NJ 08837 | http://www.enterprisedb.com/

Hey Jonah,<br />  <br /> You are correct. I have worked with oracle for a long time and only recently started working
withPostgres. I am quite satisfied that Postgres is able to deliver me most of the features/ services which Oracle used
todeliver but at much reduced cost.This is very helpful in pushing Postgres towards enterprise core infrastructure.
Howeverthere are certain fetures which are becoming key for putting postgres in areas where strong regulatory
complianceis required.<br />TDE is very helpful in storing data where there is strict privacy compliance requirement
forexample e.Government and e.Health. All columns of personal profile/health data do not need same level of security
forall users and applications. Selective data encryption is very handy in an architecture where different applications
arepulling data from a central data repository for processing and presenting to their users or where different users
arechanging different 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.<br /> 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.
Currentlythis needs code/trigger to be duplicated at each table which is a big grunt. At furthur higher level a way to
auditdata access/view for regulatory complinace like HIPPA is also needed.This should not be copy of Oracle FGA which
hasits own limitations. <br /> I welcome everyone to to send their vies on the issue.<br />  <br /> Cheers<br />  <br
/>Sanjay<br /><br /><br />> Date: Sun, 30 Mar 2008 19:10:48 -0400<br />> From: jonah.harris@gmail.com<br />>
To:sanksh@hotmail.com<br />> Subject: Re: [HACKERS] Submission of Feature Request : RFC- for Implementing
TransparentData Encryption in Postgres<br />> CC: pgsql-hackers@postgresql.org<br />> <br />> On Sun, Mar 30,
2008at 2:52 PM, sanjay sharma <sanksh@hotmail.com> wrote:<br />> > 1. Transparent Data Encryption: The
columnwhich needs to be stored in<br />> > encrypted form can be specified through DDL.<br />> <br />> Hey
Sanjay.Based on your wording, you've probably used Oracle's TDE<br />> and want to implement it in PG.
Unfortunately,nine times out of ten,<br />> cool Oracle features aren't seen as cool in this crowd. Looking at<br
/>>your responses, there's an obvious misunderstanding in regard to<br />> security (column-level access !=
encryption),and of performance<br />> (encrypt the whole thing and pay a heavy price on *all* accesses<br />>
insteadof only granular accesses to only the column(s) you're<br />> encrypting).<br />> <br />> Regardless,
ifyou want to get a feature into PG, you need to first<br />> come up with a good reason for it, get people behind
theidea, and<br />> then come up with a plan to implement it.<br />> <br />> -- <br />> Jonah H. Harris,
Sr.Software Architect | phone: 732.331.1324<br />> EnterpriseDB Corporation | fax: 732.331.1301<br />> 499
ThornallStreet, 2nd Floor | jonah.harris@enterprisedb.com<br />> Edison, NJ 08837 | http://www.enterprisedb.com/<br
/>><br />> -- <br />> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)<br />> To make
changesto your subscription:<br />> http://www.postgresql.org/mailpref/pgsql-hackers<br /><br /><br /><hr />Windows
LiveMessenger : Get connected, share yourself, make a difference the way you chat. <a
href="http://get.live.com/messenger/overview" target="_new">Check it out!</a> 

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

Hello <span id="PresenceContainer">Heikki,</span><br /><span></span> <br /><span>Although the solution could be
implementedusing views and functions and I am implementing a reference application using this approach but TDE
can greatlyreduce the design and maintenance complexcity. It would also take care of data protection in backups and
archives.</span><br/><span>You are correct to identify that TDE may not provide complete data security required for
data likecredit crad details but TDE seems to be ideally suited to take care of data privacy issues. Major chunk of the
privatedata is of no interest to hackers and criminals but needs protection only from casual observers. To implement a
fulldata security infrastucture to protect only privacy issues seems to be overkill. Compliance requirement for storing
privatedata arises from each organizations own declared privacy policies and statutory bodies like privacy
commissionersand other privacy watchdogs. These standards are not as strict as PCI, HIPPA or Sarnabes-Oxley</span><br
/><span></span> <br/><span>Compliance with HIPPA regulation requires not only maintaining all records of who created
andupdated the record but also who accessed and viewed records, when and in what context.</span><br /><span></span> <br
/><span>Cheers</span><br/><span></span> <br /><span>Sanjay Sharma </span><br /><span> </span><br
/><span><strong></strong></span> <br/><br /><br />> Date: Mon, 31 Mar 2008 09:48:46 +0100<br />> From:
heikki@enterprisedb.com<br/>> To: sanksh@hotmail.com<br />> CC: jonah.harris@gmail.com;
pgsql-hackers@postgresql.org<br/>> Subject: Re: [HACKERS] Submission of Feature Request : RFC- for Implementing
TransparentData Encryption in P<br />> <br />> sanjay sharma wrote:<br />> > However there are certain
fetureswhich are becoming key for putting postgres in areas where strong regulatory compliance is required.TDE is very
helpfulin storing data where there is strict privacy compliance requirement for example e.Government and e.Health. All
columnsof personal profile/health data do not need same level of security for all users and applications. Selective
dataencryption is very handy in an architecture where different applications are pulling data from a central data
repositoryfor processing and presenting to their users or where different users are changing different part of data set
incentral repository. These departmental applications may contain keys for decrypting and looking at only those columns
neededby their users. Encrypting just needed column takes care of compliance requirement down the line in backups and
archives.<br/>> <br />> You could implement that using views and contrib/pgcrypto. Create a view <br />> on
theunderlying table that encrypts/decrypts the data on access.<br />> <br />> I'm not sure who the encryption is
supposedto protect from in this <br />> scenario. From the superuser of the database server? It isn't really <br
/>>suitable for that: the way you describe it, the encryption/decryption is <br />> done in the server, so a
malicioussuperuser that has full access to the <br />> server can still capture the data before it's encrypted, and
canalso <br />> recover the key from the running server, by crawling through system <br />> memory or installing
hackedsoftware to print it out.<br />> <br />> It's better than nothing, as it does protect from a casual
non-malicious<br />> observer, and it does protect the backups, but what I'd rather see is a <br />> system where
thedatabase server never sees the data in plaintext. You <br />> could do the encryption/decryption in the client,
perhapsin the driver <br />> so that it's transparent to the application.<br />> <br />> I'm not familiar with
thecompliance requirements you refer to. What <br />> exactly is required?<br />> <br />> > Another area
whereI 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
verynice standard feature. Currently this needs code/trigger to be duplicated at each table which is a big grunt. At
furthurhigher level a way to audit data access/view for regulatory complinace like HIPPA is also needed.This should not
becopy of Oracle FGA which has its own limitations. <br />> <br />> This could be implemented fairly easily as an
externaltool that queries <br />> the system catalogs, and adds the required columns and triggers.<br />> <br
/>>-- <br />> Heikki Linnakangas<br />> EnterpriseDB http://www.enterprisedb.com<br /><br /><br /><hr
/>ExclusiveMarriage Proposals! Find UR life partner at Shaadi.com <a href="http://ss1.richmedia.in/recurl.asp?pid=430"
target="_new">Tryit!</a> 

sanjay sharma wrote:
> 
> Hello Heikki,
> 
> Although the solution could be implemented using views and
> functions and I am implementing a reference application using
> this approach but TDE can greatly reduce the design and maintenance
> complexcity. It would also take care of data protection in
> backups and archives.  You are correct to identify that TDE may
> not provide complete data security required for data like credit
> crad details but TDE seems to be ideally suited to take care of
> data privacy issues. Major chunk of the private data is of no
> interest to hackers and criminals but needs protection only from
> casual observers. To implement a full data security infrastucture
> to protect only privacy issues seems to be overkill. Compliance
> requirement for storing private data arises from each organizations
> own declared privacy policies and statutory bodies like privacy
> commissioners and other privacy watchdogs. These standards are
> not as strict as PCI, HIPPA or Sarnabes-Oxley
> 
> Compliance with HIPPA regulation requires not only maintaining
> all records of who created and updated the record but also who
> accessed and viewed records, when and in what context.

Agreed, the bottom line is that the tools needed to do what you want are
there, but they are probably more complex to implement than in Oracle. 
We probably offer fewer canned solutions than Oracle, but more
flexibility.

-- Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +

On Mon, 2008-03-31 at 18:21 +0000, sanjay sharma wrote:
> overkill. Compliance requirement for storing private data arises from
> each organizations own declared privacy policies and statutory bodies
> like privacy commissioners and other privacy watchdogs. These
> standards are not as strict as PCI, HIPPA or Sarnabes-Oxley
>  
> Compliance with HIPPA regulation requires not only maintaining all
> records of who created and updated the record but also who accessed
> and viewed records, when and in what context.

you'd be much better served in this case by implementing se-postgresql
with an appropriate policy...
it would allow you to do all that you need and more :-)