Hi 2014-10-10 10:57 GMT+02:00 Simon Riggs <si...@2ndquadrant.com>:
> Postgres currently supports column level SELECT privileges. > > 1. If we want to confirm a credit card number, we can issue SELECT 1 > FROM customer WHERE stored_card_number = '1234 5678 5344 7733' > > 2. If we want to look for card fraud, we need to be able to use the > full card number to join to transaction data and look up blocked card > lists etc.. > > 3. We want to block the direct retrieval of card numbers for > additional security. > In some cases, we might want to return an answer like '**** ***** **** > 7733' > > We can't do all of the above with current facilities inside the database. > > The ability to mask output for data in certain cases, for the purpose > of security, is known lately as data redaction, or column-level data > redaction. > > The best way to support this requirement would be to allow columns to > have an additional "output formatting function". This would be > executed only when data is about to be returned by a query. All other > uses of that would not restrict the data. > > This would have other uses as well, such as default report formats, so > we can store financial amounts as NUMERIC, but format them on > retrieval as $12,345.78 etc.. > > Suggested user interface would be... > FORMAT functionname(parameters, if any) > > e.g. > CREATE TABLE customer > ( id ... > ... > , stored_card_number NUMERIC FORMAT pci_card_number_redaction() > ... > ); > > We'd need to implement something to allow pg_dump to ignore format > functions. I suggest the best way to do that is by providing a BACKUP > role that can be delegated to other users. We would then allow a > parameter for SET output_formatting = on | off, which can only be set > by superuser and BACKUP role, then have pg_dump issue SET > output_formatting = off explicitly when it runs. > > I see a benefit of this feature as alternative output function .. I remember a talk about output format of boolean function. But how this feature can help to security? You should to disallow any expression over this column marked or you have to enforced output alternative output function early. When you require a alternative output format function (should be implemented in C), then there is not too less work than implementation of new type. So probably much more practical a any expression can be used like stored_card_number NUMERIC FORMAT (right(stored_card_numbe::text, 4)) Regards Pavel > Do we want redaction in PostgreSQL? > Do we want it generalised into output format functions? > > -- > Simon Riggs http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
