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

'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.

On Thu, Oct 12, 2023 at 9:31 PM Ron <ronljohnsonjr(at)gmail(dot)com> 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(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.
>>
>
> --
> Born in Arizona, moved to Babylonia.
>