Re: Backup/restore problem

Ok, thanks.
Thank you very much! "SET search_path" working.

On Tue, Oct 29, 2019 at 4:39 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Pavel Popov <pavel(dot)popov(at)gmail(dot)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
>