Xata•2mo ago

Drizzle pull/push: Permission denied for view pg_stat_user_indexes

Hi 👋 Sorry to bother you again. Quick question: After a long while I'm using the drizzle commands again and now I'm receiving the following error:

XATA_BRANCH test-template2
Pulling from ['public'] list of schemas

Using 'pg' driver for database querying
[✓] 15  tables fetched
[⣻] 207 columns fetching
[✓] 0   enums fetched
[⣻] 0   indexes fetching
[⣻] 0   foreign keys fetching
[✓] 0   policies fetched
[⣻] 0   check constraints fetching
[⣻] 0   views fetching
error: permission denied for view pg_stat_user_indexes
    at /home/michael/repos/monorepo/node_modules/.pnpm/[email protected][email protected]/node_modules/pg-pool/index.js:45:11
    at process.processTicksAndRejections (node:internal/process/task_queues:105:5)
    at async Object.query (/home/michael/repos/monorepo/node_modules/.pnpm/[email protected][email protected]_@[email protected]_@[email protected]_@t_4xfdxdbonwo2l5u4y5cl47zhfu/node_modules/drizzle-kit/bin.cjs:78762:26)
    at async <anonymous> (/home/michael/repos/monorepo/node_modules/.pnpm/[email protected][email protected]_@[email protected]_@[email protected]_@t_4xfdxdbonwo2l5u4y5cl47zhfu/node_modules/drizzle-kit/bin.cjs:37743:43) {
  length: 107,
  severity: 'ERROR',
  code: '42501',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'aclchk.c',
  line: '3770',
  routine: 'aclcheck_error'
}

XATA_BRANCH test-template2
Pulling from ['public'] list of schemas

Using 'pg' driver for database querying
[✓] 15  tables fetched
[⣻] 207 columns fetching
[✓] 0   enums fetched
[⣻] 0   indexes fetching
[⣻] 0   foreign keys fetching
[✓] 0   policies fetched
[⣻] 0   check constraints fetching
[⣻] 0   views fetching
error: permission denied for view pg_stat_user_indexes
    at /home/michael/repos/monorepo/node_modules/.pnpm/[email protected][email protected]/node_modules/pg-pool/index.js:45:11
    at process.processTicksAndRejections (node:internal/process/task_queues:105:5)
    at async Object.query (/home/michael/repos/monorepo/node_modules/.pnpm/[email protected][email protected]_@[email protected]_@[email protected]_@t_4xfdxdbonwo2l5u4y5cl47zhfu/node_modules/drizzle-kit/bin.cjs:78762:26)
    at async <anonymous> (/home/michael/repos/monorepo/node_modules/.pnpm/[email protected][email protected]_@[email protected]_@[email protected]_@t_4xfdxdbonwo2l5u4y5cl47zhfu/node_modules/drizzle-kit/bin.cjs:37743:43) {
  length: 107,
  severity: 'ERROR',
  code: '42501',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'aclchk.c',
  line: '3770',
  routine: 'aclcheck_error'
}

It seems like a Xata permission issue. But I'm not sure if the error is on drizzle's side.

5 Replies

cmck•2mo ago

Hi Michael, I'll take a look and see what's going on 👍 Thanks for bearing with me, I've been able to recreate the issue with one of my databases. Interestingly, introspection runs fine with one but not the other. There might be a missing permission somewhere so I'll take a closer look.

Michael SchaufelbergerOP•2mo ago

Thank you! Hi I'm still experiencing this issue with test-template2. It's not high-prio, since I can use drizzle-kit generate for the time being - but I would love to quickly get a "diff" of the schema versions using drizzle.

cmck•5w ago

Hi Michael, thanks for reminding me about this, I'll take another look, apologies for the delay! Hey Michael, thanks for bearing with me, I'm back to looking at this. As before, I've been able to get drizzle pull working with some databases but not with others. I'm putting some time aside today to play "spot the difference" in the schemas and permissions. Thanks for your patience.

Michael SchaufelbergerOP•5w ago

Hi cmck Thanks for looking into it. I've found that drizzle-kit uses the pg_stat_user_indexes to find all indexes in the db. https://github.com/drizzle-team/drizzle-orm/blob/e5c63db0df0eaff5cae8321d97a77e5b47c5800d/drizzle-kit/src/serializer/pgSerializer.ts#L1572-L1583 But afaik this runs for all the tables found FROM pg_catalog.pg_class c if the relkind = 'r' (https://github.com/drizzle-team/drizzle-orm/blob/e5c63db0df0eaff5cae8321d97a77e5b47c5800d/drizzle-kit/src/serializer/pgSerializer.ts#L985-L1002). Which returns just the public's schema tables when drizzle-kit runs it. Maybe that helps...

GitHub

drizzle-orm/drizzle-kit/src/serializer/pgSerializer.ts at e5c63db0d...

Headless TypeScript ORM with a head. Runs on Node, Bun and Deno. Lives on the Edge and yes, it's a JavaScript ORM too 😅 - drizzle-team/drizzle-orm

cmck•4w ago

Hi Michael, that is a useful insight. After some testing I was able to get introspection to work on an otherwise uncooperative database by exporting it with pg_dump then reimporting it with psql. I've raised the issue with the team but they're currently working on pgroll which they hope to have similar diff and introspection capabilities. I can't give an ETA on when a fix will be available.

Gaming

Programming

Drizzle pull/push: Permission denied for view pg_stat_user_indexes

Did you find this page helpful?