'rep' and 'rewinder' are all users that I created. First I used 'rep' as the user in pg_rewind and compared results with dbname=repmgr or dbname=postgres; then I created another user 'rewinder' and grant the privileges that 'pg_rewind' requires at minimum on its documentation and compared results again.
The results are the same, 'dbname=repmgr' is the root cause of the error, since 'dbname=postgres' went well with both 'user=rep' and 'user=rewinder', while 'dbname=repmgr' generates exactly the same error.
The results are the same, 'dbname=repmgr' is the root cause of the error, since 'dbname=postgres' went well with both 'user=rep' and 'user=rewinder', while 'dbname=repmgr' generates exactly the same error.
On Thu, Oct 12, 2023 at 9:31 PM Ron <ronljohnsonjr@xxxxxxxxx> wrote:
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@xxxxxxxxx> 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@xxxxxxxxx>
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@xxxxxxxxxxxxxxxxxxxx>
What is the problem with it?It always encounters such a problem if database != postgres but functions when 'dbname=postgres'Hi there!
I look into pg_rewind, and found that for a rewind user defined like
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
https://www.postgresql.org/docs/16/app-pgrewind.html
$ 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
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.
