Re: kind of RULE on SELECT depending on existence of a WHERE condition

On 1/03/2010 9:38 PM, Richard Huxton wrote:
> On 01/03/10 12:16, rawi wrote:
>>
>>> Not quite the way you suggest. You could build a series of views with
>>> the WHERE conditions built in to them, and grant permissions on those
>>> though.
>>
>> Thank you very much for your help.
>>
>> Unfortunately is this not what I hoped...
>> The permissions will be granted dynamic by the application out of the
>> user-records and expressed in the WHERE flags.
>> I'll need another approach...
>
> You could write a set-returning function that takes either:
> 1. A list of conditions
> 2. The text for a WHERE clause

SELECT my_priveleged_function('1=1');

You'll probably have to provide different functions for the use of
different roles, or have your function check the current role (see
INFORMATION_SCHEMA) and prepend something appropriate to the WHERE clause.

Even then you'll probably have to pre-filter the results in a subquery,
otherwise it's hard to protect against the user appending 'OR 1=1' or
the like to your WHERE clause.

Personally, I'd avoid any sort of textual query building - instead I'd
provide my_function_for_admins(param1, param2),
my_function_for_users(param1, param2) etc. Each one would substitute
parameters into existing SQL using `EXECUTE USING'. Possibly-null params
can be handled using COALESCE or CASE to avoid string-building.

--
Craig Ringer