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(at)smile-DEV_2019-03-22T09-32-13(dot)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(at)dgitsystems(dot)com <mailto:dwheeler(at)dgitsystems(dot)com>
> D +61 3 9663 3554  W http://dgitsystems.com
> Level 4, 313 La Trobe St, Melbourne VIC 3000.
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com