Skip site navigation (1) Skip section navigation (2)

seeking advices for function

From: "Jean-Yves F(dot) Barbier" <12ukwn(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: seeking advices for function
Date: 2011-06-22 19:30:31
Message-ID: 20110622213031.57f5f311@anubis.defcon1 (view raw or flat)
Thread:
Lists: pgsql-novice
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

Responses

pgsql-novice by date

Next:From: Merlin MoncureDate: 2011-06-22 20:07:16
Subject: Re: seeking advices for function
Previous:From: Jean-Yves F. BarbierDate: 2011-06-22 19:05:33
Subject: Re: to escape or not to

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group