Re: Column Redaction

On 10/10/14 21:57, Simon Riggs 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()
> ...
> );
>
> 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.
>
> Do we want redaction in PostgreSQL?
> Do we want it generalised into output format functions?
>

I think having a FORMAT option would be good, but I strongly feel that 
end users should NEVER EVER have direct access to any database with 
sensitive information!  And if the full details are stored, then 
obviously, at some time people will have a legitimate need to access all 
the digits - so it does not make sense to prevent this .

Also I think it would be useful to store formats, especially complicated 
ones, so they can be defined once and reused as many times as  required 
- helps for standardisation.

How about something like:

CREATE FORMAT /format-name/ [WITH] /format-spec/ [DENY | ALLOW role-1, ...];

Where the /format-spec/ is either a function, or something similar to a 
COBOL picture spec., I suspect that the implied security control with 
the ALLOW & DENY options might prove too weak for anyone determined, 
though it might be good enough in some common contexts.


CREATE FORMAT card_format_redacted WITH '**** **** **** 9999' ALLOW ALL;
CREATE FORMAT card_format_full '9999 9999 9999 9999' ALLOW admin_1;
CREATE FORMAT card_format_special special_card_formatter(); ALLOW 
admin_42, mariadba;

-- specify default FORMAT
CREATE TABLE customer
(    ...    stored_card_number  NUMERIC FORMAT card_format_redacted,    ...
)


-- unformatted, fails if role is neither admin-1 or a role that inherits 
from it
SELECT    stored_card_number
WHERE    ...;


-- using card_format_redacted
SELECT    stored_card_number FORMAT DEFAULT
WHERE    ...;


-- using card_format_full, fails if role is neither admin-1 or a role 
that inherits from it
SELECT    stored_card_number FORMAT card_format_full
WHERE    ...;



Cheers,
Gavin