Thread: kind of RULE on SELECT depending on existence of a WHERE condition
Hi please help me with the following scenario: I'd gladly have all persons (contacts, customers, staff etc) in one table "people" with some distinctive flags. My reason for this, is that simple contacts could become customers or even staff, staff members could be in the same time also customers etc and I would be glad to manage all the data in one table to avoid the same Person-Record in three tables. Now, the SELECTs would be generated from an application (hibernate) automatically inserting some WHERE condition on the flags of the recorded people depending on the rights of the user. Some isn't allowed to see staff, an other shouldn't see customers etc. But - for the case, that an application-programmer or even I (tired, middle in the night) forget to program the insertion of the needed limiting WHERE condition - it would be great to have some sort of rule ON SELECT, so a SELECT without WHERE on the flags would return NOTHING. Is there a way to achieve this? Thank you for reading and hoping my problem can be solved. Rawi -- View this message in context: http://old.nabble.com/kind-of-RULE-on-SELECT-depending-on-existence-of-a-WHERE-condition-tp27741669p27741669.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
On 01/03/10 10:07, rawi wrote: > But - for the case, that an application-programmer or even I (tired, middle > in the night) forget to program the insertion of the needed limiting WHERE > condition - it would be great to have some sort of rule ON SELECT, so a > SELECT without WHERE on the flags would return NOTHING. > > Is there a way to achieve this? 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. -- Richard Huxton Archonet Ltd
> 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... Cheers, Rawi -- View this message in context: http://old.nabble.com/kind-of-RULE-on-SELECT-depending-on-existence-of-a-WHERE-condition-tp27741669p27742718.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
We were trying to use a separate schema to put contrib modules, stored procedures and triggers from our database schema. We called the one where we stored all of the modules isscontrib and the other iss which is where the db is stored. We've fully qualified the names of our items from isscontrib and have also included that schema in the search path. Our goal is to eventually mark isscontrib as execute only. Is there any restrictions to having objects in a separate schema? We are using the citext contrib module. It doesn't appear to work when it's in the isscontrib module. We don't get any error messages however we also don't get the results we are looking for, namely case insensitive search results. However, if we move the contrib module to the public schema, everything appears to work fine. Best Regards Mike Gould -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax
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 If it gets no conditions or a blank string, it returns nothing. You will need to create the function with SECURITY DEFINER permissions, as a user who can read from the table. Make sure the application cannot read from the table and has to use the function. -- Richard Huxton Archonet Ltd
Richard Huxton wrote: > > You could write a set-returning function that takes either: > 1. A list of conditions > 2. The text for a WHERE clause > If it gets no conditions or a blank string, it returns nothing. > You will need to create the function with SECURITY DEFINER permissions, > That's a very good suggestion. I've just red about SECURITY DEFINER... great. Thanks -- View this message in context: http://old.nabble.com/kind-of-RULE-on-SELECT-depending-on-existence-of-a-WHERE-condition-tp27741669p27753818.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
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