Thread: pg_dump's restore gives "operator does not exist: public.iprange = public.iprange" but copy paste works

Hello,

I originally posted this as a StackOverflow question[1], but one of the responses there suggested I may get further
helphere on pgsql-general. The question is perhaps a little more fleshed out than what follows, but I hope this email
isself-contained.
 

I have a large database whose schema I have recently changed. Since then I cannot restore its dump using the normal
`psql-1 -f ...` route. Running `psql -1 -f ...` gives the error shown in the subject,  yet pasting the failing view in
theterminal afterwards is successful. 
 

To cut a long story short, I pruned the schema to ~100 lines but something that can reproduce the error shown in the
subject.The testcase is appended to this email for completeness[2]. No lines have been changed; I have only deleted
linesto whittle the production dump down to something that is copy-paste friendly.
 

One helpful person there was able to find the line created by pg_dump which causes the error 

```
SELECT pg_catalog.set_config('search_path', '', false);
```
However, I do not know the knock-on effects of removing this line, and it doesn't help that I will have to edit the
dumpsevery time I wish to restore them.
 

Is there something I can do to pg_dump which can suppress this error in the general case? Is this a bug or have I done
somethingwrong myself? The relevant details of the Debian stretch host:
 

ii  postgresql-9.6-ip4r                   2.4-1.pgdg80+1  
ii  postgresql-client-9.6                 9.6.22-0+deb9u1 
ii  postgresql-contrib-9.6                9.6.22-0+deb9u1

Many thanks for your time on this

Christopher


--------------------------------------------------------------------- 

[1]
https://stackoverflow.com/questions/68282538/pg-dumps-restore-gives-operator-does-not-exist-public-iprange-public-iprang?noredirect=1#comment120721406_68282538

[2]
```
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

CREATE SCHEMA archive;
CREATE SCHEMA auth;
CREATE SCHEMA data;
CREATE SCHEMA minion;
CREATE SCHEMA user_views;


CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
CREATE EXTENSION IF NOT EXISTS ip4r WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public;


CREATE TABLE data.subnet_dhcp_options_updates_log (
    subnet_range public.iprange NOT NULL,
    txid bigint NOT NULL,
    last_update timestamp without time zone NOT NULL
);

CREATE FUNCTION public.subnet_dhcp_option_last_update(arg_subnet_range public.iprange) RETURNS timestamp without time
zone
    LANGUAGE sql STABLE
    AS $$
  select last_update from data.subnet_dhcp_options_updates_log where subnet_range = arg_subnet_range;
$$;


CREATE TABLE data.subnets (
    id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
    subnet_range public.iprange NOT NULL,
    comment text DEFAULT ''::text NOT NULL,
    created_in_transaction bigint DEFAULT txid_current() NOT NULL,
    deleted_in_transaction bigint,
    subnet_name text DEFAULT ''::text NOT NULL,
    is_visible boolean DEFAULT true NOT NULL
);

CREATE VIEW archive.subnets AS
 SELECT subnets.id,
    subnets.subnet_range,
    subnets.comment,
    subnets.created_in_transaction,
    subnets.deleted_in_transaction,
    subnets.subnet_name,
    subnets.is_visible,
    public.family(subnets.subnet_range) AS ip_version
   FROM data.subnets;



CREATE TABLE data.subnet_dhcp_options (
    id uuid NOT NULL,
    kea_subnet_id integer NOT NULL,
    subnet_range public.iprange NOT NULL,
    subnet_pools public.iprange[] DEFAULT '{}'::public.iprange[] NOT NULL,
    dhcp_options jsonb DEFAULT '{}'::jsonb NOT NULL,
    unknown_client_leases boolean NOT NULL,
    comment text DEFAULT ''::text NOT NULL,
    created_in_transaction bigint DEFAULT txid_current() NOT NULL,
    deleted_in_transaction bigint
);

CREATE VIEW public.subnets AS
 SELECT subnets.id,
    subnets.subnet_range,
    subnets.comment,
    subnets.created_in_transaction,
    subnets.deleted_in_transaction,
    subnets.subnet_name,
    subnets.is_visible,
    subnets.ip_version
   FROM archive.subnets
  WHERE (subnets.deleted_in_transaction IS NULL);

CREATE VIEW archive.subnet_dhcp_options AS
 SELECT sdo.id,
    sdo.subnet_range,
    (sdo.subnet_pools)::text[] AS subnet_pools,
    sdo.dhcp_options,
    sdo.unknown_client_leases,
    sdo.kea_subnet_id,
    public.family(sdo.subnet_range) AS ip_version,
    sdo.comment,
    sdo.created_in_transaction,
    sdo.deleted_in_transaction,
    array_to_string((sdo.subnet_pools)::text[], '
'::text) AS subnet_pools_as_string,
    public.subnet_dhcp_option_last_update(sdo.subnet_range) AS last_update,
    s.id AS subnet_id
   FROM (data.subnet_dhcp_options sdo
     JOIN public.subnets s USING (subnet_range));
```

"Christopher Causer" <chy.causer@gmail.com> writes:
> I have a large database whose schema I have recently changed. Since then I cannot restore its dump using the normal
`psql-1 -f ...` route. Running `psql -1 -f ...` gives the error shown in the subject,  yet pasting the failing view in
theterminal afterwards is successful.  

The core problem here is that this view definition:

CREATE VIEW archive.subnet_dhcp_options AS
...
     JOIN public.subnets s USING (subnet_range));

is not safe against varying settings of the search_path.  It needs the
"iprange = iprange" operator, which you've placed in the public schema;
so if that's not in the search_path when the view is defined, you lose.

Ordinarily pg_dump would account for this by writing out a
schema-qualified operator name, ie OPERATOR(public.=).  But the
JOIN USING syntax involves no explicit operator name so there's
no way to do that.

This isn't the only SQL syntax that has implicit operators; CASE is
another example, and I think there are more.  We've discussed inventing
non-SQL-spec syntax that can cope with explicitly writing a qualified
operator name in all these cases, but it looks like a messy project
with an ugly final result :-(, so nothing's been done yet.

I don't think there's any good solution right now.  You could perhaps
put the iprange extension in pg_catalog not public, so that it's always
in the search path.  I don't recall right now if that has any downsides.

            regards, tom lane

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> I'll admit these have been infrequent since resolving CVE 2018-1058, but I
> still disagree with the decision to not give the DBA an option on whether
> to leave public in their search_path during a pg_dump and pg_restore.

Yeah, I was never for that decision either.  Anybody who's sufficiently
hot about it could try submitting a patch and see what happens.

I'm not quite sure how the option should work, but maybe call it
--use-unsafe-path and define it as adopting the same search_path
setting seen at dump time?  Or maybe better to provide a restore-time
option saying "use this search_path"?  It needs some thought, not
just quick-n-dirty code.

            regards, tom lane

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> IIUC, functions can force a search_path even during dump/restore by being
> created with one specified as part of the create function command.  Since
> the issue is with stored objects moreso than queries generically is it
> feasible to approach the view solution by adding a "SET" clause to CREATE
> VIEW as well?

Don't see the point.  The issue here is what search_path applies at
view definition time, and you have syntax to control that today:

    SET search_path = whatever;
    CREATE VIEW ... ;
    RESET search_path;

So the problem is not lack of a server feature, it's persuading pg_dump
to emit something other than what it does now.

            regards, tom lane