On 3/21/19 8:15 PM, David Wheeler wrote:
Hi,
We’re regularly having an issue when restoring dumps of our databases
like this
[exec] CREATE DATABASE "testRestore";
[exec] pg_restore: [archiver (db)] Error while PROCESSING TOC:
[exec] pg_restore: [archiver (db)] Error from TOC entry 15728; 0 43798 MATERIALIZED VIEW DATA fact_tax dbowner@smile-DEV_2019-03-22T09-32-13.338
[exec] pg_restore: [archiver (db)] could not execute query: ERROR: relation "basic" does not exist
[exec] LINE 1: SELECT chargegst from basic where uid = _account
[exec] ^
[exec] QUERY: SELECT chargegst from basic where uid = _account
[exec] CONTEXT: PL/pgSQL function ar.categorise_gst(integer,integer,date) line 7 at IF
[exec] Command was: REFRESH MATERIALIZED VIEW cube02.fact_tax;
The issue is that there’s a mat view that refers to a plpgsql function
that refers to a table in the public schema, but it’s not qualified.
When we create the materialized view, and when we refresh it, the table
is in the search path. But when restoring from a dump, it’s not.
Is this the desired behaviour? This is an issue for us because
pg_dump/pg_restore is part of our disaster recovery process, so if we
find this problem during restore it will mean more downtime.
Yes:
https://www.postgresql.org/about/news/1834/
The link in the above to the explanation is broken. The correct link is:
https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058:_Protect_Your_Search_Path
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3d2aed664ee8271fd6c721ed0aa10168cda112ea
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5770172cb0c9df9e6ce27c507b449557e5b45124
PG version 9.5.14. I’m attempting to find out now if it’s an issue in
more recent versions also.
TIA
*David Wheeler
**Software developer
*
E dwheeler@xxxxxxxxxxxxxxx <mailto:dwheeler@xxxxxxxxxxxxxxx>
D +61 3 9663 3554 W http://dgitsystems.com
Level 4, 313 La Trobe St, Melbourne VIC 3000.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
