Re: Backup/restore problem

Pavel Popov <pavel.popov@gmail.com> writes:
> The problem occurs when we make backup of a db on server version 11 or 12
> and restore on the same server version. When we restore on server version
> 12 a backup made on server version 9.6 this problem DOES NOT occur.

I think it would happen if you tried to dump/restore with a *current* 9.6
release.  The problem looks to be that this function is not safe
against search_path changes:

> CREATE FUNCTION chk_f_part_pricetar(_customer integer, _pricetar integer)
> RETURNS boolean
>     LANGUAGE plpgsql
>     AS $$
> BEGIN
>   RETURN EXISTS(SELECT * FROM customers c JOIN nom.firms f ON c.invoiced_by = f.id
>                                           JOIN prices.pricetar t ON f.id = t.firm
>                 WHERE c.id = _customer AND t.id = _pricetar) OR _pricetar IS NULL;
> END;$$;

and recent releases of pg_dump insist on running the script with a
restrictive search_path for security reasons.

It might be enough to schema-qualify the function's reference to
"customers", although a safer solution would be to attach a
"SET search_path" clause to the function to enforce the search
path it's expecting.

                        regards, tom lane