immich-app/immich
1
0
Fork 0
mirror of https://github.com/immich-app/immich.git synced 2026-02-05 00:30:57 +03:00
Code Issues 26 Packages Projects Releases 10 Wiki Activity

Missing view permissions when runnig pgvecto.rs and non postgres superadmin #2395

New Issue
Closed
opened 2026-02-05 06:02:18 +03:00 by OVERLORD · 0 comments
OVERLORD commented 2026-02-05 06:02:18 +03:00
Owner
Copy Link

Originally created by @nebulade on GitHub (Mar 4, 2024).

The bug

While we are working on changes to enable Immich package upgrades in Cloudron to use the 0.2.* pgvecto.rs extension, we were ruinning in a permission issue for one view only (after the vectors schema and upgrade docs are followed): trying to SELECT pg_vector_index_stat which by default is not accessible by non superusers.

The fix would be to run GRANT SELECT ON TABLE pg_vector_index_stat to ${dbuser}; which needs to be run after Immich has started up and ran the db migration scripts to create the extension in the first place. Which makes this not too practical as the users have to start the app, kill it, run the SQL command as superuser, then start the app again.

The code in question here does only exist to check for some required reindexing it seems: https://github.com/immich-app/immich/blob/main/server/src/infra/repositories/database.repository.ts#L135

So I wonder if this is entirely related to how Cloudron isolates app database in postgres or if other users which don't run it with postgres superuser credentials also hit this? Would there be any other way to detect the index freshness?

The error would be:

[Nest] 149  - 03/04/2024, 7:12:29 PM    WARN [DatabaseService] Could not run vector reindexing checks. If the extension was updated, please restart the Postgres instance.
/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:219
            throw new QueryFailedError_1.QueryFailedError(query, parameters, err);
                  ^

QueryFailedError: permission denied for view pg_vector_index_stat
    at PostgresQueryRunner.query (/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:219:19)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async DataSource.query (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:350:20)
    at async DatabaseRepository.shouldReindex (/usr/src/app/dist/infra/repositories/database.repository.js:127:25)
    at async /usr/src/app/dist/domain/database/database.service.js:41:21
    at async /usr/src/app/dist/infra/repositories/database.repository.js:185:23 {
  query: '\n' +
    '          SELECT idx_status\n' +
    '          FROM pg_vector_index_stat\n' +
    '          WHERE indexname = $1',
  parameters: [ 'clip_index' ],
  driverError: error: permission denied for view pg_vector_index_stat

The OS that Immich Server is running on

Cloudron

Version of Immich Server

v1.97.0

Version of Immich Mobile App

latest

Platform with the issue

  • Server
  • Web
  • Mobile

Your docker-compose.yml content

does not apply

Your .env content

does not apply

Reproduction steps

does not apply

Additional information

No response

Originally created by @nebulade on GitHub (Mar 4, 2024). ### The bug While we are working on changes to enable Immich package upgrades in Cloudron to use the 0.2.* pgvecto.rs extension, we were ruinning in a permission issue for one view only (after the `vectors` schema and upgrade docs are followed): trying to SELECT `pg_vector_index_stat` which by default is not accessible by non superusers. The fix would be to run `GRANT SELECT ON TABLE pg_vector_index_stat to ${dbuser};` which needs to be run __after__ Immich has started up and ran the db migration scripts to create the extension in the first place. Which makes this not too practical as the users have to start the app, kill it, run the SQL command as superuser, then start the app again. The code in question here does only exist to check for some required reindexing it seems: https://github.com/immich-app/immich/blob/main/server/src/infra/repositories/database.repository.ts#L135 So I wonder if this is entirely related to how Cloudron isolates app database in postgres or if other users which don't run it with postgres superuser credentials also hit this? Would there be any other way to detect the index freshness? The error would be: ``` [Nest] 149 - 03/04/2024, 7:12:29 PM WARN [DatabaseService] Could not run vector reindexing checks. If the extension was updated, please restart the Postgres instance. /usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:219 throw new QueryFailedError_1.QueryFailedError(query, parameters, err); ^ QueryFailedError: permission denied for view pg_vector_index_stat at PostgresQueryRunner.query (/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:219:19) at process.processTicksAndRejections (node:internal/process/task_queues:95:5) at async DataSource.query (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:350:20) at async DatabaseRepository.shouldReindex (/usr/src/app/dist/infra/repositories/database.repository.js:127:25) at async /usr/src/app/dist/domain/database/database.service.js:41:21 at async /usr/src/app/dist/infra/repositories/database.repository.js:185:23 { query: '\n' + ' SELECT idx_status\n' + ' FROM pg_vector_index_stat\n' + ' WHERE indexname = $1', parameters: [ 'clip_index' ], driverError: error: permission denied for view pg_vector_index_stat ``` ### The OS that Immich Server is running on Cloudron ### Version of Immich Server v1.97.0 ### Version of Immich Mobile App latest ### Platform with the issue - [X] Server - [ ] Web - [ ] Mobile ### Your docker-compose.yml content ```YAML does not apply ``` ### Your .env content ```Shell does not apply ``` ### Reproduction steps ```bash does not apply ``` ### Additional information _No response_
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
accessibility
changelog:enhancement
changelog:security
changelog:skip
changelog:translation
cli
date-time
documentation
external-library
format
good first issue
needs-answer
nice to have
sharing
tech-debt
📱mobile
🖥️web
🗄️server
🧠machine-learning
No Label
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
OVERLORD
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: immich-app/immich#2395
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?