pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedkind of RULE on SELECT depending on existence of a WHERE condition
6+ messages / 3 participants
[nested] [flat]
* kind of RULE on SELECT depending on existence of a WHERE condition
@ 2010-03-01 10:07 rawi <only4com@web.de>
2010-03-01 10:34 ` Re: kind of RULE on SELECT depending on existence of a WHERE condition Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 6+ messages in thread
From: rawi @ 2010-03-01 10:07 UTC (permalink / raw)
To: pgsql-sql
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-tp27741669p...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: kind of RULE on SELECT depending on existence of a WHERE condition
2010-03-01 10:07 kind of RULE on SELECT depending on existence of a WHERE condition rawi <only4com@web.de>
@ 2010-03-01 10:34 ` Richard Huxton <dev@archonet.com>
2010-03-01 12:16 ` Re: kind of RULE on SELECT depending on existence of a WHERE condition rawi <only4com@web.de>
0 siblings, 1 reply; 6+ messages in thread
From: Richard Huxton @ 2010-03-01 10:34 UTC (permalink / raw)
To: rawi <only4com@web.de>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: kind of RULE on SELECT depending on existence of a WHERE condition
2010-03-01 10:07 kind of RULE on SELECT depending on existence of a WHERE condition rawi <only4com@web.de>
2010-03-01 10:34 ` Re: kind of RULE on SELECT depending on existence of a WHERE condition Richard Huxton <dev@archonet.com>
@ 2010-03-01 12:16 ` rawi <only4com@web.de>
2010-03-01 13:38 ` Re: kind of RULE on SELECT depending on existence of a WHERE condition Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 6+ messages in thread
From: rawi @ 2010-03-01 12:16 UTC (permalink / raw)
To: pgsql-sql
> 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-tp27741669p...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: kind of RULE on SELECT depending on existence of a WHERE condition
2010-03-01 10:07 kind of RULE on SELECT depending on existence of a WHERE condition rawi <only4com@web.de>
2010-03-01 10:34 ` Re: kind of RULE on SELECT depending on existence of a WHERE condition Richard Huxton <dev@archonet.com>
2010-03-01 12:16 ` Re: kind of RULE on SELECT depending on existence of a WHERE condition rawi <only4com@web.de>
@ 2010-03-01 13:38 ` Richard Huxton <dev@archonet.com>
2010-03-02 09:43 ` Re: kind of RULE on SELECT depending on existence of a WHERE condition rawi <only4com@web.de>
2010-03-02 12:07 ` Re: kind of RULE on SELECT depending on existence of a WHERE condition Craig Ringer <craig@postnewspapers.com.au>
0 siblings, 2 replies; 6+ messages in thread
From: Richard Huxton @ 2010-03-01 13:38 UTC (permalink / raw)
To: rawi <only4com@web.de>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: kind of RULE on SELECT depending on existence of a WHERE condition
2010-03-01 10:07 kind of RULE on SELECT depending on existence of a WHERE condition rawi <only4com@web.de>
2010-03-01 10:34 ` Re: kind of RULE on SELECT depending on existence of a WHERE condition Richard Huxton <dev@archonet.com>
2010-03-01 12:16 ` Re: kind of RULE on SELECT depending on existence of a WHERE condition rawi <only4com@web.de>
2010-03-01 13:38 ` Re: kind of RULE on SELECT depending on existence of a WHERE condition Richard Huxton <dev@archonet.com>
@ 2010-03-02 09:43 ` rawi <only4com@web.de>
1 sibling, 0 replies; 6+ messages in thread
From: rawi @ 2010-03-02 09:43 UTC (permalink / raw)
To: pgsql-sql
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-tp27741669p...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: kind of RULE on SELECT depending on existence of a WHERE condition
2010-03-01 10:07 kind of RULE on SELECT depending on existence of a WHERE condition rawi <only4com@web.de>
2010-03-01 10:34 ` Re: kind of RULE on SELECT depending on existence of a WHERE condition Richard Huxton <dev@archonet.com>
2010-03-01 12:16 ` Re: kind of RULE on SELECT depending on existence of a WHERE condition rawi <only4com@web.de>
2010-03-01 13:38 ` Re: kind of RULE on SELECT depending on existence of a WHERE condition Richard Huxton <dev@archonet.com>
@ 2010-03-02 12:07 ` Craig Ringer <craig@postnewspapers.com.au>
1 sibling, 0 replies; 6+ messages in thread
From: Craig Ringer @ 2010-03-02 12:07 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: rawi <only4com@web.de>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2010-03-02 12:07 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-03-01 10:07 kind of RULE on SELECT depending on existence of a WHERE condition rawi <only4com@web.de>
2010-03-01 10:34 ` Richard Huxton <dev@archonet.com>
2010-03-01 12:16 ` rawi <only4com@web.de>
2010-03-01 13:38 ` Richard Huxton <dev@archonet.com>
2010-03-02 09:43 ` rawi <only4com@web.de>
2010-03-02 12:07 ` Craig Ringer <craig@postnewspapers.com.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox