Re: 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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter <david(at)fetter(dot)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(at)fetter(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter <david(at)fetter(dot)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(at)fetter(dot)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(at)dunslane(dot)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(at)samurai(dot)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 will not try to load it if postgres is statically linked,
assuming we can develop a reasonable test for that.

cheers

andrew

Andrew Dunstan <andrew(at)dunslane(dot)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(at)turnstep(dot)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(at)turnstep(dot)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(at)turnstep(dot)com
PGP Key: 0x14964AC8 200802211227
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAke9tdIACgkQvJuQZxSWSsieowCfQTbmdmGdIJSpWCOU5S2bHSR5
1PgAnjxjOV7Dh1X9nF3pPjDDBosiX0Tx
=Z6yR
-----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(at)turnstep(dot)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-----

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thu, 21 Feb 2008 17:34:06 -0000
"Greg Sabino Mullane" <greg(at)turnstep(dot)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 | 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,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(at)commandprompt(dot)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(at)dunslane(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Joshua D. Drake" <jd(at)commandprompt(dot)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(at)commandprompt(dot)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(at)dunslane(dot)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(at)crankycanuck(dot)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(at)druid(dot)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(at)commandprompt(dot)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(at)sss(dot)pgh(dot)pa(dot)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(at)commandprompt(dot)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(at)agliodbs(dot)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(at)sss(dot)pgh(dot)pa(dot)us>
wrote:

> Josh Berkus <josh(at)agliodbs(dot)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(at)hub(dot)org MSN . scrappy(at)hub(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Josh Berkus <josh(at)agliodbs(dot)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(at)agliodbs(dot)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(at)postgresql(dot)org [mailto:pgsql-hackers-
> owner(at)postgresql(dot)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(at)postgresql(dot)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(at)pgadmin(dot)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(at)druid(dot)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

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

Andrew Dunstan <andrew(at)dunslane(dot)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(at)pgadmin(dot)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

-----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(at)turnstep(dot)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(at)dunslane(dot)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(at)dunslane(dot)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

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(at)svana(dot)org> http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
> -- John F Kennedy

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Fri, 22 Feb 2008 12:31:14 -0500
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Andrew Dunstan <andrew(at)dunslane(dot)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-----

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

"Joshua D. Drake" <jd(at)commandprompt(dot)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 10:09 AM, in message <47BEF3A1(dot)2010502(at)dunslane(dot)net>,
Andrew Dunstan <andrew(at)dunslane(dot)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

On Thursday 21 February 2008 21:33, Tom Lane wrote:
> Josh Berkus <josh(at)agliodbs(dot)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(at)users(dot)sourceforge(dot)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(at)agliodbs(dot)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(at)commandprompt(dot)com> wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Tue, 26 Feb 2008 14:47:09 -0800
> Josh Berkus <josh(at)agliodbs(dot)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(at)hub(dot)org MSN . scrappy(at)hub(dot)org
Yahoo . yscrappy Skype: hub.org ICQ . 7615664
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.4 (FreeBSD)

iD8DBQFHxMWN4QvfyHIvDvMRAs1LAJ4su3mVq5vf6gbIcnY37woC8vRhnACg4KSK
ua9mbK9MvLjYPlhwig9WPik=
=K+uH
-----END PGP SIGNATURE-----