Thread: seeking advices for function
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
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
On Wed, 22 Jun 2011 15:07:16 -0500, Merlin Moncure <mmoncure@gmail.com> 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.
On Wed, Jun 22, 2011 at 3:45 PM, Jean-Yves F. Barbier <12ukwn@gmail.com> wrote: > On Wed, 22 Jun 2011 15:07:16 -0500, Merlin Moncure <mmoncure@gmail.com> 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
On Wed, 22 Jun 2011 16:27:39 -0500, Merlin Moncure <mmoncure@gmail.com> 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