On 10 October 2014 10:15, Thom Brown <thom@linux.com> wrote:
> On 10 October 2014 09:57, 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'
>
> 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?
Yes, it would. As would SELECT redacted_column || ' '
I'm not sure how to block such usage, other than to apply it prior to
final calculation of functions.
i.e. we apply it in the SELECT clause, but not in the other clauses
FROM ON/WHERE/GROUP/ORDER/HAVING etc..
> 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.
Yes, covert channels would always exist. It would really be down to
auditing to control such exploits.
Redaction is aimed at minimising access in normal usage.
-- Simon Riggs http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
