Re: [HACKERS] RFC: Security documentation
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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] RFC: Security documentation
On Wednesday 11 February 2004 12:46, Jim C. Nasby wrote:
On Sun, Feb 08, 2004 at 11:24:56PM -0800, Josh Berkus wrote:
The problem with this approach, of course, is that large application
developers generally like to make the database fairly passive and put
all business security logic in the middleware. I do think it would be
useful for them to realize that they are sacrificing a significant
portion of their data security by doing so.
Perhaps what would be best is some kind of a 'best practices' guide.
There's far more that people should consider beyond just quoting
strings; Josh's example is just one thing.
If written carefully, such a guide could serve both experienced DBAs as
well as people who are very new to databases, since every database has
it's own prefered way of doing things.
Was thinking if somene want to write up a series of articles discussing
security best practices, this might be a good starting point since it would
require somone to have everything figured out before getting started; you
could pick a certain section and get specific about it. We have the
infrastructure on techdocs to publish this, and once started we could use it
to determine what should or should not be added to the standard docs.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] RFC: Security documentation
On Sun, Feb 08, 2004 at 11:24:56PM -0800, Josh Berkus wrote: The problem with this approach, of course, is that large application developers generally like to make the database fairly passive and put all business security logic in the middleware. I do think it would be useful for them to realize that they are sacrificing a significant portion of their data security by doing so. Perhaps what would be best is some kind of a 'best practices' guide. There's far more that people should consider beyond just quoting strings; Josh's example is just one thing. If written carefully, such a guide could serve both experienced DBAs as well as people who are very new to databases, since every database has it's own prefered way of doing things. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] RFC: Security documentation
Alex, 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. I'd be happy to participate in discussing security strategies for PostgreSQL databases; I do a bit of this for my clients though, not, I think, on the level of need you face. A lot can be done especially with 7.4 by manipulating permissions, obfuscating database objects through views, functions, and rules, and otherwise locking down database objects. For example, if the web server user's only access to a table is via a set-returing search function, and that user has no other permissions, a SQL injection attack isn't going to obtain anything other than an error. The problem with this approach, of course, is that large application developers generally like to make the database fairly passive and put all business security logic in the middleware. I do think it would be useful for them to realize that they are sacrificing a significant portion of their data security by doing so. On a machine/network level, all I really do is lock down port access to the database server, and make sure that the db server has no ports that point to unsecured networks (as well as the usual user/database/password restrictions).My general perspective is that if an attacker can gain unrestricted port access to the database, a break-in is only a matter of time -- if through nothing else than password-guessing attacks. I'm not sure such a paper is appropriate for the main docs. But it is definitely appropriate for TechDocs. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] RFC: Security documentation
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 -- [EMAIL PROTECTED] Alex J. Avriette, Shepherd of wayward Database Administrators We are paying through the nose to be ignorant. - Larry Ellison ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] RFC: Security documentation
Nigel J. Andrews [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] RFC: Security documentation
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 -- [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] RFC: Security documentation
Alex J. Avriette [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
