pl/pgsql enabled by default

Is there a good reason that pl/pgsql is not installed in databases by
default?

I think it should be. pl/pgsql is widely used, and having it installed
by default would be one less hurdle for newbies to overcome when
learning PostgreSQL. It would also make it easier to distribute
applications that depend on PostgreSQL and use PL/PgSQL: rather than
saying "You need PostgreSQL, and then you need to do [ createlang stuff
]", those applications can just depend on a sufficiently recent version
of PostgreSQL.

AFAICS, the overhead of installing it by default would not be large:
just an extra row in pg_language and a few rows in pg_proc. So I can't
really see a major reason *not* to do this -- am I missing one?

Problem is people restoring dumps that have the plpgsql create language,
etc. commands in them.

I strongly think that pgsql should come with pl/pgsql on by default,
however ;)

Chris

Christopher Kings-Lynne wrote:

Problem is people restoring dumps that have the plpgsql create language,
etc. commands in them.

It should be possible to ignore those commands, and possibly issue a
warning. It's a bit ugly, but at least we can detect this situation
pretty unambiguously.

-Neil

Neil,

Is there a good reason that pl/pgsql is not installed in databases by
default?

The only one I can think of is "security", which is pretty weak -- we've never
had a plpgsql security issue that I know of.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus wrote:

The only one I can think of is "security", which is pretty weak -- we've never
had a plpgsql security issue that I know of.

Well, no -- for instance,

http://cve.mitre.org/cgi-bin/cvename.cgi?name=CAN-2005-0245
http://cve.mitre.org/cgi-bin/cvename.cgi?name=CAN-2005-0247

But I agree security is not a good argument against enabling it by default.

-Neil

On Fri, May 06, 2005 at 02:59:04PM +1000, Neil Conway wrote:

Is there a good reason that pl/pgsql is not installed in databases by
default?

The only reason I've seen was "if we start with including plpgsql, where
do we draw the line?" Personally, I think it should be installed by
default.
--
Jim C. Nasby, Database Consultant decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

On Fri, 6 May 2005 04:45 pm, Jim C. Nasby wrote:

On Fri, May 06, 2005 at 02:59:04PM +1000, Neil Conway wrote:

Is there a good reason that pl/pgsql is not installed in databases by
default?

The only reason I've seen was "if we start with including plpgsql, where
do we draw the line?"

Well, I thought and I'm sure it's been said, that plpgsql was our attempt to
match oracle's pl/sql. As Tom has already suggested in the Thread regarding
whether we should move PL's out or not, plpgsql is the only one that is entirely
internal to the db. This is where I would clearly draw the line. If you have a PL,
that is only reliant on the PostgreSQL being install, then you may have a case for
getting it enabled. Otherwise not a chance. I would say plpgsql is likely to be
the only PL in this situation for a long time, if the only one ever.

Personally, I think it should be installed by default.

I agree with everybody else, having it enabled by default is a good idea.

Regards

Russell Smith

Neil Conway <neilc@samurai.com> writes:

But I agree security is not a good argument against enabling it by default.

Isn't it? Even without anything that we regard as a bug, availability
of a server-side programming language is still a risk factor from the
point of view of any reasonably paranoid DBA. The denial of service
risk in particular (whether intentional or accidental) goes way up.

Another problem with this proposal is that installations without
shared-library support will stop working entirely.  I suppose we could
get around that by building plpgsql into the core backend instead of as
a shared library, but that will be risky if the other PLs migrate out
--- plpgsql really should be built the same way as the rest of them, so
that it continues to serve as an early warning system for build/link
problems.

Also, your proposal as worded does not seem to mean "installed by
default", it means "installed, period". How would a DBA who doesn't
want it get rid of it? If he later changes his mind, how does he
return to a standard configuration (short of initdb)? We don't really
have support for removing and re-adding built-in functions.

regards, tom lane

Tom Lane wrote:

Another problem with this proposal is that installations without
shared-library support will stop working entirely.

How do we manage to run regression tests for such installations?

I guess the philosphical question is "do we make the default the most
common case and provide exceptions from it up and down, or do we make it
the most widely supportable and hence minimal case?" I can see both
sides. I do wish we put the handlers somewhere other than the public
schema, though.

cheers

andrew

Andrew Dunstan <andrew@dunslane.net> writes:

Tom Lane wrote:

Another problem with this proposal is that installations without
shared-library support will stop working entirely.

How do we manage to run regression tests for such installations?

We don't. However, degraded functionality is better than none at all.
I don't want to create a scenario where initdb dies if plpgsql isn't
available.

regards, tom lane

On Fri, May 06, 2005 at 03:37:21PM +1000, Neil Conway wrote:

But I agree security is not a good argument against enabling it by default.

Sure it is. "Don't enable anything you don't need," is the first
security rule. Everything is turned off by default. If you want it,
enable it.

"Enabled by default" is what made early Linux distributions give
old UNIX hands the willies. It was bad enough that IRIX shipped with
everything turned on and suid root; at least it cost several thousand
dollars. Linux was _free_, and had many of the same problems.

A

--
Andrew Sullivan | ajs@crankycanuck.ca
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism.
--Brad Holland

I think that plpgsql should be maintained in core, enabled
by default with the ability to turn it off. This handles
the paranoid dba and security issue while giving the majority
of people what they already have by default. Of course
the dump create language statements will have to be dealt with
and any other small gotchas. A large gotcha might be a reason
to not do it, but I have not seen mention of any.

--elein
elein@varlena.com

Tom Lane wrote:

The denial of service risk in particular (whether intentional or
accidental) goes way up.

Does it really go "way up"? A malicious user who can execute SQL can DOS
the database trivially. Doing the (non-trivial) infrastructure work to
fix that is probably a good idea, but I don't see that not installing
pl/pgsql by default is going to make much of a difference.

Another problem with this proposal is that installations without
shared-library support will stop working entirely. I suppose we could
get around that by building plpgsql into the core backend instead of as
a shared library

That would be one solution. Another would be to only install pl/pgsql by
default when shared libraries are available. While that would mean
pl/pgsql wouldn't be available on platforms without shared libraries,
that's no worse than the status quo.

Also, your proposal as worded does not seem to mean "installed by
default", it means "installed, period". How would a DBA who doesn't
want it get rid of it?

If we effectively just ran the CREATE FUNCTION and CREATE LANGUAGE
commands for pl/pgsql in a late stage of initdb, the language and its
associated functions wouldn't be builtin. The DBA would then be able to
drop pl/pgsql via droplang (which might need to be hacked up a bit to do
this).

-Neil

Andrew Sullivan wrote:

Sure it is. "Don't enable anything you don't need," is the first
security rule. Everything is turned off by default. If you want it,
enable it.

So would you have us disable all the non-essential builtin functions?
(Many of which have has security problems in the past.) What about the
builtin encoding conversions, non-btree indexes, or a myriad of features
that not all users need or use?

What makes sense for the default configuration of an operating system
(which by nature must be hardened against attack) does not necessarily
make sense for a database system.

-Neil

On Sat, 2005-05-07 at 14:52 +1000, Neil Conway wrote:

Andrew Sullivan wrote:

Sure it is. "Don't enable anything you don't need," is the first
security rule. Everything is turned off by default. If you want it,
enable it.

So would you have us disable all the non-essential builtin functions?
(Many of which have has security problems in the past.) What about the
builtin encoding conversions, non-btree indexes, or a myriad of features
that not all users need or use?

I support Andrew's comment, though might reword it to
"Don't enable anything that gives users programmable features or user
exits by default".

You can't use the builtin encoding functions or non-btree indexes to
access things you are not supposed to.

Anything that is *always* there provides a platform for malware.

I'm not really sure what is wrong with the CREATE LANGUAGE statement
anyway - it is dynamically accessible, so doesn't require changes that
effect other database instance users. I do understand the wish to make
the lives of admins easier, but this isn't a hard thing to do...

What makes sense for the default configuration of an operating system
(which by nature must be hardened against attack) does not necessarily
make sense for a database system.

Security is everybody's job, not just the OS guys. Personally, I forget
that constantly, but the principle seems clear.

Best Regards, Simon Riggs

Simon Riggs wrote:

I support Andrew's comment, though might reword it to
"Don't enable anything that gives users programmable features or user
exits by default".

Users can already define SQL functions by default, which certainly
provides "programmable features". I'm not quite sure what you mean by
"user exits."

I guess I'm missing how pl/pgsql is a fundamentally greater security risk.

You can't use the builtin encoding functions or non-btree indexes to
access things you are not supposed to.

How can you use pl/pgsql to "access things you are not supposed to"?

-Neil

On Sat, May 07, 2005 at 02:52:57PM +1000, Neil Conway wrote:

So would you have us disable all the non-essential builtin functions?
(Many of which have has security problems in the past.) What about the
builtin encoding conversions, non-btree indexes, or a myriad of features
that not all users need or use?

This is not really analogous, because those are already on (and in
most cases, not easily disabled). What you're arguing for is to add
yet another on-by-default feature. Given that there's already a way
to turn it on, why make it automatic? Moreover, if some repackager
wants to make this more convenient, s/he can do so by turning it on
by default. I don't see what's wrong with conservatism here.

What makes sense for the default configuration of an operating system
(which by nature must be hardened against attack) does not necessarily
make sense for a database system.

Indeed. But that doesn't mean that the principle isn't sound for
both cases. I haven't seen an argument against that yet.

A

--
Andrew Sullivan | ajs@crankycanuck.ca
The plural of anecdote is not data.
--Roger Brinner

Neil Conway <neilc@samurai.com> writes:

Users can already define SQL functions by default, which certainly
provides "programmable features". I'm not quite sure what you mean by
"user exits."

I guess I'm missing how pl/pgsql is a fundamentally greater security risk.

plpgsql has control structures (loops, IF); SQL functions don't.
That makes for a fundamental difference in the power of the programming
language ... at least according to CS theory as I was taught it. Now
admittedly the "primitive statements" of SQL are a lot more powerful
than the primitive statements usually considered in programming language
theory, but I think there is still a pretty significant difference in
capability.

An example of why this could be interesting from a security standpoint
is that, given access to pg_shadow, it'd be pretty trivial to write
a plpgsql function that tries to break user passwords by brute force
(just generate possible passwords, hash them, and look for a match).
I don't see any way to do that in plain SQL, at least not without a
pre-existing SRF to generate the trial passwords for you.

regards, tom lane

On 2005-05-07, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Neil Conway <neilc@samurai.com> writes:

Users can already define SQL functions by default, which certainly
provides "programmable features". I'm not quite sure what you mean by
"user exits."

I guess I'm missing how pl/pgsql is a fundamentally greater security risk.

plpgsql has control structures (loops, IF); SQL functions don't.
That makes for a fundamental difference in the power of the programming
language ... at least according to CS theory as I was taught it.

SQL functions do have control structures: CASE WHEN and recursion. I have
even implemented generate_series() in pure SQL.

Now
admittedly the "primitive statements" of SQL are a lot more powerful
than the primitive statements usually considered in programming language
theory, but I think there is still a pretty significant difference in
capability.

The only thing that makes pg's SQL functions not turing-complete is the
fact that recursion depth is not unlimited. In practice this isn't much
of a restriction, since you can do large-scale iterations by using SQL
sets and joins rather than recursion.

An example of why this could be interesting from a security standpoint
is that, given access to pg_shadow, it'd be pretty trivial to write
a plpgsql function that tries to break user passwords by brute force
(just generate possible passwords, hash them, and look for a match).
I don't see any way to do that in plain SQL, at least not without a
pre-existing SRF to generate the trial passwords for you.

Writing a pure SQL SRF that generates a range of trial passwords is in
fact not hard at all. The key point to note is that you can generate
sets of literal values of moderate size using UNION ALL, and you can
then cross-join those sets against themselves multiple times to generate
much larger and more complex sets.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

People:

Before we get into more minutia regarding potential security risk of plpgsql,
are there any reasons *other* than security to not enable it?

--
Josh Berkus
Aglio Database Solutions
San Francisco