Re: BUG #17456: pg_dump creates dump that does not fully respect operator schema location - Mailing list pgsql-bugs

From Andrew Grossman
Subject Re: BUG #17456: pg_dump creates dump that does not fully respect operator schema location
Date
Msg-id CABaY9E4SGONWCvqUnU6rVFCmTQb5NiJoDg5wqZXzFRK4d8XCPw@mail.gmail.com
Whole thread Raw
In response to Re: BUG #17456: pg_dump creates dump that does not fully respect operator schema location  ("David G. Johnston" <david.g.johnston@gmail.com>)
List pgsql-bugs
Ahh, that makes sense. Thank you for the thorough explanation. I wonder if there's a good way to warn on this during the dump creation.

On Tue, Apr 5, 2022 at 12:05 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tue, Apr 5, 2022 at 8:31 AM PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:

Bug reference:      17456
Logged by:          Andrew Grossman
Email address:      agrossman@gmail.com
PostgreSQL version: 14.2
Operating system:   MacOS 12.3
Description:       

I have a case where an AFTER ROW trigger has a condition comparing two ltree
fields. The ltree extension is installed in a different schema than the
triggered table is. Upon restoration, the following error is encountered:

psql:/tmp/bugreport.sql:93: ERROR:  42883: operator does not exist:
util.ltree = util.ltree
LINE 1: ...N my_schema.my_table FOR EACH ROW WHEN ((new.path IS DISTINC...
                                                             ^
HINT:  No operator matches the given name and argument types. You might need
to add explicit type casts.
LOCATION:  op_error, parse_oper.c:731

The following sql will reproduce this case:

SET SEARCH_PATH=my_schema,util;
    FOR EACH ROW WHEN ((new.path IS DISTINCT FROM old.path))


Yes, this is a known limitation extending from our securing the search_path in order to fix a CVE.

Casting to text works since it will use the system pg_catalog.=(text,text) operator.

The other option is to avoid the indirection caused by IS DISTINCT FROM and write out the equivalent expression verbosely:

not(new.path operator("util"."=") old.path) OR (new.path IS NULL AND old.path IS NULL)

Another option is to add a SET search_path clause on the CREATE FUNCTION and move the WHEN check inside the function.  When the trigger invokes the function the attached search_path will then be put into force and the resolution of =(lpath,lpath) will find the one in the util schema.  Unfortunately, the create trigger command doesn't have a similar capability to attach a local setting value to it.

David J.

pgsql-bugs by date:

Previous
From: "David G. Johnston"
Date:
Subject: Re: BUG #17456: pg_dump creates dump that does not fully respect operator schema location
Next
From: "wangsh.fnst@fujitsu.com"
Date:
Subject: "unexpected duplicate for tablespace" problem in logical replication