On Fri, Oct 10, 2014 at 5:57 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
>
> 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'
> ...
> 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'
I wouldn't want to allow that:
select ref.ref, customer.name from (select generate_series as ref from
generate_series(0, 9999999999999999)) ref, customer
where ref.ref = stored_card_number.ref
May take a long while. Just disable everything except nestloop and
suck up the data as it comes. Can be optimized. Not sure how you'd
avoid this, not trivial at all. Not possible at all I'd venture.
But if you really really want to allow this, encrypt the column, and
provide a C function that can decrypt it. You can join encrypted
columns, and you can even include the last 4 digits unencrypted if you
want (I wouldn't want).
Has to be a C function to be able to avoid leaking the key, btw.
> 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..
view works for this pretty well
