Process for setting up immich on shared postgres without super user?

[cbc02009]

Hello,

Is there any documentation around setting up a new immich instance that uses a shared monolithic postgres db and doesn't have superuser privileges? I've been trying PG commands from changelog notes and error messages from immich microservices, but I still keep getting errors and I think it may be because I'm not running the right commands or running them as the correct user.

If it's helpful, I'm setting up immich on a flux-controlled k8s cluster. The immich setup is available here

I have run the following commands as a superuser as described in the error messages:

CREATE EXTENSION IF NOT EXISTS vectors;
CREATE EXTENSION IF NOT EXISTS earthdistance CASCADE;

and now I am getting the following error and don't know how to proceed:

[Nest] 7  - 03/28/2024, 12:21:52 PM     LOG [CommunicationRepository] Initialized websocket server
Migration "UsePgVectors1700713871511" failed, error: type "vector" does not exist
/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:219
            throw new QueryFailedError_1.QueryFailedError(query, parameters, err);
                  ^

QueryFailedError: type "vector" does not exist
    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 UsePgVectors1700713871511.up (/usr/src/app/dist/infra/migrations/1700713871511-UsePgVectors.js:19:9)
    at async MigrationExecutor.executePendingMigrations (/usr/src/app/node_modules/typeorm/migration/MigrationExecutor.js:225:17)
    at async DataSource.runMigrations (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:265:35)
    at async DatabaseRepository.runMigrations (/usr/src/app/dist/infra/repositories/database.repository.js:178:9)
    at async /usr/src/app/dist/domain/database/database.service.js:52:13
    at async /usr/src/app/dist/infra/repositories/database.repository.js:186:23 {
  query: '\n' +
    '        ALTER TABLE asset_faces \n' +
    '        ALTER COLUMN embedding SET NOT NULL,\n' +
    '        ALTER COLUMN embedding TYPE vector(512)',
  parameters: undefined,
  driverError: error: type "vector" does not exist
      at /usr/src/app/node_modules/pg/lib/client.js:526:17
      at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
      at async PostgresQueryRunner.query (/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:184:25)
      at async UsePgVectors1700713871511.up (/usr/src/app/dist/infra/migrations/1700713871511-UsePgVectors.js:19:9)
      at async MigrationExecutor.executePendingMigrations (/usr/src/app/node_modules/typeorm/migration/MigrationExecutor.js:225:17)
      at async DataSource.runMigrations (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:265:35)
      at async DatabaseRepository.runMigrations (/usr/src/app/dist/infra/repositories/database.repository.js:178:9)
      at async /usr/src/app/dist/domain/database/database.service.js:52:13
      at async /usr/src/app/dist/infra/repositories/database.repository.js:186:23 {
    length: 89,
    severity: 'ERROR',
    code: '42704',
    detail: undefined,
    hint: undefined,
    position: undefined,
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: undefined,
    table: undefined,
    column: undefined,
    dataType: undefined,
    constraint: undefined,
    file: 'parse_type.c',
    line: '270',
    routine: 'typenameType'
  },
  length: 89,
  severity: 'ERROR',
  code: '42704',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_type.c',
  line: '270',
  routine: 'typenameType'
}

Any help would be greatly appreciated, thanks!

[mmomjian]

Please connect to the immich DB and run \dx and \dz, post the results.

[mmomjian]

Try this one next: GRANT SELECT ON SCHEMA vectors TO public;

[cbc02009]

It doesn't seem to like that command.

immich=# GRANT SELECT ON SCHEMA vectors TO public;
ERROR:  invalid privilege type SELECT for schema

[mmomjian]

Sorry, my memory is failing me. Refer to my earlier comment from the 1.95 release: #7252 (reply in thread)

try
GRANT ALL ON SCHEMA vectors TO <immich username>;

please let us know if that works. If not, try GRANT SELECT ON TABLE pg_vector_index_stat to <immich username>;

[cbc02009]

Thanks!

GRANT ALL ON SCHEMA vectors TO <immich username>;
GRANT SELECT ON TABLE pg_vector_index_stat to <immich username>;

finally did the trick!

So the final full list of commands is:

CREATE EXTENSION IF NOT EXISTS vectors;
CREATE EXTENSION IF NOT EXISTS earthdistance CASCADE;
ALTER DATABASE immich SET search_path TO "$user", public, vectors;
GRANT ALL ON SCHEMA vectors TO <immich username>;
GRANT SELECT ON TABLE pg_vector_index_stat to <immich username>;

It's possible one or more of them are unnecessary, but this worked for me.

[mmomjian]

Great! I think the unnecessary one is ALTER SCHEMA vectors OWNER TO <immich username>; - this is covered by GRANT ALL.

[cbc02009]

Thanks, I will edit that one out

[mertalev]

GRANT USAGE is probably what was missing before owning the schema.

[bo0tzz]

Some of the other things that may be necessary for running Immich without postgres superuser:
Setting the search_path
Appropriate permissions (or ownership?) on the vectors schema

See also #7431

@mertalev I'm hoping that we can figure out exactly what is necessary here so we can properly document it. Do you have any other things to add?

[mmomjian]

So it looks like the final list of commands (at the moment) is:

\c <immich db>
ALTER DATABASE <immich db> OWNER TO <immich username>;
CREATE EXTENSION IF NOT EXISTS vectors;
CREATE EXTENSION IF NOT EXISTS earthdistance CASCADE;
ALTER DATABASE <immich db> SET search_path TO "$user", public, vectors;
GRANT USAGE ON SCHEMA vectors TO <immich username>;
GRANT SELECT ON TABLE pg_vector_index_stat to <immich username>;

We may need to start the Immich server before running the last command? I'm unsure if pg_vector_index_stat exists before the vecto.rs tables are created.

[mertalev]

I'm not sure either. Would GRANT SELECT ON ALL TABLES IN SCHEMA vectors take care of it if it doesn't exist yet?

[mmomjian]

I'm thinking ALTER DEFAULT PRIVILEGES IN SCHEMA vectors GRANT SELECT ON TABLES TO PUBLIC;? link

[mertalev]

That looks good, but it would only apply to new tables.

Actually, I think this table would exist after creating the extension since we query it before any migrations are run.

[mmomjian]

Exactly, it would only apply moving forward. This is the source of my confusion as I am not sure exactly when that table exists - if it indeed exists as soon as the vecto.rs extension is added, the GRANT SELECT ON TABLE pg_vector_index_stat to <immich username>; should work. Just hard to test. Lol