langgenius/dify
1
0
Fork 0
mirror of https://github.com/langgenius/dify.git synced 2026-07-21 12:05:30 -04:00
Code Issues 775 Packages Projects Releases 10 Wiki Activity

PGVector fails with InsufficientPrivilege on Azure PostgreSQL when creating already-installed vector extension with non-admin user #20716

New Issue
Closed
opened 2026-02-21 20:08:36 -05:00 by yindo · 7 comments
yindo commented 2026-02-21 20:08:36 -05:00
Owner
Copy Link
Copy Source

Originally created by @nourmami on GitHub (Dec 1, 2025).

Self Checks

  • I have read the Contributing Guide and Language Policy.
  • This is only for bug report, if you would like to ask a question, please head to Discussions.
  • I have searched for existing issues search for existing issues, including closed ones.
  • I confirm that I am using English to submit this report, otherwise it will be closed.
  • 【中文用户 & Non English User】请使用英语提交，否则会被关闭 ：）
  • Please do not modify this template :) and fill in all the required fields.

Dify version

1.9.1

Cloud or Self Hosted

Self Hosted (Docker)

Steps to reproduce

  1. Deploy Dify 1.9.1 on Kubernetes using Helm chart
  2. Set up Azure PostgreSQL Flexible Server (version 15.13) as the PGVector store
  3. As a database administrator (with azure_pg_admin role):
    • Install the vector extension in the target database: CREATE EXTENSION vector;
    • Create a database user dify_user with standard privileges (NOT azure_pg_admin)
    • Grant necessary database privileges to dify_user
  4. Configure Dify to connect with the non-admin dify_user
  5. Attempt to add knowledge/documents to a knowledge base through the Dify UI
  6. Document processing fails during vector storage creation with InsufficientPrivilege error

✔️ Expected Behavior

Dify should successfully create vector tables and store embeddings when the vector extension is already installed, without requiring the database user to have extension creation privileges.

❌ Actual Behavior

Image

The application crashes because it unconditionally attempts to execute CREATE EXTENSION IF NOT EXISTS vector on line 258 of api/core/rag/datasource/vdb/pgvector/pgvector.py, which requires azure_pg_admin role membership on Azure PostgreSQL Flexible Server, even when the extension already exists.
Error Message:

worker 2025-12-01 10:51:49,950.950 ERROR [Dummy-366] [indexing_runner.py:97] - consume document failed
Traceback (most recent call last):
File "/app/api/core/indexing_runner.py", line 81, in run
self.load(
File "/app/api/core/indexing_runner.py", line 571, in load
tokens += future.result()
File "/usr/local/lib/python3.12/concurrent/futures/base.py", line 456, in result
return self.get_result()
File "/usr/local/lib/python3.12/concurrent/futures/base.py", line 401, in get_result
raise self.exception
File "/usr/local/lib/python3.12/concurrent/futures/thread.py", line 59, in run
result = self.fn(self.args, self.kwargs)
File "/app/api/core/indexing_runner.py", line 630, in process_chunk
index_processor.load(dataset, chunk_documents, with_keywords=False)
File "/app/api/core/rag/index_processor/processor/paragraph_index_processor.py", line 82, in load
vector.create(documents)
File "/app/api/core/rag/datasource/vdb/vector_factory.py", line 197, in create
self.vector_processor.create(texts=batch, embeddings=batch_embeddings, kwargs)
File "/app/api/core/rag/datasource/vdb/pgvector/pgvector.py", line 112, in create
self.create_collection(dimension)
File "/app/api/core/rag/datasource/vdb/pgvector/pgvector.py", line 258, in create_collection
cur.execute("CREATE EXTENSION IF NOT EXISTS vector")
psycopg2.errors.InsufficientPrivilege: Because vector isn't a trusted extension, only members of "azure_pg_admin" are allowed to use CREATE EXTENSION vector
HINT: to learn how to allow an extension or see the list of allowed extensions, please refer to https://go.microsoft.com/fwlink/?linkid=2301063
Originally created by @nourmami on GitHub (Dec 1, 2025). ### Self Checks - [x] I have read the [Contributing Guide](https://github.com/langgenius/dify/blob/main/CONTRIBUTING.md) and [Language Policy](https://github.com/langgenius/dify/issues/1542). - [x] This is only for bug report, if you would like to ask a question, please head to [Discussions](https://github.com/langgenius/dify/discussions/categories/general). - [x] I have searched for existing issues [search for existing issues](https://github.com/langgenius/dify/issues), including closed ones. - [x] I confirm that I am using English to submit this report, otherwise it will be closed. - [x] 【中文用户 & Non English User】请使用英语提交，否则会被关闭 ：） - [x] Please do not modify this template :) and fill in all the required fields. ### Dify version 1.9.1 ### Cloud or Self Hosted Self Hosted (Docker) ### Steps to reproduce 1. Deploy Dify 1.9.1 on Kubernetes using Helm chart 2. Set up Azure PostgreSQL Flexible Server (version 15.13) as the PGVector store 3. As a database administrator (with `azure_pg_admin` role): - Install the `vector` extension in the target database: `CREATE EXTENSION vector;` - Create a database user `dify_user` with standard privileges (NOT `azure_pg_admin`) - Grant necessary database privileges to `dify_user` 4. Configure Dify to connect with the non-admin `dify_user` 5. Attempt to add knowledge/documents to a knowledge base through the Dify UI 6. Document processing fails during vector storage creation with `InsufficientPrivilege` error ### ✔️ Expected Behavior Dify should successfully create vector tables and store embeddings when the `vector` extension is already installed, without requiring the database user to have extension creation privileges. ### ❌ Actual Behavior <img width="326" height="132" alt="Image" src="https://github.com/user-attachments/assets/292264d5-e949-42d3-be1d-70ec3f275783" /> The application crashes because it unconditionally attempts to execute `CREATE EXTENSION IF NOT EXISTS vector` on [line 258 of `api/core/rag/datasource/vdb/pgvector/pgvector.py`](https://github.com/langgenius/dify/blob/1.9.1/api/core/rag/datasource/vdb/pgvector/pgvector.py#L258), which requires `azure_pg_admin` role membership on Azure PostgreSQL Flexible Server, even when the extension already exists. **Error** Message: ``` worker 2025-12-01 10:51:49,950.950 ERROR [Dummy-366] [indexing_runner.py:97] - consume document failed Traceback (most recent call last): File "/app/api/core/indexing_runner.py", line 81, in run self.load( File "/app/api/core/indexing_runner.py", line 571, in load tokens += future.result() File "/usr/local/lib/python3.12/concurrent/futures/base.py", line 456, in result return self.get_result() File "/usr/local/lib/python3.12/concurrent/futures/base.py", line 401, in get_result raise self.exception File "/usr/local/lib/python3.12/concurrent/futures/thread.py", line 59, in run result = self.fn(self.args, self.kwargs) File "/app/api/core/indexing_runner.py", line 630, in process_chunk index_processor.load(dataset, chunk_documents, with_keywords=False) File "/app/api/core/rag/index_processor/processor/paragraph_index_processor.py", line 82, in load vector.create(documents) File "/app/api/core/rag/datasource/vdb/vector_factory.py", line 197, in create self.vector_processor.create(texts=batch, embeddings=batch_embeddings, kwargs) File "/app/api/core/rag/datasource/vdb/pgvector/pgvector.py", line 112, in create self.create_collection(dimension) File "/app/api/core/rag/datasource/vdb/pgvector/pgvector.py", line 258, in create_collection cur.execute("CREATE EXTENSION IF NOT EXISTS vector") psycopg2.errors.InsufficientPrivilege: Because vector isn't a trusted extension, only members of "azure_pg_admin" are allowed to use CREATE EXTENSION vector HINT: to learn how to allow an extension or see the list of allowed extensions, please refer to https://go.microsoft.com/fwlink/?linkid=2301063 ```
yindo added the 🐞 bug label 2026-02-21 20:08:36 -05:00
yindo closed this issue 2026-02-21 20:08:36 -05:00
yindo commented 2026-02-21 20:08:36 -05:00
Author
Owner
Copy Link
Copy Source

@dosubot[bot] commented on GitHub (Dec 1, 2025):

Hi @nourmami! I'm Dosu and I’m helping the dify team.

This error happens because Dify always runs CREATE EXTENSION IF NOT EXISTS vector in pgvector.py when creating a collection, but on Azure PostgreSQL only users with the azure_pg_admin role can create extensions—even if the extension is already installed. Non-admin users hit an InsufficientPrivilege error and the app crashes, even though the extension exists and is ready to use. There’s no config or workaround built into Dify 1.9.1 for this.

To fix this, you can patch the _create_collection method in api/core/rag/datasource/vdb/pgvector/pgvector.py to check if the extension exists before trying to create it. Here’s a code snippet you can use:

with self._get_cursor() as cur:
    cur.execute("SELECT 1 FROM pg_extension WHERE extname = 'vector'")
    if not cur.fetchone():
        cur.execute("CREATE EXTENSION vector")
    # continue with table/index creation as before

Replace the unconditional cur.execute("CREATE EXTENSION IF NOT EXISTS vector") with this check. This way, the extension creation is only attempted if it’s actually missing, avoiding privilege errors for non-admin users on managed cloud databases like Azure PostgreSQL. The rest of the collection creation logic can remain unchanged.

There’s currently no official fix or config option for this in Dify, so this patch is the best way to avoid the crash until upstream support is added.

Reference: Dify source code and workaround details

To reply, just mention @dosu.

How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other

Chat with Dosu Open in Cursor Join Discord Share on X

@dosubot[bot] commented on GitHub (Dec 1, 2025): <!-- Greeting --> Hi @nourmami! I'm [Dosu](https://go.dosu.dev/dosubot) and I’m helping the dify team. <!-- Answer --> This error happens because Dify always runs `CREATE EXTENSION IF NOT EXISTS vector` in `pgvector.py` when creating a collection, but on Azure PostgreSQL only users with the `azure_pg_admin` role can create extensions—even if the extension is already installed. Non-admin users hit an InsufficientPrivilege error and the app crashes, even though the extension exists and is ready to use. There’s no config or workaround built into Dify 1.9.1 for this. To fix this, you can patch the `_create_collection` method in `api/core/rag/datasource/vdb/pgvector/pgvector.py` to check if the extension exists before trying to create it. Here’s a code snippet you can use: ```python with self._get_cursor() as cur: cur.execute("SELECT 1 FROM pg_extension WHERE extname = 'vector'") if not cur.fetchone(): cur.execute("CREATE EXTENSION vector") # continue with table/index creation as before ``` Replace the unconditional `cur.execute("CREATE EXTENSION IF NOT EXISTS vector")` with this check. This way, the extension creation is only attempted if it’s actually missing, avoiding privilege errors for non-admin users on managed cloud databases like Azure PostgreSQL. The rest of the collection creation logic can remain unchanged. There’s currently no official fix or config option for this in Dify, so this patch is the best way to avoid the crash until upstream support is added. Reference: [Dify source code and workaround details](https://github.com/langgenius/dify/blob/0a22bc5d05160afa0334e620a333699af1e2e2c0/api/core/rag/datasource/vdb/pgvector/pgvector.py#L100-L267) <!-- Dosu Comment Footer --> *To reply, just mention [@dosu](https://go.dosu.dev/dosubot).* --- <sup>How did I do? [Good](https://app.dosu.dev/response-feedback/ac0325dd-accd-46a2-a63b-bdda44eefe7a?feedback_type=great_response) | [Irrelevant](https://app.dosu.dev/response-feedback/ac0325dd-accd-46a2-a63b-bdda44eefe7a?feedback_type=irrelevant_answer) | [Incorrect](https://app.dosu.dev/response-feedback/ac0325dd-accd-46a2-a63b-bdda44eefe7a?feedback_type=incorrect_sources) | [Verbose](https://app.dosu.dev/response-feedback/ac0325dd-accd-46a2-a63b-bdda44eefe7a?feedback_type=too_verbose) | [Hallucination](https://app.dosu.dev/response-feedback/ac0325dd-accd-46a2-a63b-bdda44eefe7a?feedback_type=hallucination) | [Report 🐛](https://app.dosu.dev/response-feedback/ac0325dd-accd-46a2-a63b-bdda44eefe7a?feedback_type=bug_report) | [Other](https://app.dosu.dev/response-feedback/ac0325dd-accd-46a2-a63b-bdda44eefe7a?feedback_type=other)</sup> [![Chat with Dosu](https://dosu.dev/dosu-chat-badge.svg)](https://app.dosu.dev/6088a4f4-073a-43f3-b3d4-4f06fb0c05a0/ask?utm_source=github)&nbsp;[![Open in Cursor](https://dosu.dev/dosu-in-cursor.svg)](https://cursor.com/link/prompt?text=This%20error%20happens%20because%20Dify%20always%20runs%20%60CREATE%20EXTENSION%20IF%20NOT%20EXISTS%20vector%60%20in%20%60pgvector.py%60%20when%20creating%20a%20collection%2C%20but%20on%20Azure%20PostgreSQL%20only%20users%20with%20the%20%60azure_pg_admin%60%20role%20can%20create%20extensions%E2%80%94even%20if%20the%20extension%20is%20already%20installed.%20Non-admin%20users%20hit%20an%20InsufficientPrivilege%20error%20and%20the%20app%20crashes%2C%20even%20though%20the%20extension%20exists%20and%20is%20ready%20to%20use.%20There%E2%80%99s%20no%20config%20or%20workaround%20built%20into%20Dify%201.9.1%20for%20this.%0A%0ATo%20fix%20this%2C%20you%20can%20patch%20the%20%60_create_collection%60%20method%20in%20%60api/core/rag/datasource/vdb/pgvector/pgvector.py%60%20to%20check%20if%20the%20extension%20exists%20before%20trying%20to%20create%20it.%20Here%E2%80%99s%20a%20code%20snippet%20you%20can%20use%3A%0A%0A%60%60%60python%0Awith%20self._get_cursor%28%29%20as%20cur%3A%0A%20%20%20%20cur.execute%28%22SELECT%201%20FROM%20pg_extension%20WHERE%20extname%20%3D%20%27vector%27%22%29%0A%20%20%20%20if%20not%20cur.fetchone%28%29%3A%0A%20%20%20%20%20%20%20%20cur.execute%28%22CREATE%20EXTENSION%20vector%22%29%0A%20%20%20%20%23%20continue%20with%20table/index%20creation%20as%20before%0A%60%60%60%0A%0AReplace%20the%20unconditional%20%60cur.execute%28%22CREATE%20EXTENSION%20IF%20NOT%20EXISTS%20vector%22%29%60%20with%20this%20check.%20This%20way%2C%20the%20extension%20creation%20is%20only%20attempted%20if%20it%E2%80%99s%20actually%20missing%2C%20avoiding%20privilege%20errors%20for%20non-admin%20users%20on%20managed%20cloud%20databases%20like%20Azure%20PostgreSQL.%20The%20rest%20of%20the%20collection%20creation%20logic%20can%20remain%20unchanged.%20%0A%0AThere%E2%80%99s%20currently%20no%20official%20fix%20or%20config%20option%20for%20this%20in%20Dify%2C%20so%20this%20patch%20is%20the%20best%20way%20to%20avoid%20the%20crash%20until%20upstream%20support%20is%20added.%20%0A%0AReference%3A%20%5BDify%20source%20code%20and%20workaround%20details%5D%28https%3A//github.com/langgenius/dify/blob/0a22bc5d05160afa0334e620a333699af1e2e2c0/api/core/rag/datasource/vdb/pgvector/pgvector.py%23L100-L267%29)&nbsp;[![Join Discord](https://img.shields.io/badge/join-5865F2?logo=discord&logoColor=white&label=)](https://go.dosu.dev/discord-bot)&nbsp;[![Share on X](https://img.shields.io/badge/X-share-black)](https://twitter.com/intent/tweet?text=%40dosu_ai%20helped%20me%20solve%20this%20issue!&url=https%3A//github.com/langgenius/dify/issues/28989)
yindo commented 2026-02-21 20:08:36 -05:00
Author
Owner
Copy Link
Copy Source

@nourmami commented on GitHub (Dec 1, 2025):

@dosu If I want to contribute this fix as a pull request, what tests should I add and where exactly should they be placed?
The proposed change would be:

with self._get_cursor() as cur:
    cur.execute("SELECT 1 FROM pg_extension WHERE extname = 'vector'")
    if not cur.fetchone():
        cur.execute("CREATE EXTENSION vector")
    # continue with table/index creation as before

Should I add:

  • Unit tests to verify the extension existence check?
  • Integration tests for Azure PostgreSQL with non-admin users?
  • Tests to ensure backward compatibility with databases where users have extension creation privileges?

Where should these tests be located in the repository structure?

@nourmami commented on GitHub (Dec 1, 2025): @dosu If I want to contribute this fix as a pull request, what tests should I add and where exactly should they be placed? The proposed change would be: ``` with self._get_cursor() as cur: cur.execute("SELECT 1 FROM pg_extension WHERE extname = 'vector'") if not cur.fetchone(): cur.execute("CREATE EXTENSION vector") # continue with table/index creation as before ``` Should I add: - Unit tests to verify the extension existence check? - Integration tests for Azure PostgreSQL with non-admin users? - Tests to ensure backward compatibility with databases where users have extension creation privileges? Where should these tests be located in the repository structure?
yindo commented 2026-02-21 20:08:37 -05:00
Author
Owner
Copy Link
Copy Source

@dosubot[bot] commented on GitHub (Dec 1, 2025):

For your pull request, focus on integration tests in api/tests/integration_tests/vdb/pgvector/. The existing suite uses pytest and already covers pgvector integration, so add new tests here to cover:

  • The extension existence check logic (simulate both: extension present and not present).
  • Behavior when the DB user lacks extension creation privileges (simulate InsufficientPrivilege, e.g., by mocking the cursor's execute to raise a psycopg2 error).
  • Backward compatibility: verify that users with extension creation privileges still succeed.

If you refactor the extension check into a helper function, you could add unit tests for it in a new or existing test file under the same directory.

Follow the pytest conventions and structure you see in test_pgvector.py and test_vector_store.py—use fixtures and mocks as needed to simulate privilege errors and extension states. This will keep your tests consistent with the current codebase and make them easy to maintain and review.
Reference: integration test structure

To reply, just mention @dosu.

How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other

Chat with Dosu Open in Cursor Join Discord Share on X

@dosubot[bot] commented on GitHub (Dec 1, 2025): <!-- Answer --> For your pull request, focus on integration tests in `api/tests/integration_tests/vdb/pgvector/`. The existing suite uses pytest and already covers pgvector integration, so add new tests here to cover: - The extension existence check logic (simulate both: extension present and not present). - Behavior when the DB user lacks extension creation privileges (simulate InsufficientPrivilege, e.g., by mocking the cursor's `execute` to raise a psycopg2 error). - Backward compatibility: verify that users with extension creation privileges still succeed. If you refactor the extension check into a helper function, you could add unit tests for it in a new or existing test file under the same directory. Follow the pytest conventions and structure you see in `test_pgvector.py` and `test_vector_store.py`—use fixtures and mocks as needed to simulate privilege errors and extension states. This will keep your tests consistent with the current codebase and make them easy to maintain and review. [Reference: integration test structure](https://github.com/langgenius/dify/blob/0a22bc5d05160afa0334e620a333699af1e2e2c0/api/tests/integration_tests/vdb/pgvector/test_pgvector.py) <!-- Dosu Comment Footer --> *To reply, just mention [@dosu](https://go.dosu.dev/dosubot).* --- <sup>How did I do? [Good](https://app.dosu.dev/response-feedback/e07ec922-df17-4576-975d-fc53b78e387a?feedback_type=great_response) | [Irrelevant](https://app.dosu.dev/response-feedback/e07ec922-df17-4576-975d-fc53b78e387a?feedback_type=irrelevant_answer) | [Incorrect](https://app.dosu.dev/response-feedback/e07ec922-df17-4576-975d-fc53b78e387a?feedback_type=incorrect_sources) | [Verbose](https://app.dosu.dev/response-feedback/e07ec922-df17-4576-975d-fc53b78e387a?feedback_type=too_verbose) | [Hallucination](https://app.dosu.dev/response-feedback/e07ec922-df17-4576-975d-fc53b78e387a?feedback_type=hallucination) | [Report 🐛](https://app.dosu.dev/response-feedback/e07ec922-df17-4576-975d-fc53b78e387a?feedback_type=bug_report) | [Other](https://app.dosu.dev/response-feedback/e07ec922-df17-4576-975d-fc53b78e387a?feedback_type=other)</sup> [![Chat with Dosu](https://dosu.dev/dosu-chat-badge.svg)](https://app.dosu.dev/6088a4f4-073a-43f3-b3d4-4f06fb0c05a0/ask?utm_source=github)&nbsp;[![Open in Cursor](https://dosu.dev/dosu-in-cursor.svg)](https://cursor.com/link/prompt?text=For%20your%20pull%20request%2C%20focus%20on%20integration%20tests%20in%20%60api/tests/integration_tests/vdb/pgvector/%60.%20The%20existing%20suite%20uses%20pytest%20and%20already%20covers%20pgvector%20integration%2C%20so%20add%20new%20tests%20here%20to%20cover%3A%0A%0A-%20The%20extension%20existence%20check%20logic%20%28simulate%20both%3A%20extension%20present%20and%20not%20present%29.%0A-%20Behavior%20when%20the%20DB%20user%20lacks%20extension%20creation%20privileges%20%28simulate%20InsufficientPrivilege%2C%20e.g.%2C%20by%20mocking%20the%20cursor%27s%20%60execute%60%20to%20raise%20a%20psycopg2%20error%29.%0A-%20Backward%20compatibility%3A%20verify%20that%20users%20with%20extension%20creation%20privileges%20still%20succeed.%0A%0AIf%20you%20refactor%20the%20extension%20check%20into%20a%20helper%20function%2C%20you%20could%20add%20unit%20tests%20for%20it%20in%20a%20new%20or%20existing%20test%20file%20under%20the%20same%20directory.%0A%0AFollow%20the%20pytest%20conventions%20and%20structure%20you%20see%20in%20%60test_pgvector.py%60%20and%20%60test_vector_store.py%60%E2%80%94use%20fixtures%20and%20mocks%20as%20needed%20to%20simulate%20privilege%20errors%20and%20extension%20states.%20This%20will%20keep%20your%20tests%20consistent%20with%20the%20current%20codebase%20and%20make%20them%20easy%20to%20maintain%20and%20review.%20%20%0A%5BReference%3A%20integration%20test%20structure%5D%28https%3A//github.com/langgenius/dify/blob/0a22bc5d05160afa0334e620a333699af1e2e2c0/api/tests/integration_tests/vdb/pgvector/test_pgvector.py%29)&nbsp;[![Join Discord](https://img.shields.io/badge/join-5865F2?logo=discord&logoColor=white&label=)](https://go.dosu.dev/discord-bot)&nbsp;[![Share on X](https://img.shields.io/badge/X-share-black)](https://twitter.com/intent/tweet?text=%40dosu_ai%20helped%20me%20solve%20this%20issue!&url=https%3A//github.com/langgenius/dify/issues/28989)
yindo commented 2026-02-21 20:08:37 -05:00
Author
Owner
Copy Link
Copy Source

@nourmami commented on GitHub (Dec 1, 2025):

@dosu Thanks! Could you now generate the complete test code for api/tests/integration_tests/vdb/pgvector/test_pgvector.py?
Please include tests for:
Extension already exists - verify no CREATE EXTENSION is called
Extension missing - verify CREATE EXTENSION is called
InsufficientPrivilege error - mock psycopg2.errors.InsufficientPrivilege when user lacks privileges
Backward compatibility - verify admin users with full privileges still work
Include all necessary imports, mocks, fixtures, and assertions following the existing test file patterns.
Also provide the updated _create_collection method in api/core/rag/datasource/vdb/pgvector/pgvector.py with the extension check logic.

@nourmami commented on GitHub (Dec 1, 2025): @dosu Thanks! Could you now generate the complete test code for api/tests/integration_tests/vdb/pgvector/test_pgvector.py? Please include tests for: Extension already exists - verify no CREATE EXTENSION is called Extension missing - verify CREATE EXTENSION is called InsufficientPrivilege error - mock psycopg2.errors.InsufficientPrivilege when user lacks privileges Backward compatibility - verify admin users with full privileges still work Include all necessary imports, mocks, fixtures, and assertions following the existing test file patterns. Also provide the updated _create_collection method in api/core/rag/datasource/vdb/pgvector/pgvector.py with the extension check logic.
yindo commented 2026-02-21 20:08:37 -05:00
Author
Owner
Copy Link
Copy Source

@dosubot[bot] commented on GitHub (Dec 1, 2025):

Here’s the updated _create_collection method for pgvector.py with the extension existence check logic, followed by complete pytest-based tests for test_pgvector.py covering all your scenarios. The tests use unittest.mock to patch the cursor and simulate extension presence, absence, privilege errors, and admin behavior.

Updated _create_collection method (replace the old one in api/core/rag/datasource/vdb/pgvector/pgvector.py):

def _create_collection(self, dimension: int):
    cache_key = f"vector_indexing_{self._collection_name}"
    lock_name = f"{cache_key}_lock"
    with redis_client.lock(lock_name, timeout=20):
        collection_exist_cache_key = f"vector_indexing_{self._collection_name}"
        if redis_client.get(collection_exist_cache_key):
            return

        with self._get_cursor() as cur:
            # Check if the extension exists before attempting to create it
            cur.execute("SELECT 1 FROM pg_extension WHERE extname = 'vector'")
            if not cur.fetchone():
                try:
                    cur.execute("CREATE EXTENSION vector")
                except psycopg2.errors.InsufficientPrivilege:
                    # Extension creation not allowed, likely on managed cloud DBs
                    raise
            cur.execute(SQL_CREATE_TABLE.format(table_name=self.table_name, dimension=dimension))
            if dimension <= 2000:
                cur.execute(SQL_CREATE_INDEX.format(table_name=self.table_name, index_hash=self.index_hash))
            if self.pg_bigm:
                cur.execute(SQL_CREATE_INDEX_PG_BIGM.format(table_name=self.table_name, index_hash=self.index_hash))
        redis_client.set(collection_exist_cache_key, 1, ex=3600)

New tests for api/tests/integration_tests/vdb/pgvector/test_pgvector.py:

import pytest
from unittest import mock
from unittest.mock import MagicMock, patch

import psycopg2
from core.rag.datasource.vdb.pgvector.pgvector import PGVector, PGVectorConfig

@pytest.fixture
def pgvector_instance():
    config = PGVectorConfig(
        host="localhost",
        port=5433,
        user="postgres",
        password="difyai123456",
        database="dify",
        min_connection=1,
        max_connection=5,
    )
    return PGVector(collection_name="test_collection", config=config)

def make_mock_cursor(extension_exists=True, raise_on_create=False):
    """
    Returns a mock cursor:
    - extension_exists: if True, SELECT finds the extension
    - raise_on_create: if True, CREATE EXTENSION raises InsufficientPrivilege
    """
    cur = MagicMock()
    # SELECT 1 FROM pg_extension WHERE extname = 'vector'
    cur.execute = MagicMock()
    cur.fetchone = MagicMock(return_value=(1,) if extension_exists else None)
    def execute_side_effect(sql, *args, **kwargs):
        if sql.strip().startswith("CREATE EXTENSION"):
            if raise_on_create:
                raise psycopg2.errors.InsufficientPrivilege("Insufficient privilege")
    cur.execute.side_effect = execute_side_effect
    return cur

@patch("core.rag.datasource.vdb.pgvector.pgvector.redis_client")
@patch("core.rag.datasource.vdb.pgvector.pgvector.PGVector._get_cursor")
def test_extension_already_exists_no_create_called(mock_get_cursor, mock_redis, pgvector_instance):
    # Extension exists, so CREATE EXTENSION should not be called
    mock_cursor = make_mock_cursor(extension_exists=True)
    mock_get_cursor.return_value.__enter__.return_value = mock_cursor
    mock_redis.get.return_value = None  # Not cached
    pgvector_instance._collection_name = "test_collection"
    pgvector_instance.table_name = "embedding_test_collection"
    pgvector_instance.index_hash = "deadbeef"
    pgvector_instance.pg_bigm = False

    pgvector_instance._create_collection(128)
    # Should call SELECT, but not CREATE EXTENSION
    calls = [call[0][0] for call in mock_cursor.execute.call_args_list]
    assert any("SELECT 1 FROM pg_extension" in c for c in calls)
    assert not any("CREATE EXTENSION" in c for c in calls)

@patch("core.rag.datasource.vdb.pgvector.pgvector.redis_client")
@patch("core.rag.datasource.vdb.pgvector.pgvector.PGVector._get_cursor")
def test_extension_missing_create_called(mock_get_cursor, mock_redis, pgvector_instance):
    # Extension missing, so CREATE EXTENSION should be called
    mock_cursor = make_mock_cursor(extension_exists=False)
    mock_get_cursor.return_value.__enter__.return_value = mock_cursor
    mock_redis.get.return_value = None
    pgvector_instance._collection_name = "test_collection"
    pgvector_instance.table_name = "embedding_test_collection"
    pgvector_instance.index_hash = "deadbeef"
    pgvector_instance.pg_bigm = False

    pgvector_instance._create_collection(128)
    calls = [call[0][0] for call in mock_cursor.execute.call_args_list]
    assert any("SELECT 1 FROM pg_extension" in c for c in calls)
    assert any("CREATE EXTENSION" in c for c in calls)

@patch("core.rag.datasource.vdb.pgvector.pgvector.redis_client")
@patch("core.rag.datasource.vdb.pgvector.pgvector.PGVector._get_cursor")
def test_insufficient_privilege_error(mock_get_cursor, mock_redis, pgvector_instance):
    # Extension missing, CREATE EXTENSION raises InsufficientPrivilege
    mock_cursor = make_mock_cursor(extension_exists=False, raise_on_create=True)
    mock_get_cursor.return_value.__enter__.return_value = mock_cursor
    mock_redis.get.return_value = None
    pgvector_instance._collection_name = "test_collection"
    pgvector_instance.table_name = "embedding_test_collection"
    pgvector_instance.index_hash = "deadbeef"
    pgvector_instance.pg_bigm = False

    with pytest.raises(psycopg2.errors.InsufficientPrivilege):
        pgvector_instance._create_collection(128)

@patch("core.rag.datasource.vdb.pgvector.pgvector.redis_client")
@patch("core.rag.datasource.vdb.pgvector.pgvector.PGVector._get_cursor")
def test_admin_user_create_extension_success(mock_get_cursor, mock_redis, pgvector_instance):
    # Admin user: extension missing, CREATE EXTENSION succeeds
    mock_cursor = make_mock_cursor(extension_exists=False, raise_on_create=False)
    mock_get_cursor.return_value.__enter__.return_value = mock_cursor
    mock_redis.get.return_value = None
    pgvector_instance._collection_name = "test_collection"
    pgvector_instance.table_name = "embedding_test_collection"
    pgvector_instance.index_hash = "deadbeef"
    pgvector_instance.pg_bigm = False

    pgvector_instance._create_collection(128)
    calls = [call[0][0] for call in mock_cursor.execute.call_args_list]
    assert any("CREATE EXTENSION" in c for c in calls)

These tests cover all the requested scenarios and follow the conventions of the existing test file. You can add them directly to test_pgvector.py.

To reply, just mention @dosu.

How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other

Chat with Dosu Open in Cursor Join Discord Share on X

@dosubot[bot] commented on GitHub (Dec 1, 2025): <!-- Answer --> Here’s the updated `_create_collection` method for `pgvector.py` with the extension existence check logic, followed by complete pytest-based tests for `test_pgvector.py` covering all your scenarios. The tests use `unittest.mock` to patch the cursor and simulate extension presence, absence, privilege errors, and admin behavior. --- **Updated `_create_collection` method** (replace the old one in `api/core/rag/datasource/vdb/pgvector/pgvector.py`): ```python def _create_collection(self, dimension: int): cache_key = f"vector_indexing_{self._collection_name}" lock_name = f"{cache_key}_lock" with redis_client.lock(lock_name, timeout=20): collection_exist_cache_key = f"vector_indexing_{self._collection_name}" if redis_client.get(collection_exist_cache_key): return with self._get_cursor() as cur: # Check if the extension exists before attempting to create it cur.execute("SELECT 1 FROM pg_extension WHERE extname = 'vector'") if not cur.fetchone(): try: cur.execute("CREATE EXTENSION vector") except psycopg2.errors.InsufficientPrivilege: # Extension creation not allowed, likely on managed cloud DBs raise cur.execute(SQL_CREATE_TABLE.format(table_name=self.table_name, dimension=dimension)) if dimension <= 2000: cur.execute(SQL_CREATE_INDEX.format(table_name=self.table_name, index_hash=self.index_hash)) if self.pg_bigm: cur.execute(SQL_CREATE_INDEX_PG_BIGM.format(table_name=self.table_name, index_hash=self.index_hash)) redis_client.set(collection_exist_cache_key, 1, ex=3600) ``` --- **New tests for `api/tests/integration_tests/vdb/pgvector/test_pgvector.py`:** ```python import pytest from unittest import mock from unittest.mock import MagicMock, patch import psycopg2 from core.rag.datasource.vdb.pgvector.pgvector import PGVector, PGVectorConfig @pytest.fixture def pgvector_instance(): config = PGVectorConfig( host="localhost", port=5433, user="postgres", password="difyai123456", database="dify", min_connection=1, max_connection=5, ) return PGVector(collection_name="test_collection", config=config) def make_mock_cursor(extension_exists=True, raise_on_create=False): """ Returns a mock cursor: - extension_exists: if True, SELECT finds the extension - raise_on_create: if True, CREATE EXTENSION raises InsufficientPrivilege """ cur = MagicMock() # SELECT 1 FROM pg_extension WHERE extname = 'vector' cur.execute = MagicMock() cur.fetchone = MagicMock(return_value=(1,) if extension_exists else None) def execute_side_effect(sql, *args, **kwargs): if sql.strip().startswith("CREATE EXTENSION"): if raise_on_create: raise psycopg2.errors.InsufficientPrivilege("Insufficient privilege") cur.execute.side_effect = execute_side_effect return cur @patch("core.rag.datasource.vdb.pgvector.pgvector.redis_client") @patch("core.rag.datasource.vdb.pgvector.pgvector.PGVector._get_cursor") def test_extension_already_exists_no_create_called(mock_get_cursor, mock_redis, pgvector_instance): # Extension exists, so CREATE EXTENSION should not be called mock_cursor = make_mock_cursor(extension_exists=True) mock_get_cursor.return_value.__enter__.return_value = mock_cursor mock_redis.get.return_value = None # Not cached pgvector_instance._collection_name = "test_collection" pgvector_instance.table_name = "embedding_test_collection" pgvector_instance.index_hash = "deadbeef" pgvector_instance.pg_bigm = False pgvector_instance._create_collection(128) # Should call SELECT, but not CREATE EXTENSION calls = [call[0][0] for call in mock_cursor.execute.call_args_list] assert any("SELECT 1 FROM pg_extension" in c for c in calls) assert not any("CREATE EXTENSION" in c for c in calls) @patch("core.rag.datasource.vdb.pgvector.pgvector.redis_client") @patch("core.rag.datasource.vdb.pgvector.pgvector.PGVector._get_cursor") def test_extension_missing_create_called(mock_get_cursor, mock_redis, pgvector_instance): # Extension missing, so CREATE EXTENSION should be called mock_cursor = make_mock_cursor(extension_exists=False) mock_get_cursor.return_value.__enter__.return_value = mock_cursor mock_redis.get.return_value = None pgvector_instance._collection_name = "test_collection" pgvector_instance.table_name = "embedding_test_collection" pgvector_instance.index_hash = "deadbeef" pgvector_instance.pg_bigm = False pgvector_instance._create_collection(128) calls = [call[0][0] for call in mock_cursor.execute.call_args_list] assert any("SELECT 1 FROM pg_extension" in c for c in calls) assert any("CREATE EXTENSION" in c for c in calls) @patch("core.rag.datasource.vdb.pgvector.pgvector.redis_client") @patch("core.rag.datasource.vdb.pgvector.pgvector.PGVector._get_cursor") def test_insufficient_privilege_error(mock_get_cursor, mock_redis, pgvector_instance): # Extension missing, CREATE EXTENSION raises InsufficientPrivilege mock_cursor = make_mock_cursor(extension_exists=False, raise_on_create=True) mock_get_cursor.return_value.__enter__.return_value = mock_cursor mock_redis.get.return_value = None pgvector_instance._collection_name = "test_collection" pgvector_instance.table_name = "embedding_test_collection" pgvector_instance.index_hash = "deadbeef" pgvector_instance.pg_bigm = False with pytest.raises(psycopg2.errors.InsufficientPrivilege): pgvector_instance._create_collection(128) @patch("core.rag.datasource.vdb.pgvector.pgvector.redis_client") @patch("core.rag.datasource.vdb.pgvector.pgvector.PGVector._get_cursor") def test_admin_user_create_extension_success(mock_get_cursor, mock_redis, pgvector_instance): # Admin user: extension missing, CREATE EXTENSION succeeds mock_cursor = make_mock_cursor(extension_exists=False, raise_on_create=False) mock_get_cursor.return_value.__enter__.return_value = mock_cursor mock_redis.get.return_value = None pgvector_instance._collection_name = "test_collection" pgvector_instance.table_name = "embedding_test_collection" pgvector_instance.index_hash = "deadbeef" pgvector_instance.pg_bigm = False pgvector_instance._create_collection(128) calls = [call[0][0] for call in mock_cursor.execute.call_args_list] assert any("CREATE EXTENSION" in c for c in calls) ``` These tests cover all the requested scenarios and follow the conventions of the existing test file. You can add them directly to `test_pgvector.py`. <!-- Dosu Comment Footer --> *To reply, just mention [@dosu](https://go.dosu.dev/dosubot).* --- <sup>How did I do? [Good](https://app.dosu.dev/response-feedback/ee837c68-e0d9-4640-8e5a-a9e629dfcd47?feedback_type=great_response) | [Irrelevant](https://app.dosu.dev/response-feedback/ee837c68-e0d9-4640-8e5a-a9e629dfcd47?feedback_type=irrelevant_answer) | [Incorrect](https://app.dosu.dev/response-feedback/ee837c68-e0d9-4640-8e5a-a9e629dfcd47?feedback_type=incorrect_sources) | [Verbose](https://app.dosu.dev/response-feedback/ee837c68-e0d9-4640-8e5a-a9e629dfcd47?feedback_type=too_verbose) | [Hallucination](https://app.dosu.dev/response-feedback/ee837c68-e0d9-4640-8e5a-a9e629dfcd47?feedback_type=hallucination) | [Report 🐛](https://app.dosu.dev/response-feedback/ee837c68-e0d9-4640-8e5a-a9e629dfcd47?feedback_type=bug_report) | [Other](https://app.dosu.dev/response-feedback/ee837c68-e0d9-4640-8e5a-a9e629dfcd47?feedback_type=other)</sup> [![Chat with Dosu](https://dosu.dev/dosu-chat-badge.svg)](https://app.dosu.dev/6088a4f4-073a-43f3-b3d4-4f06fb0c05a0/ask?utm_source=github)&nbsp;[![Open in Cursor](https://dosu.dev/dosu-in-cursor.svg)](https://cursor.com/link/prompt?text=Here%E2%80%99s%20the%20updated%20%60_create_collection%60%20method%20for%20%60pgvector.py%60%20with%20the%20extension%20existence%20check%20logic%2C%20followed%20by%20complete%20pytest-based%20tests%20for%20%60test_pgvector.py%60%20covering%20all%20your%20scenarios.%20The%20tests%20use%20%60unittest.mock%60%20to%20patch%20the%20cursor%20and%20simulate%20extension%20presence%2C%20absence%2C%20privilege%20errors%2C%20and%20admin%20behavior.%0A%0A---%0A%0A%2A%2AUpdated%20%60_create_collection%60%20method%2A%2A%20%28replace%20the%20old%20one%20in%20%60api/core/rag/datasource/vdb/pgvector/pgvector.py%60%29%3A%0A%0A%60%60%60python%0Adef%20_create_collection%28self%2C%20dimension%3A%20int%29%3A%0A%20%20%20%20cache_key%20%3D%20f%22vector_indexing_%7Bself._collection_name%7D%22%0A%20%20%20%20lock_name%20%3D%20f%22%7Bcache_key%7D_lock%22%0A%20%20%20%20with%20redis_client.lock%28lock_name%2C%20timeout%3D20%29%3A%0A%20%20%20%20%20%20%20%20collection_exist_cache_key%20%3D%20f%22vector_indexing_%7Bself._collection_name%7D%22%0A%20%20%20%20%20%20%20%20if%20redis_client.get%28collection_exist_cache_key%29%3A%0A%20%20%20%20%20%20%20%20%20%20%20%20return%0A%0A%20%20%20%20%20%20%20%20with%20self._get_cursor%28%29%20as%20cur%3A%0A%20%20%20%20%20%20%20%20%20%20%20%20%23%20Check%20if%20the%20extension%20exists%20before%20attempting%20to%20create%20it%0A%20%20%20%20%20%20%20%20%20%20%20%20cur.execute%28%22SELECT%201%20FROM%20pg_extension%20WHERE%20extname%20%3D%20%27vector%27%22%29%0A%20%20%20%20%20%20%20%20%20%20%20%20if%20not%20cur.fetchone%28%29%3A%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20try%3A%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20cur.execute%28%22CREATE%20EXTENSION%20vector%22%29%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20except%20psycopg2.errors.InsufficientPrivilege%3A%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%23%20Extension%20creation%20not%20allowed%2C%20likely%20on%20managed%20cloud%20DBs%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20raise%0A%20%20%20%20%20%20%20%20%20%20%20%20cur.execute%28SQL_CREATE_TABLE.format%28table_name%3Dself.table_name%2C%20dimension%3Ddimension%29%29%0A%20%20%20%20%20%20%20%20%20%20%20%20if%20dimension%20%3C%3D%202000%3A%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20cur.execute%28SQL_CREATE_INDEX.format%28table_name%3Dself.table_name%2C%20index_hash%3Dself.index_hash%29%29%0A%20%20%20%20%20%20%20%20%20%20%20%20if%20self.pg_bigm%3A%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20cur.execute%28SQL_CREATE_INDEX_PG_BIGM.format%28table_name%3Dself.table_name%2C%20index_hash%3Dself.index_hash%29%29%0A%20%20%20%20%20%20%20%20redis_client.set%28collection_exist_cache_key%2C%201%2C%20ex%3D3600%29%0A%60%60%60%0A%0A---%0A%0A%2A%2ANew%20tests%20for%20%60api/tests/integration_tests/vdb/pgvector/test_pgvector.py%60%3A%2A%2A%0A%0A%60%60%60python%0Aimport%20pytest%0Afrom%20unittest%20import%20mock%0Afrom%20unittest.mock%20import%20MagicMock%2C%20patch%0A%0Aimport%20psycopg2%0Afrom%20core.rag.datasource.vdb.pgvector.pgvector%20import%20PGVector%2C%20PGVectorConfig%0A%0A%40pytest.fixture%0Adef%20pgvector_instance%28%29%3A%0A%20%20%20%20config%20%3D%20PGVectorConfig%28%0A%20%20%20%20%20%20%20%20host%3D%22localhost%22%2C%0A%20%20%20%20%20%20%20%20port%3D5433%2C%0A%20%20%20%20%20%20%20%20user%3D%22postgres%22%2C%0A%20%20%20%20%20%20%20%20password%3D%22difyai123456%22%2C%0A%20%20%20%20%20%20%20%20database%3D%22dify%22%2C%0A%20%20%20%20%20%20%20%20min_connection%3D1%2C%0A%20%20%20%20%20%20%20%20max_connection%3D5%2C%0A%20%20%20%20%29%0A%20%20%20%20return%20PGVector%28collection_name%3D%22test_collection%22%2C%20config%3Dconfig%29%0A%0Adef%20make_mock_cursor%28extension_exists%3DTrue%2C%20raise_on_create%3DFalse%29%3A%0A%20%20%20%20%22%22%22%0A%20%20%20%20Returns%20a%20mock%20cursor%3A%0A%20%20%20%20-%20extension_exists%3A%20if%20True%2C%20SELECT%20finds%20the%20extension%0A%20%20%20%20-%20raise_on_create%3A%20if%20True%2C%20CREATE%20EXTENSION%20raises%20InsufficientPrivilege%0A%20%20%20%20%22%22%22%0A%20%20%20%20cur%20%3D%20MagicMock%28%29%0A%20%20%20%20%23%20SELECT%201%20FROM%20pg_extension%20WHERE%20extname%20%3D%20%27vector%27%0A%20%20%20%20cur.execute%20%3D%20MagicMock%28%29%0A%20%20%20%20cur.fetchone%20%3D%20MagicMock%28return_value%3D%281%2C%29%20if%20extension_exists%20else%20None%29%0A%20%20%20%20def%20execute_side_effect%28sql%2C%20%2Aargs%2C%20%2A%2Akwargs%29%3A%0A%20%20%20%20%20%20%20%20if%20sql.strip%28%29.startswith%28%22CREATE%20EXTENSION%22%29%3A%0A%20%20%20%20%20%20%20%20%20%20%20%20if%20raise_on_create%3A%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20raise%20psycopg2.errors.InsufficientPrivilege%28%22Insufficient%20privilege%22%29%0A%20%20%20%20cur.execute.side_effect%20%3D%20execute_side_effect%0A%20%20%20%20return%20cur%0A%0A%40patch%28%22core.rag.datasource.vdb.pgvector.pgvector.redis_client%22%29%0A%40patch%28%22core.rag.datasource.vdb.pgvector.pgvector.PGVector._get_cursor%22%29%0Adef%20test_extension_already_exists_no_create_called%28mock_get_cursor%2C%20mock_redis%2C%20pgvector_instance%29%3A%0A%20%20%20%20%23%20Extension%20exists%2C%20so%20CREATE%20EXTENSION%20should%20not%20be%20called%0A%20%20%20%20mock_cursor%20%3D%20make_mock_cursor%28extension_exists%3DTrue%29%0A%20%20%20%20mock_get_cursor.return_value.__enter__.return_value%20%3D%20mock_cursor%0A%20%20%20%20mock_redis.get.return_value%20%3D%20None%20%20%23%20Not%20cached%0A%20%20%20%20pgvector_instance._collection_name%20%3D%20%22test_collection%22%0A%20%20%20%20pgvector_instance.table_name%20%3D%20%22embedding_test_collection%22%0A%20%20%20%20pgvector_instance.index_hash%20%3D%20%22deadbeef%22%0A%20%20%20%20pgvector_instance.pg_bigm%20%3D%20False%0A%0A%20%20%20%20pgvector_instance._create_collection%28128%29%0A%20%20%20%20%23%20Should%20call%20SELECT%2C%20but%20not%20CREATE%20EXTENSION%0A%20%20%20%20calls%20%3D%20%5Bcall%5B0%5D%5B0%5D%20for%20call%20in%20mock_cursor.execute.call_args_list%5D%0A%20%20%20%20assert%20any%28%22SELECT%201%20FROM%20pg_extension%22%20in%20c%20for%20c%20in%20calls%29%0A%20%20%20%20assert%20not%20any%28%22CREATE%20EXTENSION%22%20in%20c%20for%20c%20in%20calls%29%0A%0A%40patch%28%22core.rag.datasource.vdb.pgvector.pgvector.redis_client%22%29%0A%40patch%28%22core.rag.datasource.vdb.pgvector.pgvector.PGVector._get_cursor%22%29%0Adef%20test_extension_missing_create_called%28mock_get_cursor%2C%20mock_redis%2C%20pgvector_instance%29%3A%0A%20%20%20%20%23%20Extension%20missing%2C%20so%20CREATE%20EXTENSION%20should%20be%20called%0A%20%20%20%20mock_cursor%20%3D%20make_mock_cursor%28extension_exists%3DFalse%29%0A%20%20%20%20mock_get_cursor.return_value.__enter__.return_value%20%3D%20mock_cursor%0A%20%20%20%20mock_redis.get.return_value%20%3D%20None%0A%20%20%20%20pgvector_instance._collection_name%20%3D%20%22test_collection%22%0A%20%20%20%20pgvector_instance.table_name%20%3D%20%22embedding_test_collection%22%0A%20%20%20%20pgvector_instance.index_hash%20%3D%20%22deadbeef%22%0A%20%20%20%20pgvector_instance.pg_bigm%20%3D%20False%0A%0A%20%20%20%20pgvector_instance._create_collection%28128%29%0A%20%20%20%20calls%20%3D%20%5Bcall%5B0%5D%5B0%5D%20for%20call%20in%20mock_cursor.execute.call_args_list%5D%0A%20%20%20%20assert%20any%28%22SELECT%201%20FROM%20pg_extension%22%20in%20c%20for%20c%20in%20calls%29%0A%20%20%20%20assert%20any%28%22CREATE%20EXTENSION%22%20in%20c%20for%20c%20in%20calls%29%0A%0A%40patch%28%22core.rag.datasource.vdb.pgvector.pgvector.redis_client%22%29%0A%40patch%28%22core.rag.datasource.vdb.pgvector.pgvector.PGVector._get_cursor%22%29%0Adef%20test_insufficient_privilege_error%28mock_get_cursor%2C%20mock_redis%2C%20pgvector_instance%29%3A%0A%20%20%20%20%23%20Extension%20missing%2C%20CREATE%20EXTENSION%20raises%20InsufficientPrivilege%0A%20%20%20%20mock_cursor%20%3D%20make_mock_cursor%28extension_exists%3DFalse%2C%20raise_on_create%3DTrue%29%0A%20%20%20%20mock_get_cursor.return_value.__enter__.return_value%20%3D%20mock_cursor%0A%20%20%20%20mock_redis.get.return_value%20%3D%20None%0A%20%20%20%20pgvector_instance._collection_name%20%3D%20%22test_collection%22%0A%20%20%20%20pgvector_instance.table_name%20%3D%20%22embedding_test_collection%22%0A%20%20%20%20pgvector_instance.index_hash%20%3D%20%22deadbeef%22%0A%20%20%20%20pgvector_instance.pg_bigm%20%3D%20False%0A%0A%20%20%20%20with%20pytest.raises%28psycopg2.errors.InsufficientPrivilege%29%3A%0A%20%20%20%20%20%20%20%20pgvector_instance._create_collection%28128%29%0A%0A%40patch%28%22core.rag.datasource.vdb.pgvector.pgvector.redis_client%22%29%0A%40patch%28%22core.rag.datasource.vdb.pgvector.pgvector.PGVector._get_cursor%22%29%0Adef%20test_admin_user_create_extension_success%28mock_get_cursor%2C%20mock_redis%2C%20pgvector_instance%29%3A%0A%20%20%20%20%23%20Admin%20user%3A%20extension%20missing%2C%20CREATE%20EXTENSION%20succeeds%0A%20%20%20%20mock_cursor%20%3D%20make_mock_cursor%28extension_exists%3DFalse%2C%20raise_on_create%3DFalse%29%0A%20%20%20%20mock_get_cursor.return_value.__enter__.return_value%20%3D%20mock_cursor%0A%20%20%20%20mock_redis.get.return_value%20%3D%20None%0A%20%20%20%20pgvector_instance._collection_name%20%3D%20%22test_collection%22%0A%20%20%20%20pgvector_instance.table_name%20%3D%20%22embedding_test_collection%22%0A%20%20%20%20pgvector_instance.index_hash%20%3D%20%22deadbeef%22%0A%20%20%20%20pgvector_instance.pg_bigm%20%3D%20False%0A%0A%20%20%20%20pgvector_instance._create_collection%28128%29%0A%20%20%20%20calls%20%3D%20%5Bcall%5B0%5D%5B0%5D%20for%20call%20in%20mock_cursor.execute.call_args_list%5D%0A%20%20%20%20assert%20any%28%22CREATE%20EXTENSION%22%20in%20c%20for%20c%20in%20calls%29%0A%60%60%60%0A%0AThese%20tests%20cover%20all%20the%20requested%20scenarios%20and%20follow%20the%20conventions%20of%20the%20existing%20test%20file.%20You%20can%20add%20them%20directly%20to%20%60test_pgvector.py%60.)&nbsp;[![Join Discord](https://img.shields.io/badge/join-5865F2?logo=discord&logoColor=white&label=)](https://go.dosu.dev/discord-bot)&nbsp;[![Share on X](https://img.shields.io/badge/X-share-black)](https://twitter.com/intent/tweet?text=%40dosu_ai%20helped%20me%20solve%20this%20issue!&url=https%3A//github.com/langgenius/dify/issues/28989)
yindo commented 2026-02-21 20:08:37 -05:00
Author
Owner
Copy Link
Copy Source

@nourmami commented on GitHub (Dec 1, 2025):

@dosu I've implemented the fix and tests as suggested. According to the contributing guidelines, I need to ensure my code passes existing tests.
Could you please provide:
The specific commands to run the Dify backend application locally (from api/README.md)
The commands to run the integration tests in api/tests/integration_tests/vdb/pgvector/
How to set up the test environment with PostgreSQL to verify the extension check logic works correctly
Any additional tests I should run before submitting the PR

@nourmami commented on GitHub (Dec 1, 2025): @dosu I've implemented the fix and tests as suggested. According to the contributing guidelines, I need to ensure my code passes existing tests. Could you please provide: The specific commands to run the Dify backend application locally (from api/README.md) The commands to run the integration tests in api/tests/integration_tests/vdb/pgvector/ How to set up the test environment with PostgreSQL to verify the extension check logic works correctly Any additional tests I should run before submitting the PR
yindo commented 2026-02-21 20:08:37 -05:00
Author
Owner
Copy Link
Copy Source

@dosubot[bot] commented on GitHub (Dec 1, 2025):

Here are the steps and commands you'll need:

1. Run the Dify backend locally
From api/README.md:

# Start middleware (PostgreSQL, Redis, Weaviate)
cd ../docker
cp middleware.env.example middleware.env
docker compose -f docker-compose.middleware.yaml --profile postgresql --profile weaviate -p dify up -d
cd ../api

# Set up environment
cp .env.example .env
# Generate a SECRET_KEY (Linux)
sed -i "/^SECRET_KEY=/c\SECRET_KEY=$(openssl rand -base64 42)" .env
# Or (Mac)
secret_key=$(openssl rand -base64 42)
sed -i '' "/^SECRET_KEY=/c\\
SECRET_KEY=${secret_key}" .env

# Install dependencies
pip install uv  # or brew install uv
uv sync --dev

# Run migrations
uv run flask db upgrade

# Start backend server
uv run flask run --host 0.0.0.0 --port=5001 --debug

# (Optional) Start worker for async tasks
uv run celery -A app.celery worker -P threads -c 2 --loglevel INFO -Q dataset,priority_dataset,priority_pipeline,pipeline,mail,ops_trace,app_deletion,plugin,workflow_storage,conversation,workflow,schedule_poller,schedule_executor,triggered_workflow_dispatcher,trigger_refresh_executor

# (Optional) Start celery beat for scheduled tasks
uv run celery -A app.celery beat

2. Run the pgvector integration tests
From dev/pytest/pytest_vdb.sh:

# Run only pgvector integration tests
uv run pytest tests/integration_tests/vdb/pgvector/
# Or use the helper script (runs all vector DB tests, including pgvector)
bash dev/pytest/pytest_vdb.sh

3. Set up the PostgreSQL test environment

  • The integration tests use a .env file for DB connection. Copy and edit as needed:

    cp api/tests/integration_tests/.env.example api/tests/integration_tests/.env

    The default config is:

    DB_USERNAME=postgres
DB_PASSWORD=difyai123456
DB_HOST=localhost
DB_PORT=5432
DB_DATABASE=dify

  • You can use Docker Compose to start a local PostgreSQL with the vector extension:

    docker compose -f docker-compose.middleware.yaml --profile postgresql -p dify up -d

  • To manually install the vector extension and set up users:

    1. Connect as admin: 
      psql -h localhost -U postgres -d dify
CREATE EXTENSION IF NOT EXISTS vector;
CREATE USER dify_user WITH PASSWORD 'yourpassword';
GRANT ALL PRIVILEGES ON DATABASE dify TO dify_user;
-- Optionally, restrict privileges to simulate non-admin
    2. Update your .env to use dify_user for testing non-admin scenarios.

  • The integration tests also use TestContainers to spin up PostgreSQL and install extensions automatically, so you can run tests without manual DB setup if Docker is available (see conftest.py).

4. Additional tests and checks before submitting your PR

  • Run all tests to ensure nothing is broken: 
    uv run pytest
  • Run only unit tests: 
    uv run pytest tests/unit_tests/
  • Run only integration tests: 
    uv run pytest tests/integration_tests/
  • Code quality checks: 
    ../dev/reformat                # Formatters and linters
uv run ruff check --fix ./     # Fix linting issues
uv run ruff format ./          # Format code
uv run basedpyright .          # Type checking

If you want to simulate privilege errors, create a non-superuser and do not grant them CREATEROLE or SUPERUSER—then attempt to run the extension logic as that user.

Let me know if you need a sample Docker Compose snippet for a custom Postgres/pgvector setup or more details on user privilege simulation!

To reply, just mention @dosu.

How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other

Chat with Dosu Open in Cursor Join Discord Share on X

@dosubot[bot] commented on GitHub (Dec 1, 2025): <!-- Answer --> Here are the steps and commands you'll need: **1. Run the Dify backend locally** From [`api/README.md`](https://github.com/langgenius/dify/blob/0a22bc5d05160afa0334e620a333699af1e2e2c0/api/README.md#L13-L110): ```bash # Start middleware (PostgreSQL, Redis, Weaviate) cd ../docker cp middleware.env.example middleware.env docker compose -f docker-compose.middleware.yaml --profile postgresql --profile weaviate -p dify up -d cd ../api # Set up environment cp .env.example .env # Generate a SECRET_KEY (Linux) sed -i "/^SECRET_KEY=/c\SECRET_KEY=$(openssl rand -base64 42)" .env # Or (Mac) secret_key=$(openssl rand -base64 42) sed -i '' "/^SECRET_KEY=/c\\ SECRET_KEY=${secret_key}" .env # Install dependencies pip install uv # or brew install uv uv sync --dev # Run migrations uv run flask db upgrade # Start backend server uv run flask run --host 0.0.0.0 --port=5001 --debug # (Optional) Start worker for async tasks uv run celery -A app.celery worker -P threads -c 2 --loglevel INFO -Q dataset,priority_dataset,priority_pipeline,pipeline,mail,ops_trace,app_deletion,plugin,workflow_storage,conversation,workflow,schedule_poller,schedule_executor,triggered_workflow_dispatcher,trigger_refresh_executor # (Optional) Start celery beat for scheduled tasks uv run celery -A app.celery beat ``` **2. Run the pgvector integration tests** From [`dev/pytest/pytest_vdb.sh`](https://github.com/langgenius/dify/blob/0a22bc5d05160afa0334e620a333699af1e2e2c0/dev/pytest/pytest_vdb.sh#L4-L12): ```bash # Run only pgvector integration tests uv run pytest tests/integration_tests/vdb/pgvector/ # Or use the helper script (runs all vector DB tests, including pgvector) bash dev/pytest/pytest_vdb.sh ``` **3. Set up the PostgreSQL test environment** - The integration tests use a `.env` file for DB connection. Copy and edit as needed: ```bash cp api/tests/integration_tests/.env.example api/tests/integration_tests/.env ``` The default config is: ``` DB_USERNAME=postgres DB_PASSWORD=difyai123456 DB_HOST=localhost DB_PORT=5432 DB_DATABASE=dify ``` - You can use Docker Compose to start a local PostgreSQL with the vector extension: ```bash docker compose -f docker-compose.middleware.yaml --profile postgresql -p dify up -d ``` - To manually install the vector extension and set up users: 1. Connect as admin: ```bash psql -h localhost -U postgres -d dify CREATE EXTENSION IF NOT EXISTS vector; CREATE USER dify_user WITH PASSWORD 'yourpassword'; GRANT ALL PRIVILEGES ON DATABASE dify TO dify_user; -- Optionally, restrict privileges to simulate non-admin ``` 2. Update your `.env` to use `dify_user` for testing non-admin scenarios. - The integration tests also use TestContainers to spin up PostgreSQL and install extensions automatically, so you can run tests without manual DB setup if Docker is available ([see conftest.py](https://github.com/langgenius/dify/blob/0a22bc5d05160afa0334e620a333699af1e2e2c0/api/tests/test_containers_integration_tests/conftest.py#L76-L489)). **4. Additional tests and checks before submitting your PR** - Run all tests to ensure nothing is broken: ```bash uv run pytest ``` - Run only unit tests: ```bash uv run pytest tests/unit_tests/ ``` - Run only integration tests: ```bash uv run pytest tests/integration_tests/ ``` - Code quality checks: ```bash ../dev/reformat # Formatters and linters uv run ruff check --fix ./ # Fix linting issues uv run ruff format ./ # Format code uv run basedpyright . # Type checking ``` If you want to simulate privilege errors, create a non-superuser and do not grant them `CREATEROLE` or `SUPERUSER`—then attempt to run the extension logic as that user. Let me know if you need a sample Docker Compose snippet for a custom Postgres/pgvector setup or more details on user privilege simulation! <!-- Dosu Comment Footer --> *To reply, just mention [@dosu](https://go.dosu.dev/dosubot).* --- <sup>How did I do? [Good](https://app.dosu.dev/response-feedback/416dc036-8f57-4c1a-9b66-aa06b0324eb0?feedback_type=great_response) | [Irrelevant](https://app.dosu.dev/response-feedback/416dc036-8f57-4c1a-9b66-aa06b0324eb0?feedback_type=irrelevant_answer) | [Incorrect](https://app.dosu.dev/response-feedback/416dc036-8f57-4c1a-9b66-aa06b0324eb0?feedback_type=incorrect_sources) | [Verbose](https://app.dosu.dev/response-feedback/416dc036-8f57-4c1a-9b66-aa06b0324eb0?feedback_type=too_verbose) | [Hallucination](https://app.dosu.dev/response-feedback/416dc036-8f57-4c1a-9b66-aa06b0324eb0?feedback_type=hallucination) | [Report 🐛](https://app.dosu.dev/response-feedback/416dc036-8f57-4c1a-9b66-aa06b0324eb0?feedback_type=bug_report) | [Other](https://app.dosu.dev/response-feedback/416dc036-8f57-4c1a-9b66-aa06b0324eb0?feedback_type=other)</sup> [![Chat with Dosu](https://dosu.dev/dosu-chat-badge.svg)](https://app.dosu.dev/6088a4f4-073a-43f3-b3d4-4f06fb0c05a0/ask?utm_source=github)&nbsp;[![Open in Cursor](https://dosu.dev/dosu-in-cursor.svg)](https://cursor.com/link/prompt?text=Here%20are%20the%20steps%20and%20commands%20you%27ll%20need%3A%0A%0A%2A%2A1.%20Run%20the%20Dify%20backend%20locally%2A%2A%20%20%0AFrom%20%5B%60api/README.md%60%5D%28https%3A//github.com/langgenius/dify/blob/0a22bc5d05160afa0334e620a333699af1e2e2c0/api/README.md%23L13-L110%29%3A%0A%0A%60%60%60bash%0A%23%20Start%20middleware%20%28PostgreSQL%2C%20Redis%2C%20Weaviate%29%0Acd%20../docker%0Acp%20middleware.env.example%20middleware.env%0Adocker%20compose%20-f%20docker-compose.middleware.yaml%20--profile%20postgresql%20--profile%20weaviate%20-p%20dify%20up%20-d%0Acd%20../api%0A%0A%23%20Set%20up%20environment%0Acp%20.env.example%20.env%0A%23%20Generate%20a%20SECRET_KEY%20%28Linux%29%0Ased%20-i%20%22/%5ESECRET_KEY%3D/c%5CSECRET_KEY%3D%24%28openssl%20rand%20-base64%2042%29%22%20.env%0A%23%20Or%20%28Mac%29%0Asecret_key%3D%24%28openssl%20rand%20-base64%2042%29%0Ased%20-i%20%27%27%20%22/%5ESECRET_KEY%3D/c%5C%5C%0ASECRET_KEY%3D%24%7Bsecret_key%7D%22%20.env%0A%0A%23%20Install%20dependencies%0Apip%20install%20uv%20%20%23%20or%20brew%20install%20uv%0Auv%20sync%20--dev%0A%0A%23%20Run%20migrations%0Auv%20run%20flask%20db%20upgrade%0A%0A%23%20Start%20backend%20server%0Auv%20run%20flask%20run%20--host%200.0.0.0%20--port%3D5001%20--debug%0A%0A%23%20%28Optional%29%20Start%20worker%20for%20async%20tasks%0Auv%20run%20celery%20-A%20app.celery%20worker%20-P%20threads%20-c%202%20--loglevel%20INFO%20-Q%20dataset%2Cpriority_dataset%2Cpriority_pipeline%2Cpipeline%2Cmail%2Cops_trace%2Capp_deletion%2Cplugin%2Cworkflow_storage%2Cconversation%2Cworkflow%2Cschedule_poller%2Cschedule_executor%2Ctriggered_workflow_dispatcher%2Ctrigger_refresh_executor%0A%0A%23%20%28Optional%29%20Start%20celery%20beat%20for%20scheduled%20tasks%0Auv%20run%20celery%20-A%20app.celery%20beat%0A%60%60%60%0A%0A%2A%2A2.%20Run%20the%20pgvector%20integration%20tests%2A%2A%20%20%0AFrom%20%5B%60dev/pytest/pytest_vdb.sh%60%5D%28https%3A//github.com/langgenius/dify/blob/0a22bc5d05160afa0334e620a333699af1e2e2c0/dev/pytest/pytest_vdb.sh%23L4-L12%29%3A%0A%0A%60%60%60bash%0A%23%20Run%20only%20pgvector%20integration%20tests%0Auv%20run%20pytest%20tests/integration_tests/vdb/pgvector/%0A%23%20Or%20use%20the%20helper%20script%20%28runs%20all%20vector%20DB%20tests%2C%20including%20pgvector%29%0Abash%20dev/pytest/pytest_vdb.sh%0A%60%60%60%0A%0A%2A%2A3.%20Set%20up%20the%20PostgreSQL%20test%20environment%2A%2A%20%20%0A-%20The%20integration%20tests%20use%20a%20%60.env%60%20file%20for%20DB%20connection.%20Copy%20and%20edit%20as%20needed%3A%0A%20%20%60%60%60bash%0A%20%20cp%20api/tests/integration_tests/.env.example%20api/tests/integration_tests/.env%0A%20%20%60%60%60%0A%20%20The%20default%20config%20is%3A%0A%20%20%60%60%60%0A%20%20DB_USERNAME%3Dpostgres%0A%20%20DB_PASSWORD%3Ddifyai123456%0A%20%20DB_HOST%3Dlocalhost%0A%20%20DB_PORT%3D5432%0A%20%20DB_DATABASE%3Ddify%0A%20%20%60%60%60%0A-%20You%20can%20use%20Docker%20Compose%20to%20start%20a%20local%20PostgreSQL%20with%20the%20vector%20extension%3A%0A%20%20%60%60%60bash%0A%20%20docker%20compose%20-f%20docker-compose.middleware.yaml%20--profile%20postgresql%20-p%20dify%20up%20-d%0A%20%20%60%60%60%0A-%20To%20manually%20install%20the%20vector%20extension%20and%20set%20up%20users%3A%0A%20%201.%20Connect%20as%20admin%3A%0A%20%20%20%20%20%60%60%60bash%0A%20%20%20%20%20psql%20-h%20localhost%20-U%20postgres%20-d%20dify%0A%20%20%20%20%20CREATE%20EXTENSION%20IF%20NOT%20EXISTS%20vector%3B%0A%20%20%20%20%20CREATE%20USER%20dify_user%20WITH%20PASSWORD%20%27yourpassword%27%3B%0A%20%20%20%20%20GRANT%20ALL%20PRIVILEGES%20ON%20DATABASE%20dify%20TO%20dify_user%3B%0A%20%20%20%20%20--%20Optionally%2C%20restrict%20privileges%20to%20simulate%20non-admin%0A%20%20%20%20%20%60%60%60%0A%20%202.%20Update%20your%20%60.env%60%20to%20use%20%60dify_user%60%20for%20testing%20non-admin%20scenarios.%0A%0A-%20The%20integration%20tests%20also%20use%20TestContainers%20to%20spin%20up%20PostgreSQL%20and%20install%20extensions%20automatically%2C%20so%20you%20can%20run%20tests%20without%20manual%20DB%20setup%20if%20Docker%20is%20available%20%28%5Bsee%20conftest.py%5D%28https%3A//github.com/langgenius/dify/blob/0a22bc5d05160afa0334e620a333699af1e2e2c0/api/tests/test_containers_integration_tests/conftest.py%23L76-L489%29%29.%0A%0A%2A%2A4.%20Additional%20tests%20and%20checks%20before%20submitting%20your%20PR%2A%2A%20%20%0A-%20Run%20all%20tests%20to%20ensure%20nothing%20is%20broken%3A%0A%20%20%60%60%60bash%0A%20%20uv%20run%20pytest%0A%20%20%60%60%60%0A-%20Run%20only%20unit%20tests%3A%0A%20%20%60%60%60bash%0A%20%20uv%20run%20pytest%20tests/unit_tests/%0A%20%20%60%60%60%0A-%20Run%20only%20integration%20tests%3A%0A%20%20%60%60%60bash%0A%20%20uv%20run%20pytest%20tests/integration_tests/%0A%20%20%60%60%60%0A-%20Code%20quality%20checks%3A%0A%20%20%60%60%60bash%0A%20%20../dev/reformat%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%23%20Formatters%20and%20linters%0A%20%20uv%20run%20ruff%20check%20--fix%20./%20%20%20%20%20%23%20Fix%20linting%20issues%0A%20%20uv%20run%20ruff%20format%20./%20%20%20%20%20%20%20%20%20%20%23%20Format%20code%0A%20%20uv%20run%20basedpyright%20.%20%20%20%20%20%20%20%20%20%20%23%20Type%20checking%0A%20%20%60%60%60%0A%0AIf%20you%20want%20to%20simulate%20privilege%20errors%2C%20create%20a%20non-superuser%20and%20do%20not%20grant%20them%20%60CREATEROLE%60%20or%20%60SUPERUSER%60%E2%80%94then%20attempt%20to%20run%20the%20extension%20logic%20as%20that%20user.%0A%0ALet%20me%20know%20if%20you%20need%20a%20sample%20Docker%20Compose%20snippet%20for%20a%20custom%20Postgres/pgvector%20setup%20or%20more%20details%20on%20user%20privilege%20simulation%21)&nbsp;[![Join Discord](https://img.shields.io/badge/join-5865F2?logo=discord&logoColor=white&label=)](https://go.dosu.dev/discord-bot)&nbsp;[![Share on X](https://img.shields.io/badge/X-share-black)](https://twitter.com/intent/tweet?text=%40dosu_ai%20helped%20me%20solve%20this%20issue!&url=https%3A//github.com/langgenius/dify/issues/28989)
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
Knowledge Pipeline
Proposal
WIP
cloud
dependencies
feat:webapp
good first issue
outdated
performance
pull-request
Mirrored from GitHub Pull Request
 python:uv
refactor
size:L
stale
status: accepting prs
status: blocked
status: in discussion
stt & tts
vulnerablity
web
wont-fix
🌊 feat:workflow
🌚 invalid
🐞 bug
👻 feat:rag
💪 enhancement
📚 documentation
🔨 feat:tools
🙋‍♂️ question
🙏 help wanted
🤔 cant-reproduce
🤖 feat:agent
No Label 🐞 bug
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
yindo
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: langgenius/dify#20716
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?