Authentication failed

[ArazureX]

When i create foreing table, i cant select from there and had error. Can you help please.

[surajkharage19]

Hi @ArazureX,

It would be good if you share the below details to investigate this issue further:

• The exact steps in detail performed at your end
• Mongo database version
• Postgres/EPAS version

[ArazureX]

Hi, I have collection in mongoDB and i need view it in posgreSQL. Its my collection : info-handler-server db.getCollection("tobo").find({}) "_id" : "fe88aaa5-9c7a-41d6-9e2a-076e87b3d876", "updatedTime" : ISODate("2023-04-20T19:01:28.020+0000"), "toboId" : NumberInt(18), "name" : "Від. 12 Вінниця Арх.Артинова 38", "divType" : NumberInt(2), "divisionId" : NumberInt(685), "defaultCashTypeId" : "M", "currentCashTypeId" : "M", "siteId" : NumberInt(300658), "alternateCode" : "12", "fmoperAccessId" : NumberInt(0), "syncTimeStamp" : ISODate("2009-11-09T10:01:40.000+0000"), "toboGroupId" : NumberInt(6), "regionalCenterId" : NumberInt(0), "deliveryProviderCode" : "Bn", "_class" : "text"

I create this foreign table like this:

CREATE FOREIGN TABLE "tobo" ( _id NAME, updatedTime timestamptz, toboId integer, name text, divType integer, divisionId integer, defaultCashTypeId text, currentCashTypeId text, siteId integer, alternateCode integer, fmoperAccessId integer, syncTimeStamp timestamptz, toboGroupId integer, regionalCenterId integer, deliveryProviderCode text, _class text ) SERVER mongo_server OPTIONS (database 'info-handler-server', collection 'tobo');

But, i can`t select this foreign table, and receive Top error, i think issue with wrong datatypes witch i use

i use PosgreSQL 12.2 & and mongoDB 4.4.14

[vaibhavdalvi93]

Thanks, @ArazureX for information. This doesn't look like a datatype issue to me. I think, this error is due to wrong username/password. Please cross check the username and password options provided in user mapping. If username and password is correct, then check if that user has read access to mentioned database.

Regards, Vaibhav Dalvi

[ArazureX]

I tested password and access on database, all is good

[ArazureX]

maybe problem in connection? we use 3 servers for one mongodb instance, but i add only one ip adress in mongo_fwd

[ArazureX]

I think I solved the connection problem by adding the authentication_database parameter

but now such an error occurs, but if you change the data of the _id field from NAME to text, the column will be displayed

[vaibhavdalvi93]

@ArazureX , good to know that you are able resolve connection problem. This error is because the data type of _id field is not an ObjectId. Currently, column type NAME is compatible only with BSON type ObjectId. As a workaround, you can continue to use text type for _id field. We may work on this in future.

Thanks, Vaibhav

[ArazureX]

Hello, i still have problem with data types, can you help with it? Some columns visible but some (null)

[vaibhavdalvi93]

@ArazureX , this issue is may be due to column data type incompatibility between BOSN type on MongoDB and foreign table column data type on Postgresql.

Could you please share mongo_fdw version you're using? E.g.

`#select mongo_fdw_version(); mongo_fdw_version

         50500

(1 row) `

Following is data type mappings:

Request to follow this data type mappings and if still issue persists then please report back.

Thanks.

[ArazureX]

[vaibhavdalvi93]

Thanks for the information. To further investigate this issue, could you please share the data type of remote collection fields and data type of foreign table columns of which value is returning as "null". If possible, please share the full foreign table definition and remote collection definition.