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.
> The ability to mask output for data in certain cases, for the purpose
> of security, is known lately as data redaction, or column-level data
> redaction.
>
> The best way to support this requirement would be to allow columns to
> have an additional "output formatting function". This would be
> executed only when data is about to be returned by a query. All other
> uses of that would not restrict the data.
I don't see how that could work. Once you have access to the datum, you
can find its value in many indirect ways, without invoking the output
function. For example, write a PL/pgSQL function that takes the card
number as argument. Use < and > to binary search its value. If you block
< and >, I'm sure there are countless other ways.
And messing with output functions seems pretty, well, messy, in general.
I think the only solution that's going to work in practice is to
implement the redaction at a higher level. Don't allow direct access to
the tables with card numbers. Create functions that do whatever joins,
etc. you need to do with them, and grant privileges to only the functions.
- Heikki