Re: Fwd: Why does pg_rewind deny permission for pg_read_binary_file() other than 'dbname=postgres'?

What does user "rep" (why is "replication" on the same line?) have to do
with role "rewinder" (which is what you granted all of the permissions to,
and what you run pg_rewind as)?

On 10/12/23 07:42, Zhaoxun Yan wrote:
> Hi Ron,
> I forgot to tell you that during setting up repmgr, I have created
> database repmgr (possibly schema repmgr depending on what extension repmgr
> did)
>
> CREATE USER rep replication;
>
> CREATE database repmgr WITH OWNER rep;
>
> CREATE EXTENSION repmgr;
>
>
>
> On Thu, Oct 12, 2023 at 5:22 PM Ron <ronljohnsonjr(at)gmail(dot)com> wrote:
>
> "rewinder" is a *user*, not a database. "dbname=postgres" explicitly
> means that the *database* name is "postgres".
>
> On 10/12/23 03:48, Zhaoxun Yan wrote:
>> BTW rewinder is another USER that I made for control variable:
>>
>> $ pg_rewind -D /pgdata --source-server='host=172.17.1.2 port=5432
>> user=rewinder dbname=postgres connect_timeout=5'
>> pg_rewind: source and target cluster are on the same timeline
>> pg_rewind: no rewind required
>> $ pg_rewind -D /pgdata --source-server='host=172.17.1.2 port=5432
>> user=rewinder dbname=repmgr   connect_timeout=5'
>> pg_rewind: error: could not fetch remote file "global/pg_control":
>> ERROR:  permission denied for function pg_read_binary_file
>>
>> ---------- Forwarded message ---------
>> From: *Zhaoxun Yan* <yan(dot)zhaoxun(at)gmail(dot)com>
>> Date: Thu, Oct 12, 2023 at 4:44 PM
>> Subject: Why does pg_rewind deny permission for pg_read_binary_file()
>> other than 'dbname=postgres'?
>> To: Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org>
>>
>>
>> Hi there!
>>
>> I am using repmgr and I have to use the command repmgr node rejoin
>> --force-rewind under 'dbname=repmgr'. It always fail on using
>> pg_rewind, the error is like this:
>> pg_rewind: error: could not fetch remote file "global/pg_control":
>> ERROR:  permission denied for function pg_read_binary_file
>>
>> I look into pg_rewind, and found that for a rewind user defined like
>> https://www.postgresql.org/docs/16/app-pgrewind.html
>>
>> It always encounters such a problem if database != postgres but
>> functions when 'dbname=postgres'
>>
>> $ pg_rewind -D /pgdata --source-server='host=172.17.1.2 port=5432
>> user=rep dbname=repmgr connect_timeout=5'
>> pg_rewind: error: could not fetch remote file "global/pg_control":
>> ERROR:  permission denied for function pg_read_binary_file
>> $ pg_rewind -D /pgdata --source-server='host=172.17.1.2 port=5432
>> user=rep dbname=postgres connect_timeout=5'
>> pg_rewind: source and target cluster are on the same timeline
>> pg_rewind: no rewind required
>>
>> What is the problem with it?
>>
>> BTW, below is what I have done to USER rewinder:
>>
>> CREATE USER rewinder;
>> GRANT EXECUTE ON function pg_catalog.pg_ls_dir(text, boolean,
>> boolean) TO rewinder;
>> GRANT EXECUTE ON function pg_catalog.pg_stat_file(text, boolean) TO
>> rewinder;
>> GRANT EXECUTE ON function pg_catalog.pg_read_binary_file(text) TO
>> rewinder;
>> GRANT EXECUTE ON function pg_catalog.pg_read_binary_file(text,
>> bigint, bigint, boolean) TO rewinder;
>>
>> # below is irrelevant to postgresql's pg_rewind
>> GRANT ALL PRIVILEGES ON DATABASE repmgr TO rewinder;
>> GRANT ALL PRIVILEGES ON SCHEMA repmgr TO rewinder;
>> GRANT pg_read_all_stats TO rewinder;
>> GRANT ALL ON SCHEMA repmgr TO rewinder;
>> GRANT SELECT  ON DATABASE repmgr TO rewinder;
>> GRANT SELECT ON ALL TABLES IN SCHEMA repmgr TO rewinder;
>> GRANT SELECT ON SCHEMA repmgr TO rewinder;
>
> --
> Born in Arizona, moved to Babylonia.
>

--
Born in Arizona, moved to Babylonia.