Re: seeking advices for function

On Wed, Jun 22, 2011 at 2:30 PM, Jean-Yves F. Barbier <12ukwn(at)gmail(dot)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