Horton

Thread

  1. Security implications of (plpgsql) functions

    Marcin Owsiany <marcin@owsiany.pl> — 2002-10-19T17:17:13Z

    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

  2. Re: [GENERAL] Security implications of (plpgsql) functions

    Bruce Momjian <pgman@candle.pha.pa.us> — 2002-10-21T15:51:05Z

    [ 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

  3. Re: [GENERAL] Security implications of (plpgsql) functions

    Tom Lane <tgl@sss.pgh.pa.us> — 2002-10-21T16:02:42Z

    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

  4. Re: [GENERAL] Security implications of (plpgsql) functions

    Joe Conway <mail@joeconway.com> — 2002-10-21T16:20:36Z

    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

  5. Re: [GENERAL] Security implications of (plpgsql) functions

    Doug McNaught <doug@wireboard.com> — 2002-10-21T16:27:20Z

    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

  6. Re: [GENERAL] Security implications of (plpgsql) functions

    Joe Conway <mail@joeconway.com> — 2002-10-21T16:39:51Z

    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

  7. Re: [GENERAL] Security implications of (plpgsql) functions

    Tom Lane <tgl@sss.pgh.pa.us> — 2002-10-21T16:44:41Z

    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

  8. Re: Security implications of (plpgsql) functions

    Richard Huxton <dev@archonet.com> — 2002-10-21T16:56:40Z

    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

  9. Re: [GENERAL] Security implications of (plpgsql) functions

    Robert Treat <xzilla@users.sourceforge.net> — 2002-10-21T18:17:47Z

    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

On Mon, 2002-10-21 at 12:44, Tom Lane wrote:
> 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
>