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 1qqvko-001kEN-HY for pgsql-admin@arkaria.postgresql.org; Thu, 12 Oct 2023 13:29:30 +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 1qqvkk-006gNJ-Re for pgsql-admin@arkaria.postgresql.org; Thu, 12 Oct 2023 13:29:27 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qqvkk-006gNA-EJ for pgsql-admin@lists.postgresql.org; Thu, 12 Oct 2023 13:29:27 +0000 Received: from mail-yb1-xb2a.google.com ([2607:f8b0:4864:20::b2a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qqvkc-000BIE-QW for pgsql-admin@lists.postgresql.org; Thu, 12 Oct 2023 13:29:26 +0000 Received: by mail-yb1-xb2a.google.com with SMTP id 3f1490d57ef6-d9ad90e1038so434011276.3 for ; Thu, 12 Oct 2023 06:29:19 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1697117357; x=1697722157; 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=RjuDm0VUOBBNzpmF3jDQOb1NbSF+PICa7tAR2f16HLM=; b=E3qMCDHQ1KJIIpQysdefPcre2gYVDLfZyMIA6lSNVV4KaXGkq8IjYWElHrc5IESnT4 XrCQX40oqaJZopRa+HQwn33Z+QiUJq4BuMi51U86aP5JloxkMGtk1QlbxrSi4cziHN7l vp0hUujmPOQUJJST3KxYk/+7rrW/kABByrT1VnHyzzUla8VFkgCPMKECfjb+GQ3N2Ve4 evXvd+AXYoP1txgb7x8XAMPK0PctGfGly6s4/Jgjkva8pyJX3im9XolTytGtqlsGNDrD V5wMnTGNVcqVB4w+QLzSzoFBAbVjW8oB/XJ5/sKuAevG/RlZvOZvPgHuBJJvOF6cviDU YMrg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1697117357; x=1697722157; 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=RjuDm0VUOBBNzpmF3jDQOb1NbSF+PICa7tAR2f16HLM=; b=DS6nNzvp6QQhj+ktq+AstkehmOLuhjvBTTlLxqPd4FDgne3m8ZEqvgosjKH1nZhVoy OQYQHrDu2o+4P9jdLTT7a66wZkSt4Wul1ND7a8A2PsyQsQLO2LfWmydnUH4SGkMDHxan iyN+IHjGCIren2riCrYK0600iEN4e9czuh+7aT+GJId2PBFyizcRGOT1b+IkvF1XZq8n BgGjyjIeKgo4I7a0B+FNJnVO9Vtn+VuN3tgGVaGooFzoO63CQHDZc012v+jMw9onOmDL KGi6XQdt0Fllgkb+CnjCOgysZ5EUyKzqYsvlk7WfCLZUq9FSVgs4US1UDpyNNgBsigAD cK2w== X-Gm-Message-State: AOJu0YyNVVDjYJbUVZZgZXlZiHJvpW8U0cmJKN4jR0M9JxtIjhDcSKho 9zJPGZ+AXRuWWmRr6niYuJr+iR9wj9U= X-Google-Smtp-Source: AGHT+IHzdymr9sr/TD+x6MOsmgiGN6UB7IsR4kpyoIeU5/3UOLbFuxdfqpwtoyco4uWoxQmrSqOGtw== X-Received: by 2002:a25:3446:0:b0:d9a:dfd2:cce3 with SMTP id b67-20020a253446000000b00d9adfd2cce3mr835414yba.58.1697117356893; Thu, 12 Oct 2023 06:29:16 -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 b63-20020a253442000000b00d9a4f3bdffbsm1991190yba.11.2023.10.12.06.29.16 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 12 Oct 2023 06:29:16 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------vGHSfj97vevPDp0DxHPhUWyq" Message-ID: <90bfa111-85f5-a5db-4026-dc1587ab5a11@gmail.com> Date: Thu, 12 Oct 2023 08:29:16 -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. --------------vGHSfj97vevPDp0DxHPhUWyq Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 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* >> 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. > -- Born in Arizona, moved to Babylonia. --------------vGHSfj97vevPDp0DxHPhUWyq Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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.
--------------vGHSfj97vevPDp0DxHPhUWyq--