Обсуждение: Why does pg_rewind deny permission for pg_read_binary_file() other than 'dbname=postgres'?
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
https://www.postgresql.org/docs/16/app-pgrewind.html
$ 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
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;
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;
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;
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
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?
$ 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>
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
https://www.postgresql.org/docs/16/app-pgrewind.html
$ 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
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;
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;
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;
"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>What is the problem with it?It always encounters such a problem if database != postgres but functions when 'dbname=postgres'Hi there!I look into pg_rewind, and found that for a rewind user defined like
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
https://www.postgresql.org/docs/16/app-pgrewind.html
$ 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 requiredBTW, 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_rewindGRANT 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.
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>What is the problem with it?It always encounters such a problem if database != postgres but functions when 'dbname=postgres'Hi there!I look into pg_rewind, and found that for a rewind user defined like
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
https://www.postgresql.org/docs/16/app-pgrewind.html
$ 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 requiredBTW, 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_rewindGRANT 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.
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>What is the problem with it?It always encounters such a problem if database != postgres but functions when 'dbname=postgres'Hi there!I look into pg_rewind, and found that for a rewind user defined like
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
https://www.postgresql.org/docs/16/app-pgrewind.html
$ 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 requiredBTW, 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_rewindGRANT 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.
Born in Arizona, moved to Babylonia.
'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.
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@gmail.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@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>What is the problem with it?It always encounters such a problem if database != postgres but functions when 'dbname=postgres'Hi there!I look into pg_rewind, and found that for a rewind user defined like
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
https://www.postgresql.org/docs/16/app-pgrewind.html
$ 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 requiredBTW, 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_rewindGRANT 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.
Zhaoxun Yan <yan.zhaoxun@gmail.com> writes: > 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. As far as I can tell, you granted permissions on that function in the postgres database, but not any other database. Function permissions, like most others, are database-local in Postgres. regards, tom lane
Thanks a lot Tom!
I really appreciate it. It worked after I did these in psql:postgres-# \c repmgr
repmgr=# GRANT EXECUTE ON function pg_catalog.pg_ls_dir(text, boolean, boolean) TO rewinder;
GRANT
repmgr=# GRANT EXECUTE ON function pg_catalog.pg_stat_file(text, boolean) TO rewinder;
GRANT
repmgr=# GRANT EXECUTE ON function pg_catalog.pg_read_binary_file(text) TO rewinder;
GRANT
repmgr=# GRANT EXECUTE ON function pg_catalog.pg_read_binary_file(text, bigint, bigint, boolean) TO rewinder;
GRANT
$ pg_rewind -D /pgdata --source-server='host=172.17.1.2 port=5432 user=rewinder dbname=repmgr connect_timeout=5'
pg_rewind: source and target cluster are on the same timeline
pg_rewind: no rewind required
Still, I wish psql can specify this database limitation explicitly, either enforcing the command with 'IN DATABASE [dbname]', or emphasize it in feedback rather than a simple 'GRANT'.
On Fri, Oct 13, 2023 at 10:03 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Zhaoxun Yan <yan.zhaoxun@gmail.com> writes:
> 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.
As far as I can tell, you granted permissions on that function
in the postgres database, but not any other database. Function
permissions, like most others, are database-local in Postgres.
regards, tom lane
Re: Why does pg_rewind deny permission for pg_read_binary_file() other than 'dbname=postgres'?
От
"David G. Johnston"
Дата:
On Thursday, October 12, 2023, Zhaoxun Yan <yan.zhaoxun@gmail.com> wrote:
Now pg_rewind has no problem when user=rewinder & dbname=repmgr:
$ pg_rewind -D /pgdata --source-server='host=172.17.1.2 port=5432 user=rewinder dbname=repmgr connect_timeout=5'
pg_rewind: source and target cluster are on the same timeline
pg_rewind: no rewind requiredStill, I wish psql can specify this database limitation explicitly, either enforcing the command with 'IN DATABASE [dbname]', or emphasize it in feedback rather than a simple 'GRANT'.
Please no. All objects only exist in a single database and you must be signed into a database to execute SQL, and you cannot execute cross-database. Specifying “in database” would just be annoying and redundant. Same goes with a message saying “you executed this command in database postgres.” Especially for grant, where the worst that happens is you still can’t do something you thought you enabled.
David J.