Re: Security leak with trigger functions? - Mailing list pgsql-hackers

On 12/14/2006 01:17 PM, Peter Eisentraut wrote:
> Tom Lane wrote:
>> Peter Eisentraut <peter_e@gmx.net> writes:
>>> 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?
> 
> ...
> ("The authorization identifier of the owner of the schema that includes 
> the trigger descriptor of TR is pushed onto the authorization stack.")
> 
> 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.

Hmm, it's been not quite a dozen years, have there been later threads
that followed up on this discussion?

Is it still accurate to describe the status quo as:

- Triggers can be created only by a role with TRIGGER on the
  relation and EXECUTE on the function, checked at trigger creation
  time.

- Once created, triggers are executed with no local userid change (so,
  just as whatever role issued whatever DML fired the trigger), and
  without a check for EXECUTE on the function at that time, except:

- Internal trigger functions implementing RI checks will execute their
  queries as "the owner of whichever table the query touches (the
  referenced table for verification of FK inserts, the referencing
  table when cascading a PK change)."
  https://www.postgresql.org/message-id/5761.1509733215%40sss.pgh.pa.us

It seems to me that the 2006 conversation was complicated by the
fact that PostgreSQL triggers don't have owners of their own, and
don't have schema-qualified names, as triggers in the SQL standard do.
So the conversation was going in the direction of running as the owner
of the relation, but for the obvious consequence that it would turn
TRIGGER permission into an equivalent of the relation owner's identity.

If the idea of changing the current behavior at all could be thinkable,
could one think about changing it more along SQL standard lines, by
letting a trigger itself have a schema, and executing as the owner
of the schema?

This seems to me not too different from just letting a trigger have
an owner, and executing as that, which could seem less roundabout.
OTOH, going through the schema has the advantage of following the
standard, and may allow a little more flexibility in setting up
delegated authority; a schema with a certain owner can be created,
CREATE(*) on that schema can be selectively granted, enabling grantees
to create trigger declarations in that schema, applying to any
relations they have TRIGGER permission for. Either way, TRIGGER
permission is left with something useful to mean, unlike the
execute-as-relation-owner case, where the meaning of TRIGGER sort of
evaporates.

(*) a different permission might be better here, as many existing
schemas probably grant CREATE to roles today without intending that
they be able to create triggers that execute as the schema owner.
TRIGGER might work, having no existing meaning on a schema.

I think the execute-as-relation-owner idea might not easily fit
situations where different roles with cross-cutting concerns all have
their own reasons to put triggers on a given relation, but should not
otherwise have the same permissions or run with the same id. I think
such situations could easily be accommodated in a model where triggers
have schemas and execute as the schema owner.

I could imagine a graceful migration plan:

1. Give pg_trigger a tgnamespace column, but allow it to be null
   or InvalidOid, meaning a trigger that should have the current
   behavior, executed with no local userid change. Conversely, a
   trigger with a valid tgnamespace gets executed as the owner
   of that schema.

2. pg_upgrade preserves existing triggers as triggers with no
   namespace. The grammar is altered to allow a schema when creating
   a trigger (and to allow ALTER TRIGGER SET SCHEMA).

3. Permission checks for schemaless triggers stay as they are now:
   EXECUTE on the function checked at for the trigger's creator at
   creation time, but not at run time. For triggers in schemas, the
   schema owner is who must have EXECUTE on the function.

4. It could still be possible to avoid runtime permission checks,
   the existence of EXECUTE for the right role being checked at
   trigger creation time, if REVOKE EXECUTE on a trigger function
   includes a check through pg_depend for triggers whose execute
   right would be lost. Those would be (a) any triggers with schemas
   owned by a role having EXECUTE revoked, and (b) any schema-less
   triggers at all (as there's no telling whose permission might
   have been checked at the time they were created).

The simple check in 4(b) could be implemented on its own, without any
of the rest, as a simple way to plug the "security leak" in the status
quo.

Thoughts?

-Chap