Re: RFC: Security documentation

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(at)posixnap(dot)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

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

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.

"Nigel J. Andrews" <nandrews(at)investsystems(dot)co(dot)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

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(at)posixnap(dot)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

"Alex J. Avriette" <alex(at)posixnap(dot)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

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(at)posixnap(dot)net
Alex J. Avriette, Shepherd of wayward Database Administrators
"We are paying through the nose to be ignorant." - Larry Ellison