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.
JY
--
My doctor told me to stop having intimate dinners for four. Unless there
are three other people. -- Orson Welles
