通过MongoDB BI Connector连接Cube Server遇用户权限拒绝问题

问题背景 #

使用MongoDB部署数据库，通过mongosqld（MongoDB BI Connector）将数据转换为可SQL查询的格式，但Cube Server在获取数据时触发权限拒绝错误。

相关配置信息 #

Mongosqld.config 文件 #

bindIp: "127.0.0.1"
port: 3307
ssl:
  mode: "allowSSL"
  allowInvalidCertificates: false
  PEMKeyFile: "/mongo.pem"
  CAFile: "/certificate.pem"
  minimumTLSVersion: TLS1_1

mongodb:
  net:
    uri: "mongodb://localhost:27017"
    ssl:
      enabled: false
    auth:
      username: "kp"
      password: "kp@123"
      source: "admin"

security:
  enabled: true
  defaultMechanism: "SCRAM-SHA-256"
  defaultSource: "admin"

processManagement:
  service:
    name: "mongosql"
    displayName: "MongoSQL Service"
    description: "MongoSQL accesses MongoDB data with SQL"

Cube 环境变量文件 #

# Cube.js environment variables
CUBEJS_DB_TYPE=mongobi
CUBEJS_API_SECRET=9c7a834d9be0cd2fb4e7b86a88a2bd1368ffbc614dcf8fa8f836933b00d00c6916fa83b074c66a2b26579266edc93e0e1990b1271f6214ee799dc08b05b6a097
CUBEJS_EXTERNAL_DEFAULT=true
CUBEJS_SCHEDULED_REFRESH_DEFAULT=true
CUBEJS_WEB_SOCKETS=true
CUBEJS_DB_HOST=127.0.0.1
CUBEJS_DB_NAME=(database name)
CUBEJS_DB_USER=kp
CUBEJS_DB_PASSWORD=kp@123
CUBEJS_DB_PORT=3307
CUBEJS_DEV_MODE=true
CUBEJS_REFRESH_WORKER=true
# port=4242
# NODE_ENV=production
CUBEJS_DB_SSL=true 
#CUBEJS_DB_SSL_CA=<SSL_CA>
#CUBEJS_DB_SSL_CERT=<SSL_CERT>
#CUBEJS_DB_SSL_CIPHERS=<SSL_CIPHERS>
#CUBEJS_DB_SSL_PASSPHRASE=<SSL_PASSPHRASE>
CUBEJS_DB_SSL_REJECT_UNAUTHORIZED=false

用户kp的角色配置 #

Successfully added user: 
{
   "user" : "kp",
   "roles" : [
     {
       "role" :"userAdminAnyDatabase",
       "db" : "admin"
     },
     {
       "role" :"readWriteAnyDatabase",
       "db" : "admin"
     },
     {
       "role" :"dbAdminAnyDatabase",
       "db" : "admin"
     }
  ]
} 

Mongosqld错误日志 #

2022-12-28T14:06:22.244+0530 I NETWORK    [conn5] connection accepted from 127.0.0.1:63039 #5 (1 connection now open)                                                                    
2022-12-28T14:06:22.277+0530 E NETWORK    [conn5] handshake error: unable to authenticate conversation 0: sasl conversation error: unable to authenticate using mechanism "SCRAM-SHA-256": (AuthenticationFailed) Authentication failed.
2022-12-28T14:06:22.279+0530 I NETWORK    [conn5] end connection 127.0.0.1:63039 (0 connections now open) 

Cube Server错误日志 #

Error while loading DB schema
Error: Access denied for user 'kp' at PromiseConnection.connect (E:\mongobiCube\node_modules\mysql2\promise.js:171:22) at Object.create (E:\mongobiCube\node_modules\@cubejs-backend\mongobi-driver\src\MongoBIDriver.ts:95:30) at MongoBIDriver.testConnection (E:\mongobiCube\node_modules\@cubejs-backend\mongobi-driver\src\MongoBIDriver.ts:165:63) at CubejsServerCore.getDriver (E:\mongobiCube\node_modules\@cubejs-backend\server-core\src\core\server.ts:727:20) at processTicksAndRejections

解决步骤 #

1. 验证MongoDB用户连接有效性
   手动执行MongoDB连接命令，确认kp用户能正常访问：

   mongo --host localhost:27017 -u kp -p kp@123 --authenticationDatabase admin
   

   如果连接失败，先确认用户密码正确性，以及MongoDB的用户角色是否已正确生效。

2. 对齐SSL配置

   • 将mongosqld配置中的ssl.mode修改为requireSSL，强制使用SSL连接
   • 调整Cube环境变量，取消CUBEJS_DB_SSL_CA的注释，填入mongosqld配置中CAFile对应的路径（Windows环境需调整为本地路径格式）

3. 指定Cube认证源
   在Cube环境变量中添加以下配置，明确使用admin库作为认证源：

   CUBEJS_DB_AUTH_SOURCE=admin
   

4. 匹配认证机制
   在Cube环境变量中添加配置，指定与mongosqld一致的认证机制：

   CUBEJS_DB_SASL_MECHANISM=SCRAM-SHA-256
   

5. 重启服务
   依次重启mongosqld服务和Cube Server，重新测试数据连接。

内容的提问来源于stack exchange，提问作者Mihir