Unable to Authenticate with Current Version

[MarkCWirt]

Environment

Operating System	Ubuntu 14.04
PostgreSQL Version	9.5.3
MongoDB Version	3.0.7 and 3.2.6
FDW Version	Compiled from Github
Mongo Connector	--with-master
Execution Environment	Docker Container

Problem Description

I am able to successfully create a foreign table, but only if authentication on the database is turned off.

As an example, I can execute the following in psql:

  CREATE EXTENSION mongo_fdw;

  CREATE SERVER mongo_server_local
           FOREIGN DATA WRAPPER mongo_fdw
           OPTIONS (address '172.17.0.1', port '27027');

  CREATE USER MAPPING FOR postgres
           SERVER mongo_server_local;

  CREATE FOREIGN TABLE test (
      _id NAME,
      ID int,
      comment text)
      SERVER mongo_server_local
          OPTIONS (database 'test', collection 'test');

And I am able to query the table:

mongo=# select * from test;
           _id            | id |    comment     
--------------------------+----+----------------
 57a37f6d72077321c749ba09 |    | This is a test
 57a37f7b72077321c749ba0a |    | This is a test
 57a37f8772077321c749ba0b |    | This is a test
(3 rows)

Then I create a user (and to avoid permission problems this user is basically an administrator):

db.createUser(
  {
    user: "test",
    pwd: "test",
    roles: [ { role: "userAdminAnyDatabase", db: "admin" },
             { role: "readWriteAnyDatabase", db: "admin" },
             { role: "dbAdminAnyDatabase",   db: "admin" },
             { role: "clusterAdmin", db: "admin" }
    ]
  }
)

I have created this user in both the admin database -- which is standard -- and the test database.

When I then turn on authorization on the server and recreate the user mapping:

mongo=# drop user mapping for postgres server mongo_server_local;
DROP USER MAPPING
mongo=# CREATE USER MAPPING FOR postgres
mongo-#          SERVER mongo_server_local
mongo-#          OPTIONS (username 'test', password 'test');
CREATE USER MAPPING
mongo=# \deu+
                        List of user mappings
       Server       | User name |            FDW Options             
--------------------+-----------+------------------------------------
 mongo_server_local | postgres  | (username 'test', password 'test')
(1 row)

I receive no data back:

mongo=# select * from test;
 _id | id | comment
-----+----+---------
(0 rows)

I have verified that this account works with other tools, such as robomongo (authenticating both against the admin and test databases).

The server logs give no useful information. I can see the connection attempt being made but no indication of success or failure:

2016-08-04T18:59:24.931+0000 I NETWORK  [initandlisten] connection accepted from 172.17.0.1:47522 #9 (1 connection now open)
2016-08-04T18:59:24.935+0000 I NETWORK  [conn9] end connection 172.17.0.1:47522 (0 connections now open)
2016-08-04T18:59:24.935+0000 I NETWORK  [initandlisten] connection accepted from 172.17.0.1:47526 #10 (2 connections now open)
2016-08-04T18:59:24.936+0000 I NETWORK  [conn10] end connection 172.17.0.1:47526 (0 connections now open)
2016-08-04T18:59:24.936+0000 I NETWORK  [initandlisten] connection accepted from 172.17.0.1:47530 #11 (1 connection now open)
2016-08-04T18:59:24.937+0000 I NETWORK  [conn11] end connection 172.17.0.1:47530 (0 connections now open)

Any guidance or insight would be appreciated!

[MarkCWirt]

OK, by looking though the PostgreSQL log files I think I found the issue.

The SCRAM-SHA-1 authentication mechanism (which is the default for MongoDB 3+) requires SSL to make a connection.

You may want to consider changing the autogen.sh file to include this when the library is compiled and installed, i.e.:

function install_mongoc_driver
{
    cd mongo-c-driver
    ./configure --with-libbson=auto --enable-ssl
    make install
    cd ..
}

or at least consider mentioning it in the documentation.

Thank you! I appreciate the hard work on the wrapper.

[lifubang]

I think it's not the real reason. First, you need to confirm that the mongo server is start with --auth params? I think that in your first step you create the mapping user without username and password options, but you can query and have a result. so your mongo server didn't start with --auth params. In your second step, you create a user, so you must restart mongo with --auth params. If you restart the mongo, this version of mongo_fdw has a connection pool error. You can refer it in #60 .

[MarkCWirt]

Well, all I can say is that I got this message in my PG log:

    2016/08/04 18:59:24.0937: [   41]:    DEBUG:      cluster: Authentication failed: The "SCRAM-SHA-1" authentication mechanism requires libmongoc built with --enable-ssl

Which went away when I compiled with --enable-ssl, and at the same time it started working.

(And yes, I did go through those two steps, starting with and without --auth)

[ahsanhadi]

So the issue is resolved after you compile PG with --enable-ssl?

[MarkCWirt]

Yes, it did.