Re: Materialized view breaks pg_restore

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@dgitsystems.com <mailto:dwheeler@dgitsystems.com>
> D +61 3 9663 3554  W http://dgitsystems.com
> Level 4, 313 La Trobe St, Melbourne VIC 3000.
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com