postgis_restore.pl does not handle recent pg_dump public schema change

[mbanck]

If I create an empty postgis database, dump it and try to restore it again with postgis_restore.pl, this fails:

postgres@localhost:~$ createdb postgis
postgres@localhost:~$ echo "CREATE EXTENSION postgis;" | psql postgis
CREATE EXTENSION
postgres@localhost:~$ pg_dump --version
pg_dump (PostgreSQL) 10.14 (Debian 10.14-1.pgdg100+1)
postgres@localhost:~$ pg_dump -Fc postgis > postgis.dmp
postgres@localhost:~$ /usr/share/postgresql/10/contrib/postgis-3.0/postgis_restore.pl postgis.dmp | psql postgis
Converting postgis.dmp to ASCII on stdout...
  Reading list of functions to ignore...
  Writing manifest of things to read from dump file...
  Writing ASCII to stdout...
Done.
ALTER TABLE
ALTER TABLE
SELECT 8500
DELETE 8500
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
COPY 0
ERROR:  relation "spatial_ref_sys" does not exist
LINE 1: UPDATE spatial_ref_sys o set auth_name = n.auth_name, auth_s...
               ^
ERROR:  relation "spatial_ref_sys" does not exist
LINE 1: INSERT INTO spatial_ref_sys SELECT * FROM _pgis_restore_spat...
                    ^
DROP TABLE
ERROR:  relation "spatial_ref_sys" does not exist
ERROR:  relation "spatial_ref_sys" does not exist
ERROR:  relation "spatial_ref_sys" does not exist

postgres@localhost:~$ 

If I downgrade pg_dump to 10.2 (the last minor release that did not have the public schema handling change), I don't get an error:

postgres@localhost:~$ dropdb postgis
postgres@localhost:~$ createdb postgis
postgres@localhost:~$ echo "CREATE EXTENSION postgis;" | psql postgis
CREATE EXTENSION
postgres@localhost:~$ pg_dump --version
pg_dump (PostgreSQL) 10.2 (Debian 10.2-1.pgdg100+1)
postgres@localhost:~$ pg_dump -Fc postgis > postgis.dmp.2
postgres@localhost:~$ /usr/share/postgresql/10/contrib/postgis-3.0/postgis_restore.pl postgis.dmp.2 | psql postgis
Converting postgis.dmp.2 to ASCII on stdout...
  Reading list of functions to ignore...
  Writing manifest of things to read from dump file...
  Writing ASCII to stdout...
ALTER TABLE
ALTER TABLE
Done.
SELECT 8500
DELETE 8500
UPDATE 0
INSERT 0 8500
DROP TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
postgres@localhost:~$

[mbanck]

​https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058%3A_Protect_Your_Search_Path this is the search_path change I was talking about

[strk]

I guess a fix could be passing postgis installation schema to postgis_restore.pl ?

[strk]

Note: you can use -s to pass postgis schema to postgis_restore.pl

[robe]

strk do something with this one or push it to next milestone

[strk]

I confirm passing the -s switch fixes this problem. -s public works fine. Maybe we can make it the default ?

[Sandro Santilli <strk@…>]

In 6809237/git:

Set postgis installation schema by default to the schema found in dump

Closes #4782

[strk]

Should this be considered for backporting ?

[robe]

It's a small enough change I'd backport it to at least 3.1.4 and possibly 3.0.4

[Sandro Santilli <strk@…>]

In 1a86e86/git:

Set postgis installation schema by default to the schema found in dump

References #4782 in 3.1 branch (3.1.4dev)