Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
Zhaoxun Yan
Date:
I came across this error by using repmgr5.4 for pg15, but the problem arose in pg_rewind, so I installed pg16 and tried again, the error persists like this:

$ /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_file

Originally 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
And I have made it 'trust' among the intranet 172.17.0.0/16 in pg_hba.conf:

#host    all             all      127.0.0.1/32            ident

#host    replication     all       127.0.0.1/32            ident

host    all             rep             172.17.0.0/16           trust

host    all             postgres        172.17.0.0/16           trust


I have also made changes in postgresql.conf:

#wal_level = 'replica'

#full_page_writes = on

wal_log_hints = on

synchronous_standby_names = '*'


I used repmgr -h 172.17.0.2 -U rep -d repmgr standby clone to make the replica/standby on 172.17.1.2 and basically it called pg_basebackup to do the job. Checking the replication status from the old primary @172.17.0.2 with

SELECT * FROM pg_stat_replication; would return a table containing:
state=streaming, sync_state=async


After that, I shut down the postgresql server on the old primary. And ordered `pg_ctl  -w -D '/pgdata' promote` to turn it into the new primary.

After that is where the issue occured - the pg_rewind on the old primary won't function and threw out the error code.

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

From
Michael Paquier
Date:
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
Attachment

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

From
Zhaoxun Yan
Date:
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@paquier.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

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

From
Zhaoxun Yan
Date:
It worked via a non-replication user.

On Wed, Oct 4, 2023 at 3:09 PM Zhaoxun Yan <yan.zhaoxun@gmail.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@paquier.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