Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qqrpi-001RyU-P4 for pgsql-admin@arkaria.postgresql.org; Thu, 12 Oct 2023 09:18:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qqrpg-0058Te-J2 for pgsql-admin@arkaria.postgresql.org; Thu, 12 Oct 2023 09:18:17 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qqrpg-0058TO-4A for pgsql-admin@lists.postgresql.org; Thu, 12 Oct 2023 09:18:16 +0000 Received: from mail-yb1-xb36.google.com ([2607:f8b0:4864:20::b36]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qqrpc-0006BH-R7 for pgsql-admin@lists.postgresql.org; Thu, 12 Oct 2023 09:18:15 +0000 Received: by mail-yb1-xb36.google.com with SMTP id 3f1490d57ef6-d9a6399cf78so603133276.0 for ; Thu, 12 Oct 2023 02:18:12 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1697102291; x=1697707091; darn=lists.postgresql.org; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=5eO06dfeAc5mYmOx/MtsxB/dVxIQaOFGhQcA7glKMP4=; b=F5G4lsDcArcKuaZN05QUFHmLhAMSa6vx+sJDvEXKWwbqntpkaGIUsX9M+gRTV1agPj LpbCCkDLJ+KcVDK86rvuJG/HE0FozayZFt4sD/0Bu7TcY6mhrI6AKAosI32a9zrHhz3b 4DSIfJP68/V2Xfsfvq0ls1W/zjUgIXWBvmNidaoPM/MgGUY0z9DTqbri2VFwYLuYdGRo +VWJJZvDOltr4Rj6gYZ215HLMmTIha1DY/fe7pY2CTFnWG1yTXrzD2pRUniHSZUeVkR8 esD8Li8h4YJewWZB7jDHJEFbD24MmzMdnvVBC8G9DMBbr4XWiXGUZsePfda6wCqsHw/z 6dDg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1697102291; x=1697707091; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=5eO06dfeAc5mYmOx/MtsxB/dVxIQaOFGhQcA7glKMP4=; b=Ody2RsEt+ptxUvJ8Fs3gcsclIT8ZnVa64s/74z+Rnx/cofa/DPRv2zVCmF+YPVFYLl EtnrRSBrFVcpJnqA2eLcC06taHsXnVzkObJYP6xTa1HQIZP6Pxc6aYuEQr5fU1Rw6zRS EfdinxU6Xm96G22o6Uy49Mid5qH+4r5xyhGRjqNYjMtUhUceuPFJ32bbmhwcx7wbrRf2 tBp95SsoBt9D71h/XA8y0X47pnqSNwnr4mekTFoVmILgxAGj7vnhUaH9ob5bkgFQegi3 MEwVfJ+oHNDC0dGr9NCe6Xkk/Vn5mcD0K+SH/KfinOBYXpZHJFSu8OELSa5Ts8G2IzEy PJlQ== X-Gm-Message-State: AOJu0Yz/jqyBTv4s1knxlFiECNCSTJ8Cc1O3ltIp7wb8hzc1+Z0h2VYQ sWnW4ugoWNIv4yWv3iy8AkABFMiUtLA= X-Google-Smtp-Source: AGHT+IE7pJgWbTb+UkeGd4m6Hs1PipJ2RNLjZTYqkVG4egwI03f52XowbiJq6ZJjyypfB2lEHPtYsQ== X-Received: by 2002:a25:49c6:0:b0:d9a:b70d:e03a with SMTP id w189-20020a2549c6000000b00d9ab70de03amr1373624yba.29.1697102291434; Thu, 12 Oct 2023 02:18:11 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:9c07:69c3:3e12:3c8? ([2600:1700:bd2c:b810:9c07:69c3:3e12:3c8]) by smtp.googlemail.com with ESMTPSA id 142-20020a250b94000000b00d818ced8939sm15243ybl.1.2023.10.12.02.18.10 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 12 Oct 2023 02:18:11 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------4CTN7l0Q7HVWhHG9oNNvWGfQ" Message-ID: Date: Thu, 12 Oct 2023 04:18:10 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.11.0 Subject: Re: Fwd: Why does pg_rewind deny permission for pg_read_binary_file() other than 'dbname=postgres'? Content-Language: en-US To: pgsql-admin@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------4CTN7l0Q7HVWhHG9oNNvWGfQ Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit "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* > 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 > > > 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. --------------4CTN7l0Q7HVWhHG9oNNvWGfQ Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit "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.
--------------4CTN7l0Q7HVWhHG9oNNvWGfQ--