Thread: [GENERAL] pg_audit to mask literal sql
Is there a way in pgaudit to mask literal sqls like the below: insert into table (col1,col2) values(1,2) select * from table where col1 = 1 These sqls are typed by our QA folks using pgadmin. pgaudit records this verbatim which runs afoul of our HIPAA requirement. Prepared statements are not an issue since pgaudit provides a way to suppress values. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Mon, Oct 30, 2017 at 10:55:17AM -0700, rakeshkumar464 wrote: > Is there a way in pgaudit to mask literal sqls like the below: > > insert into table (col1,col2) values(1,2) > select * from table where col1 = 1 > > These sqls are typed by our QA folks using pgadmin. pgaudit records this > verbatim which runs afoul of our HIPAA requirement. Prepared statements are > not an issue since pgaudit provides a way to suppress values. > I doubt that. But I'm not sure that I understood you correctly. What do you mean by "mask"? Some additional examples may be useful too. -- Arthur Zakirov Postgres Professional: http://www.postgrespro.com Russian Postgres Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On 10/30/2017 10:55 AM, rakeshkumar464 wrote: > Is there a way in pgaudit to mask literal sqls like the below: > > insert into table (col1,col2) values(1,2) > select * from table where col1 = 1 > > These sqls are typed by our QA folks using pgadmin. pgaudit records this > verbatim which runs afoul of our HIPAA requirement. Prepared statements are > not an issue since pgaudit provides a way to suppress values. if you have a HIPAA requirement that says 'dont run manual sql statements', then, well, DONT. why are QA folks making changes on production databases, anyways? thats not within their domain. QA should be working on development or staging databases. -- john r pierce, recycling bits in santa cruz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On 10/30/2017 03:35 PM, John R Pierce wrote: > On 10/30/2017 10:55 AM, rakeshkumar464 wrote: >> Is there a way in pgaudit to mask literal sqls like the below: >> >> insert into table (col1,col2) values(1,2) >> select * from table where col1 = 1 >> >> These sqls are typed by our QA folks using pgadmin. pgaudit records this >> verbatim which runs afoul of our HIPAA requirement. Prepared >> statements are >> not an issue since pgaudit provides a way to suppress values. > > if you have a HIPAA requirement that says 'dont run manual sql > statements', then, well, DONT. > > why are QA folks making changes on production databases, anyways? > thats not within their domain. QA should be working on development > or staging databases. > > > I suspect the QA types are testing against production and using/seeing real names, etc with queries which create /transitory/ tables. I wonder if the QA folks have been HIPAA certified? Probable better to get them redacted data for testing. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
No they do select. It is fine in HIPAA to view data which are protected, if it is part of your job. What is not fine is being careless with that protected data and let unauthorized person view that data. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Yes all who interact with HIPAA data are trained for HIPAA SOP. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
By mask I mean pgaudit should log where ssn = '123-456-7891' as where ssn = '?' -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Greetings, * rakeshkumar464 (rakeshkumar464@outlook.com) wrote: > By mask I mean pgaudit should log where ssn = '123-456-7891' as where ssn = > '?' Data masking really isn't part of auditing, and so even if pgaudit could do so, that wouldn't really be the right place to make it happen. There have been discussions about data masking previously but they haven't really lead anywhere. Having proper auditing capabilities built into the backend and then a way to classify errors (such as syntax error or other issue where we couldn't tell what the query actually was due to a user fat-fingering something) as 'not to be logged' would at least get us closer to your goal of not wanting sensitive data in the log files, but PG isn't there yet. That said, there are quite a few people who do use PG with HIPPA and address the requirements required for it in other ways (as discussed elsewhere on this thread). Thanks! Stephen