Re: Proposal for 2.0: SODA 2.0

[Chris Travers <..hidden..>]

As an example of what's wrong with using sprocs to enforce security....

Of course we have to do this for managing users, and user
creation/alteration commands are not parameterized.

So suppose you do something like this (simplified version):

CREATE OR REPLACE FUNCTION create_user(in_username text, in_password text)
RETURNS BOOL AS
$$
BEGIN
    EXECUTE $e$CREATE USER $e$ || in_username || $e$ WITH PASSWORD
'$e$  || in_password ||$e$'$e$;
    RETURN TRUE;
END;
$$ language plpgsql SECURITY DEFINER;

If someone submitted this to me as a patch, and if I missed this in my
review, this would effectively give any user who had permission to run
the function would possibly be able to exploit the SQL injection
vulnerabilities in it to do bad things.  Drop tables, drop other
databases...  All kinds of fun could be had.

For example:
test=# create table test();
CREATE TABLE
test=# select create_user('test; drop table test; CREATE USER foo ', 'p0wned');
 create_user
-------------
 t
(1 row)

test=# drop table test;
ERROR:  table "test" does not exist
test=#

Personally, I think is best to require in-procedure escaping for all
such info and also restrict the permissions a given function has.
While it is possible to do this and still have sprocs manage security,
that adds a lot of complexity and a lot more chance for error.

Best Wishes,
Chris Travers