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;
app_user'ssearch_pathis set differently from that ofpostgres, and it looks for the objects in a different schema (namespace)?app_userto be able to create the server?app_userdatabase/schema that would help, but even grantingCREATE SERVER TO app_userdoesn't make it so.