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 08:29:16 -0500
Message-ID: <90bfa111-85f5-a5db-4026-dc1587ab5a11@gmail.com> (raw)
In-Reply-To: <CADEX6_WJO0+VCHsgt736x5Y+a9XTrQyF0MHV1JkL51A28RM-GQ@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>
<ef7585c0-8a04-a9d3-8724-de2c2abac3c7@gmail.com>
<CADEX6_WJO0+VCHsgt736x5Y+a9XTrQyF0MHV1JkL51A28RM-GQ@mail.gmail.com>
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.
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: <90bfa111-85f5-a5db-4026-dc1587ab5a11@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