Thread: What about CREATE OR REPLACE FUNCTION?

What about CREATE OR REPLACE FUNCTION?

From
Tom Lane
Date:
Did we come to any conclusion about whether to accept Gavin Sherry's
CREATE OR REPLACE FUNCTION patch?
http://fts.postgresql.org/db/mw/msg.html?mid=1035792

AFAIR, the score was that I liked it, Bruce didn't, and no one else
had expressed an opinion.

The patch itself needs a little bit of cleanup I think, but I'm willing
to fix and apply it if there's a consensus that we want the feature.
        regards, tom lane


Re: What about CREATE OR REPLACE FUNCTION?

From
Hannu Krosing
Date:
Tom Lane wrote:
> 
> Did we come to any conclusion about whether to accept Gavin Sherry's
> CREATE OR REPLACE FUNCTION patch?
> http://fts.postgresql.org/db/mw/msg.html?mid=1035792
> 
> AFAIR, the score was that I liked it, Bruce didn't, and no one else
> had expressed an opinion.
> 
> The patch itself needs a little bit of cleanup I think, but I'm willing
> to fix and apply it if there's a consensus that we want the feature.

If it enables us to for example change a trigger function without
redefining 
the trigger itself then surely it is needed.

----------------
Hannu


Re: What about CREATE OR REPLACE FUNCTION?

From
Bruce Momjian
Date:
> Did we come to any conclusion about whether to accept Gavin Sherry's
> CREATE OR REPLACE FUNCTION patch?
> http://fts.postgresql.org/db/mw/msg.html?mid=1035792
> 
> AFAIR, the score was that I liked it, Bruce didn't, and no one else
> had expressed an opinion.

I withdraw my objection.  When I read it, I thought we were going to
have CREATE FUNCTION and REPLACE FUNCTION.  I later realized it is
literally CREATE OR REPLACE FUNCTION.  Looks strange, but there is no
standard way to do this and we usually take the Oracle syntax when the
standard doesn't specify it.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 


Re: What about CREATE OR REPLACE FUNCTION?

From
Stephan Szabo
Date:
On Tue, 2 Oct 2001, Tom Lane wrote:

> Did we come to any conclusion about whether to accept Gavin Sherry's
> CREATE OR REPLACE FUNCTION patch?
> http://fts.postgresql.org/db/mw/msg.html?mid=1035792
> 
> AFAIR, the score was that I liked it, Bruce didn't, and no one else
> had expressed an opinion.

Since you're asking for opinions, 
I think that something of the sort is needed.  As for the naming, as long
as we keep things that are similar to this reasonably consistantly named
in the future (like if we decided to do a rule altering one, etc...) it
should be fine. :)




Re: What about CREATE OR REPLACE FUNCTION?

From
Jean-Michel POURE
Date:
> > Did we come to any conclusion about whether to accept Gavin Sherry's
> > CREATE OR REPLACE FUNCTION patch?
> > http://fts.postgresql.org/db/mw/msg.html?mid=1035792
> > AFAIR, the score was that I liked it, Bruce didn't, and no one else
> > had expressed an opinion.
>
>I withdraw my objection.  When I read it, I thought we were going to
>have CREATE FUNCTION and REPLACE FUNCTION.  I later realized it is
>literally CREATE OR REPLACE FUNCTION.  Looks strange, but there is no
>standard way to do this and we usually take the Oracle syntax when the
>standard doesn't specify it.
>
>Bruce Momjian                        |  http://candle.pha.pa.us
>pgman@candle.pha.pa.us               |  (610) 853-3000
>+  If your life is a hard drive,     |  830 Blythe Avenue
>+  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
>
>TIP 5: Have you checked our extensive FAQ?
>http://www.postgresql.org/users-lounge/docs/faq.html

Hello,

Does CREATE OR REPLACE FUNCTION preserve function OID?
What it the difference with CREATE OR ALTER FUNCTION?

We would like to implement pseudo-editing of functions in pgAdmin II.
Is there any solution which preserves function OID?

Best regards,
Jean-Michel POURE

Re: What about CREATE OR REPLACE FUNCTION?

From
Gavin Sherry
Date:
On Thu, 4 Oct 2001, Jean-Michel POURE wrote:

> Hello,
>
> Does CREATE OR REPLACE FUNCTION preserve function OID?
> What it the difference with CREATE OR ALTER FUNCTION?
>
> We would like to implement pseudo-editing of functions in pgAdmin II.
> Is there any solution which preserves function OID?

Yes. The idea was to preserve the OID.

>
> Best regards,
> Jean-Michel POURE

Gavin


Re: What about CREATE OR REPLACE FUNCTION?

From
Tom Lane
Date:
Jean-Michel POURE <jm.poure@freesurf.fr> writes:
> Does CREATE OR REPLACE FUNCTION preserve function OID?

Yes.  That's the whole point ...

> What it the difference with CREATE OR ALTER FUNCTION?

The former exists (now), the latter doesn't exist.

            regards, tom lane

Re: What about CREATE OR REPLACE FUNCTION?

From
Jean-Michel POURE
Date:
Dear all,

1) CREATE OR REPLACE FUNCTION
In pgAdmin II, we plan to use the CREATE OR REPLACE FUNCTION if the patch
is applied. Do you know if there is any chance it be applied for beta time?
We would very much appreciate this feature...

2) PL/pgSQL default support
It is sometimes tricky for Windows users to install a language remotely on
a Linux box (no access to createlang and/or no knowledge of handlers). So
why not enable PL/pgSQL by default?

Best regards,
Jean-Michel POURE

Re: What about CREATE OR REPLACE FUNCTION?

From
Peter Eisentraut
Date:
Jean-Michel POURE writes:

> It is sometimes tricky for Windows users to install a language remotely on
> a Linux box (no access to createlang and/or no knowledge of handlers).

Why not run createlang on the host that the server runs on?

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Re: What about CREATE OR REPLACE FUNCTION?

From
Tom Lane
Date:
Peter Eisentraut <peter_e@gmx.net> writes:
> Jean-Michel POURE writes:
>> It is sometimes tricky for Windows users to install a language remotely on
>> a Linux box (no access to createlang and/or no knowledge of handlers).

> Why not run createlang on the host that the server runs on?

I wasn't able to get excited about that argument either.

I believe the primary reason why PL languages aren't installed by
default is security considerations: users can easily create denial-of-
service conditions if given access to a PL.  (Example: write infinitely
recursive function, invoke it to cause stack overflow crash in your
backend, which forces database-wide restart, thereby negating other
people's transactions.  Repeat until DBA kicks you off...)

A DBA who does want to give access to PL languages by default can
easily do so by installing them into template1, whence they'll be
automatically duplicated into newly created databases.  Perhaps this
option needs to be documented more prominently.

            regards, tom lane

Re: What about CREATE OR REPLACE FUNCTION?

From
Peter Eisentraut
Date:
Tom Lane writes:

> I believe the primary reason why PL languages aren't installed by
> default is security considerations

Well, that argumentation seems to be analogous to giving someone login
access on a multiuser computer system but not letting him execute, say,
perl because he might write recursive functions with it.  Such setups
exist (perhaps with something else instead of perl and recursive
functions) but they are not the norm and usually fine-tuned by the
administrator.

We have realized time and time again that giving someone access to a
PostgreSQL server is already a security risk.  Any person can easily crash
the server (select cash_out(2) is prominently documented as doing that) or
exhaust time and space resources by writing appropriate queries.
Privilege systems do not guard against that.  Privilege systems are for
guarding against a reasonable user "cheating".

Now, if a procedural language is not safe (at least as safe as the rest of
the system that's accessible to an ordinary user), then it shouldn't be
marked "trusted".  Otherwise, the consequence of this chain of arguments
is that createlang selectively introduces a security whole into your
system.  Of course, we may warn, "Be careful when installing procedural
languages, because ...".  But are users going to be careful?  How do they
know what kind of care to exercise, and just *how* to do that?

No, I don't think this is the ideal situation.  I don't want to press for
changing it right now because I'm not particularly bothered by it, and the
second sentence of the previous paragraph might just be true.  In a future
life, a privilege system should give finer grained control about access to
PLs, but we might want to think about what the default should be.

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Re: What about CREATE OR REPLACE FUNCTION?

From
"Christopher Kings-Lynne"
Date:
I seem to recall that Oracle has all sorts of fancy resource limits that can
be applied to users.  If such resource limits were implemented, then maybe
the DBA could have the power to limit someone to a maximum of 20% cpu and a
few transactions per second or something.

Chris

> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org
> [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Peter Eisentraut
> Sent: Wednesday, 10 October 2001 6:36 AM
> To: Tom Lane
> Cc: Jean-Michel POURE; pgsql-hackers@postgresql.org; Bruce Momjian;
> pgadmin-hackers@postgresql.org
> Subject: Re: [HACKERS] What about CREATE OR REPLACE FUNCTION?
>
>
> Tom Lane writes:
>
> > I believe the primary reason why PL languages aren't installed by
> > default is security considerations
>
> Well, that argumentation seems to be analogous to giving someone login
> access on a multiuser computer system but not letting him execute, say,
> perl because he might write recursive functions with it.  Such setups
> exist (perhaps with something else instead of perl and recursive
> functions) but they are not the norm and usually fine-tuned by the
> administrator.
>
> We have realized time and time again that giving someone access to a
> PostgreSQL server is already a security risk.  Any person can easily crash
> the server (select cash_out(2) is prominently documented as doing that) or
> exhaust time and space resources by writing appropriate queries.
> Privilege systems do not guard against that.  Privilege systems are for
> guarding against a reasonable user "cheating".
>
> Now, if a procedural language is not safe (at least as safe as the rest of
> the system that's accessible to an ordinary user), then it shouldn't be
> marked "trusted".  Otherwise, the consequence of this chain of arguments
> is that createlang selectively introduces a security whole into your
> system.  Of course, we may warn, "Be careful when installing procedural
> languages, because ...".  But are users going to be careful?  How do they
> know what kind of care to exercise, and just *how* to do that?
>
> No, I don't think this is the ideal situation.  I don't want to press for
> changing it right now because I'm not particularly bothered by it, and the
> second sentence of the previous paragraph might just be true.  In a future
> life, a privilege system should give finer grained control about access to
> PLs, but we might want to think about what the default should be.
>
> --
> Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>


Re: What about CREATE OR REPLACE FUNCTION?

From
Mike Mascari
Date:
Christopher Kings-Lynne wrote:
>
> I seem to recall that Oracle has all sorts of fancy resource limits that can
> be applied to users.  If such resource limits were implemented, then maybe
> the DBA could have the power to limit someone to a maximum of 20% cpu and a
> few transactions per second or something.
>
> Chris

I was hoping that after completing the current project I'm working
on I might be able to contribute this feature. Oracle calls them
PROFILEs which are a set of resource limits associated with a user.
They can limit:

No. of simultaneous connections
No. of blocks read per query
No. of blocks read per connection
CPU time per query
CPU time per connection
Idle time

as well as a few more esoteric others. I haven't looked at the new
system resource reporting system that Jan wrote, but I suspect some
of the statistics he gathers might already be available. Limiting
simultaneous connections by a user might take a little effort.
Limiting idle time might as well. Both have been a requested feature
in the past, but have pitfalls associated with them. But right now
denial of service for a user with database access is easy: soak up
all available connections. Like Jan's resource statistics collector,
Oracle's profiles must be enabled in the initSID.ora configuration
file since it takes a few cycles to actually account for user
activity.

Mike Mascari
mascarm@mascari.com

> > Tom Lane writes:
> >
> > > I believe the primary reason why PL languages aren't installed by
> > > default is security considerations
> >
> > Well, that argumentation seems to be analogous to giving someone login
> > access on a multiuser computer system but not letting him execute, say,
> > perl because he might write recursive functions with it.  Such setups
> > exist (perhaps with something else instead of perl and recursive
> > functions) but they are not the norm and usually fine-tuned by the
> > administrator.
...
> >
> > Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

Re: What about CREATE OR REPLACE FUNCTION?

From
Gavin Sherry
Date:
On Mon, 8 Oct 2001, Jean-Michel POURE wrote:

> Dear all,

[snip]

> 2) PL/pgSQL default support
> It is sometimes tricky for Windows users to install a language remotely on
> a Linux box (no access to createlang and/or no knowledge of handlers). So
> why not enable PL/pgSQL by default?

Could it not just be a feature of pgadmin to allow users to enable
plpgsql? Enabling the language is, after all, just SQL.

As for having it by default, this takes some level of control out of the
hands of the administrator. What if you do not want to allow your users to
create plpgsql functions?

Gavin


Re: What about CREATE OR REPLACE FUNCTION?

From
Hannu Krosing
Date:
Gavin Sherry wrote:
>
> What if you do not want to allow your users to create plpgsql functions?

This should be part of access control anyway - what if you do not want
to
allow *some* of your users to create plpgsql functions?

--------------
Hannu