FAQ
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

Search Discussions

  • Merlin Moncure at Jun 22, 2011 at 8:07 pm

    On Wed, Jun 22, 2011 at 2:30 PM, Jean-Yves F. Barbier 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
  • Jean-Yves F. Barbier at Jun 22, 2011 at 8:45 pm
    On Wed, 22 Jun 2011 15:07:16 -0500, Merlin Moncure wrote:

    ...
    I think it's much better to use the database log to record security
    violations.
    Ok, so I suppose I have to use such a program as pg_fouine (or even just a
    script with greps) and email results to the DBA.
    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.
    This function is to be used against only one table; for joined queries,
    I intend to use the same kind of function, however involving all needed tables.

    The goal is (if possible) eliminate views because if I've got 200 user
    profiles, I'll be obliged to generate 200 x (many)viewS.
    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.
    Ok, I keep that idea in mind :), thanks.
    merlin

    --
    X-rated movies are all alike ... the only thing they leave to the
    imagination is the plot.
  • Merlin Moncure at Jun 22, 2011 at 9:27 pm

    On Wed, Jun 22, 2011 at 3:45 PM, Jean-Yves F. Barbier wrote:
    On Wed, 22 Jun 2011 15:07:16 -0500, Merlin Moncure wrote:

    ...
    I think it's much better to use the database log to record security
    violations.
    Ok, so I suppose I have to use such a program as pg_fouine (or even just a
    script with greps) and email results to the DBA.
    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.
    This function is to be used against only one table; for joined queries,
    I intend to use the same kind of function, however involving all needed tables.

    The goal is (if possible) eliminate views because if I've got 200 user
    profiles, I'll be obliged to generate 200 x (many)viewS.
    why in the world do you need to create one view/user/table? that is
    absolutely something you would want to avoid...

    merlin
  • Jean-Yves F. Barbier at Jun 22, 2011 at 9:55 pm
    On Wed, 22 Jun 2011 16:27:39 -0500, Merlin Moncure wrote:

    ...
    why in the world do you need to create one view/user/table? that is
    absolutely something you would want to avoid...
    No, I didn't meant one view/user/table; what I meant is, for example, that
    for some tables (that just need to be read alone, w/o joins) I'll juste have
    one function instead of Nb tables x view - furthermore, I can't use a view for
    some tables as I need to slice answers eg: suppose I've got 15,000 clients, I
    can't load the whole list at once; and AFAIK views can't do that.

    I also meant using this kind of function (extended of course) to retrieve
    joined rows.

    But even if I don't have a view per table, my application needs a bunch of
    them, which needs to be multiplied by the number of users profiles (eg: buyers
    can set purchase price & minimum profit margin up, but salers won't; so,
    for this example we already have 2 different views, which also means we must
    have 2 different schemas for these categories of users... and so on)

    Thing would be *much* easier if a 'SELECT *' returned only the columns on
    which user have the SELECT privilege - I guess SQL standard forbid that &| it
    is hard to implement.

    --
    Necessity has no law.
    -- St. Augustine

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJun 22, '11 at 7:30p
activeJun 22, '11 at 9:55p
posts5
users2
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase