Re: Security leak with trigger functions?

Peter Eisentraut wrote:
> Tom Lane wrote:
>> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>>> Tom Lane wrote:
>>>> The question in my mind is what privilege to check and when.
>>>
>>> By extrapolation of the SQL standard, I'd say we'd need to check
>>> the EXECUTE privilege of the function at run time.
>>
>> Certainly EXECUTE privilege is what to check, but whose privilege?
>
> PostgreSQL only allows a trigger action of "call this function", so in

> the SQL standard context that would mean we'd need to check the
EXECUTE
> privilege of the owner of the trigger. The trick is figuring out who
> the owner is. If it's the owner of the table, then TRIGGER privilege
> is effectively total control over the owner of the table. If it's
> whoever created the trigger, it might be useful, but I don't see how
> that is compatible with the intent of the SQL standard.

Looking at pg_trigger I have the impression that there is no such thing
as an 'owner of a trigger', and consequently the owner of the trigger
would automatically be the table owner.

I understand the reservations about the TRIGGER privilege, but I think
that it is obvious anyway that anybody who can add a trigger can
basically do everything with the table.

When adding a trigger, I would check if both the table owner and
the user who adds the trigger have EXECUTE privilege on the function.
That doesn't seem too restrictive to me.

For trigger execution, I see two options:
1) Check for EXECUTE privilege of the table owner at statement begin
time, as Tom Lane suggested. We cannot be sure if the trigger would
actually be executed, right? Should there be an error message even
when the trigger is not fired? Or should the trigger be silently
disabled?
2) Whenever EXECUTE on a function is revoked, disable triggers on all
tables whose owners have now no longer execute privilege.
This should probably not be silent and require something like a
CASCADE option for REVOKE...
Also, there'd have to be an update whenever table ownership is
changed...
Seems quite difficult, but would save checking at runtime.

Yours,
Laurenz Albe