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

Hello All,

I would like to submit following feature request for Postgres:

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 apper in encrypted form for all users except the users specified through a grant to see the data in decrypted form.

I would like to hook-up with people who are working in the postgres security area to refine the feature detail and work on its implementation.

Please guide me how to go about it.

Sanjay Sharma
Victoria, Canada

_________________________________________________________________
Technology : Catch up on updates on the latest Gadgets, Reviews, Gaming and Tips to use technology etc.
http://computing.in.msn.com/

sanjay sharma <sanksh(at)hotmail(dot)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 apper in 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?

regards, tom lane

On Sun, Mar 30, 2008 at 4:36 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> sanjay sharma <sanksh(at)hotmail(dot)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 apper in 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(at)hotmail(dot)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(dot)harris(at)enterprisedb(dot)com
Edison, NJ 08837 | http://www.enterprisedb.com/

Hey Jonah,

You are correct. I have worked with oracle for a long time and only recently started working with Postgres. I am quite satisfied that Postgres is able to deliver me most of the features/ services which Oracle used to deliver but at much reduced cost.This is very helpful in pushing Postgres towards enterprise core infrastructure. 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.
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.
I welcome everyone to to send their vies on the issue.

Cheers

Sanjay
> Date: Sun, 30 Mar 2008 19:10:48 -0400> From: jonah(dot)harris(at)gmail(dot)com> To: sanksh(at)hotmail(dot)com> Subject: Re: [HACKERS] Submission of Feature Request : RFC- for Implementing Transparent Data Encryption in Postgres> CC: pgsql-hackers(at)postgresql(dot)org> > On Sun, Mar 30, 2008 at 2:52 PM, sanjay sharma <sanksh(at)hotmail(dot)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(dot)harris(at)enterprisedb(dot)com> Edison, NJ 08837 | http://www.enterprisedb.com/> > -- > Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)> To make changes to your subscription:> http://www.postgresql.org/mailpref/pgsql-hackers
_________________________________________________________________
Education: Are exams worrying you all the day long? Write to MSN education experts for help.
http://education.in.msn.com/

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

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.

Cheers

Sanjay Sharma


> Date: Mon, 31 Mar 2008 09:48:46 +0100> From: heikki(at)enterprisedb(dot)com> To: sanksh(at)hotmail(dot)com> CC: jonah(dot)harris(at)gmail(dot)com; pgsql-hackers(at)postgresql(dot)org> Subject: Re: [HACKERS] 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
_________________________________________________________________
Technology : Catch up on updates on the latest Gadgets, Reviews, Gaming and Tips to use technology etc.
http://computing.in.msn.com/

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(at)momjian(dot)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 :-)