1

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?

Jivan
  • 21,522
  • 15
  • 80
  • 131
  • 1
    I suspect that you are doing something different from what you say you are doing, else your second statement would fail. – Laurenz Albe Apr 20 '20 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 Apr 20 '20 at 10:31
  • 1
    Then you must be running the statements in different databases. – Laurenz Albe Apr 20 '20 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 Apr 20 '20 at 10:37

1 Answers1

4

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263