Re: [HACKERS] column level privileges
It would be great help to me, and I am sure for many other people too who are working with security solutions, if this feature is released as patch before 8.4 release. Sanjay Sharma> Date: Tue, 1 Apr 2008 22:02:30 -0400> From: [EMAIL PROTECTED]> To: [EMAIL PROTECTED]> CC: pgsql-hackers@postgresql.org> Subject: Re: [HACKERS] column level privileges> > > > The earliest will be 8.4, which is many many months away.> > It should be possible to produce a patch for 8.3 if you're interested.> > cheers> > andrew> > sanjay sharma wrote:> > Hello Andrew,> > > > When do you expect this patch to go in production and available for > > public use? I would keep an eye for its release.> > > > Sanjay Sharma> >> > > Date: Tue, 1 Apr 2008 18:40:24 -0400> > > From: [EMAIL PROTECTED]> > > To: pgsql-hackers@postgresql.org> > > Subject: [HACKERS] column level privileges> > >> > >> > > Apologies if this gets duplicated - original seems to have been dropped> > > due to patch size - this time I am sending it gzipped.> > >> > > cheers> > >> > > andrew> > >> > > Original Message > > > Subject: column level privileges> > > Date: Tue, 01 Apr 2008 08:32:25 -0400> > > From: Andrew Dunstan <[EMAIL PROTECTED]>> > > To: Patches (PostgreSQL) <[EMAIL PROTECTED]>> > >> > >> > >> > > This patch by Golden Lui was his work for the last Google SoC. I was > > his> > > mentor for the project. I have just realised that he didn't send his> > > final patch to the list.> > >> > > I guess it's too late for the current commit-fest, but it really needs> > > to go on a patch queue (my memory on this was jogged by Tom's recent> > > mention of $Subject).> > >> > > I'm going to see how much bitrot there is and see what changes are> > > necessary to get it to apply.> > >> > > cheers> > >> > > andrew> > >> > >> > > -> > > Here is a README for the whole patch.> > >> > > According to the SQL92 standard, there are four levels in the privilege> > > hierarchy, i.e. database, tablespace, table, and column. Most > > commercial> > > DBMSs support all the levels, but column-level privilege is hitherto> > > unaddressed in the PostgreSQL, and this patch try to implement it.> > >> > > What this patch have done:> > > 1. The execution of GRANT/REVOKE for column privileges. Now only> > > INSERT/UPDATE/REFERENCES privileges are supported, as SQL92 specified.> > > SELECT privilege is now not supported. This part includes:> > > 1.1 Add a column named 'attrel' in pg_attribute catalog to store> > > column privileges. Now all column privileges are stored, no matter> > > whether they could be implied from table-level privilege.> > > 1.2 Parser for the new kind of GRANT/REVOKE commands.> > > 1.3 Execution of GRANT/REVOKE for column privileges. Corresponding> > > column privileges will be added/removed automatically if no column is> > > specified, as SQL standard specified.> > > 2. Column-level privilege check.> > > Now for UPDATE/INSERT/REFERENCES privilege, privilege check will be> > > done ONLY on column level. Table-level privilege check was done in the> > > function InitPlan. Now in this patch, these three kind of privilege are> > > checked during the parse phase.> > > 2.1 For UPDATE/INSERT commands. Privilege check is done in the> > > function transformUpdateStmt/transformInsertStmt.> > > 2.2 For REFERENCES, privilege check is done in the function> > > ATAddForeignKeyConstraint. This function will be called whenever a> > > foreign key constraint is added, like create table, alter table, etc.> > > 2.3 For COPY command, INSERT privilege is check in the function> > > DoCopy. SELECT command is checked in DoCopy too.> > > 3. While adding a new column to a table using ALTER TABLE command, set> > > appropriate privilege for the new column according to privilege already> > > granted on the table.> > > 4. Allow pg_dump and pg_dumpall to dump in/out column privileges.> > > 5. Add a column named objsubid in pg_shdepend catalog to record ACL> > > dependencies between column and roles.> > > 6. modify the grammar of ECPG to support column level privileges.> > > 7. change psql's \z (\d
Re: [HACKERS] [GENERAL] SHA1 on postgres 8.3
Hi Tom, md5 is not being recommended anywhere because it contains hash collision. Therefore either it should be replaced with SHA1 or any other good hash algorithm or taken out of core completely. md5 in core is worthless now.I am not using it in my application. I am using SHA1 in client/web tier for password hashing. Would replacing md5 with SHA1 in core involve much work? Sanjay Sharma> To: [EMAIL PROTECTED]> CC: pgsql-hackers@postgresql.org> Subject: Re: [HACKERS] [GENERAL] SHA1 on postgres 8.3 > Date: Wed, 2 Apr 2008 11:38:31 -0400> From: [EMAIL PROTECTED]> > "Greg Sabino Mullane" <[EMAIL PROTECTED]> writes:> > I don't agree that we should just close discussion. Nobody seems happy> > with the status quo, which is that we provide md5 but not sha1,> > There may be a few people who are unhappy, but the above claim seems> vastly overblown. md5 is sufficient for the purpose it is intended> for in core postgres (namely, obscuring the true source text of> passwords), and if you have needs much beyond that you'll soon be> installing pgcrypto anyway.> > regards, tom lane> > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)> To make changes to your subscription:> http://www.postgresql.org/mailpref/pgsql-hackers _ Tried the new MSN Messenger? It’s cool! Download now. http://messenger.msn.com/Download/Default.aspx?mkt=en-in
Re: [HACKERS] column level privileges
Hello Andrew, When do you expect this patch to go in production and available for public use? I would keep an eye for its release. Sanjay Sharma> Date: Tue, 1 Apr 2008 18:40:24 -0400> From: [EMAIL PROTECTED]> To: pgsql-hackers@postgresql.org> Subject: [HACKERS] column level privileges> > > Apologies if this gets duplicated - original seems to have been dropped > due to patch size - this time I am sending it gzipped.> > cheers> > andrew> > Original Message > Subject: column level privileges> Date: Tue, 01 Apr 2008 08:32:25 -0400> From: Andrew Dunstan <[EMAIL PROTECTED]>> To: Patches (PostgreSQL) <[EMAIL PROTECTED]>> > > > This patch by Golden Lui was his work for the last Google SoC. I was his > mentor for the project. I have just realised that he didn't send his > final patch to the list.> > I guess it's too late for the current commit-fest, but it really needs > to go on a patch queue (my memory on this was jogged by Tom's recent > mention of $Subject).> > I'm going to see how much bitrot there is and see what changes are > necessary to get it to apply.> > cheers> > andrew> > > -> Here is a README for the whole patch.> > According to the SQL92 standard, there are four levels in the privilege > hierarchy, i.e. database, tablespace, table, and column. Most commercial > DBMSs support all the levels, but column-level privilege is hitherto > unaddressed in the PostgreSQL, and this patch try to implement it.> > What this patch have done:> 1. The execution of GRANT/REVOKE for column privileges. Now only > INSERT/UPDATE/REFERENCES privileges are supported, as SQL92 specified. > SELECT privilege is now not supported. This part includes:> 1.1 Add a column named 'attrel' in pg_attribute catalog to store > column privileges. Now all column privileges are stored, no matter > whether they could be implied from table-level privilege.> 1.2 Parser for the new kind of GRANT/REVOKE commands.> 1.3 Execution of GRANT/REVOKE for column privileges. Corresponding > column privileges will be added/removed automatically if no column is > specified, as SQL standard specified.> 2. Column-level privilege check.> Now for UPDATE/INSERT/REFERENCES privilege, privilege check will be > done ONLY on column level. Table-level privilege check was done in the > function InitPlan. Now in this patch, these three kind of privilege are > checked during the parse phase.> 2.1 For UPDATE/INSERT commands. Privilege check is done in the > function transformUpdateStmt/transformInsertStmt.> 2.2 For REFERENCES, privilege check is done in the function > ATAddForeignKeyConstraint. This function will be called whenever a > foreign key constraint is added, like create table, alter table, etc.> 2.3 For COPY command, INSERT privilege is check in the function > DoCopy. SELECT command is checked in DoCopy too.> 3. While adding a new column to a table using ALTER TABLE command, set > appropriate privilege for the new column according to privilege already > granted on the table.> 4. Allow pg_dump and pg_dumpall to dump in/out column privileges.> 5. Add a column named objsubid in pg_shdepend catalog to record ACL > dependencies between column and roles.> 6. modify the grammar of ECPG to support column level privileges.> 7. change psql's \z (\dp) command to support listing column privileges > for tables and views. If \z(\dp) is run with a pattern, column > privileges are listed after table level privileges.> 8. Regression test for column-level privileges. I changed both > privileges.sql and expected/privileges.out, so regression check is now > all passed.> > Best wishes> Dong> -- > Guodong Liu> Database Lab, School of EECS, Peking University> Room 314, Building 42, Peking University, Beijing, 100871, China> > _ Technology : Catch up on updates on the latest Gadgets, Reviews, Gaming and Tips to use technology etc. http://computing.in.msn.com/
Re: [HACKERS] Submission of Feature Request : RFC- for Implementing Transparent Data Encryption in P
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: [EMAIL PROTECTED]> To: [EMAIL > PROTECTED]> CC: [EMAIL PROTECTED]; pgsql-hackers@postgresql.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/
Re: [HACKERS] Submission of Feature Request : RFC- for Implementing Transparent Data Encryption in P
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: [EMAIL PROTECTED]> To: [EMAIL > PROTECTED]> Subject: Re: [HACKERS] Submission of Feature Request : RFC- for > Implementing Transparent Data Encryption in Postgres> CC: > pgsql-hackers@postgresql.org> > On Sun, Mar 30, 2008 at 2:52 PM, sanjay > sharma <[EMAIL PROTECTED]> 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 | [EMAIL PROTECTED]> Edison, NJ 08837 | > http://www.enterprisedb.com/> > -- > Sent via pgsql-hackers mailing list > (pgsql-hackers@postgresql.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/
[HACKERS] 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/
