Re: feature suggestion and return to pgsql-general :-)

On Fri, Apr 24, 2009 at 12:53 AM, Dennis Gearon <gearond@sbcglobal.net> wrote:
>
> Hi guys, I am back after a long time of doing other kinds of work. Don't know if anybody remembers me - not a biggee.
>
> I am again working on a design of a web application for myself. Security seems MUCH more of an issue, EVERY day.
>
> So I had these to suggestions/feature requests.
>
> To prevent SQL injection, have either permissions-per-role, or DB wide, or DB instance configurable with defalaults
andover ride configuration, blah blah, the following. 
>
> A flag/permission to allow ONLY prepared queries
> A flag/permission to allow ONLY UDF/procedures.

This is a pretty easy feature to implement actually.  We went a step
further and run all queries through a white list (obviously this only
makes sense for paramaterized queries).

Everything you need is in tcop.c.  Everytime a query come in from the
client, you have an opportunity to intercept and run your own logic
about what's allowed.  I don't think functions have to be handled
specially, you force them to be parameterized like any other query.
username is conveniently provided so you can use that in your logic.
It's quite effective.

This is a highly ad-hoc security strategy though, so I don't know if
it's worth a push to get it into core.  Might be, though.

merlin