Unable to Create Vector from UI: Permission related issue

[Pula_Bhargavi]

Hi we are getting this error Error creating vector storage: permission denied to create extension "vector" while creating vector in ui, checking logs in docker I can see STATEMENT: CREATE EXTENSION IF NOT EXISTS vector; CREATE TABLE __retool_vector_printer_f10707f2_003b_407f_b3b6_a8811456359d (id serial PRIMARY KEY, embedding vector(1536), content TEXT, source TEXT); 2025-04-10 11:58:29 2025-04-10 06:28:29.885 UTC [240] WARNING: there is no transaction in progress 2025-04-10 11:58:54 2025-04-10 06:28:54.641 UTC [242] ERROR: permission denied to create extension "vector" 2025-04-10 11:58:54 2025-04-10 06:28:54.641 UTC [242] HINT: Must be superuser to create this extension.

But the same command I am trying from command line is working so i am assuming its due to permission on UI level.

Is it due to the permission on Retool DB in UI on Read connection i am not able to change it into write

I am testing retool version 3.148.5 in my local.

Any help with this will be greatly appreciated,
Thanks

[Darren]

Welcome to the community, @Pula_Bhargavi! Thanks for reaching out.

You have two different options here:

1. Give the Retool user elevated SUPERUSER permissions
2. Connect to the database as an existing SUPERUSER and install the pgvector extension

In either case, you'll need to make sure you're connecting to the right database. You can find the necessary credentials in the RetoolDB UI.

Let me know if you have any specific questions about either approach!

[zelterNN]

It works well....but i think Retool can give a holistic instruction at some point to help self host user enabling the Vector feature. Current instruction is not very helpful.

• Install pgvector, an open-source PostgreSQL extension for similarity search, in the same database cluster as Retool Database. Ensure your pgvector version is compatible with your PostgreSQL version.

Here's my approach, not sure if there's any even simplier one:

1. Modify image part of retooldb-postgres in docker-compose.yml to be the one that has pgvector pre-installed. Be sure to match the postgre version (14 in this case).

    # image: "postgres:14.3"
    image: "pgvector/pgvector:pg14"

2. Re-build the containers

3. Run below query in

   • psql CLI in the active running retooldb-postgres container
     OR
   • the query library of Retool if you have setup the Retool DB as a resource

   SELECT * FROM pg_user
   

   Copy all user names except root in the returning result.
   There should be two. I assume one is for production env the other is for staging env. If you have setup other env, you might have more.

4. Run below query, still in one of the options in the previous step

   ALTER USER "<username1>" WITH SUPERUSER;
   ALTER USER "<username2>" WITH SUPERUSER;
   

   Replace the <username> with the ones you obtained from the previous step.
   Don't remove the double quote around.

You are then good to go!

[Darren]

That's definitely a valid workaround, @zelterNN! Thanks for jumping in and for the thorough documentation. One thing to note - if you use the pgvector image from the very beginning, then there shouldn't be any need to additionally grant elevated permissions to the database users.

[zelterNN]

What I have encountered:

If I don't grant the SUPERUSER access to the Retool DB user(s), I can use the Retool DB without issue (create table, insert records, etc.). But when I create the vector, it will show the exact error as in OP. I'm not sure what's the exact mechanism behind

@Pula_Bhargavi