Thread: Remote administration contrib module

Remote administration contrib module

From
"Dave Page"
Date:
A thread on -general
(http://archives.postgresql.org/pgsql-general/2006-03/msg01023.php)
recently ended in the idea that the pgAdmin 'adminpack' be included as
an 'official' PostgreSQL contrib module. Currently the code is an add-on
available from the pgAdmin website, unless you run the Windows
pgInstaller distribution of PostgreSQL in which case it is already
included.

For those that don't know, the adminpack is a set of additional
functions designed to allow pgAdmin (and potentially any other client
application) the ability to rewrite server configuration files and
browse & read logfiles etc. The full readme can be found at
http://svn.pgadmin.org/cgi-bin/viewcvs.cgi/trunk/pgadmin3/xtra/admin81/R
EADME.admin81?rev=5024&view=markup, and the code can be seen at
http://svn.pgadmin.org/cgi-bin/viewcvs.cgi/trunk/pgadmin3/xtra/admin81/.

Tom's concern in the -general thread was that the pgAdmin licence is the
Artistic License, however we are now in a position to offer the code
under the BSD licence. So, how would people feel about including this as
a contrib module in the future, until(/if) equivalent functionality
becomes available in -core in some form?

Regards, Dave.


Re: Remote administration contrib module

From
Peter Eisentraut
Date:
Am Montag, 27. März 2006 15:12 schrieb Dave Page:
> So, how would people feel about including this as
> a contrib module in the future, until(/if) equivalent functionality
> becomes available in -core in some form?

Right now you have got plenty of time to get it in shape for inclusion in 
core, so we might not even have to take the detour through a contrib module.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/


Re: Remote administration contrib module

From
"Dave Page"
Date:


-----Original Message-----
From: Peter Eisentraut [mailto:peter_e@gmx.net]
Sent: Mon 3/27/2006 3:00 PM
To: pgsql-hackers@postgresql.org
Cc: Dave Page
Subject: Re: [HACKERS] Remote administration contrib module
Am Montag, 27. März 2006 15:12 schrieb Dave Page:
> > So, how would people feel about including this as
> > a contrib module in the future, until(/if) equivalent functionality
> > becomes available in -core in some form?
>
> Right now you have got plenty of time to get it in shape for inclusion in
> core, so we might not even have to take the detour through a contrib module.

As it stands it was previously rejected for inclusion in -core in it's current form. The main objector was Tom, but as
itwas him that encouraged me to change the licencing for inclusion as a contrib module I assume he doesn't object to
that.

I have submitted a conference discussion proposal to talk about ways of implementing the remote configuration that we
wantwhich was the main thing I think Tom objected to originally, but that's some time away and would likely result in a
significantlymore complex solution that may or may not get done for 8.2. In addition, there are one or two helper
functionsin there that were previously rejected more on the 'no use to end user' basis iirc (see pg_logdir_ls) which
maynever end up in -core without reconsideration of the need. It would be good (for us at least) to get it in as a
contrib,even if we do manage to replace it, or some of it, by release. 

Regards, Dave



Re: Remote administration contrib module

From
Bruce Momjian
Date:
Dave Page wrote:
> > Right now you have got plenty of time to get it in shape for inclusion in
> > core, so we might not even have to take the detour through a contrib module.
> 
> As it stands it was previously rejected for inclusion in -core in it's
> current form. The main objector was Tom, but as it was him that
> encouraged me to change the licencing for inclusion as a contrib module
> I assume he doesn't object to that.
> 
> I have submitted a conference discussion proposal to talk about ways
> of implementing the remote configuration that we want which was the
> main thing I think Tom objected to originally, but that's some time
> away and would likely result in a significantly more complex solution
> that may or may not get done for 8.2. In addition, there are one or
> two helper functions in there that were previously rejected more on
> the 'no use to end user' basis iirc (see pg_logdir_ls) which may never
> end up in -core without reconsideration of the need. It would be good
> (for us at least) to get it in as a contrib, even if we do manage to
> replace it, or some of it, by release.

+1 for /contrib

I think the issue was that adding these fuctions adds a potential
security opening, so we didn't want it in core by default, but /contrib
seems logical because anyone who needs it can just add it.

This is similar to the fact we don't include plpgsql by default in
databases, for the same reason, but we have the ability to use
createlang to add it.  Adding these functions to contrib gives us the
same control.

-- Bruce Momjian   http://candle.pha.pa.us EnterpriseDB    http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +


Re: Remote administration contrib module

From
Peter Eisentraut
Date:
Bruce Momjian wrote:
> I think the issue was that adding these fuctions adds a potential
> security opening, so we didn't want it in core by default, but
> /contrib seems logical because anyone who needs it can just add it.

Well, if there are security issues, then this is a poor fix.  A lot of 
people use pgAdmin, many of them less experienced with PostgreSQL, so 
before long all of these functions are going to be installed at many 
sites anyway.  If there are _security_ issues, they need to be fixed 
before things go into contrib.

> This is similar to the fact we don't include plpgsql by default in
> databases, for the same reason,

I doubt that that is really the reason.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/


Re: Remote administration contrib module

From
Bruce Momjian
Date:
Peter Eisentraut wrote:
> Bruce Momjian wrote:
> > I think the issue was that adding these fuctions adds a potential
> > security opening, so we didn't want it in core by default, but
> > /contrib seems logical because anyone who needs it can just add it.
> 
> Well, if there are security issues, then this is a poor fix.  A lot of 
> people use pgAdmin, many of them less experienced with PostgreSQL, so 
> before long all of these functions are going to be installed at many 
> sites anyway.  If there are _security_ issues, they need to be fixed 
> before things go into contrib.

The logic is why add functionality by default that can be used as a
potential security hole if you are not using it.

> > This is similar to the fact we don't include plpgsql by default in
> > databases, for the same reason,
> 
> I doubt that that is really the reason.

It actually is the reason I have heard.

--  Bruce Momjian   http://candle.pha.pa.us EnterpriseDB    http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +


Re: Remote administration contrib module

From
"Dave Page"
Date:

-----Original Message-----
From: "Peter Eisentraut"<peter_e@gmx.net>
Sent: 10/04/06 22:43:05
To: "Bruce Momjian"<pgman@candle.pha.pa.us>
Cc: "Dave Page"<dpage@vale-housing.co.uk>, "pgsql-hackers@postgresql.org"<pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Remote administration contrib module

>  If there are _security_ issues, they need to be fixed
> before things go into contrib.

(From memory) There were concerns, rather than actual issues. The functions are all superuser-only where appropriate,
andwhile the only potentially destructive ones (pg_file_write, pg_file_rename, pg_file_unlink) can kill files under
$PGDATA- but then, so can COPY just as easily. 

> > This is similar to the fact we don't include plpgsql by default in
> > databases, for the same reason,

> I doubt that that is really the reason.

It's the only reason I ever heard.

/D

-----Unmodified Original Message-----
Bruce Momjian wrote:
> I think the issue was that adding these fuctions adds a potential
> security opening, so we didn't want it in core by default, but
> /contrib seems logical because anyone who needs it can just add it.

Well, if there are security issues, then this is a poor fix.  A lot of
people use pgAdmin, many of them less experienced with PostgreSQL, so
before long all of these functions are going to be installed at many
sites anyway.  If there are _security_ issues, they need to be fixed
before things go into contrib.

> This is similar to the fact we don't include plpgsql by default in
> databases, for the same reason,

I doubt that that is really the reason.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/


plpgsql by default (was: Re: Remote administration contrib module)

From
Andrew - Supernews
Date:
On 2006-04-10, Bruce Momjian <pgman@candle.pha.pa.us> wrote:
> Peter Eisentraut wrote:
>> > This is similar to the fact we don't include plpgsql by default in
>> > databases, for the same reason,
[the reason being "security"]
>> 
>> I doubt that that is really the reason.
>
> It actually is the reason I have heard.

And it was duly debunked.

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


Re: plpgsql by default (was: Re: Remote administration contrib module)

From
Tom Lane
Date:
Andrew - Supernews <andrew+nonews@supernews.com> writes:
> On 2006-04-10, Bruce Momjian <pgman@candle.pha.pa.us> wrote:
>>> [ security ]
>> It actually is the reason I have heard.

> And it was duly debunked.

That is the reasoning, and personally I agree with it.  You don't leave
sharp objects sitting around if you have no need to have them out.
The availability of plpgsql or other PLs makes for a significant jump
in what a bad guy can do if he gets access to the database, so if a
particular DB doesn't actually need the capability, it's best that it
not be there.  And that's without considering the possibility of genuine
security holes in the PL, but just supposing that it only does what it's
supposed to do.
        regards, tom lane


Re: plpgsql by default (was: Re: Remote administration contrib module)

From
Andrew - Supernews
Date:
On 2006-04-11, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Andrew - Supernews <andrew+nonews@supernews.com> writes:
>> On 2006-04-10, Bruce Momjian <pgman@candle.pha.pa.us> wrote:
>>>> [ security ]
>>> It actually is the reason I have heard.
>
>> And it was duly debunked.
>
> That is the reasoning, and personally I agree with it.  You don't leave
> sharp objects sitting around if you have no need to have them out.
> The availability of plpgsql or other PLs makes for a significant jump
> in what a bad guy can do if he gets access to the database,

Example please.

Last time this was discussed, the claimed examples were things like
running infinite loops as a resource exhaustion attack, which is pretty
trivial to do in plain SQL functions or even in plain SQL without functions,
and running things like brute-force attacks on password hashes (which also
isn't hard using plain SQL functions).

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


Re: plpgsql by default

From
"Joshua D. Drake"
Date:
Tom Lane wrote:
> Andrew - Supernews <andrew+nonews@supernews.com> writes:
>> On 2006-04-10, Bruce Momjian <pgman@candle.pha.pa.us> wrote:
>>>> [ security ]
>>> It actually is the reason I have heard.
> 
>> And it was duly debunked.
> 
> That is the reasoning, and personally I agree with it.  You don't leave
> sharp objects sitting around if you have no need to have them out.

Uhmmm exactly how is plpgsql a sharp object? plPerl... ok that makes 
sense but you can't access the underlying OS with plpgsql.

> The availability of plpgsql or other PLs makes for a significant jump
> in what a bad guy can do if he gets access to the database,

What does enabling plpgsql do via access that you can't just do from an 
SQL query?

Joshua D. Drake
 so if a
> particular DB doesn't actually need the capability, it's best that it
> not be there.  And that's without considering the possibility of genuine
> security holes in the PL, but just supposing that it only does what it's
> supposed to do.
> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
> 


-- 
            === The PostgreSQL Company: Command Prompt, Inc. ===      Sales/Support: +1.503.667.4564 || 24x7/Emergency:
+1.800.492.2240     Providing the most comprehensive  PostgreSQL solutions since 1997
http://www.commandprompt.com/




Re: plpgsql by default

From
"Marc G. Fournier"
Date:
On Mon, 10 Apr 2006, Joshua D. Drake wrote:

> Tom Lane wrote:
>> Andrew - Supernews <andrew+nonews@supernews.com> writes:
>>> On 2006-04-10, Bruce Momjian <pgman@candle.pha.pa.us> wrote:
>>>>> [ security ]
>>>> It actually is the reason I have heard.
>> 
>>> And it was duly debunked.
>> 
>> That is the reasoning, and personally I agree with it.  You don't leave
>> sharp objects sitting around if you have no need to have them out.
>
> Uhmmm exactly how is plpgsql a sharp object? plPerl... ok that makes sense 
> but you can't access the underlying OS with plpgsql.

Can you guarantee unequivocally that there are absolutely not security 
issues in plpgsql?

I believe Tom's point is that it is not possible to do so, and, since 
plpgsql isn't something that all applications need/use, it isn't something 
that needs to be 'loaded by default' ... its like loading mod_perl in 
apache for an application that only uses PHP ... you can do it, but why 
bother?

If Tom could cite any security issues with plpgsql, he would have probably 
fixed it by now ... but I don't believe he'd go out on a limb and state 
that there weren't any either ...


----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664


Re: plpgsql by default

From
Tom Lane
Date:
"Joshua D. Drake" <jd@commandprompt.com> writes:
> What does enabling plpgsql do via access that you can't just do from an 
> SQL query?

SQL isn't Turing-complete --- plpgsql is.  So if our would-be hacker has
a need to do some computation incidental to his hack, he can certainly
get it done in plpgsql, but not necessarily in plain SQL.

I don't feel a need to offer specific examples as requested by Andrew.
The point here is that we're offering a significantly more powerful
swiss army knife when we include plpgsql (or any other PL), and it's
hard to foresee the implications of that with any certainty.
        regards, tom lane


Re: plpgsql by default

From
David Fetter
Date:
On Tue, Apr 11, 2006 at 12:47:03AM -0400, Tom Lane wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
> > What does enabling plpgsql do via access that you can't just do from an 
> > SQL query?
> 
> SQL isn't Turing-complete

With all due respect, SQL *is* Turing-complete.  Here's a little demo
of this Turing-completeness:

CREATE TABLE fib_mem(   n numeric PRIMARY KEY,   fib_n numeric NOT NULL
);

CREATE OR REPLACE FUNCTION memoize_fib(n numeric, fib_n numeric)
RETURNS numeric
STRICT
LANGUAGE SQL
AS $$   INSERT INTO fib_mem VALUES ($1, $2);   SELECT $2;
$$;

CREATE OR REPLACE FUNCTION fib(numeric)
RETURNS numeric
LANGUAGE SQL
AS $$   SELECT COALESCE(       (SELECT fib_n FROM fib_mem WHERE n=$1),       memoize_fib(           $1,           CASE
WHEN$1 < 2 THEN $1 ELSE fib($1-2) + fib($1-1) END       )   );
 
$$;

> --- plpgsql is.  So if our would-be hacker has a need to do some
> computation incidental to his hack, he can certainly get it done in
> plpgsql, but not necessarily in plain SQL.
> 
> I don't feel a need to offer specific examples as requested by
> Andrew.  The point here is that we're offering a significantly more
> powerful swiss army knife when we include plpgsql (or any other PL),
> and it's hard to foresee the implications of that with any
> certainty.

The cat is already out of the bag with SQL because it has branching
and recursion, which is enough for Turing-completeness.  Whether we
decide to include PL/whatever by default or not shouldn't be
predicated on the wrong assumption that these PLs have more power
inside the database than SQL does.

That said, I believe it's a *great* idea not to include untrusted PLs
by default :)

Cheers,
D
-- 
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666                             Skype: davidfetter

Remember to vote!


Re: plpgsql by default

From
Andrew - Supernews
Date:
On 2006-04-11, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
>> What does enabling plpgsql do via access that you can't just do from an 
>> SQL query?
>
> SQL isn't Turing-complete

SQL with the ability to create recursive functions, as exists in pg, is
certainly turing-complete (within the usual practical sense of the term,
since no real machine has unlimited storage space).

A formal proof is left as an exercise for the reader; but several examples
of the power of SQL (not pl/pgsql) functions for performing iterative
operations can be found in the newsysviews source, along with a working
implementation of generate_series for 7.4 in plain SQL. (For convenience
that implementation has a limited range, but merely adding a few more
cross joins would extend that range as far as desired.)

Pl/pgsql may offer notational conveniences, but it has no real computational
power above plain SQL functions.

> I don't feel a need to offer specific examples as requested by Andrew.

Why not? You're basing your entire argument on a false premise (that
pl/pgsql is more powerful than SQL); I can provide specific examples of
why this is not the case, or refute any that you care to provide. For
example, here is an SQL function to generate all alphabetic strings of
a specified length:

create function alpha(integer) returns setof text language sql as $$   select x || chr(c) from alpha($1-1) s1(x),
                   generate_series(97,122) s2(c) where $1 > 0   union all select '' where $1 <= 0
 
$$;

(and yes, I can do it without generate_series if need be)

That takes ~97 seconds to execute alpha(5) on one of my machines, whereas
a simple generate_series returning the same number of rows takes ~30
seconds, so the performance is not at all bad.

> The point here is that we're offering a significantly more powerful
> swiss army knife when we include plpgsql (or any other PL), and it's
> hard to foresee the implications of that with any certainty.

pl/pgsql is not comparable to other PLs in this case. Specifically, it
does not provide access to any functionality that is not already part of
Postgres itself.

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


Re: plpgsql by default

From
Richard Huxton
Date:
Andrew - Supernews wrote:
> On 2006-04-11, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I don't feel a need to offer specific examples as requested by Andrew.
> 
> Why not? You're basing your entire argument on a false premise (that
> pl/pgsql is more powerful than SQL); I can provide specific examples of
> why this is not the case, or refute any that you care to provide. 

You can write trigger functions in plpgsql.

--   Richard Huxton  Archonet Ltd


Re: plpgsql by default

From
Andrew - Supernews
Date:
On 2006-04-11, Richard Huxton <dev@archonet.com> wrote:
> Andrew - Supernews wrote:
>> On 2006-04-11, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> I don't feel a need to offer specific examples as requested by Andrew.
>> 
>> Why not? You're basing your entire argument on a false premise (that
>> pl/pgsql is more powerful than SQL); I can provide specific examples of
>> why this is not the case, or refute any that you care to provide. 
>
> You can write trigger functions in plpgsql.

You can write rules without plpgsql.

While rules and triggers are not equivalent, I think you'll be hard-pressed
to come up with an example where a malicious intruder, with sufficient
access to the system to create pl/pgsql functions if pl/pgsql is loaded,
can carry out a useful attack using triggers that would not be possible
without them.

Let's try a simple example; changing the value of a column in future
inserts into a table. Doing it without a trigger turns out to be simple;
as a demonstration, this method allows an SQL function to be invoked:

create function foox(foo) returns integer language sql as $$ update foo set value='bogus' where id=$1.id; select 1;
$$;

create rule foo_rule as on insert to foo do insert into bar values (foox(NEW));

insert into foo values (2,'bar');
INSERT 0 1

select * from foo;id | value 
----+------- 1 | foo 2 | bogus
(2 rows)

So that's triggers without pl/pgsql. Anyone else want to try a challenge?

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


Re: plpgsql by default

From
"Joshua D. Drake"
Date:
> Can you guarantee unequivocally that there are absolutely not security 
> issues in plpgsql?

Can you guarantee unequivocally that there are absolutely not security 
issues in PostgreSQL?

> 
> I believe Tom's point is that it is not possible to do so, and, since 
> plpgsql isn't something that all applications need/use, it isn't 
> something that needs to be 'loaded by default' ... its like loading 
> mod_perl in apache for an application that only uses PHP ... you can do 
> it, but why bother?

Well.... many distributions do but no it is not the same. plPGSQL is the 
default procedural language for PostgreSQL. It is not a contrib module,
and it is built by default. So why not install it by default to make it 
just one step easier for our community?

Sincerely,

Joshua D. Drake

> 
> If Tom could cite any security issues with plpgsql, he would have 
> probably fixed it by now ... but I don't believe he'd go out on a limb 
> and state that there weren't any either ...
> 
> 
> ----
> Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
> Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664
> 


-- 
            === The PostgreSQL Company: Command Prompt, Inc. ===      Sales/Support: +1.503.667.4564 || 24x7/Emergency:
+1.800.492.2240     Providing the most comprehensive  PostgreSQL solutions since 1997
http://www.commandprompt.com/




Re: plpgsql by default

From
"Joshua D. Drake"
Date:
Tom Lane wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
>> What does enabling plpgsql do via access that you can't just do from an 
>> SQL query?
> 
> SQL isn't Turing-complete --- plpgsql is.  So if our would-be hacker has
> a need to do some computation incidental to his hack, he can certainly
> get it done in plpgsql, but not necessarily in plain SQL.

O.k. sure... but if the hackers wants to do something really bad it is 
easy to do so in SQL... TRUNCATE, DELETE FROM, VACUUM FULL, DROP... ,
SELECT generate_series()

Sincerely,

Joshua D. Drake


-- 
            === The PostgreSQL Company: Command Prompt, Inc. ===      Sales/Support: +1.503.667.4564 || 24x7/Emergency:
+1.800.492.2240     Providing the most comprehensive  PostgreSQL solutions since 1997
http://www.commandprompt.com/




Re: plpgsql by default

From
"Joshua D. Drake"
Date:
Richard Huxton wrote:
> Andrew - Supernews wrote:
>> On 2006-04-11, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> I don't feel a need to offer specific examples as requested by Andrew.
>>
>> Why not? You're basing your entire argument on a false premise (that
>> pl/pgsql is more powerful than SQL); I can provide specific examples of
>> why this is not the case, or refute any that you care to provide. 
> 
> You can write trigger functions in plpgsql.

That doesn't make it more powerful, just that it has another feature.
Keep in mind that all internal functions that PostgreSQL includes are 
called from SQL.

Joshua D. Drake

> 


-- 
            === The PostgreSQL Company: Command Prompt, Inc. ===      Sales/Support: +1.503.667.4564 || 24x7/Emergency:
+1.800.492.2240     Providing the most comprehensive  PostgreSQL solutions since 1997
http://www.commandprompt.com/




Re: plpgsql by default

From
"Jim C. Nasby"
Date:
On Mon, Apr 10, 2006 at 11:02:50PM -0700, David Fetter wrote:
> On Tue, Apr 11, 2006 at 12:47:03AM -0400, Tom Lane wrote:
> > "Joshua D. Drake" <jd@commandprompt.com> writes:
> > > What does enabling plpgsql do via access that you can't just do from an 
> > > SQL query?
> > 
> > SQL isn't Turing-complete
> 
> With all due respect, SQL *is* Turing-complete.  Here's a little demo
> of this Turing-completeness:

Rather than debate how turing complete SQL is, look at the real issue:
is a compromised system with plPGSQL installed more dangerous than a
compromised system without plPGSQL. As far as I can see, it's not.
SQL makes it just as easy to DoS the machine (just select a large
cartesian product). plPGSQL doesn't provide any inherent ability to
damage data outside the database, and it doesn't make trashing the
database any easier than it is with plain SQL. About the only thing I
can think of that plPGSQL lets you do that SQL doesn't is to raise
arbitrary errors, but that hardly seems like much of an increased risk.

There is some limited truth to the argument that plPGSQL potentially
opens more potential for a machine to be compromised, but much less so
than allowing connections from any IP does for example. I haven't seen
any real reason not to include plPGSQL by default, especially since
removing whatever slight risk exists is a simple DROP LANGUAGE away.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461


Re: plpgsql by default

From
Tom Lane
Date:
"Jim C. Nasby" <jnasby@pervasive.com> writes:
> Rather than debate how turing complete SQL is, look at the real issue:
> is a compromised system with plPGSQL installed more dangerous than a
> compromised system without plPGSQL. As far as I can see, it's not.

You're disregarding the possibility that plpgsql itself is the source of
a security hole ...

More realistically, though, the theoretical point that you can do
arbitrary calculations by turning loops into recursive SQL functions is
mostly just theoretical, and the reason is that you won't be able to
loop very many times before running out of stack space.  (On my machine
it looks like you can recurse a trivial SQL function only about 600
times before hitting the default stack limit.)  If you have an exploit
that involves moderate amounts of calculation within the server --- say,
brute force password cracking --- the availability of a PL will render
that exploit actually practical, whereas with only SQL functions to work
with it won't be.
        regards, tom lane


Re: plpgsql by default

From
David Fetter
Date:
On Tue, Apr 11, 2006 at 04:35:05PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <jnasby@pervasive.com> writes:
> > Rather than debate how turing complete SQL is, look at the real
> > issue: is a compromised system with plPGSQL installed more
> > dangerous than a compromised system without plPGSQL. As far as I
> > can see, it's not.
> 
> You're disregarding the possibility that plpgsql itself is the
> source of a security hole ...

So might SQL.

> More realistically, though, the theoretical point that you can do
> arbitrary calculations by turning loops into recursive SQL functions
> is mostly just theoretical, and the reason is that you won't be able
> to loop very many times before running out of stack space.  (On my
> machine it looks like you can recurse a trivial SQL function only
> about 600 times before hitting the default stack limit.)  If you
> have an exploit that involves moderate amounts of calculation within
> the server --- say, brute force password cracking --- the
> availability of a PL will render that exploit actually practical,
> whereas with only SQL functions to work with it won't be.

The function I sent memoizes to a table, which avoids the stack space
problem you mentioned.

Cheers,
D
-- 
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666                             Skype: davidfetter

Remember to vote!


Re: plpgsql by default

From
Tom Lane
Date:
David Fetter <david@fetter.org> writes:
> On Tue, Apr 11, 2006 at 04:35:05PM -0400, Tom Lane wrote:
>> More realistically, though, the theoretical point that you can do
>> arbitrary calculations by turning loops into recursive SQL functions
>> is mostly just theoretical, and the reason is that you won't be able
>> to loop very many times before running out of stack space.  (On my
>> machine it looks like you can recurse a trivial SQL function only
>> about 600 times before hitting the default stack limit.)  If you
>> have an exploit that involves moderate amounts of calculation within
>> the server --- say, brute force password cracking --- the
>> availability of a PL will render that exploit actually practical,
>> whereas with only SQL functions to work with it won't be.

> The function I sent memoizes to a table, which avoids the stack space
> problem you mentioned.

In general that's not possible, and even for the specific case, it still
looks to me like fib(n) will use O(n) recursion levels if the table is
initially empty.
        regards, tom lane


Re: plpgsql by default

From
David Fetter
Date:
On Tue, Apr 11, 2006 at 05:01:17PM -0400, Tom Lane wrote:
> David Fetter <david@fetter.org> writes:
> > On Tue, Apr 11, 2006 at 04:35:05PM -0400, Tom Lane wrote:
> >> More realistically, though, the theoretical point that you can do
> >> arbitrary calculations by turning loops into recursive SQL
> >> functions is mostly just theoretical, and the reason is that you
> >> won't be able to loop very many times before running out of stack
> >> space.  (On my machine it looks like you can recurse a trivial
> >> SQL function only about 600 times before hitting the default
> >> stack limit.)  If you have an exploit that involves moderate
> >> amounts of calculation within the server --- say, brute force
> >> password cracking --- the availability of a PL will render that
> >> exploit actually practical, whereas with only SQL functions to
> >> work with it won't be.
> 
> > The function I sent memoizes to a table, which avoids the stack
> > space problem you mentioned.
> 
> In general that's not possible, and even for the specific case, it
> still looks to me like fib(n) will use O(n) recursion levels if the
> table is initially empty.

I don't get your not getting this 'cause you're a very smart guy.  Are
you under the impression that an attacker will stop because he has to
try a few times?

Cheers,
D
-- 
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666                             Skype: davidfetter

Remember to vote!


Re: plpgsql by default

From
Tom Lane
Date:
David Fetter <david@fetter.org> writes:
> I don't get your not getting this 'cause you're a very smart guy.  Are
> you under the impression that an attacker will stop because he has to
> try a few times?

No, I'm saying that having access to a PL renders certain classes of
attacks significantly more efficient.  A determined attacker with
unlimited time may not care, but in the real world, security is
relative.  You don't have to make yourself an impenetrable target,
only a harder target than the next IP address --- or at least hard
enough that the attacker's likely to get noticed before he's succeeded.
(And certainly, doing anything compute-intensive via recursive SQL
functions is not the way to go unnoticed.)

In the end it's only one small component of security, but any security
expert will tell you that you take all the layers of security that you
can get.  If you don't need a given bit of functionality, it shouldn't
get installed.
        regards, tom lane


Re: plpgsql by default

From
Mischa Sandberg
Date:
Are there are more possibilities for some bug in the plpgsql engine to allow an 
exploit: actually changing the stack through a buffer overflow, or a bug in an 
intrinsic function, or allowing an injection that crosses some privilege 
boundary, via someone else's EXECUTE?

It's a lot easier to verify the few places where straight SQL can interact with 
the outside world (NOTIFY, COPY, and trojan .so's come to mind). It is harder 
for someone to find an unexpected combined-effect exploit, since there's not 
much you can combine.

Perhaps somebody in the core team has reservations about possible points of 
error to certify in plpgsql: is every possible weird array-overflow case 
covered? Further, can some innocuous side-effects in execution (INOUT 
parameters; function ownership; schema settings) combine to create a hole?
There's just that much more to worry about.

As they say, in theory, theory and practice are the same.
In practice, they differ :0)

I can understand someone being cautious about making guarantees (or even risk 
estimates) about plpgsql versus
the core engine. And so, just like not INITIALLY letting the server listen on 
all TCP sockets, it's modest conservatism to let the default be a bit restricted.

-- 
Engineers think that equations approximate reality.
Physicists think that reality approximates the equations.
Mathematicians never make the connection.


Re: plpgsql by default

From
"Marc G. Fournier"
Date:
On Tue, 11 Apr 2006, Joshua D. Drake wrote:

>
>> Can you guarantee unequivocally that there are absolutely not security 
>> issues in plpgsql?
>
> Can you guarantee unequivocally that there are absolutely not security issues 
> in PostgreSQL?

No, but does that mean we should increase the potential by adding in 
something that not everyone that runs PostgreSQL actually uses?

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664


Re: plpgsql by default

From
David Fetter
Date:
On Tue, Apr 11, 2006 at 05:20:02PM -0400, Tom Lane wrote:
> David Fetter <david@fetter.org> writes:
> > I don't get your not getting this 'cause you're a very smart guy.
> > Are you under the impression that an attacker will stop because he
> > has to try a few times?
> 
> No, I'm saying that having access to a PL renders certain classes of
> attacks significantly more efficient.  A determined attacker with
> unlimited time may not care, but in the real world, security is
> relative.  You don't have to make yourself an impenetrable target,
> only a harder target than the next IP address --- or at least hard
> enough that the attacker's likely to get noticed before he's
> succeeded.  (And certainly, doing anything compute-intensive via
> recursive SQL functions is not the way to go unnoticed.)
> 
> In the end it's only one small component of security, but any
> security expert will tell you that you take all the layers of
> security that you can get.  If you don't need a given bit of
> functionality, it shouldn't get installed.

As others have mentioned, and I will reiterate here:

1.  Anyone who imagines that PL/PgSQL presents a bigger or more
vulnerable attack surface can remove it via DROP LANGUAGE.

2.  Anybody who wants to do harm inside the database can do it to
arbitrary levels of damage in SQL with RULEs, recursive functions,
set-returning functions, etc.

Cheers,
D
-- 
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666                             Skype: davidfetter

Remember to vote!


Re: plpgsql by default

From
Thomas Hallgren
Date:
Tom Lane wrote:
> In the end it's only one small component of security, but any security
> expert will tell you that you take all the layers of security that you
> can get.  If you don't need a given bit of functionality, it shouldn't
> get installed.
> 
I think any security expert would say that if let non trustworthy people get so far as to 
create their own SQL statements, you're in big trouble. Plpgsql or not. I fail to see what 
the real issue is here. Your argument is analog to saying "don't install bash on a Linux 
system by default. People might do bad things with it".

Regards,
Thomas Hallgren



Re: plpgsql by default

From
"Joshua D. Drake"
Date:
> No, but does that mean we should increase the potential by adding in 
> something that not everyone that runs PostgreSQL actually uses?

Using this argument I could say that we don't need primary keys, foreign
keys, views or rules. Especially the latter 3 ;).

Sincerely,

Joshua D. Drake




> 
> ----
> Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
> Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664
> 
-- 
           === The PostgreSQL Company: Command Prompt, Inc. ===     Sales/Support: +1.503.667.4564 || 24x7/Emergency:
+1.800.492.2240    Providing the most comprehensive  PostgreSQL solutions since 1997
http://www.commandprompt.com/






Re: plpgsql by default

From
Andrew - Supernews
Date:
On 2006-04-11, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> David Fetter <david@fetter.org> writes:
>> I don't get your not getting this 'cause you're a very smart guy.  Are
>> you under the impression that an attacker will stop because he has to
>> try a few times?
>
> No, I'm saying that having access to a PL renders certain classes of
> attacks significantly more efficient.

Not significantly, and I'll happily back up that assertion with code
examples. (I've already posted an example brute-force search to illustrate
that.)

> A determined attacker with
> unlimited time may not care, but in the real world, security is
> relative.  You don't have to make yourself an impenetrable target,
> only a harder target than the next IP address --- or at least hard
> enough that the attacker's likely to get noticed before he's succeeded.
> (And certainly, doing anything compute-intensive via recursive SQL
> functions is not the way to go unnoticed.)

Doing something compute-intensive with pl/pgsql functions will be just as
noticable.

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


Re: plpgsql by default

From
Andrew - Supernews
Date:
On 2006-04-11, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> More realistically, though, the theoretical point that you can do
> arbitrary calculations by turning loops into recursive SQL functions is
> mostly just theoretical,

It's not at all theoretical. The very practical problem of trying to write
code that does useful stuff (like generate_series on 7.4 or parsing the
values in pg_trigger.tgargs) without using pl/pgsql is a wonderful
demonstration of just how much you can really do in plain SQL functions
by using appropriate techniques. Sure, it requires some specialised
approaches, but then so does system cracking ...

> and the reason is that you won't be able to
> loop very many times before running out of stack space.  (On my machine
> it looks like you can recurse a trivial SQL function only about 600
> times before hitting the default stack limit.)

600 times is enough for the function to do more computation than could
ever be done in the lifetime of the universe. (Consider: how long would it
take to do the Towers of Hanoi with 600 disks?)

> If you have an exploit
> that involves moderate amounts of calculation within the server --- say,
> brute force password cracking --- the availability of a PL will render
> that exploit actually practical, whereas with only SQL functions to work
> with it won't be.

Tom, when you're engaged in a debate on a topic, it's polite to actually
_read_ what other people are posting.

I've already posted a very straightforward example of code that will happily
loop over 300 million values using a recursion depth of no greater than 7,
and I specifically chose it because it shows how easily large brute-force
searches can be done in plain SQL. The existence of cross joins means that
arbitrarily large loops can be constructed without needing either deep
recursion or large materialized function result sets. In many cases these
methods give you code which is both simpler and faster than the equivalent
in pl/pgsql (why code naive nested loops in pl/pgsql, for example, when the
executor already has that functionality built in?).

Here's your brute-force password crack (try it! should only take an hour or
two) using the simple alpha(n) function example from my other post:

select a||b||c from alpha(3) s1(a), alpha(3) s2(b), alpha(2) s3(c)where md5(a||b||c||'andrew') =
'ff113aee991f0a3519c3d4f97414561a'limit1;
 

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


Re: plpgsql by default

From
Neil Conway
Date:
On Tue, 2006-04-11 at 17:20 -0400, Tom Lane wrote:
> No, I'm saying that having access to a PL renders certain classes of
> attacks significantly more efficient.  A determined attacker with
> unlimited time may not care, but in the real world, security is
> relative.

That's a fair point.

Perhaps a compromise would be to enable pl/pgsql by default, but not
grant the USAGE privilege on it. This would allow superusers to define
pl/pgsql functions without taking any additional steps. Non-superusers
could be given access to pl/pgsql via a simple GRANT -- either for all
users via GRANT TO PUBLIC, or on a more granular basis as desired. This
would lower the barrier to using pl/pgsql by a fairly significant
margin, but not cause any additional security exposure that I can see.

-Neil




Re: plpgsql by default

From
"Marc G. Fournier"
Date:
On Tue, 11 Apr 2006, Joshua D. Drake wrote:

>
>> No, but does that mean we should increase the potential by adding in
>> something that not everyone that runs PostgreSQL actually uses?
>
> Using this argument I could say that we don't need primary keys, foreign
> keys, views or rules. Especially the latter 3 ;).

*slap forehead* *groan*

then again, if we could pull it out and move it into loadable modules ... 
hmmmm ... >:)


----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664


Re: plpgsql by default

From
"Joshua D. Drake"
Date:
On Tue, 2006-04-11 at 19:35 -0300, Marc G. Fournier wrote:
> On Tue, 11 Apr 2006, Joshua D. Drake wrote:
> 
> >
> >> No, but does that mean we should increase the potential by adding in
> >> something that not everyone that runs PostgreSQL actually uses?
> >
> > Using this argument I could say that we don't need primary keys, foreign
> > keys, views or rules. Especially the latter 3 ;).
> 
> *slap forehead* *groan*
> 
> then again, if we could pull it out and move it into loadable modules ... 
> hmmmm ... >:)

Oh goodness. We could declare that we are better then MySQL because our
referential integrity is optional... oh wait...

Joshua D. Drake

> 
> 
> ----
> Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
> Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664
> 
-- 
           === The PostgreSQL Company: Command Prompt, Inc. ===     Sales/Support: +1.503.667.4564 || 24x7/Emergency:
+1.800.492.2240    Providing the most comprehensive  PostgreSQL solutions since 1997
http://www.commandprompt.com/






Re: plpgsql by default

From
"Jim C. Nasby"
Date:
On Tue, Apr 11, 2006 at 03:43:56PM -0700, Joshua D. Drake wrote:
> On Tue, 2006-04-11 at 19:35 -0300, Marc G. Fournier wrote:
> > On Tue, 11 Apr 2006, Joshua D. Drake wrote:
> > 
> > >
> > >> No, but does that mean we should increase the potential by adding in
> > >> something that not everyone that runs PostgreSQL actually uses?
> > >
> > > Using this argument I could say that we don't need primary keys, foreign
> > > keys, views or rules. Especially the latter 3 ;).
> > 
> > *slap forehead* *groan*
> > 
> > then again, if we could pull it out and move it into loadable modules ... 
> > hmmmm ... >:)
> 
> Oh goodness. We could declare that we are better then MySQL because our
> referential integrity is optional... oh wait...

Hey, if our RI was optional but we threw an error when you tried to use
it when it was disabled we *would* be better than MySQL...
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461


Re: plpgsql by default

From
"Joshua D. Drake"
Date:
> That's a fair point.
> 
> Perhaps a compromise would be to enable pl/pgsql by default, but not
> grant the USAGE privilege on it. This would allow superusers to define
> pl/pgsql functions without taking any additional steps. Non-superusers
> could be given access to pl/pgsql via a simple GRANT -- either for all
> users via GRANT TO PUBLIC, or on a more granular basis as desired. This
> would lower the barrier to using pl/pgsql by a fairly significant
> margin, but not cause any additional security exposure that I can see.

That seems reasonable.

Joshua D. Drake


> 
> -Neil
> 
> 
-- 
           === The PostgreSQL Company: Command Prompt, Inc. ===     Sales/Support: +1.503.667.4564 || 24x7/Emergency:
+1.800.492.2240    Providing the most comprehensive  PostgreSQL solutions since 1997
http://www.commandprompt.com/






Re: plpgsql by default

From
Josh Berkus
Date:
Neil,

> Perhaps a compromise would be to enable pl/pgsql by default, but not
> grant the USAGE privilege on it. This would allow superusers to define
> pl/pgsql functions without taking any additional steps. Non-superusers
> could be given access to pl/pgsql via a simple GRANT -- either for all
> users via GRANT TO PUBLIC, or on a more granular basis as desired. This
> would lower the barrier to using pl/pgsql by a fairly significant
> margin, but not cause any additional security exposure that I can see.

Would this support PL/pgSQL based admin functions, though?

-- 
Josh Berkus
Aglio Database Solutions
San Francisco


Re: plpgsql by default

From
Peter Eisentraut
Date:
Am Dienstag, 11. April 2006 23:20 schrieb Tom Lane:
> In the end it's only one small component of security, but any security
> expert will tell you that you take all the layers of security that you
> can get.

I think what the security experts are saying is that you need a thorough 
evaluation of assets, attackers, risks, and countermeasures, and I don't see 
that here.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/


Re: plpgsql by default

From
David Fetter
Date:
On Wed, Apr 12, 2006 at 12:32:52PM +0200, Peter Eisentraut wrote:
> Am Dienstag, 11. April 2006 23:20 schrieb Tom Lane:
> > In the end it's only one small component of security, but any
> > security expert will tell you that you take all the layers of
> > security that you can get.
> 
> I think what the security experts are saying is that you need a
> thorough evaluation of assets, attackers, risks, and
> countermeasures, and I don't see that here.

Exactly.  One security expert you may have heard of, Bruce Schneier,
has laid out a 5-step process, and we haven't gotten to step 1 yet
where the proposal is "turn PL/PgSQL off by default."
               Bruce Schneier's 5-Step Security Evaluation
   1. What assets are you trying to protect?   2. What are the risks to those assets?   3. How well does the security
solutionmitigate those risks?   4. What other risks does the security solution cause?   5. What costs and tradeoffs
doesthe security solution impose?
 

Let's start with step 1 and go forward from there.

Cheers,
D
-- 
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666                             Skype: davidfetter

Remember to vote!


Re: plpgsql by default

From
"Merlin Moncure"
Date:
On 4/11/06, Neil Conway <neilc@samurai.com> wrote:
> On Tue, 2006-04-11 at 17:20 -0400, Tom Lane wrote:
> > No, I'm saying that having access to a PL renders certain classes of
> > attacks significantly more efficient.  A determined attacker with
> > unlimited time may not care, but in the real world, security is
> > relative.
>
> That's a fair point.
>
> Perhaps a compromise would be to enable pl/pgsql by default, but not
> grant the USAGE privilege on it. This would allow superusers to define

+1 (+10 if I could, and I'm doing my best not to pontificate about security)

merlin


Re: plpgsql by default

From
Bruce Momjian
Date:
Marc G. Fournier wrote:
> On Tue, 11 Apr 2006, Joshua D. Drake wrote:
> 
> >
> >> Can you guarantee unequivocally that there are absolutely not security 
> >> issues in plpgsql?
> >
> > Can you guarantee unequivocally that there are absolutely not security issues 
> > in PostgreSQL?
> 
> No, but does that mean we should increase the potential by adding in 
> something that not everyone that runs PostgreSQL actually uses?

Now that we throw a clear suggestion to use createlang for people who
try to create plpgsql functions, the requests to include plpgsql by
default has dropped to almost zero:
test=> create function x() as '' language 'plpgsql';ERROR:  language "plpgsql" does not existHINT:  Use CREATE LANGUAGE
toload the language into the database.
 

--  Bruce Momjian   http://candle.pha.pa.us EnterpriseDB    http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +