Re: pg_rewind: ERROR: could not fetch remote file "global/pg_control": ERROR: permission denied

It worked via a non-replication user.

On Wed, Oct 4, 2023 at 3:09 PM Zhaoxun Yan <yan(dot)zhaoxun(at)gmail(dot)com> wrote:

> Hi Michael!
> Thank you for looking into this. I did check the old standby/new primary
> for its authorization. I found the commands in .psql_history in postgresql
> home directory. And then I grant the privileges again on it. But it did not
> work.
>
> I noticed that documentation mentioned the rewind user CANNOT be a
> replication user at the same time. I would create a user just for rewind
> and try again. If that still doesn't work, I believe it is a bug.
>
> On Fri, Sep 29, 2023 at 2:50 PM Michael Paquier <michael(at)paquier(dot)xyz>
> wrote:
>
>> On Wed, Sep 27, 2023 at 05:27:44PM +0800, Zhaoxun Yan wrote:
>> > $ /usr/pgsql-16/bin/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_fileOriginally the server
>> > here at 172.17.0.2 was the primary and 172.17.1.2 was the standby
>> > replication server. I have created the user 'rep' with full replication
>> > privilege as described on page:
>> > https://www.postgresql.org/docs/16/app-pgrewind.html
>> >
>> > CREATE EXTENSION repmgr;
>> > GRANT pg_checkpoint TO rep;
>> > GRANT pg_read_all_stats TO rep;
>> > GRANT EXECUTE ON function pg_catalog.pg_ls_dir(text, boolean, boolean)
>> TO rep;
>> > GRANT EXECUTE ON function pg_catalog.pg_stat_file(text, boolean) TO rep;
>> > GRANT EXECUTE ON function pg_catalog.pg_read_binary_file(text) TO rep;
>> > GRANT EXECUTE ON function pg_catalog.pg_read_binary_file(text, bigint,
>> > bigint, boolean
>>
>> This set of permissions should be enough, so you got the idea from the
>> documentation the right way. One guess: are you sure that these GRANT
>> queries have been run on the same database as the one queried by
>> pg_rewind when getting the files from an online source for the target
>> cluster to rewind?
>> --
>> Michael
>>
>