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