Re: Column Redaction

On Fri, Oct 10, 2014 at 9:57 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> 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()
> ...
> );

I like that idea a lot - could be very useful (it reminds me of my Pick days).

> 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.

That seems like a reasonable approach. I can imagine other uses for a
BACKUP role in the future.

> Do we want redaction in PostgreSQL?

+1

> Do we want it generalised into output format functions?

+1

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company