Re: [HACKERS] MERGE SQL Statement for PG11

On 24 March 2018 at 12:27, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Sat, Mar 24, 2018 at 8:16 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Thu, Mar 22, 2018 at 7:13 PM, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
>>> While I think this this particular HINT buglet is pretty harmless, I
>>> continue to be concerned about the unintended consequences of having
>>> multiple RTEs for MERGE's target table. Each RTE comes from a
>>> different lookup path -- the first one goes through setTargetTable()'s
>>> parserOpenTable() + addRangeTableEntryForRelation() calls. The second
>>> one goes through transformFromClauseItem(), for the join, which
>>> ultimately ends up calling transformTableEntry()/addRangeTableEntry().
>>> Consider commit 5f173040, which fixed a privilege escalation security
>>> bug around multiple name lookup. Could the approach taken by MERGE
>>> here introduce a similar security issue?
>>
>> Yeah, that seems really bad. I don't think there is a huge problem
>> with having multiple RTEs; for example, we very commonly end up with
>> both rte->inh and !rte->inh RTEs for the same table, and that is OK.
>> However, generating those RTEs by doing multiple name lookups for the
>> same table is a big problem. Imagine, for example, that a user has a
>> search_path of a, b and that there is a table b.foo. The user does a
>> merge on foo. Between the first name lookup and the second, somebody
>> creates a.foo. Now, potentially, half of the MERGE statement is going
>> to be running against b.foo and the other half against a.foo. I don't
>> know whether that will crash or bomb out with a strange error or just
>> make some unexpected modification to one of those tables, but the
>> behavior, even if not insecure, will certainly be wrong.
>
> If it's possible to identify the two OIDs that are supposed to match
> and cross-check that the OIDs are the same, then we could just bomb
> out with an error if they aren't.

Since we now have MVCC catalog scans, all the name lookups are
performed using the same snapshot so in the above scenario the newly
created object would be invisible to the second name lookup.

So I don't see anyway for the ERROR to occur and hence no need for a
cross check, for UPDATE or MERGE.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services