Message-ID: <4B8CFF9C.9080305@postnewspapers.com.au>
Date: Tue, 02 Mar 2010 20:07:56 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.0; en-GB;
	rv:1.9.1.7) Gecko/20100111 Thunderbird/3.0.1
MIME-Version: 1.0
To: Richard Huxton <dev@archonet.com>
CC: rawi <only4com@web.de>, pgsql-sql@postgresql.org
Subject: Re: kind of RULE on SELECT depending on existence of a WHERE
 condition
References: <27741669.post@talk.nabble.com> <4B8B983B.4080205@archonet.com>
	<27742718.post@talk.nabble.com> <4B8BC36E.4040007@archonet.com>
In-Reply-To: <4B8BC36E.4040007@archonet.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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