Re: [HACKERS] Including PL/PgSQL by default
On Thursday 21 February 2008 21:33, Tom Lane wrote:
Josh Berkus [EMAIL PROTECTED] writes:
On Thursday 21 February 2008 11:36, Tom Lane wrote:
Would it satisfy people if plpgsql were in postgres, but neither
template DB, after initdb?
No, the real-world use-case we're trying to satisfy is hosted and/or
locked-down installations where the developer doesn't have superuser
access. So putting it in postgres wouldn't help with that.
That statement is content-free, Josh. Exactly what are you assuming
this developer *does* have? For example, if he hasn't got createdb
privilege, it will hardly matter to him whether any DBs other than
postgres contain plpgsql. If he does have createdb, it's already
possible by default for him to create trusted languages including
plpgsql in his new DB. So it's still 100% unclear to me who we are
catering to.
There are a lot of people who have a database provider of some sort who
creates a database for them, giving them ownership of that specific database,
with pg_hba.conf specifying connection only to that db. They are then free to
muck about that database, installing anything they want, but they cannot load
any procedural languages since they only have non-superuser accounts. (This
does give them access to plsql, but not plpgsql). Sadly a lot of these
arrangements preclude (for valid reasons or not) the installation of any
contrib modules or installation of any procedural languages. It is these
users that 3rd party application developers (ie. mediawiki types) are trying
to accommodate. They would like to be able to take advantage of plpgsql in
their applications, but without it being included by default they have to
exclude it from their application.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [HACKERS] Including PL/PgSQL by default
Robert Treat wrote: They are then free to muck about that database, installing anything they want, but they cannot load any procedural languages since they only have non-superuser accounts. Except that they can in 8.3. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Including PL/PgSQL by default
Robert Treat wrote: There are a lot of people who have a database provider of some sort who creates a database for them, giving them ownership of that specific database, with pg_hba.conf specifying connection only to that db. They are then free to muck about that database, installing anything they want, but they cannot load any procedural languages since they only have non-superuser accounts. (This does give them access to plsql, but not plpgsql). This is no longer true. Please read the whole thread. cheers andrew ---(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] Including PL/PgSQL by default
Robert Treat [EMAIL PROTECTED] writes: On Thursday 21 February 2008 21:33, Tom Lane wrote: So it's still 100% unclear to me who we are catering to. There are a lot of people who have a database provider of some sort who creates a database for them, giving them ownership of that specific database, with pg_hba.conf specifying connection only to that db. They are then free to muck about that database, installing anything they want, but they cannot load any procedural languages since they only have non-superuser accounts. (This does give them access to plsql, but not plpgsql). Sadly a lot of these arrangements preclude (for valid reasons or not) the installation of any contrib modules or installation of any procedural languages. It is these users that 3rd party application developers (ie. mediawiki types) are trying to accommodate. They would like to be able to take advantage of plpgsql in their applications, but without it being included by default they have to exclude it from their application. That argument *was* valid ... before 8.3. Nowadays non-superuser DB owners can install trusted PLs in their DBs by themselves. (At least by default.) So I'm still unconvinced that we need more changes. regards, tom lane ---(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] Including PL/PgSQL by default
Tom, That argument *was* valid ... before 8.3. Nowadays non-superuser DB owners can install trusted PLs in their DBs by themselves. (At least by default.) So I'm still unconvinced that we need more changes. I agree. -- Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Including PL/PgSQL by default
On Tuesday 26 February 2008 12:20, Andrew Dunstan wrote:
Robert Treat wrote:
There are a lot of people who have a database provider of some sort who
creates a database for them, giving them ownership of that specific
database, with pg_hba.conf specifying connection only to that db. They
are then free to muck about that database, installing anything they want,
but they cannot load any procedural languages since they only have
non-superuser accounts. (This does give them access to plsql, but not
plpgsql).
This is no longer true. Please read the whole thread.
Interesting, seems pghackers dropped me from the list, so I missed several
messages in the thread. While wrangling with majordomo, I had time to
reflect that we're still causing issues for setups where you aren't db owner,
though I dont know what the breakdown is for these types of setups. (Although
since many 3rd party apps try to run as unprivileged users, I'm sure it's
more of a pain than people think)
*shrug*
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Including PL/PgSQL by default
Am Freitag, 22. Februar 2008 schrieb Dave Page: I know I'm gonna regret wading in on this, but in my mind this is akin to one of the arguments for including tsearch in the core server - namely that too many brain dead hosting providers won't add a contrib module or anything else in a customer's database because they don't understand that just because it's not there by default doesn't mean it's in any way second rate. Including pl/pgsql in template1 will help those folks who forwhatever reason use such providers, whilst more savvy providers can easily disable it post-initdb if thats what they want to do. Half of this entire thread is content-free because the participants are apparently not aware that a database owner can add plpgsql *without* superuser privileges. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Including PL/PgSQL by default
Tom Lane wrote: Josh Berkus [EMAIL PROTECTED] writes: On Thursday 21 February 2008 11:36, Tom Lane wrote: Would it satisfy people if plpgsql were in postgres, but neither template DB, after initdb? No, the real-world use-case we're trying to satisfy is hosted and/or locked-down installations where the developer doesn't have superuser access. So putting it in postgres wouldn't help with that. That statement is content-free, Josh. Exactly what are you assuming this developer *does* have? For example, if he hasn't got createdb privilege, it will hardly matter to him whether any DBs other than postgres contain plpgsql. If he does have createdb, it's already possible by default for him to create trusted languages including plpgsql in his new DB. So it's still 100% unclear to me who we are catering to. I probably shouldn't be answering this at two in the morning but... As I understand it in a hosted environment it is quite common that a superuser will do this: create database foo owner foo; Database foo would get plpgsql (as would user foo) at that point because template1 had plpgsql. Sincerely, Joshua D. Drake regards, tom lane ---(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] Including PL/PgSQL by default
Joshua D. Drake wrote: I probably shouldn't be answering this at two in the morning but... As I understand it in a hosted environment it is quite common that a superuser will do this: create database foo owner foo; Database foo would get plpgsql (as would user foo) at that point because template1 had plpgsql. I'm not seeing the benefit: alvherre=# create user plpg; CREATE ROLE alvherre=# create database plpg owner plpg; CREATE DATABASE alvherre=# \c plpg plpg Vous êtes maintenant connecté à la base de données « plpg »comme utilisateur « plpg ». plpg= create language plpgsql; CREATE LANGUAGE plpg= Yes, this is new in 8.3. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Including PL/PgSQL by default
Tom Lane wrote: Certainly you can cause massive DOS-type problems in plain SQL without any access to plpgsql, but that type of juvenile delinquency isn't what concerns me. What I'm worried about is whether plpgsql isn't a useful tool for the sort of professional who would much rather you never knew he was there. It's perhaps true that with generate_series() for looping and CASE for conditionals, plain SQL is Turing-complete and therefore could do anything, but it'd be awfully unpleasant and inefficient to use as a procedural language. The pro who doesn't want you to know he's there is never going to try to do password cracking that way; the resource consumption would be large enough to be noticed. plpgsql on the other hand is fast enough to be a *practical* tool for nefarious purposes. As a matter of interest, are there any other databases that have procedural languages that don't have them turned on by default? In fact, are there any that allow you to turn them off? It certainly looks like MySQL's PL is always on, unless I'm missing something, and ISTR PL/SQL is always on in Oracle, although it's now quite some years since I touched it in anger. I understand the argument about providing a platform for stealth computing, but our peers in the DB world don't seem too fussed, and neither do the world's security professionals. (I should add that I think DBMS servers with sensitive or mission critical data should never be exposed to the Internet nor indeed to anything but a trusted network. All access by end users should be via middleware with appropriately restricted privileges - including restrictions on the creation of functions) cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Including PL/PgSQL by default
-Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-hackers- [EMAIL PROTECTED] On Behalf Of Andrew Dunstan Sent: Friday, February 22, 2008 9:28 AM To: Tom Lane Cc: Joshua D. Drake; Greg Sabino Mullane; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Including PL/PgSQL by default Tom Lane wrote: Certainly you can cause massive DOS-type problems in plain SQL without any access to plpgsql, but that type of juvenile delinquency isn't what concerns me. What I'm worried about is whether plpgsql isn't a useful tool for the sort of professional who would much rather you never knew he was there. It's perhaps true that with generate_series() for looping and CASE for conditionals, plain SQL is Turing-complete and therefore could do anything, but it'd be awfully unpleasant and inefficient to use as a procedural language. The pro who doesn't want you to know he's there is never going to try to do password cracking that way; the resource consumption would be large enough to be noticed. plpgsql on the other hand is fast enough to be a *practical* tool for nefarious purposes. As a matter of interest, are there any other databases that have procedural languages that don't have them turned on by default? In fact, are there any that allow you to turn them off? It certainly looks like MySQL's PL is always on, unless I'm missing something, and ISTR PL/SQL is always on in Oracle, although it's now quite some years since I touched it in anger. PL/SQL is there by default and so are Java Stored Procedures. Neither can be removed. However, you can not create anything in Oracle without being given permission to create it. The notion that you can create a function because you have connect rights to the database is foreign to me. Connect should mean connect, not connect AND create. Include the language by default and remove CREATE on the public schema. Jon ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Including PL/PgSQL by default
On Fri, 22 Feb 2008 07:37:55 + Dave Page [EMAIL PROTECTED] wrote: I know I'm gonna regret wading in on this, but in my mind this is akin to one of the arguments for including tsearch in the core server - namely that too many brain dead hosting providers won't add a contrib module or anything else in a customer's database because they don't So their clients will go somewhere PLUG URL=http://www.Vex.Net/; / that does understand what they are installing and can support their users properly. How far are we supposed to go to support the clueless? understand that just because it's not there by default doesn't mean it's in any way second rate. Including pl/pgsql in template1 will help those folks who forwhatever reason use such providers, whilst more savvy providers can easily disable it post-initdb if thats what they want to do. And the first time someone uses pl/pgsql to do harm, even if it is due to their mis-configuration, who gets blamed? -- D'Arcy J.M. Cain [EMAIL PROTECTED] | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Including PL/PgSQL by default
Roberts, Jon wrote: However, you can not create anything in Oracle without being given permission to create it. The notion that you can create a function because you have connect rights to the database is foreign to me. Connect should mean connect, not connect AND create. Include the language by default and remove CREATE on the public schema. You'd need more than that. For example, since we don't support temp functions, we should probably ban the creation of functions in temp schemas (which I found was possible). cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Including PL/PgSQL by default
Andrew Dunstan [EMAIL PROTECTED] writes: Roberts, Jon wrote: However, you can not create anything in Oracle without being given permission to create it. The notion that you can create a function because you have connect rights to the database is foreign to me. Connect should mean connect, not connect AND create. You'd need more than that. For example, since we don't support temp functions, we should probably ban the creation of functions in temp schemas (which I found was possible). What for? If you don't want someone to use a language, you should either revoke his USAGE privilege on that language, or remove it from his database altogether. We have plenty of access-control mechanisms in place already, we don't need weird special-case restrictions on top of those. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Including PL/PgSQL by default
On Fri, 22 Feb 2008, D'Arcy J.M. Cain wrote: On Fri, 22 Feb 2008 07:37:55 + Dave Page [EMAIL PROTECTED] wrote: I know I'm gonna regret wading in on this, but in my mind this is akin to one of the arguments for including tsearch in the core server - namely that too many brain dead hosting providers won't add a contrib module or anything else in a customer's database because they don't So their clients will go somewhere PLUG URL=http://www.Vex.Net/; / that does understand what they are installing and can support their users properly. How far are we supposed to go to support the clueless? Being someone on one of these clueless providers, I wrote the patch (which made it into 8.3) which allows database owners to create trusted languages. For me, this was just far enough. The clueless tend to CREATE DATABASE %s OWNER %s, so then I can CREATE LANGUAGE plpgsql if I want it. This does not provide any detriment to the clueful, who can always REVOKE the privilege to create any PL (the patch also added ACL stuff for this). And, since the clueful tend to run web apps and such as non-database owners, if the web app was compromised and the db did not explicitly load plpgsql, the attacker could not use it. understand that just because it's not there by default doesn't mean it's in any way second rate. Including pl/pgsql in template1 will help those folks who forwhatever reason use such providers, whilst more savvy providers can easily disable it post-initdb if thats what they want to do. And the first time someone uses pl/pgsql to do harm, even if it is due to their mis-configuration, who gets blamed? -- The primary theme of SoupCon is communication. The acronym LEO represents the secondary theme: Law Enforcement Officials The overall theme of SoupCon shall be: Avoiding Communication with Law Enforcement Officials -- M. Gallaher ---(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] Including PL/PgSQL by default
Speaking as someone who is all about packaging PG for end users, and in truth could care less what is included by default, I can tell you that the top 3 requests I get from end users that don't want to muck around with building and installing themselves are for pl/pgsql, tsearch2 (now included) and PostGIS. The reasons are that most people don't want to have to know all the little details just to get started. Reading through this thread, the arguments really seem to boil down to 'it's added default bloat that is not required' and 'it is the procedural language of the platform and should be included'. (all the security concerns really boil down to implementation details, SQL injection with standard SQL is just as dangerous) As a packager, I respond to customer pressure by solving their needs, so I pre-package those contrib's as needed, but I do feel that they should be reviewed as potential core inclusions Andrew Satori - Owner Janitor Druware Software Designs Business Solutions for Small Business http://www.druware.com/ On Feb 22, 2008, at 11:09 AM, Andrew Dunstan wrote: Roberts, Jon wrote: However, you can not create anything in Oracle without being given permission to create it. The notion that you can create a function because you have connect rights to the database is foreign to me. Connect should mean connect, not connect AND create. Include the language by default and remove CREATE on the public schema. You'd need more than that. For example, since we don't support temp functions, we should probably ban the creation of functions in temp schemas (which I found was possible). cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend smime.p7s Description: S/MIME cryptographic signature
Re: [HACKERS] Including PL/PgSQL by default
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 D'Arcy J.M. Cain wrote: Besides, proof that it would do no extra harm is hardly a strong argumet for including it. Given how easy it is to add it to any DB that needs it, I fail to see why we should add it by default. Because we're not talking about people who have access to a psql command line. Personally I would like to see more things removed from PG and have them added as modules when required. Yes, that will do wonders for our mindshare and adoption rate. (Dave Page) I know I'm gonna regret wading in on this, but in my mind this is akin to one of the arguments for including tsearch in the core server - namely that too many brain dead hosting providers won't add a contrib module or anything else in a customer's database because they don't (D'Arcy) So their clients will go somewhere PLUG URL=http://www.Vex.Net/; / that does understand what they are installing and can support their users properly. How far are we supposed to go to support the clueless? Clueless is rather a harsh word to throw out. There's a spectrum of Postgres users - from backend hackers that install Postgres via cvs HEAD, to people who do a yum install, to people who are using an app which uses Postgres in the backend, and who are barely aware that Postgres is being used. Supporting them all is a balancing act, but things like putting tsearch2 in core is absolutely a step in the right direction. And the first time someone uses pl/pgsql to do harm, even if it is due to their mis-configuration, who gets blamed? The person who did the harm perhaps? This just seems unnecessary FUD. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200802221147 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAke+/QUACgkQvJuQZxSWSsgWlgCdElnDyCKvoD57Oz7UyqIw1hJe wsYAn3u54vmDAt4qRNlI08A7w3dj2b7q =IJzq -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Including PL/PgSQL by default
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: For example, since we don't support temp functions, we should probably ban the creation of functions in temp schemas (which I found was possible). What for? If you don't want someone to use a language, you should either revoke his USAGE privilege on that language, or remove it from his database altogether. Good point. Actually, this has made me rethink the whole proposal. Things could get quite sticky if we have initdb put plpgsql in template1. The language would be owned by the superuser, not the db owner, and so the db owner would not be able to control usage on it. And if we withdrew usage on it from public in template1 the db owner wouldn't even be able to use the language. So on reflection I'm now inclined to say we should not change what we are now doing, which is simply to allow the db owner to install and control access to the language. Perhaps there is a case for removing public usage from the default ACL for languages, or at least for installable PLs, but I suspect that would just break huge numbers of apps, unless we had some sort of grandfather clause. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Including PL/PgSQL by default
Andrew Dunstan [EMAIL PROTECTED] writes: So on reflection I'm now inclined to say we should not change what we are now doing, which is simply to allow the db owner to install and control access to the language. +1. It's worth pointing out here that we just changed the rules in 8.3 to make this easier. We should at least wait to gain some field experience with 8.3 before we conclude that we need to change them again. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Including PL/PgSQL by default
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Fri, 22 Feb 2008 12:31:14 -0500 Tom Lane [EMAIL PROTECTED] wrote: Andrew Dunstan [EMAIL PROTECTED] writes: So on reflection I'm now inclined to say we should not change what we are now doing, which is simply to allow the db owner to install and control access to the language. +1. It's worth pointing out here that we just changed the rules in 8.3 to make this easier. We should at least wait to gain some field experience with 8.3 before we conclude that we need to change them again. Not really sure what to think here. On the one hand I agree that since the dbowner can load it at their leisure its cool. On the other hand I wonder why we continue to add extra unnecessary steps to our life. Yes, it is a simple step but it is one that doesn't need to be taken, so why are we making people expend the calories on it? Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHvwj/ATb/zqfZUUQRAnbeAJ0QOGN6oNs+IzdQLpB9VZ2p1grmegCeN4Zc ZbOO5Rg1fPce1eIFdx+YAWs= =rqrK -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Including PL/PgSQL by default
Joshua D. Drake [EMAIL PROTECTED] writes: Not really sure what to think here. On the one hand I agree that since the dbowner can load it at their leisure its cool. On the other hand I wonder why we continue to add extra unnecessary steps to our life. Yes, it is a simple step but it is one that doesn't need to be taken, so why are we making people expend the calories on it? As Andrew pointed out, a preinstalled language will be much harder for db owners to manage. And I think it would make doing database dump/restore as a non-superuser virtually impossible. It's not going to be all a bed of roses if we do that. 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] Including PL/PgSQL by default
On Fri, Feb 22, 2008 at 11:30:28AM -0500, Andrew Satori wrote: As a packager, I respond to customer pressure by solving their needs, so I pre-package those contrib's as needed, but I do feel that they should be reviewed as potential core inclusions Given that you don't need to be superuser to create trusted languages anymore, maybe we should make an alias: ENABLE LANGUAGE plpgsql; Then people will think it's already installed, but they need to enable it... Postgis is harder. Ideas for a module system have been floated before but never got anywhere... Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Those who make peaceful revolution impossible will make violent revolution inevitable. -- John F Kennedy ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Including PL/PgSQL by default
On Fri, Feb 22, 2008 at 10:09 AM, in message [EMAIL PROTECTED], Andrew Dunstan [EMAIL PROTECTED] wrote: Roberts, Jon wrote: However, you can not create anything in Oracle without being given permission to create it. The notion that you can create a function because you have connect rights to the database is foreign to me. Connect should mean connect, not connect AND create. Include the language by default and remove CREATE on the public schema. You'd need more than that. For example, since we don't support temp functions, we should probably ban the creation of functions in temp schemas (which I found was possible). I also found the default rights within a database surprising. It is now our standard practice to adjust the rights in a database along these lines when it is created: postgres=# create database dtr with owner dtrowner; CREATE DATABASE postgres=# \c dtr You are now connected to database dtr. dtr=# revoke create on database dtr from public; REVOKE dtr=# revoke create on schema public from public; REVOKE dtr=# grant create on schema public to dtrowner; GRANT The goal is to restrict creation of non-temporary objects to the database owner. If you know of some way to create any object as a user other than the database owner or a superuser in a database set up this way, please elaborate. If I need to follow up on it, I'll take it over to the ADMIN list. -Kevin ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Including PL/PgSQL by default
Peter, Half of this entire thread is content-free because the participants are apparently not aware that a database owner can add plpgsql *without* superuser privileges. Yep. Among 280+ new features for 8.3, most of us missed that patch. Thanks, Jeremy! All, I think Jeremy's patch pretty much solves most use-cases. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Including PL/PgSQL by default
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 I grow weary of repeating this: it's not about resource consumption, nor about potential security holes in plpgsql itself. It's about handing attackers the capability to further exploit *other* security holes. Well, without specific examples, I'm not sure I understand what plpgsql buys you that you could not do other ways (e.g. generate_series() for looping). An earlier thread mentioned someone with access to pg_shadow writing a function to hash random passwords and comparing them, but if someone has access to pg_shadow, surely they can simply download the info to their local box for a more efficient cracking attempt? In any rate, that's not really a security hole, so perhaps a better example exists. There are so many simple ways to do bad things /without/ plpgsql, I just don't see how the theoretical harm in it being used as an attack vector even comes close to the benefits of having it installed by default. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200802211227 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAke9tdIACgkQvJuQZxSWSsieowCfQTbmdmGdIJSpWCOU5S2bHSR5 1PgAnjxjOV7Dh1X9nF3pPjDDBosiX0Tx =Z6yR -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Including PL/PgSQL by default
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thu, 21 Feb 2008 17:34:06 - Greg Sabino Mullane [EMAIL PROTECTED] wrote: There are so many simple ways to do bad things /without/ plpgsql, I just don't see how the theoretical harm in it being used as an attack vector even comes close to the benefits of having it installed by default. Exactly, once a hacker has access all bets are off. This theorectical implication of badness isn't helpful without some level of practical application. It is so easy to DOS or DELETE a postgresql database if it were compromised that adding plpgsql is hardly a consideration with that argument. Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHvbsXATb/zqfZUUQRAvW0AKCnr6I7lXqJXV9v3hCVgShp06w4lwCePaCx xWL/HvG0IGyztE0pzXJ7/kc= =h9tg -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Including PL/PgSQL by default
Tom, I grow weary of repeating this: it's not about resource consumption, nor about potential security holes in plpgsql itself. It's about handing attackers the capability to further exploit *other* security holes. Well, without specific examples, I'm not sure I understand what plpgsql buys you that you could not do other ways (e.g. generate_series() for looping). I have to agree with Greg here: I don't see what significant new security issues PL/pgSQL opens up. Certainly including PL/perl or PL/sh would, but PL/pgSQL? One of the reasons we advertise to use PostgreSQL is our ability to do sophisticated backend database things, which other OSDBs don't have. I agree that there should be some way to disable PL/pgSQL for locked down installations, but I think the majority of users want it to just be there. -- Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Including PL/PgSQL by default
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thu, 21 Feb 2008 17:34:06 - Greg Sabino Mullane [EMAIL PROTECTED] wrote: There are so many simple ways to do bad things /without/ plpgsql, I just don't see how the theoretical harm in it being used as an attack vector even comes close to the benefits of having it installed by default. Since we are asking for something more than theoretical harm, here is some practical harm: postgres= select usename,usecreatedb,usesuper,usecatupd from pg_user; usename | usecreatedb | usesuper | usecatupd - ---+-+--+--- ledgersmb | t | f| f foo | f | f| f postgres | t | t| t (3 rows) Notice that user foo is not a super user. Now I log into PostgreSQL and connect to the postgres database (the super users database) as the non privileged user foo. The user foo in theory has *zero* rights here accept that he can connect. psql -U foo postgres Welcome to psql 8.2.6, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit postgres= create table watchmedie (a text); CREATE TABLE postgres= insert into watchmedie values ( generate_series(1,1)) postgres- postgres= insert into watchmedie values ( generate_series(1,1)); INSERT 0 1 postgres= In one fell swoop I could crash *any* postgresql database running 8.2.6 or below (I haven't tested this on 8.3). Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHvbyzATb/zqfZUUQRAgjwAJ0XKBlOPRgwjW2eFQELXkoWXlZ9SgCcCz0h CD53HCmUZY/Nu/KpgYqwjEA= =E7gn -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Including PL/PgSQL by default
Joshua D. Drake wrote: Notice that user foo is not a super user. Now I log into PostgreSQL and connect to the postgres database (the super users database) as the non privileged user foo. The user foo in theory has *zero* rights here accept that he can connect. That's not true. The public schema has public UC privs, and always has had. There is nothing surprising (expect possibly to you) here. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Including PL/PgSQL by default
Joshua D. Drake [EMAIL PROTECTED] writes: In one fell swoop I could crash *any* postgresql database running 8.2.6 or below (I haven't tested this on 8.3). Uh, I seem to have missed where the crash was in this example? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Including PL/PgSQL by default
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thu, 21 Feb 2008 13:33:44 -0500 Andrew Dunstan [EMAIL PROTECTED] wrote: That's not true. The public schema has public UC privs, and always has had. This disproves my point how? There is nothing surprising (expect possibly to you) here. It is not a surprise to me, you can check the archives I bring it up often. It is poor implementation and proof that the theoretical security implications that are being brought up in this thread are far from the practical reality. Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHvcZRATb/zqfZUUQRAvp9AJ9nLf/CW3NvscqJ7zciZWDVil0X8QCghNZY tiUyxmuWyd4DFjrRZdz2Dao= =IQdD -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Including PL/PgSQL by default
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thu, 21 Feb 2008 13:38:50 -0500 Tom Lane [EMAIL PROTECTED] wrote: Joshua D. Drake [EMAIL PROTECTED] writes: In one fell swoop I could crash *any* postgresql database running 8.2.6 or below (I haven't tested this on 8.3). Uh, I seem to have missed where the crash was in this example? I wasn't willing to dump my machine. However I could: A. Exhaust all resources B. Fill up my hard drive C. Render the application unusable for other users D. Lock out DDL operations by beginning a transaction E. Cause xid wrap around by leaving an open transaction idle and thus force a shutdown of postgresql Although you are correct, I should not use the term crash. The above would not crash PostgreSQL, (although Linux might kill it). It does however have the ability to wreak havoc on the environment. Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHvcb2ATb/zqfZUUQRAlgBAJ4y2tFWXJgGwJD95kcg91wIVCk6jwCfbVZe 91Q4CkmzbM1ctM0GX86Kdeg= =7KDu -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Including PL/PgSQL by default
Joshua D. Drake wrote: Notice that user foo is not a super user. Now I log into PostgreSQL and connect to the postgres database (the super users database) as the non privileged user foo. The user foo in theory has *zero* rights here accept that he can connect. That's not true. The public schema has public UC privs, and always has had. Is it safe to remove UC privs on the public schema? Having rights to connect should mean connect, not connect and create. Jon Jon ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Including PL/PgSQL by default
On Thu, Feb 21, 2008 at 10:43:27AM -0800, Joshua D. Drake wrote: often. It is poor implementation and proof that the theoretical security implications that are being brought up in this thread are far from the practical reality. We have this hole over here for historical reasons, so let's maybe open a new one over there? A ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Including PL/PgSQL by default
Joshua D. Drake [EMAIL PROTECTED] writes: Uh, I seem to have missed where the crash was in this example? I wasn't willing to dump my machine. However I could: A. Exhaust all resources B. Fill up my hard drive C. Render the application unusable for other users D. Lock out DDL operations by beginning a transaction E. Cause xid wrap around by leaving an open transaction idle and thus force a shutdown of postgresql Certainly you can cause massive DOS-type problems in plain SQL without any access to plpgsql, but that type of juvenile delinquency isn't what concerns me. What I'm worried about is whether plpgsql isn't a useful tool for the sort of professional who would much rather you never knew he was there. It's perhaps true that with generate_series() for looping and CASE for conditionals, plain SQL is Turing-complete and therefore could do anything, but it'd be awfully unpleasant and inefficient to use as a procedural language. The pro who doesn't want you to know he's there is never going to try to do password cracking that way; the resource consumption would be large enough to be noticed. plpgsql on the other hand is fast enough to be a *practical* tool for nefarious purposes. Anyway, as I said before, I don't object to installing plpgsql by default. What I do object to is installing it in a way that makes it difficult for the DBA to remove it, as would be the case if it were in template0 for example. regards, tom lane ---(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] Including PL/PgSQL by default
Joshua D. Drake wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thu, 21 Feb 2008 13:33:44 -0500 Andrew Dunstan [EMAIL PROTECTED] wrote: That's not true. The public schema has public UC privs, and always has had. This disproves my point how? You stated that this user is in theory has zero rights here. But they have them by intention, so where does this theory come from? If you had said that you believed they should not have such rights I would have not have quibbled, but that's a different matter. cheers andrew ---(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] Including PL/PgSQL by default
Tom Lane wrote: Anyway, as I said before, I don't object to installing plpgsql by default. What I do object to is installing it in a way that makes it difficult for the DBA to remove it, as would be the case if it were in template0 for example. ... which means it can't be installed in template1 either, because template0 is copied from there. Perhaps it can be installed in template1 after the copy, if a certain initdb option is passed? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Including PL/PgSQL by default
On Thu, 21 Feb 2008 14:14:48 -0500 Andrew Sullivan [EMAIL PROTECTED] wrote: On Thu, Feb 21, 2008 at 10:43:27AM -0800, Joshua D. Drake wrote: often. It is poor implementation and proof that the theoretical security implications that are being brought up in this thread are far from the practical reality. We have this hole over here for historical reasons, so let's maybe open a new one over there? Besides, proof that it would do no extra harm is hardly a strong argumet for including it. Given how easy it is to add it to any DB that needs it, I fail to see why we should add it by default. Personally I would like to see more things removed from PG and have them added as modules when required. Of course, we would need a proper module system first. -- D'Arcy J.M. Cain [EMAIL PROTECTED] | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(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] Including PL/PgSQL by default
Alvaro Herrera [EMAIL PROTECTED] writes: Tom Lane wrote: Anyway, as I said before, I don't object to installing plpgsql by default. What I do object to is installing it in a way that makes it difficult for the DBA to remove it, as would be the case if it were in template0 for example. Perhaps it can be installed in template1 after the copy, if a certain initdb option is passed? Yeah, we'd have to rejigger initdb a bit. The bigger problem is that traditionally template0 has been seen as a backup for template1, and it wouldn't be (quite) that if the initial contents are different. Would it satisfy people if plpgsql were in postgres, but neither template DB, after initdb? This would make it available to the sort of person who's too lazy to learn about CREATE DATABASE, and one would think that if they can handle CREATE DATABASE then CREATE LANGUAGE is not beyond their powers. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Including PL/PgSQL by default
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thu, 21 Feb 2008 14:15:28 -0500 Tom Lane [EMAIL PROTECTED] wrote: Anyway, as I said before, I don't object to installing plpgsql by default. What I do object to is installing it in a way that makes it difficult for the DBA to remove it, as would be the case if it were in template0 for example. O.k. then can we :) modify initdb so that when it copies template0 to template1, the step right after that is createlang? Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHvdYnATb/zqfZUUQRAtkQAJ9TZpTTnxm6dKdKvj4nwkJ6x6c0lwCfRMHm pbNPYpzgi/3AKr3hscB02HI= =kdzf -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Including PL/PgSQL by default
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Tom Lane wrote: Anyway, as I said before, I don't object to installing plpgsql by default. What I do object to is installing it in a way that makes it difficult for the DBA to remove it, as would be the case if it were in template0 for example. Perhaps it can be installed in template1 after the copy, if a certain initdb option is passed? Yeah, we'd have to rejigger initdb a bit. The bigger problem is that traditionally template0 has been seen as a backup for template1, and it wouldn't be (quite) that if the initial contents are different. Would it satisfy people if plpgsql were in postgres, but neither template DB, after initdb? This would make it available to the sort of person who's too lazy to learn about CREATE DATABASE, and one would think that if they can handle CREATE DATABASE then CREATE LANGUAGE is not beyond their powers. I don't see any point in doing it at all unless it gets into new DBs by default. So, no, I don't think that's going to be very helpful. I don't see a huge problem in loading it to template1 after we copy template1 to template0 - anyone who is going to touch template0 at any time is likely to have enough postgres-fu to be able to manage. cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Including PL/PgSQL by default
On Thursday 21 February 2008 11:36, Tom Lane wrote: Would it satisfy people if plpgsql were in postgres, but neither template DB, after initdb? T No, the real-world use-case we're trying to satisfy is hosted and/or locked-down installations where the developer doesn't have superuser access. So putting it in postgres wouldn't help with that. -- Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Including PL/PgSQL by default
Josh Berkus [EMAIL PROTECTED] writes: On Thursday 21 February 2008 11:36, Tom Lane wrote: Would it satisfy people if plpgsql were in postgres, but neither template DB, after initdb? No, the real-world use-case we're trying to satisfy is hosted and/or locked-down installations where the developer doesn't have superuser access. So putting it in postgres wouldn't help with that. That statement is content-free, Josh. Exactly what are you assuming this developer *does* have? For example, if he hasn't got createdb privilege, it will hardly matter to him whether any DBs other than postgres contain plpgsql. If he does have createdb, it's already possible by default for him to create trusted languages including plpgsql in his new DB. So it's still 100% unclear to me who we are catering to. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Including PL/PgSQL by default
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 - --On Thursday, February 21, 2008 21:33:03 -0500 Tom Lane [EMAIL PROTECTED] wrote: Josh Berkus [EMAIL PROTECTED] writes: On Thursday 21 February 2008 11:36, Tom Lane wrote: Would it satisfy people if plpgsql were in postgres, but neither template DB, after initdb? No, the real-world use-case we're trying to satisfy is hosted and/or locked-down installations where the developer doesn't have superuser access. So putting it in postgres wouldn't help with that. That statement is content-free, Josh. Exactly what are you assuming this developer *does* have? For example, if he hasn't got createdb privilege, it will hardly matter to him whether any DBs other than postgres contain plpgsql. If he does have createdb, it's already possible by default for him to create trusted languages including plpgsql in his new DB. So it's still 100% unclear to me who we are catering to. in my case, a client can createdb through a web interface, but can't load plpgsql, so we try and remember to add it to the default template when we build the server ... ... but, in that case, the interface should be extended to allow loading available languages too ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED] Yahoo . yscrappy Skype: hub.orgICQ . 7615664 -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.4 (FreeBSD) iD8DBQFHvjqm4QvfyHIvDvMRAnblAJ9ecKlFQB6ihHuQ1XZ7XBhc0K46nACg3yaO OIrUlX+KKW3t7sNa6eUZVXU= =UQ0i -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Including PL/PgSQL by default
On Fri, Feb 22, 2008 at 2:33 AM, Tom Lane [EMAIL PROTECTED] wrote: Josh Berkus [EMAIL PROTECTED] writes: On Thursday 21 February 2008 11:36, Tom Lane wrote: Would it satisfy people if plpgsql were in postgres, but neither template DB, after initdb? No, the real-world use-case we're trying to satisfy is hosted and/or locked-down installations where the developer doesn't have superuser access. So putting it in postgres wouldn't help with that. That statement is content-free, Josh. Exactly what are you assuming this developer *does* have? For example, if he hasn't got createdb privilege, it will hardly matter to him whether any DBs other than postgres contain plpgsql. If he does have createdb, it's already possible by default for him to create trusted languages including plpgsql in his new DB. So it's still 100% unclear to me who we are catering to. I know I'm gonna regret wading in on this, but in my mind this is akin to one of the arguments for including tsearch in the core server - namely that too many brain dead hosting providers won't add a contrib module or anything else in a customer's database because they don't understand that just because it's not there by default doesn't mean it's in any way second rate. Including pl/pgsql in template1 will help those folks who forwhatever reason use such providers, whilst more savvy providers can easily disable it post-initdb if thats what they want to do. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com The Oracle-compatible database company ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Including PL/PgSQL by default
On Tue, Feb 19, 2008 at 08:37:51PM -0500, Andrew Dunstan wrote: The way I intended to do it would indeed allow it to be undone simply by executing 'drop language plpgsql' in template1. Why isn't it enough that administrators can do CREATE LANGUAGE plpgsql in template1? I think this is completely unneeded, given the ease with which this can be enabled. It seems to me the source distribution of the code ought to be minimalist. Moreover, given that the trend in daemons is to turn everything off by default, just in case, I'm puzzled why we want to do the opposite here. Note that packagers are in a different boat entirely; I see no reason why packages might not turn this on by default. But they have a narrower target of users. I'd be more persuaded by a convenience package of things to enable by default that ships with the code, and can be run by the installing party. We'd at least then have an argument to the security community that we require explicit administrator action to enable the features. A ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Including PL/PgSQL by default
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 The way I intended to do it would indeed allow it to be undone simply by executing 'drop language plpgsql' in template1. Why isn't it enough that administrators can do CREATE LANGUAGE plpgsql in template1? Because people do not have the rights, or the knowledge, or both. I'm glad most packagers are choosing to enable it by default, because it can be a real pain for applications like MediaWiki, which has a point and click GUI installation that is made extraordinarily harder by having to explain: what plpgsql and tsearch2 are, how to install them, what a superuser is, what they should tell their hosting provider, etc. I'm not sure I understand the security implications of turning plpgsql on: has there been some security concerns in the past? Does having access to plpgsql really faciliate an attacker that much above what they might already be capable of without it? It seems quite trivial to write a function in sql that ties up resources just as effectively as plpgsql. +1 on installed by default, in case it wasn't clear from the above. :) - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200802202019 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAke80bUACgkQvJuQZxSWSsgH/ACcD2A/BjKqT3DHWsb7ybKWGL0H AEYAoMKcvd+tBhyB4NpFzOMi5nT7Y6zq =dP0/ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Including PL/PgSQL by default
Greg Sabino Mullane [EMAIL PROTECTED] writes: I'm not sure I understand the security implications of turning plpgsql on: has there been some security concerns in the past? Does having access to plpgsql really faciliate an attacker that much above what they might already be capable of without it? It seems quite trivial to write a function in sql that ties up resources just as effectively as plpgsql. I grow weary of repeating this: it's not about resource consumption, nor about potential security holes in plpgsql itself. It's about handing attackers the capability to further exploit *other* security holes. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Including PL/PgSQL by default
On Tue, Feb 19, 2008 at 12:11:05PM -0500, Tom Lane wrote: David Fetter [EMAIL PROTECTED] writes: Let's put PL/PgSQL in template1 by default, as some downstream packagers are already doing. If someone really must remove it, they can still do that. This has been proposed before, and rejected before. Have you got any new arguments? The longer it's been since the last vuln in PL/PgSQL, the harder it is to argue for having it not be there by default. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Including PL/PgSQL by default
David Fetter [EMAIL PROTECTED] writes: Let's put PL/PgSQL in template1 by default, as some downstream packagers are already doing. If someone really must remove it, they can still do that. This has been proposed before, and rejected before. Have you got any new arguments? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Including PL/PgSQL by default
David Fetter [EMAIL PROTECTED] writes: On Tue, Feb 19, 2008 at 12:11:05PM -0500, Tom Lane wrote: This has been proposed before, and rejected before. Have you got any new arguments? The longer it's been since the last vuln in PL/PgSQL, the harder it is to argue for having it not be there by default. You are attacking a straw man, which is that the only argument against having PL/PgSQL installed is the risk of security holes in it. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Including PL/PgSQL by default
Tom Lane wrote: David Fetter [EMAIL PROTECTED] writes: On Tue, Feb 19, 2008 at 12:11:05PM -0500, Tom Lane wrote: This has been proposed before, and rejected before. Have you got any new arguments? The longer it's been since the last vuln in PL/PgSQL, the harder it is to argue for having it not be there by default. You are attacking a straw man, which is that the only argument against having PL/PgSQL installed is the risk of security holes in it. I am having trouble locating the previous thread - can someone please point me at it? cheers andrew ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Including PL/PgSQL by default
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 19 Feb 2008 18:13:53 -0500 Andrew Dunstan [EMAIL PROTECTED] wrote: I am having trouble locating the previous thread - can someone please point me at it? I am having trouble finding one that makes a cohesive argument against but here we go: http://archives.postgresql.org/pgsql-sql/2000-05/msg00215.php http://archives.postgresql.org/pgsql-hackers/2004-04/msg00952.php Of course there are tons of results of users wondering why we don't offer such as simple and useful feature. Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHu2ayATb/zqfZUUQRAmL7AJoCQyNmbLIbZNXG9JjMQu2ax/vRJQCfcevF TF6TzTSr/1ep8PuSNMcGK2g= =bFqN -END PGP SIGNATURE- ---(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] Including PL/PgSQL by default
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 19 Feb 2008 15:25:44 -0800 Neil Conway [EMAIL PROTECTED] wrote: On Tue, 2008-02-19 at 18:13 -0500, Andrew Dunstan wrote: I am having trouble locating the previous thread - can someone please point me at it? http://markmail.org/message/kyjbj5qovadfoe3w Excellent that thread is better than the two I found. Sincerely, Joshua D. Drake -Neil ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHu2dCATb/zqfZUUQRAqT9AJ0WaUpPj/5mvw+VfRKgY86gTyjURgCeJxUL Cx2L5WvrXMDg1j/NW7QlD54= =/yV6 -END PGP SIGNATURE- ---(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] Including PL/PgSQL by default
On Tue, 2008-02-19 at 18:13 -0500, Andrew Dunstan wrote: I am having trouble locating the previous thread - can someone please point me at it? http://markmail.org/message/kyjbj5qovadfoe3w -Neil ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Including PL/PgSQL by default
Neil Conway wrote: On Tue, 2008-02-19 at 18:13 -0500, Andrew Dunstan wrote: I am having trouble locating the previous thread - can someone please point me at it? http://markmail.org/message/kyjbj5qovadfoe3w Thanks. The only significant problem I saw mentioned other than the rather ephemeral security issues was the one regarding statically linked postgres. I therefore propose that a) loading plpgsql in template1 can be disabled by an initdb switch, and b) initdb will not try to load it if postgres is statically linked, assuming we can develop a reasonable test for that. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Including PL/PgSQL by default
Andrew Dunstan [EMAIL PROTECTED] writes: Thanks. The only significant problem I saw mentioned other than the rather ephemeral security issues was the one regarding statically linked postgres. Nothing like establishing one's point by carefully ignoring all the nontrivial problems. I think the real $64 issue is that plpgsql provides a usable procedural programming language on the server side, and is therefore a springboard to enable users doing things the DBA might not like --- the example of using server-side resources to do password cracking is one. Another example is that it'd enable use of covert communication channels such as CPU usage, which'd be a heck of a lot harder to do with only SQL access. Thus it is entirely reasonable for a DBA to see plpgsql as exacerbating any security issues that might exist, *whether or not plpgsql itself has any holes*. Indeed, I'd say a DBA who does not realize that that's a risk is a fool. What was that again about let's be secure by default? This proposal is certainly not moving in that direction. Still and all, I will hold still for having it be installed by default as long as there is a simple way for the DBA to change that default --- let's say, roughly as simple as it is now for the DBA to make it the default if he wishes (ie create language plpgsql in template1) and revoke that again if he changes his mind (drop language plpgsql in template1). initdb-time switches are not an adequate answer, not least because most packagers don't make it easy to control them. BTW, why all the pressure for this when we've already made it possible for database owners to create the language by default? regards, tom lane ---(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] Including PL/PgSQL by default
Tom Lane wrote: Still and all, I will hold still for having it be installed by default as long as there is a simple way for the DBA to change that default --- let's say, roughly as simple as it is now for the DBA to make it the default if he wishes (ie create language plpgsql in template1) and revoke that again if he changes his mind (drop language plpgsql in template1). initdb-time switches are not an adequate answer, not least because most packagers don't make it easy to control them. The way I intended to do it would indeed allow it to be undone simply by executing 'drop language plpgsql' in template1. I'm not clear about what else you want. cheers andrew ---(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
