Re: [HACKERS] plpgsql by default
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 exist HINT: Use CREATE LANGUAGE to load the language into the database. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] plpgsql by default
Eric Lauzon wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Merlin Moncure Sent: 12 avril 2006 12:22 To: Neil Conway Cc: Tom Lane; David Fetter; Jim C. Nasby; Joshua D. Drake; [EMAIL PROTECTED]; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] plpgsql by default On 4/11/06, Neil Conway <[EMAIL PROTECTED]> 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 One way to circumvent the hassle of having to create the language is to create the database from a template that has the language , hence semi-default plpgsql handler by "default". On the security side, if you implement strong ACLS on the data manipulation if the database is compromised to a level where a low priviliged user database access is compromised there shouldn't be any danger toward having them using SQL or plpgsql. The dark side of this could be some type of privilege escalation scheme present inside postgresql. As example MS-SQL xp_* stored proc, are a vulnerability vector if the compromised user can execute them. So if by default the attacked application is running as the "postgres" user, what will you do to prevent them from manipulating internal's? :) This is just a little safer than surfing the internet with MSSQL installed and the sa user having no password :-) I wonder if a less-privileged user should be present in the database by default, with some advise to use that user instead of postgres for standard connections. I wouldn't be surprised if >80 % of win32 pgsql installations have a single user only... Regards, Andreas ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] plpgsql by default
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of > Merlin Moncure > Sent: 12 avril 2006 12:22 > To: Neil Conway > Cc: Tom Lane; David Fetter; Jim C. Nasby; Joshua D. Drake; > [EMAIL PROTECTED]; pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] plpgsql by default > > On 4/11/06, Neil Conway <[EMAIL PROTECTED]> 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 > One way to circumvent the hassle of having to create the language is to create the database from a template that has the language , hence semi-default plpgsql handler by "default". On the security side, if you implement strong ACLS on the data manipulation if the database is compromised to a level where a low priviliged user database access is compromised there shouldn't be any danger toward having them using SQL or plpgsql. The dark side of this could be some type of privilege escalation scheme present inside postgresql. As example MS-SQL xp_* stored proc, are a vulnerability vector if the compromised user can execute them. So if by default the attacked application is running as the "postgres" user, what will you do to prevent them from manipulating internal's? :) -elz AVERTISSEMENT CONCERNANT LA CONFIDENTIALITE Le present message est a l'usage exclusif du ou des destinataires mentionnes ci-dessus. Son contenu est confidentiel et peut etre assujetti au secret professionnel. Si vous avez recu le present message par erreur, veuillez nous en aviser immediatement et le detruire en vous abstenant d'en faire une copie, d'en divulguer le contenu ou d'y donner suite. CONFIDENTIALITY NOTICE This communication is intended for the exclusive use of the addressee identified above. Its content is confidential and may contain privileged information. If you have received this communication by error, please notify the sender and delete the message without copying or disclosing it. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] plpgsql by default
On 4/11/06, Neil Conway <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] plpgsql by default
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 solution mitigate those risks? 4. What other risks does the security solution cause? 5. What costs and tradeoffs does the security solution impose? Let's start with step 1 and go forward from there. Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] plpgsql by default
> > I wonder if Oracle ever recommended disabling PL/SQL (not to > mention MS Transact-SQL)... > Don't know abiout Oracle, but you can't disable Transact-SQL in SQL Server 7.0 or 2000 (don't know about 2003^h5) because Enterprise Manager and sp_help* require it. And +1 for not installing plpgsql by default. I just had to CREATELANG on 20-odd servers scattered across the US, and it was no big thing. Just rolled it out with the upgrade that needed it. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] plpgsql by default
Dave Page wrote: Keeping PostgreSQL as secure as possible out of the box pretty much requires us to do the same in my mind - if an major feature such as pl/pgsql is easy for the user to enable should they want it, then it should be disabled by default to minimise the number of attack vectors for all those users that do not want it. I wonder if Oracle ever recommended disabling PL/SQL (not to mention MS Transact-SQL)... Regards, Andreas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] plpgsql by default
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of > Peter Eisentraut > Sent: 12 April 2006 11:33 > To: pgsql-hackers@postgresql.org > Cc: Tom Lane; David Fetter; Jim C. Nasby; Joshua D. Drake; > [EMAIL PROTECTED] > Subject: Re: [HACKERS] plpgsql by default > > 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. Regardless of any evaluations, or any proven or thoretical risks in any given code it's Basic Security 101 stuff to disable/remove anything that is not required in a system to immediately reduce the number of potential attacks that could be made. Microsoft are the classic example - they enabled pretty much everything by default in Windows leaving it vulnerable to attack through services many people weren't using (NetBios on a single home user machine for example). You install a modern version of Windows now though and you'll see virtually every network service is disabled, or even uninstalled by default, leaving it up the user to install as required. In addition of course, those services are still subject to the normal bug fixes and updates for those users that do require them. Keeping PostgreSQL as secure as possible out of the box pretty much requires us to do the same in my mind - if an major feature such as pl/pgsql is easy for the user to enable should they want it, then it should be disabled by default to minimise the number of attack vectors for all those users that do not want it. Regards, Dave ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] plpgsql by default
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/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] plpgsql by default
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] plpgsql by default
> 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/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] plpgsql by default
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 ... > > h ... >:) > > 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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] plpgsql by default
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 ... > h ... >:) 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: [EMAIL PROTECTED] 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/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] plpgsql by default
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 ... h ... >:) Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] plpgsql by default
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] plpgsql by default
On 2006-04-11, Tom Lane <[EMAIL PROTECTED]> 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' limit 1; -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] plpgsql by default
On 2006-04-11, Tom Lane <[EMAIL PROTECTED]> wrote: > David Fetter <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] plpgsql by default
> 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: [EMAIL PROTECTED] 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/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] plpgsql by default
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] plpgsql by default
On Tue, Apr 11, 2006 at 05:20:02PM -0400, Tom Lane wrote: > David Fetter <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] plpgsql by default
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: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] plpgsql by default
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. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] plpgsql by default
David Fetter <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] plpgsql by default
On Tue, Apr 11, 2006 at 05:01:17PM -0400, Tom Lane wrote: > David Fetter <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] plpgsql by default
David Fetter <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] plpgsql by default
On Tue, Apr 11, 2006 at 04:35:05PM -0400, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] plpgsql by default
"Jim C. Nasby" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] plpgsql by default
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" <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] plpgsql by default
Richard Huxton wrote: Andrew - Supernews wrote: On 2006-04-11, Tom Lane <[EMAIL PROTECTED]> 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/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] plpgsql by default
Tom Lane wrote: "Joshua D. Drake" <[EMAIL PROTECTED]> 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/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] plpgsql by default
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: [EMAIL PROTECTED] 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/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] plpgsql by default
On 2006-04-11, Richard Huxton wrote: > Andrew - Supernews wrote: >> On 2006-04-11, Tom Lane <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] plpgsql by default
Andrew - Supernews wrote: On 2006-04-11, Tom Lane <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] plpgsql by default
On 2006-04-11, Tom Lane <[EMAIL PROTECTED]> wrote: > "Joshua D. Drake" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] plpgsql by default
On Tue, Apr 11, 2006 at 12:47:03AM -0400, Tom Lane wrote: > "Joshua D. Drake" <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] plpgsql by default
"Joshua D. Drake" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] plpgsql by default
On Mon, 10 Apr 2006, Joshua D. Drake wrote: Tom Lane wrote: Andrew - Supernews <[EMAIL PROTECTED]> writes: On 2006-04-10, Bruce Momjian 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: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] plpgsql by default
Tom Lane wrote: Andrew - Supernews <[EMAIL PROTECTED]> writes: On 2006-04-10, Bruce Momjian 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 [EMAIL PROTECTED] 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/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] plpgsql by default (was: Re: Remote administration contrib module)
On 2006-04-11, Tom Lane <[EMAIL PROTECTED]> wrote: > Andrew - Supernews <[EMAIL PROTECTED]> writes: >> On 2006-04-10, Bruce Momjian 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] plpgsql by default (was: Re: Remote administration contrib module)
Andrew - Supernews <[EMAIL PROTECTED]> writes: > On 2006-04-10, Bruce Momjian 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] plpgsql by default (was: Re: Remote administration contrib module)
On 2006-04-10, Bruce Momjian 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
