Subject: Re: Failure of postgres_fdw because of TimeZone setting

From: Adrian Klaver <[email-masked]>
Date: Fri, 5 Apr 2024 08:05:33 -0700

On 4/5/24 02:39, Adnan Dautovic wrote:
> Dear Adrian,
> 
> Adrian Klaver <[email-masked]> wrote:
>> Define 'read-only', especially as it applies to the privileges on the 
>> public schema.
> 
> I am not quite sure which information you are looking for
> exactly. According to this [1], I ran the following query:
> 
> WITH "names"("name") AS (
>    SELECT n.nspname AS "name"
>      FROM pg_catalog.pg_namespace n
>        WHERE n.nspname !~ '^pg_'
>          AND n.nspname <> 'information_schema'
> ) SELECT "name",
>    pg_catalog.has_schema_privilege(current_user, "name", 'CREATE') AS
> "create",
>    pg_catalog.has_schema_privilege(current_user, "name", 'USAGE') AS 
> "usage"
>      FROM "names";
> 
> And recieved the following result:
> 
> "name"    "create"    "usage"
> "public"    true    true

Looks alright. The below is the issue.

> 
>> Per Tom Lane's comments on timezone, log into the remote server and do:
>>
>> SHOW timezone;
> Europe/Berlin
> 
>> SET timezone = 'etc/UTC';
> ERROR: invalid value for parameter "TimeZone": "etc/UTC"
> SQL state: 22023
> 
>> SET timezone = 'UTC';
> ERROR: invalid value for parameter "TimeZone": "UTC"
> SQL state: 22023
> 
> However, this lead me to [2] and I find the output very
> interesting:
> 
> SELECT * FROM pg_timezone_names ORDER BY name;

The below is cut down from the actual output as there should be at least:

Europe/Berlin  CEST  02:00:00 t

present also?

> 
>> "name"    "abbrev"    "utc_offset"    "is_dst"
>> "Turkey"    "+03"    "03:00:00"    false
>> "UCT"    "UCT"    "00:00:00"    false

Hmm I get:

UCT   UTC  00:00:00  f

could be version difference though.

>> "Universal"    "UTC"    "00:00:00"    false
>> "W-SU"    "MSK"    "03:00:00"    false
> 
> 
> And then attempting
> 
> SET timezone = 'Universal';
> 
>> SET
>> Query returned successfully in 100 msec.
> 
> Any ideas on how to proceed?

1) For the long term contact whomever is in charge of the remote server 
and ask them what they have done with the timezones, why and can they 
fix it?

2) In short term per the link from your first post and with no guarantees:

https://github.com/postgres/postgres/blob/936e3fa3787a51397280c1081587586e83c20399/contrib/postgres_fdw/connection.c#L677

In the source code change

do_sql_command(conn, "SET timezone = 'UTC'");

to

do_sql_command(conn, "SET timezone = 'Universal'");

As from the link: "Set remote timezone; this is basically just cosmetic"

Then recompile the extension.

> 
> Kind regards,
> 
> Adnan Dautovic
> 
> 
> [1]: https://stackoverflow.com/a/36095257
> [2]: https://stackoverflow.com/a/32009497
> 

-- 
Adrian Klaver
[email-masked]

---

From: Adnan Dautovic <[email-masked]>
Date: Wed, 10 Apr 2024 21:48:30 +0200

Dear Adrian,

>Europe/Berlin  CEST  02:00:00 t

Correct! That entry also exists. I only included the snippet
where I would have expected the "UTC" entry to be.

>1) For the long term contact whomever is in charge of the remote 
>server and ask them what they have done with the timezones, why and 
>can they fix it?

This will probably be long term indeed. But I am curious and want
to see if I can get some information from the responsible
person(s).

>Then recompile the extension.

Thank you, I got around to trying this route and it worked! Now I
just have to tinker around a bit to see how I can best include
the modified extension into the Docker image, but that is a task
I can grapple with outside of this mailing list. :-)

I am happy to have learned a few things and thank you for your
help tom and Adrian.

Kind regards,

Adnan Dautovic