Re: seeking advices for function
От | Merlin Moncure |
---|---|
Тема | Re: seeking advices for function |
Дата | |
Msg-id | BANLkTimFuLeT1np_nSYFMpcvLSkO+TuN_A@mail.gmail.com обсуждение исходный текст |
Ответ на | seeking advices for function ("Jean-Yves F. Barbier" <12ukwn@gmail.com>) |
Ответы |
Re: seeking advices for function
("Jean-Yves F. Barbier" <12ukwn@gmail.com>)
|
Список | pgsql-novice |
On Wed, Jun 22, 2011 at 2:30 PM, Jean-Yves F. Barbier <12ukwn@gmail.com> wrote: > Hi list, > > I've got some questions about this function: > > ============================= > DROP FUNCTION tsttst(TEXT, TEXT, BOOLEAN, INT8, INT8, TEXT[]); > CREATE FUNCTION tsttst(TEXT, -- FQTN > TEXT, -- Ordering column > BOOLEAN, -- TRUE=ASC / FALSE=DESC > INT8, -- LIMIT > INT8, -- OFFSET > TEXT[]) -- Columns' names array > RETURNS SETOF RECORD AS $$ > DECLARE > usr TEXT; > ord TEXT; > collist TEXT; > qry TEXT; > BEGIN > -- Retrieve real caller's name > usr := session_user; > -- First check for table SELECT privilege > IF NOT has_table_privilege(usr, $1, 'SELECT') THEN > -- If needed, check SELECT privilege per column > FOR i IN 1 .. array_length($6, 1) LOOP > IF NOT has_column_privilege(usr, $1, $6[i], 'SELECT') THEN > RAISE EXCEPTION 'FNCT: tsttst: Call forbidden'; > -- ADD HERE LOGING IN TABLE security.alert > -- YEAH, BUT HOW TO AVOID security.alert NEW ROW BEING > -- VOIDED FROM A ROLLBACK ??? > END IF; > END LOOP; > END IF; > -- Set ordering direction > IF $3 THEN > ord := 'ASC'; > ELSE > ord := 'DESC'; > END IF; > -- Construct columns full list > collist := array_to_string($6, ','); > -- Build query from input parms > qry = 'SELECT ' || collist || ' FROM ' || $1 || ' ORDER BY ' || $2 || ' ' > || ord || ' LIMIT ' || $4 || ' OFFSET ' || $5 || ';'; > -- Return the whole query > RETURN QUERY EXECUTE qry; > END; > $$ LANGUAGE plpgsql STABLE STRICT SECURITY DEFINER; > ============================= > > * Is it totally non-vulnerable to SQL injection? > > * I intend to use this kind of function for data I/O, as a replacement for > views in an ERP project. > Considering that overhead introduced by builtin SELECT privilege check is > far from negligible (from 110ns to 800ns, one row select) but on the other > hand that it could replace thousands views and that an ERP isn't an > application that generates hundreds queries per second. > Is it a good idea or not? > > * A big problem is the implementation of trespassing attempts loging (see > comment in function) which shouldn't be subject to the subsequent rollback; > how can I do that? > > Any constructive critics will be welcome. I think it's much better to use the database log to record security violations. Wrapping SQL with a function like this is going to be a problem factory. For example, it's a total optimization fence if you ever need to do something like join against your 'view'. IMO, it's a total non-starter. If you *must* log to a table in a view definition, or want to sneakily hook custom behaviors to a view generally, you can do something like this. say your view is: CREATE VIEW v as SELECT * FROM foo; organize your plpgsql function like this: CREATE FUNCTION priv_check(_view text) RETURNS bool AS $$ BEGIN IF NOT has_table_privilege(session_user, _view, 'SELECT') THEN /* insert into log via dblink (see dblink docs) */ RAISE ... END IF; RETURN true; END; $$ LANGUAGE PLPGSQL; now you can work up your view like this: CREATE VIEW v as SELECT * FROM foo WHERE (SELECT priv_check('foo')); I'm pretty sure postgres is going to be smart enough to run priv_check only once per select from the view in all reasonable cases. dblink remains the only way to emit records you want to keep from a transaction that you want to roll back without recovering. merlin
В списке pgsql-novice по дате отправления:
Следующее
От: Merlin MoncureДата:
Сообщение: Re: How to trap error: nextval: reached maximum value of sequence