pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedWhy does pg_rewind deny permission for pg_read_binary_file() other than 'dbname=postgres'?
9+ messages / 4 participants
[nested] [flat]
* Why does pg_rewind deny permission for pg_read_binary_file() other than 'dbname=postgres'?
@ 2023-10-12 08:44 Zhaoxun Yan <yan.zhaoxun@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Zhaoxun Yan @ 2023-10-12 08:44 UTC (permalink / raw)
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;
^ permalink raw reply [nested|flat] 9+ messages in thread
* Fwd: Why does pg_rewind deny permission for pg_read_binary_file() other than 'dbname=postgres'?
@ 2023-10-12 08:48 Zhaoxun Yan <yan.zhaoxun@gmail.com>
parent: Zhaoxun Yan <yan.zhaoxun@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Zhaoxun Yan @ 2023-10-12 08:48 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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;
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Fwd: Why does pg_rewind deny permission for pg_read_binary_file() other than 'dbname=postgres'?
@ 2023-10-12 09:18 Ron <ronljohnsonjr@gmail.com>
parent: Zhaoxun Yan <yan.zhaoxun@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Ron @ 2023-10-12 09:18 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
"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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Fwd: Why does pg_rewind deny permission for pg_read_binary_file() other than 'dbname=postgres'?
@ 2023-10-12 12:42 Zhaoxun Yan <yan.zhaoxun@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Zhaoxun Yan @ 2023-10-12 12:42 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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.
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Fwd: Why does pg_rewind deny permission for pg_read_binary_file() other than 'dbname=postgres'?
@ 2023-10-12 13:29 Ron <ronljohnsonjr@gmail.com>
parent: Zhaoxun Yan <yan.zhaoxun@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Ron @ 2023-10-12 13:29 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Fwd: Why does pg_rewind deny permission for pg_read_binary_file() other than 'dbname=postgres'?
@ 2023-10-13 01:41 Zhaoxun Yan <yan.zhaoxun@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Zhaoxun Yan @ 2023-10-13 01:41 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
'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@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>
>>
>>
>> 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.
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Fwd: Why does pg_rewind deny permission for pg_read_binary_file() other than 'dbname=postgres'?
@ 2023-10-13 02:03 Tom Lane <tgl@sss.pgh.pa.us>
parent: Zhaoxun Yan <yan.zhaoxun@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2023-10-13 02:03 UTC (permalink / raw)
To: Zhaoxun Yan <yan.zhaoxun@gmail.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Fwd: Why does pg_rewind deny permission for pg_read_binary_file() other than 'dbname=postgres'?
@ 2023-10-13 06:40 Zhaoxun Yan <yan.zhaoxun@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Zhaoxun Yan @ 2023-10-13 06:40 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
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
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 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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Why does pg_rewind deny permission for pg_read_binary_file() other than 'dbname=postgres'?
@ 2023-10-13 07:04 David G. Johnston <david.g.johnston@gmail.com>
parent: Zhaoxun Yan <yan.zhaoxun@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: David G. Johnston @ 2023-10-13 07:04 UTC (permalink / raw)
To: Zhaoxun Yan <yan.zhaoxun@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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 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'.
>
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2023-10-13 07:04 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-12 08:44 Why does pg_rewind deny permission for pg_read_binary_file() other than 'dbname=postgres'? Zhaoxun Yan <yan.zhaoxun@gmail.com>
2023-10-12 08:48 ` Zhaoxun Yan <yan.zhaoxun@gmail.com>
2023-10-12 09:18 ` Ron <ronljohnsonjr@gmail.com>
2023-10-12 12:42 ` Zhaoxun Yan <yan.zhaoxun@gmail.com>
2023-10-12 13:29 ` Ron <ronljohnsonjr@gmail.com>
2023-10-13 01:41 ` Zhaoxun Yan <yan.zhaoxun@gmail.com>
2023-10-13 02:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-10-13 06:40 ` Zhaoxun Yan <yan.zhaoxun@gmail.com>
2023-10-13 07:04 ` David G. Johnston <david.g.johnston@gmail.com>
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