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@gmail.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.zhaoxun@gmail.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@lists.postgresql.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.