Error in Connector on MongoDB executing listIndex: "system.views"

[csellis]

Bug description

I'm trying to run npx prisma db pull and getting the following error:

Error: Error in connector: Error querying the database: Command failed (Unauthorized): not authorized on production to execute command { listIndexes: "system.views", $db: "production", lsid: { id: UUID("f2bcf8c5-711e-4cbc-a1d9-be5fb1d83311") }, $clusterTime: { clusterTime: Timestamp(1671864907, 1), signature: { hash: BinData(0, A4BB38EC8ED9DE2C1A3702C918C511366CC90261), keyId: 7130669438289838082 } }, $readPreference: { mode: "primary" } })

The connection string is correct and the user has full DB admin privileges. I can't find mention of "system.views" in the Prisma codebase.

How to reproduce

1. Run the command with 4.8.0 on MongoDB 4.4.18

Expected behavior

Expected to be able to import the Database

Prisma information

generator client {
  provider      = "prisma-client-js"
  binaryTargets = ["native"]
}

datasource db {
  provider = "mongodb"
  url      = env("DATABASE_URL")
}

Environment & setup

• OS: MacOS
• Database: MongoDB 4.4.18
• Node.js version: 16.18.0

Prisma Version

prisma                  : 4.8.0
@prisma/client          : 4.8.0
Current platform        : darwin-arm64
Query Engine (Node-API) : libquery-engine d6e67a83f971b175a593ccc12e15c4a757f93ffe (at ../../.nvm/versions/node/v16.18.0/lib/node_modules/prisma/node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
Migration Engine        : migration-engine-cli d6e67a83f971b175a593ccc12e15c4a757f93ffe (at ../../.nvm/versions/node/v16.18.0/lib/node_modules/prisma/node_modules/@prisma/engines/migration-engine-darwin-arm64)
Introspection Engine    : introspection-core d6e67a83f971b175a593ccc12e15c4a757f93ffe (at ../../.nvm/versions/node/v16.18.0/lib/node_modules/prisma/node_modules/@prisma/engines/introspection-engine-darwin-arm64)
Format Binary           : prisma-fmt d6e67a83f971b175a593ccc12e15c4a757f93ffe (at ../../.nvm/versions/node/v16.18.0/lib/node_modules/prisma/node_modules/@prisma/engines/prisma-fmt-darwin-arm64)
Format Wasm             : @prisma/prisma-fmt-wasm 4.8.0-61.d6e67a83f971b175a593ccc12e15c4a757f93ffe
Default Engines Hash    : d6e67a83f971b175a593ccc12e15c4a757f93ffe
Studio                  : 0.479.0

[jkomyno]

This issue may be correlated to #16179

[yannickschuchmann]

can confirm for npx prisma db push as well.

Environment & setup

• OS: MacOS
• Database: MongoDB 5.08 as replica set
• Node.js version: 16.15.0

Prisma version

prisma                  : 3.14.0
@prisma/client          : 3.14.0
Current platform        : darwin
Query Engine (Node-API) : libquery-engine 2b0c12756921c891fec4f68d9444e18c7d5d4a6a (at node_modules/@prisma/engines/libquery_engine-darwin.dylib.node)
Migration Engine        : migration-engine-cli 2b0c12756921c891fec4f68d9444e18c7d5d4a6a (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine    : introspection-core 2b0c12756921c891fec4f68d9444e18c7d5d4a6a (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary           : prisma-fmt 2b0c12756921c891fec4f68d9444e18c7d5d4a6a (at node_modules/@prisma/engines/prisma-fmt-darwin)
Default Engines Hash    : 2b0c12756921c891fec4f68d9444e18c7d5d4a6a
Studio                  : 0.460.0

Any idea of working around it for now?

[Jolg42]

You could try creating a role / adding privileges, so your user can read "system.views" like in https://dba.stackexchange.com/a/248945

[gfay63]

I can confirm this exact same issue. Essentially, if you have any views in your database, it makes introspection impossible. I even tried the suggestion recommended by @Jolg42, but MongoDB Atlas version 5.0 and above does not allow configuring a Custom Role with access to system.views.

[yannickschuchmann]

Thx @Jolg42.

I had to add the listIndexes action as well to the snippet from stackexchange. So what works for me in the meantime is:

use admin
db.runCommand({ createRole: "readViewCollection",
  privileges: [
    { resource: { db: "", collection: "system.views" }, actions: [ "find", "listIndexes"] }],
    roles : []
})
db.grantRolesToUser('<your_user>', ['readViewCollection']);

[gfay63]

@Jolg42 @yannickschuchmann

use admin
db.runCommand({ createRole: "readViewCollection",
  privileges: [
    { resource: { db: "", collection: "system.views" }, actions: [ "find", "listIndexes"] }],
    roles : []
})
db.grantRolesToUser('<your_user>', ['readViewCollection']);

The admin user can no longer access this collection as it is internal. This is documented here. This is the error you get for MongoDB 5.0 or higher:

MongoServerError: not authorized on admin to execute command { createRole: "readViewCollection", privileges: [ { resource: { db: "", collection: "system.views" }, actions: [ "find", "listIndexes" ] } ], roles: [], lsid: { id: UUID("7e2582f9-72ce-4ec1-8516-b995848d6011") }, $clusterTime: { clusterTime: Timestamp(1673288282, 1), signature: { hash: BinData(0, FFF638BF83E62A8FBF0B05BE2567B1D0825538D2), keyId: 7154416750556086274 } }, $db: "admin" }

Therefore, I currently see no workaround for version 5.0+ unless I am missing something.

[yannickschuchmann]

@gfay63 hmm I see. I'm on 5.08 but I created the role as root user, maybe that's allowed still. But also I'm running a self-hosted replicaset instead of Atlas service.

I'm curious what's the reason for the query to system.views by prisma tho. Can somebody elaborate on this maybe?

[gfay63]

Yeah it’s an Atlas restriction beginning with 5.0. I’m assuming it’s not intentional that it’s requiring a lookup to the system.views. Even creating a special “prism” user that only has access to the collections I want it to access, does not solve it. Hopefully someone can respond re: when might be fixed. For now, I removed all views. Needless to say, that is not desirable in the medium term.

…

On Tue, Jan 10, 2023 at 10:05 AM Yannick Schuchmann < ***@***.***> wrote: @gfay63 <https://github.com/gfay63> hmm I see. I'm on 5.08 but I created the role as root user, maybe that's allowed still. But also I'm running a self-hosted replicaset instead of Atlas service. I'm curious what's the reason for the query to system.views by prisma tho. Can somebody elaborate on this maybe? — Reply to this email directly, view it on GitHub <#17006 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABC4PO57L3ZT3U4WD6TWMM3WRWCC3ANCNFSM6AAAAAATIJ4DGE> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[janpio]

Next step is for someone from our side to confirm this, and if we can (which I expect) to implement the changes necessary to not look at the views (or whatever it needs to avoid this).

[robneal]

Have the same issue does any one know the status of getting a fix for this?

[jamiter]

If you're using Atlas, you can give your db user the atlasAdmin@admin role, which is a default available role. This will give the user the correct rights to run the command.

Of course, this is a security issue and should not be considered a valid workaround.

I hope a actual fix for this issue (and all other view related issues) will be available soon.

[maxmousse]

Hi everyone, any news on this ? I've seen the views are in the pipe, and it seems to be already available for postgres with a preview flag, so I'm right by guessing it should come any time soon ?

This bug and the #18424 are still pretty bloquing to work with mongo and introspection.

[FarhanMS123]

Hi! Is there any info regarding this? Could we ask Prisma to skip the system.views check?

[Jolg42]

That sounds like a good idea to me.
We unfortunately need to prioritize other things at the moment, and I can't guarantee that this will be fixed soon.

If you're interested, you could try to contribute, I think the relevant piece of code that needs a tweak is here:
https://github.com/prisma/prisma-engines/blob/3627f6a934756969c8af062d734c4fc8812b8c1b/schema-engine/mongodb-schema-describer/src/lib.rs#L22

[christianledgard]

@Jolg42, will a trivial continue work? Related to #18424.

[Jolg42]

Note: this is now fixed in 5.3.0-dev.9 if you want to try it out. Let us know if it works like expected.
It will be released in the official 5.3.0 version on Tuesday, September, 12th.

[maxmousse]

Seems to work for me! Thanks a lot for the fix =)

[FarhanMS123]

I use 5.3.0-dev.9 and it seems works fine for now. Thankyou.

[Jolg42]

Thanks for trying it out 🙌🏼 and Thanks to @christianledgard for the PR 💚