Bug#928942: Bug #928942: udd: cannot import database dump into PostgreSQL ("function release_name(text) does not exist")

[Michael Banck <michael.banck@credativ.de>]

retitle 928942 udd: cannot import current dump due to matview referencing function without schema
done

Hi,

Am Montag, den 13.05.2019, 15:44 -0300 schrieb Leandro Doctors:
> Package: qa.debian.org
> Severity: grave
> Justification: causes non-serious data loss> 

not sure whether that is warranted; a materialized view is by definition
no new data that could be lost.

> 6) Finally, I import the dump
>     sudo -u postgres pg_restore -j 8 --no-owner -v -C -d postgres
> udd.dump.2019.05.06.dump
> 
> 
> After step 6, I get the following error:
>
>     pg_restore: launching item 4101 MATERIALIZED VIEW DATA tmp
>     pg_restore: creating MATERIALIZED VIEW DATA "public.tmp"
>     pg_restore: [archiver (db)] Error from TOC entry 4101; 0 91852869
> MATERIALIZED VIEW DATA tmp udd
>     pg_restore: [archiver (db)] could not execute query: ERROR:
> function release_name(text) does not exist
>     LINE 1: SELECT release_name($1)
>                    ^
>     HINT:  No function matches the given name and argument types. You
> might need to add explicit type casts.
>     QUERY:  SELECT release_name($1)
>     CONTEXT:  PL/pgSQL function public.bugs_rt_affects_dist(text) line
> 4 during statement block local variable initialization
>         Command was: REFRESH MATERIALIZED VIEW public.tmp;

The problem is that the UDD schema does not schema-qualify functions
when using them. As it seems to use the public schema, that was fine
until a while ago, but a recent PostgreSQL patch release has reset the
schema search path for security reasons. So that means functions
referenced in materialized views need to be schema-qualified now or else
the restore will fail.

I had a look at the schema.sql at https://salsa.debian.org/qa/udd/blob/m
aster/sql/udd-schema.sql and this one does not have any materialized
views, so public.tmp is apparently a hack in production (hence the
name). The actual problem appears to be in the bugs_rt_affect_dist
function that calls release_name($1) without schema-qualifiying it:

https://salsa.debian.org/qa/udd/blob/master/sql/udd-schema.sql#L553


cheers,

Michael

-- 
Michael Banck
Projektleiter / Senior Berater
Tel.: +49 2166 9901-171
Fax:  +49 2166 9901-100
Email: michael.banck@credativ.de

credativ GmbH, HRB Mönchengladbach 12080
USt-ID-Nummer: DE204566209
Trompeterallee 108, 41189 Mönchengladbach
Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha Heuer

Unser Umgang mit personenbezogenen Daten unterliegt
folgenden Bestimmungen: https://www.credativ.de/datenschutz