Обсуждение: Security implications of (plpgsql) functions

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

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