pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedPG FDW query fails, works local, same credentials
3+ messages / 2 participants
[nested] [flat]
* PG FDW query fails, works local, same credentials
@ 2023-08-31 03:52 Pete O'Such <posuch@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Pete O'Such @ 2023-08-31 03:52 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
I've got a view on server A (PG 15.2) that fails when queried via FDW from
server B (also PG 15.2). Querying it as a foreign table from server B
yields a message like "ERROR: function blah(type) does not exist".
Confusingly, I succeed when: I log into server A, set my role to match the
role used with the FDW, and query with the exact query text that server B
sent to server A (according to the error on server B).
The function that it complains about does exist, and I have made the effort
to provide appropriate grants to the role in question. So it does work as
expected from within server A, as the same role. But why would it then fail
from server B?
Both cases use the same role/credentials. Plenty of other foreign tables
work in this same setup between these two servers, whether pointing to
tables or views. I hit and resolved similar issues earlier where the role
lacked necessary grants. Having experienced it before, this time I'm
pretty sure that the right grants are in place, yet it still fails when
used via FDW.
I'm running out of things to try and wondering if this will turn out to be
a bug. What should I be trying before reporting it as a bug?
Thanks,
Pete
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: PG FDW query fails, works local, same credentials
@ 2023-08-31 04:46 Tom Lane <tgl@sss.pgh.pa.us>
parent: Pete O'Such <posuch@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2023-08-31 04:46 UTC (permalink / raw)
To: Pete O'Such <posuch@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
"Pete O'Such" <posuch@gmail.com> writes:
> I've got a view on server A (PG 15.2) that fails when queried via FDW from
> server B (also PG 15.2). Querying it as a foreign table from server B
> yields a message like "ERROR: function blah(type) does not exist".
Check your search path assumptions. postgres_fdw runs remote queries
with a very minimal search_path setting, so that unqualified references
to non-built-in objects are likely to fail.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: PG FDW query fails, works local, same credentials
@ 2023-09-01 07:10 Pete O'Such <posuch@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Pete O'Such @ 2023-09-01 07:10 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@lists.postgresql.org
> Check your search path assumptions. postgres_fdw runs remote
> queries with a very minimal search_path setting,
Indeed it was the search path, combined with references to user-defined
functions that weren't schema-qualified.
Thank you for the pointer!
-Pete
On Thu, Aug 31, 2023 at 12:46 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Pete O'Such" <posuch@gmail.com> writes:
> > I've got a view on server A (PG 15.2) that fails when queried via FDW
> from
> > server B (also PG 15.2). Querying it as a foreign table from server B
> > yields a message like "ERROR: function blah(type) does not exist".
>
> Check your search path assumptions. postgres_fdw runs remote queries
> with a very minimal search_path setting, so that unqualified references
> to non-built-in objects are likely to fail.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-09-01 07:10 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-08-31 03:52 PG FDW query fails, works local, same credentials Pete O'Such <posuch@gmail.com>
2023-08-31 04:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-09-01 07:10 ` Pete O'Such <posuch@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