Subject: Re: Errors when restoring backup created by pg_dumpall

From: Tom Lane <[email-masked]>
Date: Sun, 01 Dec 2024 16:14:18 -0500

Adrian Klaver <[email-masked]> writes:
> On 12/1/24 12:05, PopeRigby wrote:
>> I'm still getting this error:
>> 
>> psql:all.sql:4102: ERROR:  type "earth" does not exist
>> LINE 1: ...ians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth

> The issue is still this:
> SELECT pg_catalog.set_config('search_path', '', false);
> in the pg_dumpall output.

We've done that for some time, though.

> As was suggested before change the above to:
> SELECT pg_catalog.set_config('search_path', 'public', false);
> in the pg_dumpall output file.

I'm betting that won't help.  The new-in-17 behavior is that
maintenance commands such as CREATE INDEX internally force
a "safe" search_path, regardless of the prevailing setting.

It would be useful to know what is the command at line 4102
of all.sql.

			regards, tom lane

---

From: Adrian Klaver <[email-masked]>
Date: Sun, 1 Dec 2024 13:36:36 -0800

> It would be useful to know what is the command at line 4102

It is here:

https://gist.github.com/poperigby/fcb59eb6c22c6051800e06a0ec482b49

CREATE TABLE public.geodata_places (
     id integer NOT NULL,
     name character varying(200) NOT NULL,
     longitude double precision NOT NULL,
     latitude double precision NOT NULL,
     "countryCode" character(2) NOT NULL,
     "admin1Code" character varying(20),
     "admin2Code" character varying(80),
     "modificationDate" date NOT NULL,
     "earthCoord" public.earth GENERATED ALWAYS AS 
(public.ll_to_earth(latitude, longitude)) STORED,
     "admin1Name" character varying,
     "admin2Name" character varying,
     "alternateNames" character varying
);

-- 
Adrian Klaver
[email-masked]

---

From: Tom Lane <[email-masked]>
Date: Sun, 01 Dec 2024 16:55:08 -0500

>      "alternateNames" character varying

Ah!  Then the failure occurs because we do a planning pass on the
GENERATED expression (I don't remember exactly why that's needed
during CREATE TABLE).  So maybe messing with the dump script's
search_path setting *would* be enough to get you past that.

Having said that, the CREATE should have been seeing the new-style
definition of ll_to_earth() if the 1.2 version of earthdistance
was correctly installed.

			regards, tom lane

---

From: PopeRigby <[email-masked]>
Date: Thu, 5 Dec 2024 14:32:11 -0800

> definition of ll_to_earth() if the 1.2 version of earthdistance

So, is there anything I can do to fix this particular backup? I'm kind 
of stuck here. There's also the issue with it for some reason failing to 
connect to the lldap database after it literally just created it. Some 
weird things going on.

---

From: Adrian Klaver <[email-masked]>
Date: Thu, 5 Dec 2024 14:48:34 -0800

> connect to the lldap database after it literally just created it. Some 

In the pg_dumpall sql script did you change:

to

?

Show us the connection error you got for the lldap database.

---

From: PopeRigby <[email-masked]>
Date: Sat, 7 Dec 2024 11:25:29 -0800

On 12/5/24 14:48, Adrian Klaver wrote:
>> So, is there anything I can do to fix this particular backup? I'm 
>> kind of stuck here. There's also the issue with it for some reason 
>> failing to connect to the lldap database after it literally just 
>> created it. Some weird things going on.
>>
>
It actually looks like setting those all to have public fixed all the 
errors, including the one with lldap. So, how can I get it to not put 
public there automatically for next time?

---

From: "David G. Johnston" <[email-masked]>
Date: Sat, 7 Dec 2024 12:58:23 -0700

> public there automatically for next time?
I assume you mean "get it to put public there" (i.e., the "not" is a typo)

You cannot.  The security team has decided to not permit an opt-in bypass
of the lock-downs implemented to fix CVE-2018-1058.

Your only real choice at the moment is to replace the function call in the
generated expression with a custom function and in that custom function's
create function command attach a "set search_path to public" clause.  That
will prevent inlining and also ensure the public schema is in the
search_path when executing the public.ll_to_earth function call.  With that
in place the empty search_path in the dump file will no longer matter.

David J.

---

From: PopeRigby <[email-masked]>
Date: Mon, 9 Dec 2024 14:14:04 -0800

On 12/7/24 11:58, David G. Johnston wrote:
> You cannot. The security team has decided to not permit an opt-in 
> bypass of the lock-downs implemented to fix CVE-2018-1058.
>
> Your only real choice at the moment is to replace the function call in 
> the generated expression with a custom function and in that custom 
> function's create function command attach a "set search_path to 
> public" clause.  That will prevent inlining and also ensure the public 
> schema is in the search_path when executing the public.ll_to_earth 
> function call.  With that in place the empty search_path in the dump 
> file will no longer matter.
>
> David J.
>
Yeah, that was a typo. It seems weird that this behavior would be broken 
by default though, is there anything that could fix it upstream?

---

From: "David G. Johnston" <[email-masked]>
Date: Mon, 9 Dec 2024 15:31:52 -0700

> by default though, is there anything that could fix it upstream?

You saw and tried the work being done "upstream" to fix the situation.
It's a big knot in the system and it isn't easy (or highly motivated) to
untangle unfortunately...

David J.

---

From: PopeRigby <[email-masked]>
Date: Mon, 9 Dec 2024 14:34:27 -0800

On 12/9/24 14:31, David G. Johnston wrote:
>>     You cannot.  The security team has decided to not permit an
>>     opt-in bypass of the lock-downs implemented to fix CVE-2018-1058.
>>
>>     Your only real choice at the moment is to replace the function
>>     call in the generated expression with a custom function and in
>>     that custom function's create function command attach a "set
>>     search_path to public" clause.  That will prevent inlining and
>>     also ensure the public schema is in the search_path when
>>     executing the public.ll_to_earth function call.  With that in
>>     place the empty search_path in the dump file will no longer matter.
>>
>     Yeah, that was a typo. It seems weird that this behavior would be
>     broken by default though, is there anything that could fix it
>     upstream?
>
>
> It's a big knot in the system and it isn't easy (or highly motivated) 
> to untangle unfortunately...
>
> David J.
>
Understood. Well, at least it was a fairly easy fix. Thanks for the help :)

---

From: Adrian Klaver <[email-masked]>
Date: Mon, 9 Dec 2024 14:47:07 -0800

> by default though, is there anything that could fix it upstream?

You could file an issue here:

https://www.postgresql.org/account/login/?next=/account/submitbug/

Ask if the developers could use the mechanisms available here:

https://www.postgresql.org/docs/current/extend-extensions.html#EXTEND-EXTENSIONS-RELOCATION

to schema qualify the objects in the extension.

Not sure if that will fly or not, but it is worth a shot.

---

From: PopeRigby <[email-masked]>
Date: Mon, 9 Dec 2024 14:49:14 -0800

On 12/9/24 14:47, Adrian Klaver wrote:
>>> Your only real choice at the moment is to replace the function call 
>>> in the generated expression with a custom function and in that 
>>> custom function's create function command attach a "set search_path 
>>> to public" clause.  That will prevent inlining and also ensure the 
>>> public schema is in the search_path when executing the 
>>> public.ll_to_earth function call.  With that in place the empty 
>>> search_path in the dump file will no longer matter.
>>>
>>> David J.
>>>
>> broken by default though, is there anything that could fix it upstream?
>>
>
Will do!

---

From: Tom Lane <[email-masked]>
Date: Mon, 09 Dec 2024 18:23:26 -0500

> https://www.postgresql.org/docs/current/extend-extensions.html#EXTEND-EXTENSIONS-RELOCATION

This wouldn't really move the needle, since (a) there is not some
magic group of people that will become involved who aren't already
and (b) the requested fix seems exactly the same as the work
already in progress at [1].

What would help is to figure out why the proposed patch didn't
seem to work for you.  I continue to suspect that you didn't
really install the updated extension, but it's unclear.

			regards, tom lane

[1] https://www.postgresql.org/message-id/flat/[email-masked]

---

From: PopeRigby <[email-masked]>
Date: Mon, 9 Dec 2024 15:30:40 -0800

> [1] https://www.postgresql.org/message-id/flat/[email-masked]

I compiled and installed PostgreSQL with the patch, and restarted it, so 
unless there are other steps I'm not sure what I could have done wrong.

---

From: Adrian Klaver <[email-masked]>
Date: Mon, 9 Dec 2024 16:07:53 -0800

> already in progress at [1].

I made the suggestion to the OP, my mistake. Somewhere I missed that 
work was being done on this.

> really install the updated extension, but it's unclea

Are you referring to this?:

https://www.postgresql.org/message-id/3316564.aeNJFYEL58@aivenlaptop

---

From: Adrian Klaver <[email-masked]>
Date: Mon, 9 Dec 2024 16:31:49 -0800

On 12/9/24 15:30, PopeRigby wrote:
>> [1] 
>> https://www.postgresql.org/message-id/flat/[email-masked]
> 

What was the source of the patch?

---

From: PopeRigby <[email-masked]>
Date: Tue, 10 Dec 2024 19:05:53 -0800

On 12/9/24 16:31, Adrian Klaver wrote:
>> I compiled and installed PostgreSQL with the patch, and restarted it, 
>> so unless there are other steps I'm not sure what I could have done 
>> wrong.
>>
>
>
This: 
https://www.postgresql.org/message-id/attachment/122092/0002-earthdistance-sql-functions.patch

---

From: Adrian Klaver <[email-masked]>
Date: Tue, 10 Dec 2024 21:22:01 -0800

> https://www.postgresql.org/message-id/attachment/122092/0002-earthdistance-sql-functions.patch

If I am following correctly I believe you need this one:

https://www.postgresql.org/message-id/attachment/166859/v2-0002-Use-new-style-SQL-function-in-earthdistance-exten.patch

It has the changes that include the schema qualifications. The one you 
used does not have those modifications.

---

From: PopeRigby <[email-masked]>
Date: Thu, 12 Dec 2024 22:19:11 -0800

On 12/10/24 21:22, Adrian Klaver wrote:
>>>> I compiled and installed PostgreSQL with the patch, and restarted 
>>>> it, so unless there are other steps I'm not sure what I could have 
>>>> done wrong.
>>>>
>>>
>>>
Beautiful. How can I follow this so I know when it gets upstream?

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 13 Dec 2024 08:46:56 -0800

> Beautiful. How can I follow this so I know when it gets upstream?

You can check here:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=tree;f=contrib/earthdistance;h=282f5a80b204a488b5c8ec6d24eaaab8a714bfaf;hb=HEAD

and see when the earthdistance--1.1--1.2.sql and earthdistance--1.2.sql
scripts show up.

Though a formal release won't happen until the dates listed here:

https://www.postgresql.org/developer/roadmap/

with nearest one being February 13th, 2025.

FYI, these are target dates and not set in stone.