MONGO_FDW on Postgres user privileges

Question

Setting up MONGO_FDW in Postgres for the first time.

First step is to add the extension after connecting as the default postgres user:

CREATE EXTENSION mongo_fdw;

The application user is app_user, so I grant this:

GRANT CREATE SERVER TO app_user;

I then connect as app_user and issue:

CREATE SERVER "app_rem_db"
 FOREIGN DATA WRAPPER mongo_fdw
 OPTIONS (
 address 'localhost',
 port '27017'
 );

But I get the error:

ERROR: foreign-data wrapper "mongo_fdw" does not exist

If however I run the CREATE SERVER as postgres it works.

How do I allow other users to create the server?

Continuing with the postgres user though, I next create the USER MAPPING:

CREATE USER MAPPING FOR app_user
 SERVER "app_rem_db"
 OPTIONS (username 'app_user_pg', password 'hello123');

If I then again connect as app_user and try to create a foreign table I get (The mongoDB has a database mongo_test and a collection testcol therein):

CREATE FOREIGN TABLE mongo_testtab
(
 data JSON
)
SERVER "app_rem_db"
OPTIONS (database 'mongo_test', collection 'testcol');

I again get an error:

ERROR: server "app_rem_db" does not exist

If I switch back to postgres user I can create the foreign table.

But then when I try to query the table I get:

select * from mongo_testtab;
ERROR: relation "mongo_testtab" does not exist
LINE 1: select * from mongo_testtab;

• mustaccio: Perhaps the app_user's search_path is set differently from that of postgres, and it looks for the objects in a different schema (namespace)?
• Laurenz Albe: You are connecting to different databases (foreign data wrappers don't live in a schema).
• TenG: @mustaccio is there a way to get the app_user to be able to create the server?
• TenG: @LaurenzAlbe perhaps if I could find a way to create the fdw server in the app_user database/schema that would help, but even granting CREATE SERVER TO app_user doesn't make it so.

Answer 1

The extension must be created as superuser, there is no way around that. When you create the extension, make sure that you connect to the correct database (not to postgres).

Then there are two options:

1. You grant the app_user the USAGE privilege on the FDW:

   GRANT USAGE ON FOREIGN DATA WRAPPER mongo_fdw TO app_user;
   

   Then you can create the foreign server as app_user.

   • advantage: you need fewer actions as superuser

   • disadvantage: you modify an object that belongs to the extension, which will get lost during dump/restore

2. You create the foreign server as superuser, then grant app_user the USAGE privilege on the foreign server:

   GRANT USAGE ON FOREIGN SERVER app_rem_db TO app_user;
   

   • advantage: you don't modify extension objects

   • disadvantage: more jobs to be done as superuser

• TenG: Thanks this helped. I switch to the app_users db \c app_db as postgres then ran the CREATE EXTENSION mongo_fdw WITH SCHEMA app_schem. Then connected as app_user and was then able to complete the process. Thanks for your help.