Re: Fwd: Why does pg_rewind deny permission for pg_read_binary_file() other than 'dbname=postgres'?

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(at)gmail(dot)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(dot)zhaoxun(at)gmail(dot)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(at)lists(dot)postgresql(dot)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.
>