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
