Thread: cannot restore schema with is not distinct from on hstore since PG9.6.8
Hi,
This is a really simple test case, I think it's an unintended
consequence of CVE-2018-1058:
demo=# create extension hstore;
CREATE EXTENSION
demo=# create table test (a hstore);
CREATE TABLE
demo=# create index idx_test_not_distinct on test(a) where a is not
distinct from '';
CREATE INDEX
Then dump this database with a simple pg_dump and try to restore it in
another database:
9.6.9/0 [marc@marco-portable pg_dump]$ psql -e demo2 -f /tmp/demo_dump
SET statement_timeout = 0;
SET
SET lock_timeout = 0;
SET
SET idle_in_transaction_session_timeout = 0;
SET
SET client_encoding = 'UTF8';
SET
SET standard_conforming_strings = on;
SET
SELECT pg_catalog.set_config('search_path', '', false);
set_config
------------
(1 row)
SET check_function_bodies = false;
SET
SET client_min_messages = warning;
SET
SET row_security = off;
SET
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
CREATE EXTENSION
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
COMMENT
CREATE EXTENSION IF NOT EXISTS hstore WITH SCHEMA public;
CREATE EXTENSION
COMMENT ON EXTENSION hstore IS 'data type for storing sets of (key,
value) pairs';
COMMENT
SET default_tablespace = '';
SET
SET default_with_oids = false;
SET
CREATE TABLE public.test (
a public.hstore
);
CREATE TABLE
ALTER TABLE public.test OWNER TO marc;
ALTER TABLE
COPY public.test (a) FROM stdin;
COPY 0
CREATE INDEX idx_test_not_distinct ON public.test USING btree (a) WHERE
(NOT (a IS DISTINCT FROM ''::public.hstore));
psql:/tmp/demo_bug:73: ERROR: operator does not exist: public.hstore =
public.hstore
LINE 1: ...inct ON public.test USING btree (a) WHERE (NOT (a IS DISTINC...
^
HINT: No operator matches the given name and argument type(s). You
might need to add explicit type casts.
As we are working on the 9.6 branch, it appeared after upgrading to
9.6.8 and 9.6.9 following CVE-2018-1058 I presume. Removing the
set_config fixes it (but removes the security fix...)
Of course, my use case is not this simple example, it's failing on a
trigger WHEN ( old.hstorecol is distinct from new.hstorecol )
Regards
Marc
Attachment
Marc Cousin <cousinmarc@gmail.com> writes:
> This is a really simple test case, I think it's an unintended
> consequence of CVE-2018-1058:
> demo=# create extension hstore;
> CREATE EXTENSION
> demo=# create table test (a hstore);
> CREATE TABLE
> demo=# create index idx_test_not_distinct on test(a) where a is not
> distinct from '';
> CREATE INDEX
> [ whereupon dump/restore fails with ]
> CREATE INDEX idx_test_not_distinct ON public.test USING btree (a) WHERE
> (NOT (a IS DISTINCT FROM ''::public.hstore));
> psql:/tmp/demo_bug:73: ERROR: operator does not exist: public.hstore =
> public.hstore
Yeah, the core of the problem here is that there's no way to
schema-qualify IS [NOT] DISTINCT FROM's choice of underlying operator.
It was possible to ignore that as long as the operator you wanted
was in the search path, but now that we've tightened up pg_dump's
search path settings, we can't play fast and loose anymore.
I think the most practical way to deal with this probably is to change
the parser so that the lookup works by finding a default btree or hash
opclass rather than by looking for "=" by name. We've made similar
changes in the past to get rid of implicit dependencies on operator
names, but those efforts never reached IS [NOT] DISTINCT FROM.
I have a nasty feeling that there are still operator name dependencies
elsewhere, notably in CASE expressions, but haven't researched it yet.
Although this doesn't seem like an outlandish change to make in HEAD,
back-patching it might cause some issues. On the other hand, I don't
see what choice we have. Leaving things as they stand isn't very
workable, and inventing some kind of schema-qualification syntax for
IS [NOT] DISTINCT FROM is surely even worse from a backwards
compatibility standpoint.
Thoughts?
regards, tom lane
Marc Cousin <cousinmarc@gmail.com> writes:
> This is a really simple test case, I think it's an unintended
> consequence of CVE-2018-1058:
> demo=# create extension hstore;
> CREATE EXTENSION
> demo=# create table test (a hstore);
> CREATE TABLE
> demo=# create index idx_test_not_distinct on test(a) where a is not
> distinct from '';
> CREATE INDEX
> [ whereupon dump/restore fails with ]
> CREATE INDEX idx_test_not_distinct ON public.test USING btree (a) WHERE
> (NOT (a IS DISTINCT FROM ''::public.hstore));
> psql:/tmp/demo_bug:73: ERROR: operator does not exist: public.hstore =
> public.hstore
Yeah, the core of the problem here is that there's no way to
schema-qualify IS [NOT] DISTINCT FROM's choice of underlying operator.
It was possible to ignore that as long as the operator you wanted
was in the search path, but now that we've tightened up pg_dump's
search path settings, we can't play fast and loose anymore.
I think the most practical way to deal with this probably is to change
the parser so that the lookup works by finding a default btree or hash
opclass rather than by looking for "=" by name. We've made similar
changes in the past to get rid of implicit dependencies on operator
names, but those efforts never reached IS [NOT] DISTINCT FROM.
I have a nasty feeling that there are still operator name dependencies
elsewhere, notably in CASE expressions, but haven't researched it yet.
Although this doesn't seem like an outlandish change to make in HEAD,
back-patching it might cause some issues. On the other hand, I don't
see what choice we have. Leaving things as they stand isn't very
workable, and inventing some kind of schema-qualification syntax for
IS [NOT] DISTINCT FROM is surely even worse from a backwards
compatibility standpoint.
Thoughts?
regards, tom lane
Re: cannot restore schema with is not distinct from on hstore sincePG 9.6.8
From
Andrew Dunstan
Date:
On 07/09/2018 11:34 AM, Tom Lane wrote: > Marc Cousin <cousinmarc@gmail.com> writes: >> This is a really simple test case, I think it's an unintended >> consequence of CVE-2018-1058: >> demo=# create extension hstore; >> CREATE EXTENSION >> demo=# create table test (a hstore); >> CREATE TABLE >> demo=# create index idx_test_not_distinct on test(a) where a is not >> distinct from ''; >> CREATE INDEX >> [ whereupon dump/restore fails with ] >> CREATE INDEX idx_test_not_distinct ON public.test USING btree (a) WHERE >> (NOT (a IS DISTINCT FROM ''::public.hstore)); >> psql:/tmp/demo_bug:73: ERROR: operator does not exist: public.hstore = >> public.hstore > Yeah, the core of the problem here is that there's no way to > schema-qualify IS [NOT] DISTINCT FROM's choice of underlying operator. > It was possible to ignore that as long as the operator you wanted > was in the search path, but now that we've tightened up pg_dump's > search path settings, we can't play fast and loose anymore. > > I think the most practical way to deal with this probably is to change > the parser so that the lookup works by finding a default btree or hash > opclass rather than by looking for "=" by name. We've made similar > changes in the past to get rid of implicit dependencies on operator > names, but those efforts never reached IS [NOT] DISTINCT FROM. > > I have a nasty feeling that there are still operator name dependencies > elsewhere, notably in CASE expressions, but haven't researched it yet. > > Although this doesn't seem like an outlandish change to make in HEAD, > back-patching it might cause some issues. On the other hand, I don't > see what choice we have. Leaving things as they stand isn't very > workable, and inventing some kind of schema-qualification syntax for > IS [NOT] DISTINCT FROM is surely even worse from a backwards > compatibility standpoint. I agree with your approach, including backpatching. I guess we'll have to try to think of some scenario that backpatchingwould break. Maybe to minimize any effect it should fall back on a default opclass if the search for "=" fails?Dunno how practical that is. cheers andrew -- Andrew Dunstan https://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: cannot restore schema with is not distinct from on hstore sincePG 9.6.8
From
Andrew Dunstan
Date:
On 07/09/2018 11:34 AM, Tom Lane wrote: > Marc Cousin <cousinmarc@gmail.com> writes: >> This is a really simple test case, I think it's an unintended >> consequence of CVE-2018-1058: >> demo=# create extension hstore; >> CREATE EXTENSION >> demo=# create table test (a hstore); >> CREATE TABLE >> demo=# create index idx_test_not_distinct on test(a) where a is not >> distinct from ''; >> CREATE INDEX >> [ whereupon dump/restore fails with ] >> CREATE INDEX idx_test_not_distinct ON public.test USING btree (a) WHERE >> (NOT (a IS DISTINCT FROM ''::public.hstore)); >> psql:/tmp/demo_bug:73: ERROR: operator does not exist: public.hstore = >> public.hstore > Yeah, the core of the problem here is that there's no way to > schema-qualify IS [NOT] DISTINCT FROM's choice of underlying operator. > It was possible to ignore that as long as the operator you wanted > was in the search path, but now that we've tightened up pg_dump's > search path settings, we can't play fast and loose anymore. > > I think the most practical way to deal with this probably is to change > the parser so that the lookup works by finding a default btree or hash > opclass rather than by looking for "=" by name. We've made similar > changes in the past to get rid of implicit dependencies on operator > names, but those efforts never reached IS [NOT] DISTINCT FROM. > > I have a nasty feeling that there are still operator name dependencies > elsewhere, notably in CASE expressions, but haven't researched it yet. > > Although this doesn't seem like an outlandish change to make in HEAD, > back-patching it might cause some issues. On the other hand, I don't > see what choice we have. Leaving things as they stand isn't very > workable, and inventing some kind of schema-qualification syntax for > IS [NOT] DISTINCT FROM is surely even worse from a backwards > compatibility standpoint. I agree with your approach, including backpatching. I guess we'll have to try to think of some scenario that backpatchingwould break. Maybe to minimize any effect it should fall back on a default opclass if the search for "=" fails?Dunno how practical that is. cheers andrew -- Andrew Dunstan https://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services