Thread

1. RFC: Security documentation

   Alex J. Avriette <alex@posixnap.net> — 2004-02-07T18:12:17Z

   Hello again.
   
   Recently, an application of mine, which faces the internet, came under
   attack. The form of the attack was the standard DOS attack. Open a
   bunch of sockets, don't close them, and see if you can break the
   availability of the application.
   
   This attack came within six hours of the application going live. While
   I can't give details, I can say that this application is running within
   a very high visibility organization, and we are more or less under
   continual attack of various forms.
   
   I realized that a DOS attack was relatively unsophisticated, and that
   over the lifetime of this product, we will se more concerted,
   intelligent attacks on our code. This is troubling to me. When I began
   searching for documentation on securing postgres, all of the available
   docs seem to focus on access to the database through pg_hba.conf.
   
   While I can appreciate that this is useful (eg using ssl and md5
   instead of plaintext trusted accounts), I feel that there is
   substantial documentation missing on securing it at an application
   level.
   
   I mentioned some time ago, that on IRIX, it is possible to crash the
   postmaster by feeding it 'select 1/0'. My concern was that something
   like this might come down the pipe, or somebody may be passing in the
   de rigeur '; select * from sensitive_table; ...' attempts (this is very
   common, as you know, in CGI applications).
   
   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///.
   
   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.
   
   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.
   
   Thanks for your time,
   alex
   
   --
   alex@posixnap.net
   Alex J. Avriette, Unix Systems Gladiator
   "You cannot invade the mainland United States. There would be a rifle behind each blade of grass." - Admiral Isoroku Yamamoto 
   
   

2. Re: RFC: Security documentation

   Nigel J. Andrews <nandrews@investsystems.co.uk> — 2004-02-08T10:52:07Z

   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
   
   
   

3. Re: RFC: Security documentation

   Peter Eisentraut <peter_e@gmx.net> — 2004-02-08T12:02:30Z

   Alex J. Avriette wrote:
   > 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.
   
   I don't think that such information, while certainly worthwhile, belongs 
   into the PostgreSQL documentation, especially because it would cover 
   things that are not even included in PostgreSQL, such as DBD::Pg or PHP 
   or some mysterious generic "applications".  But in any case it's more 
   important to actually write something than worry about where or how it 
   will be published.
   
   
   

4. Re: RFC: Security documentation

   Tom Lane <tgl@sss.pgh.pa.us> — 2004-02-08T18:33:31Z

   "Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
   > 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.
   
   Actually, the extended-query message in the new FE/BE protocol works
   exactly that way.  This was done for protocol-simplicity reasons not for
   security, but you could use it for that.  The new protocol's ability to
   separate parameter values from SQL command is also useful for ensuring
   security.
   
   > At some stage your interface code has to accept responsibility for preventing
   > dangerous input from reaching libpq.
   
   However, I quite agree with that statement.  The app programmer has to
   take responsibility for properly segregating or quoting data strings.
   We can (and do) provide tools to make this easier, but it's still the
   programmer's responsibility to use the tools correctly.
   
   			regards, tom lane
   
   

5. Re: RFC: Security documentation

   Alex J. Avriette <alex@posixnap.net> — 2004-02-08T23:42:38Z

   On Sun, Feb 08, 2004 at 01:33:31PM -0500, Tom Lane wrote:
   
   > > 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.
   > 
   > Actually, the extended-query message in the new FE/BE protocol works
   > exactly that way.  This was done for protocol-simplicity reasons not for
   > security, but you could use it for that.  The new protocol's ability to
   > separate parameter values from SQL command is also useful for ensuring
   > security.
   
   (Tom is referring to this: 
   	http://archives.postgresql.org/pgsql-interfaces/2003-03/msg00017.php)
   
   How would you suggest implementing this? Having a "no subqueries" setting?
   Asking the postmaster to throw an exception on queries-within-data? I 
   can think of several ways to do it, but I'd like to know what you had in 
   mind.
   
   > > At some stage your interface code has to accept responsibility for preventing
   > > dangerous input from reaching libpq.
   > 
   > However, I quite agree with that statement.  The app programmer has to
   > take responsibility for properly segregating or quoting data strings.
   > We can (and do) provide tools to make this easier, but it's still the
   > programmer's responsibility to use the tools correctly.
   
   I agree with this as well. In my original message, I complained that there
   was no documentation at all. Since we offer documentation on how to code
   in plpgsql, pltcl, plperl, etc., it might be nice to include something.
   Even if it were something brief, such as suggesting escaped quotes and
   other suspicious characters, it would be better than the nothing that is
   there presently. Like I said, it allows some disclaiming of culpability
   for the programmer -- "I did what the docs said" -- and it gives them
   an idea of where to start.
   
   My initial feeling is that a small addition to the 'Server Programming'
   section would be reasonable, or perhaps in the Appendix.
   
   I can't see why anyone would be opposed to this, however. I'm happy to
   write the document and provide a patch for inclusion if we can come to
   agreeance on some basic policies. The reason I posted the original 
   message in this thread is I wanted to know what others felt were 
   appropriate policies, and to suggest said policies wound up in a doc.
   
   Alex
   
   --
   alex@posixnap.net
   Alex J. Avriette, Unix Systems Gladiator
   "I favor the Civil Rights Act of 1965, and it must be enforced at gunpoint if necessary." - Ronald Reagan
   
   

6. Re: RFC: Security documentation

   Tom Lane <tgl@sss.pgh.pa.us> — 2004-02-09T02:34:15Z

   "Alex J. Avriette" <alex@posixnap.net> writes:
   > On Sun, Feb 08, 2004 at 01:33:31PM -0500, Tom Lane wrote:
   >> Actually, the extended-query message in the new FE/BE protocol works
   >> exactly that way.
   
   > (Tom is referring to this: 
   > 	http://archives.postgresql.org/pgsql-interfaces/2003-03/msg00017.php)
   
   That's not a particularly helpful link, since it predates the whole
   concept of the extended query protocol.  See
   http://www.postgresql.org/docs/7.4/static/protocol.html#PROTOCOL-QUERY-CONCEPTS
   http://www.postgresql.org/docs/7.4/static/protocol-flow.html#AEN52626
   particularly the NOTE in the latter section.
   
   > How would you suggest implementing this? Having a "no subqueries" setting?
   
   The app programmer could choose to use only extended queries and not
   simple Query messages.  (If using libpq, this means only PQexecParams
   and never PQexec.)
   
   > I agree with this as well. In my original message, I complained that there
   > was no documentation at all. Since we offer documentation on how to code
   > in plpgsql, pltcl, plperl, etc., it might be nice to include something.
   > Even if it were something brief, such as suggesting escaped quotes and
   > other suspicious characters, it would be better than the nothing that is
   > there presently.
   
   Is this "nothing"?
   http://www.postgresql.org/docs/7.4/static/libpq-exec.html#LIBPQ-EXEC-ESCAPE-STRING
   
   I don't think the docs are nearly as bereft of security-related items as
   you claim.  They may be scattered and poorly indexed, but they're there.
   
   			regards, tom lane
   
   

7. Re: RFC: Security documentation

   Alex J. Avriette <alex@posixnap.net> — 2004-02-09T15:37:55Z

   On Sun, Feb 08, 2004 at 09:34:15PM -0500, Tom Lane wrote:
   
   > Is this "nothing"?
   > http://www.postgresql.org/docs/7.4/static/libpq-exec.html#LIBPQ-EXEC-ESCAPE-STRING
   > 
   > I don't think the docs are nearly as bereft of security-related items as
   > you claim.  They may be scattered and poorly indexed, but they're there.
   
   Tom, I think this is largely a semantic issue. If documentation exists,
   but is difficult to find, or stored in such a way as to not be quickly
   available to somebody looking for it, it isn't useful. While not
   "nothing" as such, it doesn't count for much.
   
   I've liked what I've heard so far in this thread. Is there a consensus
   that some documentation could be added regarding security? If we can
   agree on that, I would be happy to start doing some collating of data
   on the subject. Could it go in the distributed documentation? I know
   there was some debate as to whether it belonged in the docs themselves,
   or in techdocs.
   
   Personally, I feel that distributing it in the main documentation would
   be preferable. However, I don't have any particular allegiance to that
   method; I mostly look for answers to questions via google first. If the
   docs were included on techdocs, google would find them soon enough. I
   suppose, also, anyone who was interested in securing their database
   would look a little further than the included documentation.
   
   Opinions?
   
   Alex
   
   --
   alex@posixnap.net
   Alex J. Avriette, Shepherd of wayward Database Administrators
   "We are paying through the nose to be ignorant." - Larry Ellison