Re: RFC: Security documentation - Mailing list pgsql-hackers

While I can understand your concern over security I simply do not know how you
can protect against:

On Sat, 7 Feb 2004, Alex J. Avriette wrote:
> ... or somebody may be passing in the
> de rigeur '; select * from sensitive_table; ...' attempts (this is very
> common, as you know, in CGI applications).

Actually I can and it involves changing the backend to not permit multiple
statements in one request. I can't imagine how that could sensibly be
implemented, if at all, though.

At some stage your interface code has to accept responsibility for preventing
dangerous input from reaching libpq. Sure this means that if someone can bypass
your that interface code then they can then inject the dangerous input but
let's face it, if they're at that stage there's not a lot you can do to stop
them submiting 'select * from sensitive_table' to the backend without all the
leading/trailing crud to try and force that statement to execute in the middle
of what should be a single statement. That immediately means that anything
you've done to prevent multiple statements in one request is also bypassed.

> The program in question is a set of stored procedures which are called
> from Perl libraries (via DBD::Pg) I can't think of any way to ensure
> that malicious input is sanitized, from within plpgsql. From within
> perl, I can use DBI::quote, or I can come up with my own function using
> y///.

The simplist way is to use place holders in a prepared statement and then
execute the statement supplying the data for those placeholders. DBI escapes
the data automatically.

> But when I began asking people what the "final word" was on the
> subject, if there was somebody who was willing to suggest a path to
> data security and stick by it, nobody could point you anywhere.
> Essentially, it boils down to this:  I can't put in the documentation
> for my application "well, some guy on IRC said that this was safe
> enough." I'd be fired if the application was compromised and the only
> checking I had done was by asking people on IRC.
> 
> As such, I would like to see some documentation about securing the
> database at a data and application level. It would be nice to have some
> general guidelines, as well as being able to cite documentation when
> setting up a security policy for a database application.

General guidlines for an application:

Setup two db users, one is the owner of all the database objects, the other is
granted select priviledges only on what it requires.

If there is a exception that requires writing priviledges for the read-only
side of the application, for example tracking pages a website visitor views,
then create that interface function with owner execute flag.

Oh, and did I mention, use functions, aka. stored procs, to do the work.
Although that's a more contentious I think.

> 
> That having been said, I would have submitted a patch with said
> documentation if I knew where to start. I have submitted this RFC -- a
> request for comments, nothing more serious than that -- because I'd
> like to know what we can do to get some documentation included in the
> next release. I don't feel that having zero documentation on this 
> subject is acceptable.

Are you saying here you _do_ have some documentation to contribute?


-- 
Nigel J. Andrews