Обсуждение: Including PL/PgSQL by default
Folks, 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. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter <david@fetter.org> 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
On Tue, Feb 19, 2008 at 12:11:05PM -0500, Tom Lane wrote: > David Fetter <david@fetter.org> 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 <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter <david@fetter.org> 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
Tom Lane wrote: > David Fetter <david@fetter.org> 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
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
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Tue, 19 Feb 2008 18:13:53 -0500 Andrew Dunstan <andrew@dunslane.net> 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-----
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Tue, 19 Feb 2008 15:25:44 -0800 Neil Conway <neilc@samurai.com> 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-----
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 willnot try to load it if postgres is statically linked, assuming we can develop a reasonable test for that. cheers andrew
Andrew Dunstan <andrew@dunslane.net> 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
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
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
-----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 greg@turnstep.com PGP Key: 0x14964AC8 200802202019 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAke80bUACgkQvJuQZxSWSsgH/ACcD2A/BjKqT3DHWsb7ybKWGL0H AEYAoMKcvd+tBhyB4NpFzOMi5nT7Y6zq =dP0/ -----END PGP SIGNATURE-----
"Greg Sabino Mullane" <greg@turnstep.com> 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
-----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 greg@turnstep.com PGP Key: 0x14964AC8 200802211227 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAke9tdIACgkQvJuQZxSWSsieowCfQTbmdmGdIJSpWCOU5S2bHSR5 1PgAnjxjOV7Dh1X9nF3pPjDDBosiX0Tx =Z6yR -----END PGP SIGNATURE-----
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Thu, 21 Feb 2008 17:34:06 -0000 "Greg Sabino Mullane" <greg@turnstep.com> 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-----
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
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Thu, 21 Feb 2008 17:34:06 -0000 "Greg Sabino Mullane" <greg@turnstep.com> 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 | ffoo | f | f | fpostgres | 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 orterminate with semicolon to execute query \q to quit postgres=> create table watchmedie (a text); CREATE TABLE postgres=> insert into watchmedie values ( generate_series(1,10000)) postgres-> postgres=> insert into watchmedie values ( generate_series(1,10000)); INSERT 0 10000 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-----
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
"Joshua D. Drake" <jd@commandprompt.com> 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
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Thu, 21 Feb 2008 13:33:44 -0500 Andrew Dunstan <andrew@dunslane.net> 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-----
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Thu, 21 Feb 2008 13:38:50 -0500 Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Joshua D. Drake" <jd@commandprompt.com> 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-----
> > 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
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
"Joshua D. Drake" <jd@commandprompt.com> 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
Joshua D. Drake wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On Thu, 21 Feb 2008 13:33:44 -0500 > Andrew Dunstan <andrew@dunslane.net> 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
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 Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On Thu, 21 Feb 2008 14:14:48 -0500 Andrew Sullivan <ajs@crankycanuck.ca> 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 <darcy@druid.net> | 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.
Alvaro Herrera <alvherre@commandprompt.com> 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
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Thu, 21 Feb 2008 14:15:28 -0500 Tom Lane <tgl@sss.pgh.pa.us> 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-----
Tom Lane wrote: > Alvaro Herrera <alvherre@commandprompt.com> 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
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
Josh Berkus <josh@agliodbs.com> 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
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 - --On Thursday, February 21, 2008 21:33:03 -0500 Tom Lane <tgl@sss.pgh.pa.us> wrote: > Josh Berkus <josh@agliodbs.com> 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 . scrappy@hub.org MSN . scrappy@hub.org Yahoo . yscrappy Skype: hub.org ICQ . 7615664 -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.4 (FreeBSD) iD8DBQFHvjqm4QvfyHIvDvMRAnblAJ9ecKlFQB6ihHuQ1XZ7XBhc0K46nACg3yaO OIrUlX+KKW3t7sNa6eUZVXU= =UQ0i -----END PGP SIGNATURE-----
On Fri, Feb 22, 2008 at 2:33 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Josh Berkus <josh@agliodbs.com> 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
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/
Tom Lane wrote: > Josh Berkus <josh@agliodbs.com> 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 >
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 Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
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
> -----Original Message----- > From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers- > owner@postgresql.org] 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
On Fri, 22 Feb 2008 07:37:55 +0000 "Dave Page" <dpage@pgadmin.org> 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 <darcy@druid.net> | 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.
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
Andrew Dunstan <andrew@dunslane.net> 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
On Fri, 22 Feb 2008, D'Arcy J.M. Cain wrote: > On Fri, 22 Feb 2008 07:37:55 +0000 > "Dave Page" <dpage@pgadmin.org> 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
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
-----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 greg@turnstep.com PGP Key: 0x14964AC8 200802221147 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAke+/QUACgkQvJuQZxSWSsgWlgCdElnDyCKvoD57Oz7UyqIw1hJe wsYAn3u54vmDAt4qRNlI08A7w3dj2b7q =IJzq -----END PGP SIGNATURE-----
Tom Lane wrote: > Andrew Dunstan <andrew@dunslane.net> 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
Andrew Dunstan <andrew@dunslane.net> 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
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Fri, 22 Feb 2008 12:31:14 -0500 Tom Lane <tgl@sss.pgh.pa.us> wrote: > Andrew Dunstan <andrew@dunslane.net> 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-----
"Joshua D. Drake" <jd@commandprompt.com> 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
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 <kleptog@svana.org> http://svana.org/kleptog/ > Those who make peaceful revolution impossible will make violent revolution inevitable. > -- John F Kennedy
>>> On Fri, Feb 22, 2008 at 10:09 AM, in message <47BEF3A1.2010502@dunslane.net>, Andrew Dunstan <andrew@dunslane.net> 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
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
On Thursday 21 February 2008 21:33, Tom Lane wrote: > Josh Berkus <josh@agliodbs.com> 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
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 Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
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
Robert Treat <xzilla@users.sourceforge.net> 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
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
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
Robert, > Interesting, seems pghackers dropped me from the list, so I missed > several messages in the thread. Huh. I thougth this was just my full mailbox ... I missed all of the messages this weekend. Who else got nailed? Marc? -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Tue, 26 Feb 2008 14:47:09 -0800 Josh Berkus <josh@agliodbs.com> wrote: > Robert, > > > Interesting, seems pghackers dropped me from the list, so I missed > > several messages in the thread. > > Huh. I thougth this was just my full mailbox ... I missed all of the > messages this weekend. Who else got nailed? > > Marc? > I have received all messages as far as I can tell. 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) iD8DBQFHxJldATb/zqfZUUQRAoSSAKCd6hdznWGG3g9cz2UlPQly7JUHTQCggERO Etx/NWdYVtSm5BFTKChPppY= =EDRE -----END PGP SIGNATURE-----
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 - --On Tuesday, February 26, 2008 14:57:33 -0800 "Joshua D. Drake" <jd@commandprompt.com> wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On Tue, 26 Feb 2008 14:47:09 -0800 > Josh Berkus <josh@agliodbs.com> wrote: > >> Robert, >> >> > Interesting, seems pghackers dropped me from the list, so I missed >> > several messages in the thread. >> >> Huh. I thougth this was just my full mailbox ... I missed all of the >> messages this weekend. Who else got nailed? >> >> Marc? >> > > I have received all messages as far as I can tell. *dons hat of shame and goes to sit in the corner* Hate to admit, but ... my fault. This weekend, I generated a list of email addresses that have been bouncing messages to sent to them, scanned the list for anyone that I recognized so that they didn't get removed, and unregistered the rest :( Looks like I scanned *too* fast and missed several ppl ... this was purely human error on my part :( - ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email . scrappy@hub.org MSN . scrappy@hub.org Yahoo . yscrappy Skype: hub.org ICQ . 7615664 -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.4 (FreeBSD) iD8DBQFHxMWN4QvfyHIvDvMRAs1LAJ4su3mVq5vf6gbIcnY37woC8vRhnACg4KSK ua9mbK9MvLjYPlhwig9WPik= =K+uH -----END PGP SIGNATURE-----