From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Fwd: Why does pg_rewind deny permission for pg_read_binary_file() other than 'dbname=postgres'?
Date: Thu, 12 Oct 2023 04:18:10 -0500
Message-ID: <ef7585c0-8a04-a9d3-8724-de2c2abac3c7@gmail.com> (raw)
In-Reply-To: <CADEX6_U=8O1OoYzAOvBuq1Mc1+pm_6ZB+Oo+_g6yr-vZ_7n=uA@mail.gmail.com>
References: <CADEX6_U5V8CoNY7ftjy-fcrEYR0beG2_6PBSEDtEVb3zYt-_sg@mail.gmail.com>
<CADEX6_U=8O1OoYzAOvBuq1Mc1+pm_6ZB+Oo+_g6yr-vZ_7n=uA@mail.gmail.com>
"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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Fwd: Why does pg_rewind deny permission for pg_read_binary_file() other than 'dbname=postgres'?
In-Reply-To: <ef7585c0-8a04-a9d3-8724-de2c2abac3c7@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox