Re: CREATE OR REPLACE FUNCTION vs ownership - Mailing list pgsql-hackers

Robert Haas wrote:
> 2009/10/1 KaiGai Kohei <kaigai@ak.jp.nec.com>:
>> Robert Haas wrote:
>>> On Thu, Oct 1, 2009 at 8:52 PM, Euler Taveira de Oliveira
>>> <euler@timbira.com> wrote:
>>>> David E. Wheeler escreveu:
>>>>> On Oct 1, 2009, at 3:42 PM, Tom Lane wrote:
>>>>>
>>>>>> My inclination is to think that the right behavior for REPLACE FUNCTION
>>>>>> is to keep the old proowner and proacl values, because that's what it
>>>>>> always has done and nobody's complained.  But I suppose a case could
>>>>>> be made that you're completely replacing the function and so you should
>>>>>> replace its ownership/permissions too.  The CREATE FUNCTION reference
>>>>>> page fails to specify either way, which is a documentation bug as well.
>>>>>>
>>>>>> Comments?
>>>>> The latter, I think. If I replace a function, I should be the new owner.
>>>>> To me it makes no sense for someone else to own it.
>>>>>
>>>> Hmm... Using the same logic, if I add a new column should I be the table
>>>> owner? If you're changing the function that is because you have permission.
>>>>
>>>> IMHO the owner should be preserved. In my mind, REPLACE is for changing the
>>>> content and not the properties (name, owner, etc).
>> If so, it seems to me CREATE OR REPLACE is equivalent to ALTER FUNCTION
>> with currently unsupported option. In this case, it is not necessary to
>> check CREATE privilege on the namespace because it does not affect to
>> its name/schema.
> 
> Right - so the subtle point here is that ALTER means something
> different from CREATE OR REPLACE.  "ALTER" means to make a
> modification to something; to change it; to adjust one particular
> property of the object without disturbing the others.  On the other
> hand, "REPLACE" means to get rid of something and replace it with an
> entirely new thing.  I think that is exactly why we have ALTER TABLE
> but CREATE OR REPLACE FUNCTION.
> 
> Now, if we want to have an ALTER FUNCTION that replaces the function
> definition and leaves the owner intact - fine!  But that is not what
> REPLACE means.
> 
>>> I disagree.  I think David has this one right.  I expect the results
>>> of CREATE OR REPLACE to be the same as the result of CREATE would have
>>> been had the object not existed.
>> If so, it seems to me CREATE OR REPLACE is equivalent to a pair of
>> actions: 1) DROP FUNCTION (if exist) and 2) CREATE FUNCTION.
> 
> Except that you don't have to drop and recreate the dependencies, if any.

Indeed, but here is one other issue from the perspective of security.

For example, a superuser can define a new type which has input/output
handler using user defined functions. Its ownership is not limited to
superuser, so it means non-privilege user can replace the type handler
owned by himself later.

If we also rebuild dependencies on the CREATE OR REPLACE FUNCTION,
we can prevent other user implicitly invokes replaced malicaious
function (it may perform as a trojan-horse), because dependency
mechanism abort this peudo DROP FUNCTION.

However, similar issue can be happen on ALTER FUNCTION OWNER TO.
IMO, we need a mechanism to prevent ALTER or REPLACE functions
which are used for other stuff without permission checks to
execute it.

Thanks,
-- 
OSS Platform Development Division, NEC
KaiGai Kohei <kaigai@ak.jp.nec.com>