Обсуждение: Remote administration contrib module
A thread on -general (http://archives.postgresql.org/pgsql-general/2006-03/msg01023.php) recently ended in the idea that the pgAdmin 'adminpack' be included as an 'official' PostgreSQL contrib module. Currently the code is an add-on available from the pgAdmin website, unless you run the Windows pgInstaller distribution of PostgreSQL in which case it is already included. For those that don't know, the adminpack is a set of additional functions designed to allow pgAdmin (and potentially any other client application) the ability to rewrite server configuration files and browse & read logfiles etc. The full readme can be found at http://svn.pgadmin.org/cgi-bin/viewcvs.cgi/trunk/pgadmin3/xtra/admin81/R EADME.admin81?rev=5024&view=markup, and the code can be seen at http://svn.pgadmin.org/cgi-bin/viewcvs.cgi/trunk/pgadmin3/xtra/admin81/. Tom's concern in the -general thread was that the pgAdmin licence is the Artistic License, however we are now in a position to offer the code under the BSD licence. So, how would people feel about including this as a contrib module in the future, until(/if) equivalent functionality becomes available in -core in some form? Regards, Dave.
Am Montag, 27. März 2006 15:12 schrieb Dave Page: > So, how would people feel about including this as > a contrib module in the future, until(/if) equivalent functionality > becomes available in -core in some form? Right now you have got plenty of time to get it in shape for inclusion in core, so we might not even have to take the detour through a contrib module. -- Peter Eisentraut http://developer.postgresql.org/~petere/
-----Original Message----- From: Peter Eisentraut [mailto:peter_e@gmx.net] Sent: Mon 3/27/2006 3:00 PM To: pgsql-hackers@postgresql.org Cc: Dave Page Subject: Re: [HACKERS] Remote administration contrib module Am Montag, 27. März 2006 15:12 schrieb Dave Page: > > So, how would people feel about including this as > > a contrib module in the future, until(/if) equivalent functionality > > becomes available in -core in some form? > > Right now you have got plenty of time to get it in shape for inclusion in > core, so we might not even have to take the detour through a contrib module. As it stands it was previously rejected for inclusion in -core in it's current form. The main objector was Tom, but as itwas him that encouraged me to change the licencing for inclusion as a contrib module I assume he doesn't object to that. I have submitted a conference discussion proposal to talk about ways of implementing the remote configuration that we wantwhich was the main thing I think Tom objected to originally, but that's some time away and would likely result in a significantlymore complex solution that may or may not get done for 8.2. In addition, there are one or two helper functionsin there that were previously rejected more on the 'no use to end user' basis iirc (see pg_logdir_ls) which maynever end up in -core without reconsideration of the need. It would be good (for us at least) to get it in as a contrib,even if we do manage to replace it, or some of it, by release. Regards, Dave
Dave Page wrote: > > Right now you have got plenty of time to get it in shape for inclusion in > > core, so we might not even have to take the detour through a contrib module. > > As it stands it was previously rejected for inclusion in -core in it's > current form. The main objector was Tom, but as it was him that > encouraged me to change the licencing for inclusion as a contrib module > I assume he doesn't object to that. > > I have submitted a conference discussion proposal to talk about ways > of implementing the remote configuration that we want which was the > main thing I think Tom objected to originally, but that's some time > away and would likely result in a significantly more complex solution > that may or may not get done for 8.2. In addition, there are one or > two helper functions in there that were previously rejected more on > the 'no use to end user' basis iirc (see pg_logdir_ls) which may never > end up in -core without reconsideration of the need. It would be good > (for us at least) to get it in as a contrib, even if we do manage to > replace it, or some of it, by release. +1 for /contrib I think the issue was that adding these fuctions adds a potential security opening, so we didn't want it in core by default, but /contrib seems logical because anyone who needs it can just add it. This is similar to the fact we don't include plpgsql by default in databases, for the same reason, but we have the ability to use createlang to add it. Adding these functions to contrib gives us the same control. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian wrote: > I think the issue was that adding these fuctions adds a potential > security opening, so we didn't want it in core by default, but > /contrib seems logical because anyone who needs it can just add it. Well, if there are security issues, then this is a poor fix. A lot of people use pgAdmin, many of them less experienced with PostgreSQL, so before long all of these functions are going to be installed at many sites anyway. If there are _security_ issues, they need to be fixed before things go into contrib. > This is similar to the fact we don't include plpgsql by default in > databases, for the same reason, I doubt that that is really the reason. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Peter Eisentraut wrote: > Bruce Momjian wrote: > > I think the issue was that adding these fuctions adds a potential > > security opening, so we didn't want it in core by default, but > > /contrib seems logical because anyone who needs it can just add it. > > Well, if there are security issues, then this is a poor fix. A lot of > people use pgAdmin, many of them less experienced with PostgreSQL, so > before long all of these functions are going to be installed at many > sites anyway. If there are _security_ issues, they need to be fixed > before things go into contrib. The logic is why add functionality by default that can be used as a potential security hole if you are not using it. > > This is similar to the fact we don't include plpgsql by default in > > databases, for the same reason, > > I doubt that that is really the reason. It actually is the reason I have heard. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
-----Original Message----- From: "Peter Eisentraut"<peter_e@gmx.net> Sent: 10/04/06 22:43:05 To: "Bruce Momjian"<pgman@candle.pha.pa.us> Cc: "Dave Page"<dpage@vale-housing.co.uk>, "pgsql-hackers@postgresql.org"<pgsql-hackers@postgresql.org> Subject: Re: [HACKERS] Remote administration contrib module > If there are _security_ issues, they need to be fixed > before things go into contrib. (From memory) There were concerns, rather than actual issues. The functions are all superuser-only where appropriate, andwhile the only potentially destructive ones (pg_file_write, pg_file_rename, pg_file_unlink) can kill files under $PGDATA- but then, so can COPY just as easily. > > This is similar to the fact we don't include plpgsql by default in > > databases, for the same reason, > I doubt that that is really the reason. It's the only reason I ever heard. /D -----Unmodified Original Message----- Bruce Momjian wrote: > I think the issue was that adding these fuctions adds a potential > security opening, so we didn't want it in core by default, but > /contrib seems logical because anyone who needs it can just add it. Well, if there are security issues, then this is a poor fix. A lot of people use pgAdmin, many of them less experienced with PostgreSQL, so before long all of these functions are going to be installed at many sites anyway. If there are _security_ issues, they need to be fixed before things go into contrib. > This is similar to the fact we don't include plpgsql by default in > databases, for the same reason, I doubt that that is really the reason. -- Peter Eisentraut http://developer.postgresql.org/~petere/
On 2006-04-10, Bruce Momjian <pgman@candle.pha.pa.us> wrote: > Peter Eisentraut wrote: >> > This is similar to the fact we don't include plpgsql by default in >> > databases, for the same reason, [the reason being "security"] >> >> I doubt that that is really the reason. > > It actually is the reason I have heard. And it was duly debunked. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services
Andrew - Supernews <andrew+nonews@supernews.com> writes:
> On 2006-04-10, Bruce Momjian <pgman@candle.pha.pa.us> wrote:
>>> [ security ]
>> It actually is the reason I have heard.
> And it was duly debunked.
That is the reasoning, and personally I agree with it. You don't leave
sharp objects sitting around if you have no need to have them out.
The availability of plpgsql or other PLs makes for a significant jump
in what a bad guy can do if he gets access to the database, so if a
particular DB doesn't actually need the capability, it's best that it
not be there. And that's without considering the possibility of genuine
security holes in the PL, but just supposing that it only does what it's
supposed to do.
regards, tom lane
On 2006-04-11, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Andrew - Supernews <andrew+nonews@supernews.com> writes: >> On 2006-04-10, Bruce Momjian <pgman@candle.pha.pa.us> wrote: >>>> [ security ] >>> It actually is the reason I have heard. > >> And it was duly debunked. > > That is the reasoning, and personally I agree with it. You don't leave > sharp objects sitting around if you have no need to have them out. > The availability of plpgsql or other PLs makes for a significant jump > in what a bad guy can do if he gets access to the database, Example please. Last time this was discussed, the claimed examples were things like running infinite loops as a resource exhaustion attack, which is pretty trivial to do in plain SQL functions or even in plain SQL without functions, and running things like brute-force attacks on password hashes (which also isn't hard using plain SQL functions). -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services
Tom Lane wrote:
> Andrew - Supernews <andrew+nonews@supernews.com> writes:
>> On 2006-04-10, Bruce Momjian <pgman@candle.pha.pa.us> wrote:
>>>> [ security ]
>>> It actually is the reason I have heard.
>
>> And it was duly debunked.
>
> That is the reasoning, and personally I agree with it. You don't leave
> sharp objects sitting around if you have no need to have them out.
Uhmmm exactly how is plpgsql a sharp object? plPerl... ok that makes
sense but you can't access the underlying OS with plpgsql.
> The availability of plpgsql or other PLs makes for a significant jump
> in what a bad guy can do if he gets access to the database,
What does enabling plpgsql do via access that you can't just do from an
SQL query?
Joshua D. Drake
so if a
> particular DB doesn't actually need the capability, it's best that it
> not be there. And that's without considering the possibility of genuine
> security holes in the PL, but just supposing that it only does what it's
> supposed to do.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
=== The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency:
+1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
On Mon, 10 Apr 2006, Joshua D. Drake wrote: > Tom Lane wrote: >> Andrew - Supernews <andrew+nonews@supernews.com> writes: >>> On 2006-04-10, Bruce Momjian <pgman@candle.pha.pa.us> wrote: >>>>> [ security ] >>>> It actually is the reason I have heard. >> >>> And it was duly debunked. >> >> That is the reasoning, and personally I agree with it. You don't leave >> sharp objects sitting around if you have no need to have them out. > > Uhmmm exactly how is plpgsql a sharp object? plPerl... ok that makes sense > but you can't access the underlying OS with plpgsql. Can you guarantee unequivocally that there are absolutely not security issues in plpgsql? I believe Tom's point is that it is not possible to do so, and, since plpgsql isn't something that all applications need/use, it isn't something that needs to be 'loaded by default' ... its like loading mod_perl in apache for an application that only uses PHP ... you can do it, but why bother? If Tom could cite any security issues with plpgsql, he would have probably fixed it by now ... but I don't believe he'd go out on a limb and state that there weren't any either ... ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
"Joshua D. Drake" <jd@commandprompt.com> writes:
> What does enabling plpgsql do via access that you can't just do from an
> SQL query?
SQL isn't Turing-complete --- plpgsql is. So if our would-be hacker has
a need to do some computation incidental to his hack, he can certainly
get it done in plpgsql, but not necessarily in plain SQL.
I don't feel a need to offer specific examples as requested by Andrew.
The point here is that we're offering a significantly more powerful
swiss army knife when we include plpgsql (or any other PL), and it's
hard to foresee the implications of that with any certainty.
regards, tom lane
On Tue, Apr 11, 2006 at 12:47:03AM -0400, Tom Lane wrote: > "Joshua D. Drake" <jd@commandprompt.com> writes: > > What does enabling plpgsql do via access that you can't just do from an > > SQL query? > > SQL isn't Turing-complete With all due respect, SQL *is* Turing-complete. Here's a little demo of this Turing-completeness: CREATE TABLE fib_mem( n numeric PRIMARY KEY, fib_n numeric NOT NULL ); CREATE OR REPLACE FUNCTION memoize_fib(n numeric, fib_n numeric) RETURNS numeric STRICT LANGUAGE SQL AS $$ INSERT INTO fib_mem VALUES ($1, $2); SELECT $2; $$; CREATE OR REPLACE FUNCTION fib(numeric) RETURNS numeric LANGUAGE SQL AS $$ SELECT COALESCE( (SELECT fib_n FROM fib_mem WHERE n=$1), memoize_fib( $1, CASE WHEN$1 < 2 THEN $1 ELSE fib($1-2) + fib($1-1) END ) ); $$; > --- plpgsql is. So if our would-be hacker has a need to do some > computation incidental to his hack, he can certainly get it done in > plpgsql, but not necessarily in plain SQL. > > I don't feel a need to offer specific examples as requested by > Andrew. The point here is that we're offering a significantly more > powerful swiss army knife when we include plpgsql (or any other PL), > and it's hard to foresee the implications of that with any > certainty. The cat is already out of the bag with SQL because it has branching and recursion, which is enough for Turing-completeness. Whether we decide to include PL/whatever by default or not shouldn't be predicated on the wrong assumption that these PLs have more power inside the database than SQL does. That said, I believe it's a *great* idea not to include untrusted PLs by default :) Cheers, D -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote!
On 2006-04-11, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
>> What does enabling plpgsql do via access that you can't just do from an
>> SQL query?
>
> SQL isn't Turing-complete
SQL with the ability to create recursive functions, as exists in pg, is
certainly turing-complete (within the usual practical sense of the term,
since no real machine has unlimited storage space).
A formal proof is left as an exercise for the reader; but several examples
of the power of SQL (not pl/pgsql) functions for performing iterative
operations can be found in the newsysviews source, along with a working
implementation of generate_series for 7.4 in plain SQL. (For convenience
that implementation has a limited range, but merely adding a few more
cross joins would extend that range as far as desired.)
Pl/pgsql may offer notational conveniences, but it has no real computational
power above plain SQL functions.
> I don't feel a need to offer specific examples as requested by Andrew.
Why not? You're basing your entire argument on a false premise (that
pl/pgsql is more powerful than SQL); I can provide specific examples of
why this is not the case, or refute any that you care to provide. For
example, here is an SQL function to generate all alphabetic strings of
a specified length:
create function alpha(integer) returns setof text language sql as $$ select x || chr(c) from alpha($1-1) s1(x),
generate_series(97,122) s2(c) where $1 > 0 union all select '' where $1 <= 0
$$;
(and yes, I can do it without generate_series if need be)
That takes ~97 seconds to execute alpha(5) on one of my machines, whereas
a simple generate_series returning the same number of rows takes ~30
seconds, so the performance is not at all bad.
> The point here is that we're offering a significantly more powerful
> swiss army knife when we include plpgsql (or any other PL), and it's
> hard to foresee the implications of that with any certainty.
pl/pgsql is not comparable to other PLs in this case. Specifically, it
does not provide access to any functionality that is not already part of
Postgres itself.
--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services
Andrew - Supernews wrote: > On 2006-04-11, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> I don't feel a need to offer specific examples as requested by Andrew. > > Why not? You're basing your entire argument on a false premise (that > pl/pgsql is more powerful than SQL); I can provide specific examples of > why this is not the case, or refute any that you care to provide. You can write trigger functions in plpgsql. -- Richard Huxton Archonet Ltd
On 2006-04-11, Richard Huxton <dev@archonet.com> wrote: > Andrew - Supernews wrote: >> On 2006-04-11, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> I don't feel a need to offer specific examples as requested by Andrew. >> >> Why not? You're basing your entire argument on a false premise (that >> pl/pgsql is more powerful than SQL); I can provide specific examples of >> why this is not the case, or refute any that you care to provide. > > You can write trigger functions in plpgsql. You can write rules without plpgsql. While rules and triggers are not equivalent, I think you'll be hard-pressed to come up with an example where a malicious intruder, with sufficient access to the system to create pl/pgsql functions if pl/pgsql is loaded, can carry out a useful attack using triggers that would not be possible without them. Let's try a simple example; changing the value of a column in future inserts into a table. Doing it without a trigger turns out to be simple; as a demonstration, this method allows an SQL function to be invoked: create function foox(foo) returns integer language sql as $$ update foo set value='bogus' where id=$1.id; select 1; $$; create rule foo_rule as on insert to foo do insert into bar values (foox(NEW)); insert into foo values (2,'bar'); INSERT 0 1 select * from foo;id | value ----+------- 1 | foo 2 | bogus (2 rows) So that's triggers without pl/pgsql. Anyone else want to try a challenge? -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services
> Can you guarantee unequivocally that there are absolutely not security > issues in plpgsql? Can you guarantee unequivocally that there are absolutely not security issues in PostgreSQL? > > I believe Tom's point is that it is not possible to do so, and, since > plpgsql isn't something that all applications need/use, it isn't > something that needs to be 'loaded by default' ... its like loading > mod_perl in apache for an application that only uses PHP ... you can do > it, but why bother? Well.... many distributions do but no it is not the same. plPGSQL is the default procedural language for PostgreSQL. It is not a contrib module, and it is built by default. So why not install it by default to make it just one step easier for our community? Sincerely, Joshua D. Drake > > If Tom could cite any security issues with plpgsql, he would have > probably fixed it by now ... but I don't believe he'd go out on a limb > and state that there weren't any either ... > > > ---- > Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) > Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664 > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/
Tom Lane wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
>> What does enabling plpgsql do via access that you can't just do from an
>> SQL query?
>
> SQL isn't Turing-complete --- plpgsql is. So if our would-be hacker has
> a need to do some computation incidental to his hack, he can certainly
> get it done in plpgsql, but not necessarily in plain SQL.
O.k. sure... but if the hackers wants to do something really bad it is
easy to do so in SQL... TRUNCATE, DELETE FROM, VACUUM FULL, DROP... ,
SELECT generate_series()
Sincerely,
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency:
+1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Richard Huxton wrote:
> Andrew - Supernews wrote:
>> On 2006-04-11, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> I don't feel a need to offer specific examples as requested by Andrew.
>>
>> Why not? You're basing your entire argument on a false premise (that
>> pl/pgsql is more powerful than SQL); I can provide specific examples of
>> why this is not the case, or refute any that you care to provide.
>
> You can write trigger functions in plpgsql.
That doesn't make it more powerful, just that it has another feature.
Keep in mind that all internal functions that PostgreSQL includes are
called from SQL.
Joshua D. Drake
>
--
=== The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency:
+1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
On Mon, Apr 10, 2006 at 11:02:50PM -0700, David Fetter wrote: > On Tue, Apr 11, 2006 at 12:47:03AM -0400, Tom Lane wrote: > > "Joshua D. Drake" <jd@commandprompt.com> writes: > > > What does enabling plpgsql do via access that you can't just do from an > > > SQL query? > > > > SQL isn't Turing-complete > > With all due respect, SQL *is* Turing-complete. Here's a little demo > of this Turing-completeness: Rather than debate how turing complete SQL is, look at the real issue: is a compromised system with plPGSQL installed more dangerous than a compromised system without plPGSQL. As far as I can see, it's not. SQL makes it just as easy to DoS the machine (just select a large cartesian product). plPGSQL doesn't provide any inherent ability to damage data outside the database, and it doesn't make trashing the database any easier than it is with plain SQL. About the only thing I can think of that plPGSQL lets you do that SQL doesn't is to raise arbitrary errors, but that hardly seems like much of an increased risk. There is some limited truth to the argument that plPGSQL potentially opens more potential for a machine to be compromised, but much less so than allowing connections from any IP does for example. I haven't seen any real reason not to include plPGSQL by default, especially since removing whatever slight risk exists is a simple DROP LANGUAGE away. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
"Jim C. Nasby" <jnasby@pervasive.com> writes:
> Rather than debate how turing complete SQL is, look at the real issue:
> is a compromised system with plPGSQL installed more dangerous than a
> compromised system without plPGSQL. As far as I can see, it's not.
You're disregarding the possibility that plpgsql itself is the source of
a security hole ...
More realistically, though, the theoretical point that you can do
arbitrary calculations by turning loops into recursive SQL functions is
mostly just theoretical, and the reason is that you won't be able to
loop very many times before running out of stack space. (On my machine
it looks like you can recurse a trivial SQL function only about 600
times before hitting the default stack limit.) If you have an exploit
that involves moderate amounts of calculation within the server --- say,
brute force password cracking --- the availability of a PL will render
that exploit actually practical, whereas with only SQL functions to work
with it won't be.
regards, tom lane
On Tue, Apr 11, 2006 at 04:35:05PM -0400, Tom Lane wrote: > "Jim C. Nasby" <jnasby@pervasive.com> writes: > > Rather than debate how turing complete SQL is, look at the real > > issue: is a compromised system with plPGSQL installed more > > dangerous than a compromised system without plPGSQL. As far as I > > can see, it's not. > > You're disregarding the possibility that plpgsql itself is the > source of a security hole ... So might SQL. > More realistically, though, the theoretical point that you can do > arbitrary calculations by turning loops into recursive SQL functions > is mostly just theoretical, and the reason is that you won't be able > to loop very many times before running out of stack space. (On my > machine it looks like you can recurse a trivial SQL function only > about 600 times before hitting the default stack limit.) If you > have an exploit that involves moderate amounts of calculation within > the server --- say, brute force password cracking --- the > availability of a PL will render that exploit actually practical, > whereas with only SQL functions to work with it won't be. The function I sent memoizes to a table, which avoids the stack space problem you mentioned. Cheers, D -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote!
David Fetter <david@fetter.org> writes:
> On Tue, Apr 11, 2006 at 04:35:05PM -0400, Tom Lane wrote:
>> More realistically, though, the theoretical point that you can do
>> arbitrary calculations by turning loops into recursive SQL functions
>> is mostly just theoretical, and the reason is that you won't be able
>> to loop very many times before running out of stack space. (On my
>> machine it looks like you can recurse a trivial SQL function only
>> about 600 times before hitting the default stack limit.) If you
>> have an exploit that involves moderate amounts of calculation within
>> the server --- say, brute force password cracking --- the
>> availability of a PL will render that exploit actually practical,
>> whereas with only SQL functions to work with it won't be.
> The function I sent memoizes to a table, which avoids the stack space
> problem you mentioned.
In general that's not possible, and even for the specific case, it still
looks to me like fib(n) will use O(n) recursion levels if the table is
initially empty.
regards, tom lane
On Tue, Apr 11, 2006 at 05:01:17PM -0400, Tom Lane wrote: > David Fetter <david@fetter.org> writes: > > On Tue, Apr 11, 2006 at 04:35:05PM -0400, Tom Lane wrote: > >> More realistically, though, the theoretical point that you can do > >> arbitrary calculations by turning loops into recursive SQL > >> functions is mostly just theoretical, and the reason is that you > >> won't be able to loop very many times before running out of stack > >> space. (On my machine it looks like you can recurse a trivial > >> SQL function only about 600 times before hitting the default > >> stack limit.) If you have an exploit that involves moderate > >> amounts of calculation within the server --- say, brute force > >> password cracking --- the availability of a PL will render that > >> exploit actually practical, whereas with only SQL functions to > >> work with it won't be. > > > The function I sent memoizes to a table, which avoids the stack > > space problem you mentioned. > > In general that's not possible, and even for the specific case, it > still looks to me like fib(n) will use O(n) recursion levels if the > table is initially empty. I don't get your not getting this 'cause you're a very smart guy. Are you under the impression that an attacker will stop because he has to try a few times? Cheers, D -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote!
David Fetter <david@fetter.org> writes:
> I don't get your not getting this 'cause you're a very smart guy. Are
> you under the impression that an attacker will stop because he has to
> try a few times?
No, I'm saying that having access to a PL renders certain classes of
attacks significantly more efficient. A determined attacker with
unlimited time may not care, but in the real world, security is
relative. You don't have to make yourself an impenetrable target,
only a harder target than the next IP address --- or at least hard
enough that the attacker's likely to get noticed before he's succeeded.
(And certainly, doing anything compute-intensive via recursive SQL
functions is not the way to go unnoticed.)
In the end it's only one small component of security, but any security
expert will tell you that you take all the layers of security that you
can get. If you don't need a given bit of functionality, it shouldn't
get installed.
regards, tom lane
Are there are more possibilities for some bug in the plpgsql engine to allow an exploit: actually changing the stack through a buffer overflow, or a bug in an intrinsic function, or allowing an injection that crosses some privilege boundary, via someone else's EXECUTE? It's a lot easier to verify the few places where straight SQL can interact with the outside world (NOTIFY, COPY, and trojan .so's come to mind). It is harder for someone to find an unexpected combined-effect exploit, since there's not much you can combine. Perhaps somebody in the core team has reservations about possible points of error to certify in plpgsql: is every possible weird array-overflow case covered? Further, can some innocuous side-effects in execution (INOUT parameters; function ownership; schema settings) combine to create a hole? There's just that much more to worry about. As they say, in theory, theory and practice are the same. In practice, they differ :0) I can understand someone being cautious about making guarantees (or even risk estimates) about plpgsql versus the core engine. And so, just like not INITIALLY letting the server listen on all TCP sockets, it's modest conservatism to let the default be a bit restricted. -- Engineers think that equations approximate reality. Physicists think that reality approximates the equations. Mathematicians never make the connection.
On Tue, 11 Apr 2006, Joshua D. Drake wrote: > >> Can you guarantee unequivocally that there are absolutely not security >> issues in plpgsql? > > Can you guarantee unequivocally that there are absolutely not security issues > in PostgreSQL? No, but does that mean we should increase the potential by adding in something that not everyone that runs PostgreSQL actually uses? ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
On Tue, Apr 11, 2006 at 05:20:02PM -0400, Tom Lane wrote: > David Fetter <david@fetter.org> writes: > > I don't get your not getting this 'cause you're a very smart guy. > > Are you under the impression that an attacker will stop because he > > has to try a few times? > > No, I'm saying that having access to a PL renders certain classes of > attacks significantly more efficient. A determined attacker with > unlimited time may not care, but in the real world, security is > relative. You don't have to make yourself an impenetrable target, > only a harder target than the next IP address --- or at least hard > enough that the attacker's likely to get noticed before he's > succeeded. (And certainly, doing anything compute-intensive via > recursive SQL functions is not the way to go unnoticed.) > > In the end it's only one small component of security, but any > security expert will tell you that you take all the layers of > security that you can get. If you don't need a given bit of > functionality, it shouldn't get installed. As others have mentioned, and I will reiterate here: 1. Anyone who imagines that PL/PgSQL presents a bigger or more vulnerable attack surface can remove it via DROP LANGUAGE. 2. Anybody who wants to do harm inside the database can do it to arbitrary levels of damage in SQL with RULEs, recursive functions, set-returning functions, etc. Cheers, D -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote!
Tom Lane wrote: > In the end it's only one small component of security, but any security > expert will tell you that you take all the layers of security that you > can get. If you don't need a given bit of functionality, it shouldn't > get installed. > I think any security expert would say that if let non trustworthy people get so far as to create their own SQL statements, you're in big trouble. Plpgsql or not. I fail to see what the real issue is here. Your argument is analog to saying "don't install bash on a Linux system by default. People might do bad things with it". Regards, Thomas Hallgren
> No, but does that mean we should increase the potential by adding in > something that not everyone that runs PostgreSQL actually uses? Using this argument I could say that we don't need primary keys, foreign keys, views or rules. Especially the latter 3 ;). Sincerely, Joshua D. Drake > > ---- > Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) > Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664 > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/
On 2006-04-11, Tom Lane <tgl@sss.pgh.pa.us> wrote: > David Fetter <david@fetter.org> writes: >> I don't get your not getting this 'cause you're a very smart guy. Are >> you under the impression that an attacker will stop because he has to >> try a few times? > > No, I'm saying that having access to a PL renders certain classes of > attacks significantly more efficient. Not significantly, and I'll happily back up that assertion with code examples. (I've already posted an example brute-force search to illustrate that.) > A determined attacker with > unlimited time may not care, but in the real world, security is > relative. You don't have to make yourself an impenetrable target, > only a harder target than the next IP address --- or at least hard > enough that the attacker's likely to get noticed before he's succeeded. > (And certainly, doing anything compute-intensive via recursive SQL > functions is not the way to go unnoticed.) Doing something compute-intensive with pl/pgsql functions will be just as noticable. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services
On 2006-04-11, Tom Lane <tgl@sss.pgh.pa.us> wrote: > More realistically, though, the theoretical point that you can do > arbitrary calculations by turning loops into recursive SQL functions is > mostly just theoretical, It's not at all theoretical. The very practical problem of trying to write code that does useful stuff (like generate_series on 7.4 or parsing the values in pg_trigger.tgargs) without using pl/pgsql is a wonderful demonstration of just how much you can really do in plain SQL functions by using appropriate techniques. Sure, it requires some specialised approaches, but then so does system cracking ... > and the reason is that you won't be able to > loop very many times before running out of stack space. (On my machine > it looks like you can recurse a trivial SQL function only about 600 > times before hitting the default stack limit.) 600 times is enough for the function to do more computation than could ever be done in the lifetime of the universe. (Consider: how long would it take to do the Towers of Hanoi with 600 disks?) > If you have an exploit > that involves moderate amounts of calculation within the server --- say, > brute force password cracking --- the availability of a PL will render > that exploit actually practical, whereas with only SQL functions to work > with it won't be. Tom, when you're engaged in a debate on a topic, it's polite to actually _read_ what other people are posting. I've already posted a very straightforward example of code that will happily loop over 300 million values using a recursion depth of no greater than 7, and I specifically chose it because it shows how easily large brute-force searches can be done in plain SQL. The existence of cross joins means that arbitrarily large loops can be constructed without needing either deep recursion or large materialized function result sets. In many cases these methods give you code which is both simpler and faster than the equivalent in pl/pgsql (why code naive nested loops in pl/pgsql, for example, when the executor already has that functionality built in?). Here's your brute-force password crack (try it! should only take an hour or two) using the simple alpha(n) function example from my other post: select a||b||c from alpha(3) s1(a), alpha(3) s2(b), alpha(2) s3(c)where md5(a||b||c||'andrew') = 'ff113aee991f0a3519c3d4f97414561a'limit1; -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services
On Tue, 2006-04-11 at 17:20 -0400, Tom Lane wrote: > No, I'm saying that having access to a PL renders certain classes of > attacks significantly more efficient. A determined attacker with > unlimited time may not care, but in the real world, security is > relative. That's a fair point. Perhaps a compromise would be to enable pl/pgsql by default, but not grant the USAGE privilege on it. This would allow superusers to define pl/pgsql functions without taking any additional steps. Non-superusers could be given access to pl/pgsql via a simple GRANT -- either for all users via GRANT TO PUBLIC, or on a more granular basis as desired. This would lower the barrier to using pl/pgsql by a fairly significant margin, but not cause any additional security exposure that I can see. -Neil
On Tue, 11 Apr 2006, Joshua D. Drake wrote: > >> No, but does that mean we should increase the potential by adding in >> something that not everyone that runs PostgreSQL actually uses? > > Using this argument I could say that we don't need primary keys, foreign > keys, views or rules. Especially the latter 3 ;). *slap forehead* *groan* then again, if we could pull it out and move it into loadable modules ... hmmmm ... >:) ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
On Tue, 2006-04-11 at 19:35 -0300, Marc G. Fournier wrote: > On Tue, 11 Apr 2006, Joshua D. Drake wrote: > > > > >> No, but does that mean we should increase the potential by adding in > >> something that not everyone that runs PostgreSQL actually uses? > > > > Using this argument I could say that we don't need primary keys, foreign > > keys, views or rules. Especially the latter 3 ;). > > *slap forehead* *groan* > > then again, if we could pull it out and move it into loadable modules ... > hmmmm ... >:) Oh goodness. We could declare that we are better then MySQL because our referential integrity is optional... oh wait... Joshua D. Drake > > > ---- > Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) > Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664 > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/
On Tue, Apr 11, 2006 at 03:43:56PM -0700, Joshua D. Drake wrote: > On Tue, 2006-04-11 at 19:35 -0300, Marc G. Fournier wrote: > > On Tue, 11 Apr 2006, Joshua D. Drake wrote: > > > > > > > >> No, but does that mean we should increase the potential by adding in > > >> something that not everyone that runs PostgreSQL actually uses? > > > > > > Using this argument I could say that we don't need primary keys, foreign > > > keys, views or rules. Especially the latter 3 ;). > > > > *slap forehead* *groan* > > > > then again, if we could pull it out and move it into loadable modules ... > > hmmmm ... >:) > > Oh goodness. We could declare that we are better then MySQL because our > referential integrity is optional... oh wait... Hey, if our RI was optional but we threw an error when you tried to use it when it was disabled we *would* be better than MySQL... -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
> That's a fair point.
>
> Perhaps a compromise would be to enable pl/pgsql by default, but not
> grant the USAGE privilege on it. This would allow superusers to define
> pl/pgsql functions without taking any additional steps. Non-superusers
> could be given access to pl/pgsql via a simple GRANT -- either for all
> users via GRANT TO PUBLIC, or on a more granular basis as desired. This
> would lower the barrier to using pl/pgsql by a fairly significant
> margin, but not cause any additional security exposure that I can see.
That seems reasonable.
Joshua D. Drake
>
> -Neil
>
>
--
=== The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency:
+1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Neil, > Perhaps a compromise would be to enable pl/pgsql by default, but not > grant the USAGE privilege on it. This would allow superusers to define > pl/pgsql functions without taking any additional steps. Non-superusers > could be given access to pl/pgsql via a simple GRANT -- either for all > users via GRANT TO PUBLIC, or on a more granular basis as desired. This > would lower the barrier to using pl/pgsql by a fairly significant > margin, but not cause any additional security exposure that I can see. Would this support PL/pgSQL based admin functions, though? -- Josh Berkus Aglio Database Solutions San Francisco
Am Dienstag, 11. April 2006 23:20 schrieb Tom Lane: > In the end it's only one small component of security, but any security > expert will tell you that you take all the layers of security that you > can get. I think what the security experts are saying is that you need a thorough evaluation of assets, attackers, risks, and countermeasures, and I don't see that here. -- Peter Eisentraut http://developer.postgresql.org/~petere/
On Wed, Apr 12, 2006 at 12:32:52PM +0200, Peter Eisentraut wrote:
> Am Dienstag, 11. April 2006 23:20 schrieb Tom Lane:
> > In the end it's only one small component of security, but any
> > security expert will tell you that you take all the layers of
> > security that you can get.
>
> I think what the security experts are saying is that you need a
> thorough evaluation of assets, attackers, risks, and
> countermeasures, and I don't see that here.
Exactly. One security expert you may have heard of, Bruce Schneier,
has laid out a 5-step process, and we haven't gotten to step 1 yet
where the proposal is "turn PL/PgSQL off by default."
Bruce Schneier's 5-Step Security Evaluation
1. What assets are you trying to protect? 2. What are the risks to those assets? 3. How well does the security
solutionmitigate those risks? 4. What other risks does the security solution cause? 5. What costs and tradeoffs
doesthe security solution impose?
Let's start with step 1 and go forward from there.
Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter
Remember to vote!
On 4/11/06, Neil Conway <neilc@samurai.com> wrote: > On Tue, 2006-04-11 at 17:20 -0400, Tom Lane wrote: > > No, I'm saying that having access to a PL renders certain classes of > > attacks significantly more efficient. A determined attacker with > > unlimited time may not care, but in the real world, security is > > relative. > > That's a fair point. > > Perhaps a compromise would be to enable pl/pgsql by default, but not > grant the USAGE privilege on it. This would allow superusers to define +1 (+10 if I could, and I'm doing my best not to pontificate about security) merlin
Marc G. Fournier wrote: > On Tue, 11 Apr 2006, Joshua D. Drake wrote: > > > > >> Can you guarantee unequivocally that there are absolutely not security > >> issues in plpgsql? > > > > Can you guarantee unequivocally that there are absolutely not security issues > > in PostgreSQL? > > No, but does that mean we should increase the potential by adding in > something that not everyone that runs PostgreSQL actually uses? Now that we throw a clear suggestion to use createlang for people who try to create plpgsql functions, the requests to include plpgsql by default has dropped to almost zero: test=> create function x() as '' language 'plpgsql';ERROR: language "plpgsql" does not existHINT: Use CREATE LANGUAGE toload the language into the database. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +