Re: [SQL] kind of RULE on SELECT depending on existence of a WHERE condition
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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] determine the curval() of a view
Hi, I am working with a view that has a "_INSERT" rule. I'm wondering if there is a way to determine what the curval() of the PK (serial type) after an insert occurs. Given the name of the view I'd like to determine the sequence associated with the view's PK. Actually, I wonder if it is even possible to determine the PK from the view name. I'm guessing that it might be possible because the view must be associated with a table/s. And they will have PK's. Any hints will be very helpful. Johnf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
