Re: Column Redaction

On 10 October 2014 09:57, Simon Riggs <simon(at)2ndquadrant(dot)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'

One question that immediately springs to mind is: would the format
apply when passing columns to other functions? If not, wouldn't
something like

SELECT upper(redacted_column::text) ...

just bypass the formatting?

Also, how would casting be handled? Would it be forbidden for such cases?

And couldn't the card number be worked out using:

SELECT 1 FROM customer WHERE stored_card_number LIKE '%1 7733';
?column?
----------
(0 rows)

SELECT 1 FROM customer WHERE stored_card_number LIKE '%2 7733';
?column?
----------
1
(1 row)

SELECT 1 FROM customer WHERE stored_card_number LIKE '%12 7733';
?column?
----------
(0 rows)

.. and so on, which could be scripted in a DO statement?

Not so much a challenge to the idea, but just wishing to understand
how it would work.

--
Thom