Foreign-data wrapper "postgres_fdw" does not exist (even if it does)

Question

Using PostgreSQL 10.10, from superuser postgres:

CREATE EXTENSION postgres_fdw;
GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO my_user;

Then when doing the following from my_user:

CREATE SERVER my_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (...);

This error message is displayed:

Query 1 ERROR: ERROR:  foreign-data wrapper "postgres_fdw" does not exist

Here is the list of currently active foreign data wrappers (from psql):

postgres=# \dew
                      List of foreign-data wrappers
     Name     |  Owner   |       Handler        |       Validator
--------------+----------+----------------------+------------------------
 postgres_fdw | postgres | postgres_fdw_handler | postgres_fdw_validator
(1 row)

How comes, even after having been granted USAGE, the user my_user is still not able to see/use postgres_fdw foreign data wrapper, as if the latter didn't exist? Are there more steps necessary?

I suspect that you are doing something different from what you say you are doing, else your second statement would fail. — Laurenz Albe
– Laurenz Albe, Commented Apr 20, 2020 at 10:20
@LaurenzAlbe you were right, I tried creating the wrapper first and then installed the extension. Starting again from a fresh installation and not creating the wrapper myself (only installing the extension) leads to the same result unfortunately. — Jivan
– Jivan, Commented Apr 20, 2020 at 10:31
Then you must be running the statements in different databases. — Laurenz Albe
– Laurenz Albe, Commented Apr 20, 2020 at 10:34
ah!!! that must be it! I was naively assuming that foreign data wrappers were db-agnostic — EDIT: that was effectively it, thanks for your help. You might make it an answer which I'll accept for future folks who get this problem. — Jivan
– Jivan, Commented Apr 20, 2020 at 10:37

Laurenz Albe · Accepted Answer · 2020-04-20 10:42:57Z

5

Since foreign data wrappers do not live in a schema, the only explanation would be that CREATE EXTENSION and CREATE SERVER were run in different databases (foreign data wrappers are not “global objects”). YOu have to run these statements in the same database.

By the way, the explicit CREATE FOREIGN DATA WRAPPER would result in an error, since a foreign data wrapper of that name is already created by the extension.

answered Apr 20, 2020 at 10:42

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Foreign-data wrapper "postgres_fdw" does not exist (even if it does)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related