Permissions on aggregate component functions

I just noticed that there is no permission check anywhere in CREATE
AGGREGATE concerning the aggregate's transition and final functions.
This means anyone can trivially bypass the function EXECUTE permission
check: just make an aggregate function to call it for you. (Now, this
works only for functions whose signature fits what an aggregate
expects, but for most one- and two-argument functions you can do it.)

Clearly this is a must-fix issue, but I'm wondering exactly where the
check should be enforced. Is it sufficient to check at the time of
CREATE AGGREGATE that the creator has appropriate rights, or do we need
to do it every time the aggregate is used?

regards, tom lane

On Thu, Jan 27, 2005 at 15:27:54 -0500,
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I just noticed that there is no permission check anywhere in CREATE
> AGGREGATE concerning the aggregate's transition and final functions.
> This means anyone can trivially bypass the function EXECUTE permission
> check: just make an aggregate function to call it for you. (Now, this
> works only for functions whose signature fits what an aggregate
> expects, but for most one- and two-argument functions you can do it.)
>
> Clearly this is a must-fix issue, but I'm wondering exactly where the
> check should be enforced. Is it sufficient to check at the time of
> CREATE AGGREGATE that the creator has appropriate rights, or do we need
> to do it every time the aggregate is used?

I would think both would be best. If you don't check at runtime the function
owner can't easily revoke access (dropping the function might be a pain
if it is used in lots of places). It is nice to check at creation so as
to give immediate feedback if there is a problem.

On Thu, 2005-01-27 at 15:27 -0500, Tom Lane wrote:
> I just noticed that there is no permission check anywhere in CREATE
> AGGREGATE concerning the aggregate's transition and final functions.
> This means anyone can trivially bypass the function EXECUTE permission
> check: just make an aggregate function to call it for you. (Now, this
> works only for functions whose signature fits what an aggregate
> expects, but for most one- and two-argument functions you can do it.)
>
> Clearly this is a must-fix issue, but I'm wondering exactly where the
> check should be enforced. Is it sufficient to check at the time of
> CREATE AGGREGATE that the creator has appropriate rights, or do we need
> to do it every time the aggregate is used?

Well spotted.

Check should be once for each SQL statement in which the function is
attempted to be used. Otherwise, an administrator might revoke EXECUTE
privilege on a function that was used as part of an AGGREGATE, then
discover that the user could still execute it in the way you suggest.

--
Best Regards, Simon Riggs

Simon Riggs wrote:
>>
>>Clearly this is a must-fix issue, but I'm wondering exactly where the
>>check should be enforced. Is it sufficient to check at the time of
>>CREATE AGGREGATE that the creator has appropriate rights, or do we need
>>to do it every time the aggregate is used?
>
>
> Well spotted.
>
> Check should be once for each SQL statement in which the function is
> attempted to be used. Otherwise, an administrator might revoke EXECUTE
> privilege on a function that was used as part of an AGGREGATE, then
> discover that the user could still execute it in the way you suggest.

Or some sort of CASCADE should be required.

--
Richard Huxton
Archonet Ltd