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.

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

From thomas at tada.se Mon Mar 27 14:15:38 2006
From: thomas at tada.se (Thomas Hallgren)
Date: Mon, 27 Mar 2006 16:15:38 +0200
Subject: [Pljava-dev] [HACKERS] Shared memory
In-Reply-To: <A760BE24-97E1-4B3D-9F5F-7D5F4D2D1E62@sptci.com>
References: <4423CF32.3060406@tada.se>
<20060324125851.GC8718@svana.org> <4427A8F1.3020009@tada.se>
<20060327104506.GB30791@svana.org> <4427DF17.7040700@tada.se>
<A760BE24-97E1-4B3D-9F5F-7D5F4D2D1E62@sptci.com>
Message-ID: <4427F38A.6090408@tada.se>

Rakesh Vidyadharan wrote:

I agree with your view of connection pools. The only time I wished
that PL/Java did not have such a "heavy" initialisation requirement
was when I needed to use a Java stored procedure from C (it was too
trivial to explore using a connection pool). I ended up writing an
equivalent stored procedure in pgsql. This may be a major issue for
some people, since one of the main benefits of using stored procedures
is that your database logic becomes available to multiple technologies.

Good point.

In my view the main benefit that may be derived form using a shared
JVM is that you may be able to support more connections. Maybe you
can just wait for the multi-tasking JVM to arrive on the scene. I am
not sure how far the Java committee is on that, but there has been
talk of developing one for quite a while now.

I think you mean the JSR121, where many JVM's can share resources by
running as "Java Isolates" in a MVM?. Unfortunately that will not help
much. A PostgreSQL backend process will still need to use ipc in order
to talk to the MVM process. What would help is if multiple JVM's, each
running as a separate process, could share a lot of resources but I
haven't seen any viable effort in that direction. I doubt it's doable
without major parts of the JVM being built into the kernel.

Regards,
Thomas Hallgren

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

Regards, Dave

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. +

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/

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. +

-----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, and while 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/

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

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

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

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/

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

"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

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!

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

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

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

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/

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/

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/