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
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;
