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?
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
- --
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?
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@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-----
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.
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@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 | 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
