On 10/10/2014 11:38 AM, Simon Riggs wrote:
> On 10 October 2014 10:29, Heikki Linnakangas <hlinnakangas@vmware.com> wrote:
>> On 10/10/2014 11:57 AM, 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.
>>
>> Deny access to the underlying tables. Write SQL functions to do 1. and 2.,
>> and grant privileges to the functions, instead. For 3. create views that do
>> the redaction.
> If everything were easy to lock down the approach you suggest is of
> course the best way.
>
> The problem there is that the SQL for (2) changes frequently, so we
> want to give people SQL access.
1. Give people access to development system with "safe" data where they
write their functions
2. once function is working, pass it to auditors
3. deploy and use the function.
> Just not the ability to retrieve data in a usable form.
For an attacker any access is "in a usable form", for honest people you
can just provide a view or set-returning function.
btw, one way to do the "redaction" you suggested above is to write a
special
type, which redacts data on output.
You can even make the type output function dependent on backup role.
Just make sure that users are aware that it is not really a security
feature
which protects against attackers.
Cheers
--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ