Re: insert/update/delete returning and rules - Mailing list pgsql-hackers

I wrote:
> After some further thought, I think we could make it work if we treat
> XXX RETURNING as a distinct rule event type and make the following
> restrictions (which are exactly analogous to the restrictions for ON
> SELECT rules) for ON XXX RETURNING rules:

After working on this for a bit, I realized that there's a serious,
probably fatal objection to this approach: it's arguably a security
hole.  Suppose that you have a regular table on which you've defined
rules that you consider security-critical --- maybe an ON INSERT DO ALSO
that logs the action in a log table, for example.  Now you migrate your
database to 8.2.  If we treat INSERT RETURNING as a separate rule event
type, then any DB user can bypass your security-critical rules simply
by using INSERT RETURNING instead of INSERT.  Yeah, you can fix that by
adding more rules, but it's not comfy-making to think that DB schemas
will be insecure as soon as they are ported to 8.2 until they are fixed.
In any case this thought blows out of the water the assumption that we
can disallow auxiliary rules for RETURNING events --- on a plain table,
that's an important feature to have.

So here's my Plan B: the set of rule event types stays the same,
and we give the rewriter a little bit of smarts about how to handle
RETURNING, while still putting the burden on the rule author to say
exactly what to return.  Specifically, I suggest:

* A rule can have a command with a RETURNING clause only if it's an
unconditional DO INSTEAD rule, and there can be only one RETURNING
clause among a table's rules for a particular event type.  The clause
must match the datatypes of the relation's columns.

* When rewriting a query that does not have a RETURNING clause, the
rewriter simply throws away any RETURNING clause in the rule.

* When rewriting a query that does have a RETURNING clause, the rewriter
rewrites the rule's RETURNING clause to generate the data required by
the query RETURNING clause (same transformation as we do on a view
SELECT targetlist).  If there's no RETURNING in the rules, throw an
error.

With this approach, you still have to update your rules if you want
to support RETURNING on your views --- but if you don't update them,
you don't have a security hole.  Basically the standard setup for an
updatable view would use"ON INSERT DO INSTEAD INSERT INTO ... RETURNING ..."
where today you don't write any RETURNING.

Again, this is something we might want to generalize later, but it
seems to be a reasonable basic capability.

Thoughts?  Have I missed something (again)?
        regards, tom lane