Security implications of (plpgsql) functions

[ Thread moved to hackers.]

Crash reproduced here.

My guess is that you are recursing and crashing the backend, which then
forces the other backends to reset. I think we need to fix this by
either setting a limit in the amount of function recursion, or allowing
only the offending backend to crash without forcing all the other
backends to crash.

---------------------------------------------------------------------------

Marcin Owsiany wrote:

CREATE FUNCTION "x" (integer) RETURNS integer AS '
BEGIN
RETURN x(0);
END' LANGUAGE 'plpgsql' WITH ( isstrict );
SELECT x(0);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>

The log says:
DEBUG: server process (pid 3550) was terminated by signal 11
DEBUG: terminating any other active server processes

The thing that worries me most is the last message: postmaster terminates all
other backends, therefore overflowing the stack via a plpgsql function can be
used to DoS-attack the server.

Is there anything one (as a postgresql installation admin) can do? Disabling
procedural languages in databases of the users I don't trust comes to mind, but
does it really make me safe? I mean: won't such attack still be possible with
pure SQL?

regards,

Marcin
--
Marcin Owsiany <marcin@owsiany.pl> http://marcin.owsiany.pl/
GnuPG: 1024D/60F41216 FE67 DA2D 0ACA FC5E 3F75 D6F6 3A0D 8AA0 60F4 1216

"Every program in development at MIT expands until it can read mail."
-- Unknown

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Crash reproduced here.

FWIW, I got a regular "out of memory" elog. But I can see that this
would depend on the relative sizes of data limit and stack limit on
a particular platform.

I think we need to fix this by
either setting a limit in the amount of function recursion, or allowing
only the offending backend to crash without forcing all the other
backends to crash.

Unless you can think of a way to distinguish stack overflow from other
kinds of SIGSEGV, I don't think we can avoid a system-wide restart.
Reducing stack overflow to a plain elog(ERROR) would be really nice,
but how?

A depth limit for PL-function recursion is perhaps feasible, but I can't
say that I care for it a whole lot ... anyone have better ideas?

regards, tom lane

Tom Lane wrote:

A depth limit for PL-function recursion is perhaps feasible, but I can't
say that I care for it a whole lot ... anyone have better ideas?

Is there any way to recognize infinite recursion by analyzing the saved
execution tree -- i.e. can we assume that a function that calls itself, with
the same arguments with which it was called, constitutes infinite recursion?

Joe

Joe Conway <mail@joeconway.com> writes:

Tom Lane wrote:

A depth limit for PL-function recursion is perhaps feasible, but I can't
say that I care for it a whole lot ... anyone have better ideas?

Is there any way to recognize infinite recursion by analyzing the
saved execution tree -- i.e. can we assume that a function that calls
itself, with the same arguments with which it was called, constitutes
infinite recursion?

Solved the halting problem lately? ;)

Someone determined to DoS could probably get around any practical
implementation of your idea, using dummy argument, mutual recursion or
whatever.

-Doug

Doug McNaught wrote:

Solved the halting problem lately? ;)

nah -- I'll leave that as an exercise for the reader ;-)

Someone determined to DoS could probably get around any practical
implementation of your idea, using dummy argument, mutual recursion or
whatever.

I see your point. I guess a max recursion limit would be the way to go.
Probably should be a configurable setting.

Joe

Joe Conway <mail@joeconway.com> writes:

Is there any way to recognize infinite recursion by analyzing the saved
execution tree -- i.e. can we assume that a function that calls itself, with
the same arguments with which it was called, constitutes infinite recursion?

A bulletproof solution would be equivalent to solving the halting
problem, I believe. The test you mentioned is easily defeated by
recursing between two functions. Also, a would-be instigator of
DOS doesn't need *infinite* recursion; it could be quite finite and
still blow out your stack. For example ask for factorial(10million)
where factorial is defined in the traditional recursive way...

regards, tom lane

On Saturday 19 Oct 2002 5:17 pm, Marcin Owsiany wrote:
[snipped infinite recursion function]

The log says:
DEBUG: server process (pid 3550) was terminated by signal 11
DEBUG: terminating any other active server processes

The thing that worries me most is the last message: postmaster terminates
all other backends, therefore overflowing the stack via a plpgsql function
can be used to DoS-attack the server.

You need to be careful allowing create access for *any* procedural language
(and paranoid about 'C'). If nothing else, someone could craft a procedure
that allocates a lot of memory and just wait. I'm note sure a sig11 is what
we'd be looking for here though - I'd expect "unable to allocate memory". If
one of the developers don't spot this, I'd report it as a bug.

Is there anything one (as a postgresql installation admin) can do?
Disabling procedural languages in databases of the users I don't trust
comes to mind, but does it really make me safe? I mean: won't such attack
still be possible with pure SQL?

You can run a DoS fairly simply if you can connect multiple times - just
select * from bigtable as a, bigtable as b will soon exhaust memory. It's not
difficult to craft queries to drain CPU/disk IO either. You can control the
amount of shared memory, sort memory and number of connections allowed though
(see postgresql.conf) which gives an upper limit on the drain PG places on
your machine.

You're along the right lines, restricting access to languages - only grant
permissions where needed (this applies to tables etc too).
--
Richard Huxton

I've seen the idea of "user resource limits" bandied about before as a
way to address these problems; depending on implementation that might be
the way to go.

Robert Treat