Re: pl/pgsql enabled by default

Lists: pgsql-hackers
From: Neil Conway <neilc(at)samurai(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: pl/pgsql enabled by default
Date: 2005-05-06 04:59:04
Message-ID: 427AF998.1060904@samurai.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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?

A related issue is where the PL validator and handler functions for
PL/PgSQL would be placed if it was installed by default. I think placing
them in pg_catalog (rather than public, where they are currently
installed by createlang) would probably be best.

-Neil


From: Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>
To: Neil Conway <neilc(at)samurai(dot)com>
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pl/pgsql enabled by default
Date: 2005-05-06 05:21:36
Message-ID: 427AFEE0.405@familyhealth.com.au
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

> 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


From: Neil Conway <neilc(at)samurai(dot)com>
To: Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pl/pgsql enabled by default
Date: 2005-05-06 05:26:48
Message-ID: 427B0018.2050209@samurai.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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


From: Josh Berkus <josh(at)agliodbs(dot)com>
To: Neil Conway <neilc(at)samurai(dot)com>
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pl/pgsql enabled by default
Date: 2005-05-06 05:31:16
Message-ID: 200505052231.16908.josh@agliodbs.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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


From: Neil Conway <neilc(at)samurai(dot)com>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pl/pgsql enabled by default
Date: 2005-05-06 05:37:21
Message-ID: 427B0291.5020709@samurai.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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


From: "Jim C(dot) Nasby" <decibel(at)decibel(dot)org>
To: Neil Conway <neilc(at)samurai(dot)com>
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pl/pgsql enabled by default
Date: 2005-05-06 06:45:02
Message-ID: 20050506064502.GK88920@decibel.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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(at)decibel(dot)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?"


From: Russell Smith <mr-russ(at)pws(dot)com(dot)au>
To: "Jim C(dot) Nasby" <decibel(at)decibel(dot)org>
Cc: Neil Conway <neilc(at)samurai(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pl/pgsql enabled by default
Date: 2005-05-06 10:34:49
Message-ID: 200505062034.50287.mr-russ@pws.com.au
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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


From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Neil Conway <neilc(at)samurai(dot)com>
Cc: Josh Berkus <josh(at)agliodbs(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pl/pgsql enabled by default
Date: 2005-05-06 14:14:08
Message-ID: 13959.1115388848@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Neil Conway <neilc(at)samurai(dot)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


From: Andrew Dunstan <andrew(at)dunslane(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Neil Conway <neilc(at)samurai(dot)com>, Josh Berkus <josh(at)agliodbs(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pl/pgsql enabled by default
Date: 2005-05-06 16:13:41
Message-ID: 427B97B5.7040306@dunslane.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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


From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Andrew Dunstan <andrew(at)dunslane(dot)net>
Cc: Neil Conway <neilc(at)samurai(dot)com>, Josh Berkus <josh(at)agliodbs(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pl/pgsql enabled by default
Date: 2005-05-06 16:16:20
Message-ID: 15341.1115396180@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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


From: Andrew Sullivan <ajs(at)crankycanuck(dot)ca>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: pl/pgsql enabled by default
Date: 2005-05-06 16:19:12
Message-ID: 20050506161912.GF13044@phlogiston.dyndns.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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(at)crankycanuck(dot)ca
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism.
--Brad Holland


From: elein(at)varlena(dot)com (elein)
To: Andrew Sullivan <ajs(at)crankycanuck(dot)ca>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: pl/pgsql enabled by default
Date: 2005-05-06 19:15:52
Message-ID: 20050506191552.GR7396@varlena.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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(at)varlena(dot)com

On Fri, May 06, 2005 at 12:19:12PM -0400, Andrew Sullivan wrote:
> 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(at)crankycanuck(dot)ca
> In the future this spectacle of the middle classes shocking the avant-
> garde will probably become the textbook definition of Postmodernism.
> --Brad Holland
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>


From: Neil Conway <neilc(at)samurai(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Josh Berkus <josh(at)agliodbs(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pl/pgsql enabled by default
Date: 2005-05-07 04:39:38
Message-ID: 427C468A.7080106@samurai.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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


From: Neil Conway <neilc(at)samurai(dot)com>
To: Andrew Sullivan <ajs(at)crankycanuck(dot)ca>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: pl/pgsql enabled by default
Date: 2005-05-07 04:52:57
Message-ID: 427C49A9.9030503@samurai.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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


From: Simon Riggs <simon(at)2ndquadrant(dot)com>
To: Neil Conway <neilc(at)samurai(dot)com>
Cc: Andrew Sullivan <ajs(at)crankycanuck(dot)ca>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: pl/pgsql enabled by default
Date: 2005-05-07 09:47:07
Message-ID: 1115459227.3517.85.camel@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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


From: Neil Conway <neilc(at)samurai(dot)com>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: Andrew Sullivan <ajs(at)crankycanuck(dot)ca>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: pl/pgsql enabled by default
Date: 2005-05-07 10:29:54
Message-ID: 427C98A2.6090703@samurai.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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


From: Andrew Sullivan <ajs(at)crankycanuck(dot)ca>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: pl/pgsql enabled by default
Date: 2005-05-07 14:56:42
Message-ID: 20050507145642.GA15586@phlogiston.dyndns.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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(at)crankycanuck(dot)ca
The plural of anecdote is not data.
--Roger Brinner


From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Neil Conway <neilc(at)samurai(dot)com>
Cc: Simon Riggs <simon(at)2ndquadrant(dot)com>, Andrew Sullivan <ajs(at)crankycanuck(dot)ca>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: pl/pgsql enabled by default
Date: 2005-05-07 15:38:40
Message-ID: 2877.1115480320@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Neil Conway <neilc(at)samurai(dot)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


From: Andrew - Supernews <andrew+nonews(at)supernews(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: pl/pgsql enabled by default
Date: 2005-05-07 19:08:46
Message-ID: slrnd7q4hu.2ep3.andrew+nonews@trinity.supernews.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On 2005-05-07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Neil Conway <neilc(at)samurai(dot)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


From: Josh Berkus <josh(at)agliodbs(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: pl/pgsql enabled by default
Date: 2005-05-07 19:18:27
Message-ID: 200505071218.27611.josh@agliodbs.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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


From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: pl/pgsql enabled by default
Date: 2005-05-07 23:13:50
Message-ID: 6324.1115507630@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> Before we get into more minutia regarding potential security risk of plpgsql,
> are there any reasons *other* than security to not enable it?

Several potential issues have already been mentioned in this thread,
eg, what about shared library dependency vs linking plpgsql statically
into the backend, how will we deal with existing dump files, how will
it be possible for a DBA to disable the feature if he wishes.

But security is the easiest to handwave about with no facts ;-) ...
so if it gets shot down on that basis then no one need do the work
to figure out solutions to the other questions.

regards, tom lane


From: Neil Conway <neilc(at)samurai(dot)com>
To: Andrew Sullivan <ajs(at)crankycanuck(dot)ca>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: pl/pgsql enabled by default
Date: 2005-05-07 23:22:55
Message-ID: 427D4DCF.5050808@samurai.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Andrew Sullivan wrote:
> This is not really analogous, because those are already on

Which is my point: you're suggesting we retrofit a security policy onto
PG that does not apply to the vast majority of the base system -- and
that if applied would require fundamental changes.

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

Security (in the limited sense of "disabling features by default") is
not free; there is a tradeoff between security and convenience, security
and administrative simplicity, and so on. Given that I have yet to see a
single substantive argument for pl/pgsql being a security risk that has
withstood any scrutiny, I don't see that the "security" side of the
tradeoff has a lot of merit.

-Neil


From: Mike Mascari <mascarm(at)mascari(dot)com>
To: Neil Conway <neilc(at)samurai(dot)com>
Cc: Andrew Sullivan <ajs(at)crankycanuck(dot)ca>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: pl/pgsql enabled by default
Date: 2005-05-08 01:59:39
Message-ID: 427D728B.8020105@mascari.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Neil Conway wrote:
> Andrew Sullivan wrote:
>> This is not really analogous, because those are already on
> Security (in the limited sense of "disabling features by default") is
> not free; there is a tradeoff between security and convenience, security
> and administrative simplicity, and so on. Given that I have yet to see a
> single substantive argument for pl/pgsql being a security risk that has
> withstood any scrutiny, I don't see that the "security" side of the
> tradeoff has a lot of merit.

People who use views to achieve row security, which is a rather common
paradigm, cannot allow users to create functions with side effects.

Mike Mascari


From: Neil Conway <neilc(at)samurai(dot)com>
To: Mike Mascari <mascarm(at)mascari(dot)com>
Cc: Andrew Sullivan <ajs(at)crankycanuck(dot)ca>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: pl/pgsql enabled by default
Date: 2005-05-08 02:05:57
Message-ID: 427D7405.2090605@samurai.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Mike Mascari wrote:
> People who use views to achieve row security, which is a rather common
> paradigm, cannot allow users to create functions with side effects.

Can you elaborate? I'm not sure I follow you.

(I'll note anyway that (1) SQL functions can have side effects: CREATE
FUNCTION foo() RETURNS VOID AS 'DELETE FROM ...', for example (2)
Administrators can always choose to drop pl/pgsql for a particular
database, disable it at initdb time, or REVOKE usage of pl/pgsql for
particular users.)

-Neil


From: Mike Mascari <mascarm(at)mascari(dot)com>
To: Neil Conway <neilc(at)samurai(dot)com>
Cc: Andrew Sullivan <ajs(at)crankycanuck(dot)ca>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: pl/pgsql enabled by default
Date: 2005-05-08 02:20:55
Message-ID: 427D7787.90507@mascari.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Neil Conway wrote:
> Mike Mascari wrote:
>
>> People who use views to achieve row security, which is a rather common
>> paradigm, cannot allow users to create functions with side effects.
>
>
> Can you elaborate? I'm not sure I follow you.
>
> (I'll note anyway that (1) SQL functions can have side effects: CREATE
> FUNCTION foo() RETURNS VOID AS 'DELETE FROM ...', for example

Wow. That's a problem IMHO. Many people use views to allow userA to
query his salary without seeing userB's. If userA can just:

1. Create a SQL function which inserts into another table its arguments

2. Issue a query like:

SELECT *
FROM view_of_salaries_based_on_current_user
WHERE my_side_effect_function_that_inserts_into_a_temp_table(salary,
employee);

and that function is writable with just 'SQL', then many, many people
have a serious security risk on their hands. Perhaps this is why
Oracle's standard UDFs cannot perform inserts, updates, or deletes.

Mike Mascari


From: Andrew Dunstan <andrew(at)dunslane(dot)net>
To: Mike Mascari <mascarm(at)mascari(dot)com>
Cc: Neil Conway <neilc(at)samurai(dot)com>, Andrew Sullivan <ajs(at)crankycanuck(dot)ca>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: pl/pgsql enabled by default
Date: 2005-05-08 02:40:36
Message-ID: 427D7C24.8050509@dunslane.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Mike Mascari wrote:

> Neil Conway wrote:
>
>> Mike Mascari wrote:
>>
>>> People who use views to achieve row security, which is a rather
>>> common paradigm, cannot allow users to create functions with side
>>> effects.
>>
>>
>>
>> Can you elaborate? I'm not sure I follow you.
>>
>> (I'll note anyway that (1) SQL functions can have side effects:
>> CREATE FUNCTION foo() RETURNS VOID AS 'DELETE FROM ...', for example
>
>
> Wow. That's a problem IMHO. Many people use views to allow userA to
> query his salary without seeing userB's. If userA can just:
>
> 1. Create a SQL function which inserts into another table its arguments
>
> 2. Issue a query like:
>
> SELECT *
> FROM view_of_salaries_based_on_current_user
> WHERE my_side_effect_function_that_inserts_into_a_temp_table(salary,
> employee);
>
> and that function is writable with just 'SQL', then many, many people
> have a serious security risk on their hands. Perhaps this is why
> Oracle's standard UDFs cannot perform inserts, updates, or deletes.
>
>

but the side effect function will only run (unless you set it with
security definer) with the privileges of the caller - it won't grant
visibility to things that user can't otherwise see.

In any case, you should define your security setup with the
capabilities / limitations of the db engine in mind. If there is any
security problem in your scenario, it is that you appear to have made
unwarranted assumptions about how postgres works, rather than that
postgres has a problem.

Either way, this does not illustrate how enabling plpgsql by default is
a security risk.

cheers

andrew


From: Mike Mascari <mascarm(at)mascari(dot)com>
To: Andrew Dunstan <andrew(at)dunslane(dot)net>
Cc: Neil Conway <neilc(at)samurai(dot)com>, Andrew Sullivan <ajs(at)crankycanuck(dot)ca>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: pl/pgsql enabled by default
Date: 2005-05-08 02:49:24
Message-ID: 427D7E34.6040300@mascari.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Andrew Dunstan wrote:
>
>
> Mike Mascari wrote:

> but the side effect function will only run (unless you set it with
> security definer) with the privileges of the caller - it won't grant
> visibility to things that user can't otherwise see.

If the visibility is determined by view definitions, such as using
CURRENT_USER, which is an exceedingly common practice, then the caller
will be able to record tuples before they are filtered by the executor.

> In any case, you should define your security setup with the
> capabilities / limitations of the db engine in mind. If there is any
> security problem in your scenario, it is that you appear to have made
> unwarranted assumptions about how postgres works, rather than that
> postgres has a problem.

I think most people coming from any other enterprise-class RDBMS
environment will be surprised that they cannot use VIEWs to provide
user-specific views on data. I could be wrong, but I'd put money on it...

> Either way, this does not illustrate how enabling plpgsql by default is
> a security risk.

Correct, as the vulnerability exists within the 'SQL' language as well.
The only difference is that enabling plpgsql by default changes it from
a leak to a full blown flood.

Mike Mascari


From: Neil Conway <neilc(at)samurai(dot)com>
To: Mike Mascari <mascarm(at)mascari(dot)com>
Cc: Andrew Dunstan <andrew(at)dunslane(dot)net>, Andrew Sullivan <ajs(at)crankycanuck(dot)ca>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: pl/pgsql enabled by default
Date: 2005-05-08 02:54:22
Message-ID: 427D7F5E.4000406@samurai.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Mike Mascari wrote:
> Correct, as the vulnerability exists within the 'SQL' language as well.
> The only difference is that enabling plpgsql by default changes it from
> a leak to a full blown flood.

How does it make any difference at all?

-Neil


From: Rod Taylor <pg(at)rbt(dot)ca>
To: Mike Mascari <mascarm(at)mascari(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org, Andrew Sullivan <ajs(at)crankycanuck(dot)ca>, Neil Conway <neilc(at)samurai(dot)com>
Subject: Re: pl/pgsql enabled by default
Date: 2005-05-08 11:39:33
Message-ID: 1115552373.683.30.camel@home
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

> 2. Issue a query like:
>
> SELECT *
> FROM view_of_salaries_based_on_current_user
> WHERE my_side_effect_function_that_inserts_into_a_temp_table(salary,
> employee);

An SRF will guarantee an execution order and work for security purposes,
but getting your function with side effects to run early would be rather
challenging. Somehow you would need to make the planner think the
function is fairly selective without it actually being that way and the
only way that I'm aware of to get the planner to consider a functions
selectivity is to index it. But how do you index the function without
knowing all of the values?

--


From: Josh Berkus <josh(at)agliodbs(dot)com>
To: Mike Mascari <mascarm(at)mascari(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: pl/pgsql enabled by default
Date: 2005-05-08 18:03:36
Message-ID: 200505081103.36708.josh@agliodbs.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Mike,

> I think most people coming from any other enterprise-class RDBMS
> environment will be surprised that they cannot use VIEWs to provide
> user-specific views on data. I could be wrong, but I'd put money on it...

Well, I'd say that giving regular users the "create" permission on your
database/schema is unwise, period. I don't, even when the only user is
"phpuser". SQL injections attacks are no fun.

Also, as Andrew points out, this can't be used to circumvent view-based
security if you've set it up correctly; if the user can't "select * from
table", then he can't write a function to "select * from table."

--
Josh Berkus
Aglio Database Solutions
San Francisco


From: Andrew Dunstan <andrew(at)dunslane(dot)net>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: Mike Mascari <mascarm(at)mascari(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: pl/pgsql enabled by default
Date: 2005-05-08 21:48:21
Message-ID: 427E8925.9060007@dunslane.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Josh Berkus wrote:

>Mike,
>
>
>
>>I think most people coming from any other enterprise-class RDBMS
>>environment will be surprised that they cannot use VIEWs to provide
>>user-specific views on data. I could be wrong, but I'd put money on it...
>>
>>
>
>Well, I'd say that giving regular users the "create" permission on your
>database/schema is unwise, period. I don't, even when the only user is
>"phpuser". SQL injections attacks are no fun.
>
>Also, as Andrew points out, this can't be used to circumvent view-based
>security if you've set it up correctly; if the user can't "select * from
>table", then he can't write a function to "select * from table."
>
>
>

Seems it's a bit more complicated. Kris Jurka has explained to me how
one might be able to, at least theoretically. Perhaps Mike needs to do
something like:

revoke usage on language sql from public;

on his db. Then users could continue to use functions he has defined,
but not subvert things via their own functions. That will certainly go
into my list of db hardening tips and tricks.

cheers

andrew


From: Greg Stark <gsstark(at)mit(dot)edu>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: pl/pgsql enabled by default
Date: 2005-05-09 03:48:10
Message-ID: 87y8aprret.fsf@stark.xeocode.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Mike Mascari <mascarm(at)mascari(dot)com> writes:

> 2. Issue a query like:
>
> SELECT *
> FROM view_of_salaries_based_on_current_user
> WHERE my_side_effect_function_that_inserts_into_a_temp_table(salary, employee);

That's just exactly equivalent to

SELECT *
FROM (select *
from all_salaries
where user = CURRENT_USER
)
WHERE malicious_function(salary,employee)

Hm. If you incorrectly mark your function as IMMUTABLE even though it has side
effects then the planner may indeed collapse this. Does the planner know it
can't collapse views if the underlying tables aren't accessible to the user?

--
greg


From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Greg Stark <gsstark(at)mit(dot)edu>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: pl/pgsql enabled by default
Date: 2005-05-09 04:00:21
Message-ID: 16567.1115611221@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Greg Stark <gsstark(at)mit(dot)edu> writes:
> Hm. If you incorrectly mark your function as IMMUTABLE even though it
> has side effects then the planner may indeed collapse this. Does the
> planner know it can't collapse views if the underlying tables aren't
> accessible to the user?

There are no cases where function or view collapsing elides permissions
checks (if you have a counterexample please provide it!!). They could
change the time at which permissions checks are applied, though; which
has the potential for a REVOKE to not disallow execution of already-
planned queries that ideally it should prevent. I believe that this
risk will be fixed by the planned forcing of replanning after schema
changes.

regards, tom lane


From: Greg Stark <gsstark(at)mit(dot)edu>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Greg Stark <gsstark(at)mit(dot)edu>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: pl/pgsql enabled by default
Date: 2005-05-09 05:31:55
Message-ID: 87psw1rmlw.fsf@stark.xeocode.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Greg Stark <gsstark(at)mit(dot)edu> writes:
> > Hm. If you incorrectly mark your function as IMMUTABLE even though it
> > has side effects then the planner may indeed collapse this. Does the
> > planner know it can't collapse views if the underlying tables aren't
> > accessible to the user?
>
> There are no cases where function or view collapsing elides permissions
> checks (if you have a counterexample please provide it!!).

I'm talking about something like this. In guess there isn't a problem after
all but it depends critically on the AND short-circuiting (and the order of
evaluation of the expression not being changed).

db=> create view vtest as select * from test where a > 1 ;
db=> create or replace function f(integer) returns integer as 'begin raise notice ''foo %'', $1; return $1; end' language plpgsql;

db=> explain select * from vtest where f(a)>0;
QUERY PLAN
-------------------------------------------------------
Seq Scan on test (cost=0.00..27.50 rows=112 width=4)
Filter: ((a > 1) AND (f(a) > 0))
(2 rows)

I can't come up with any circumstances where the function will get called
before the a>1 clause. If it were indexed then it would be "evaluated" first
but it would no longer be relevant since the function wouldn't be getting
called.

But it's something to watch out for. If ever it seems like a wise idea to have
the optimizer fiddle with the order of evaluation, say based on the
selectivity or computational expense of the conditions then it could create a
problem.

--
greg