Thread

1. Can't create a table with vector type as a non-super user

   mrudula attili <mrudula.attili@gmail.com> — 2025-09-30T13:35:06Z

   Hello Team,
   
   
   I have recently installed the pgvector extension on one of our production
   databases.
   Managed to create the extension as super user and create table with the
   vector type.
   
   But a non super user (a read write user of the database) is not able to
   create a table with vector type and keeps getting the below error.
   
   CREATE TABLE items (id bigserial PRIMARY KEY, embedding vector(3))
   [2025-09-30 09:22:29] [42704] ERROR: type "vector" does not exist
   [2025-09-30 09:22:29] Position: 57
   
   Actions taken:
   
      1. Granted usage of type vector to user
   
   
      - No luck
   
   
      2. Granted usage on schema public to user
   
   
      - Worked
   
   Concern:
   As its a production environment, we are not really happy to give away the
   usage on public schema.
   
   Is there a way we could get the end users make use of the extension without
   granting usage on public schema
   
   
   Thanks,
   SA
   

2. Re: Can't create a table with vector type as a non-super user

   Tom Lane <tgl@sss.pgh.pa.us> — 2025-09-30T16:28:33Z

   mrudula attili <mrudula.attili@gmail.com> writes:
   > But a non super user (a read write user of the database) is not able to
   > create a table with vector type and keeps getting the below error.
   
   > CREATE TABLE items (id bigserial PRIMARY KEY, embedding vector(3))
   > [2025-09-30 09:22:29] [42704] ERROR: type "vector" does not exist
   > [2025-09-30 09:22:29] Position: 57
   
   Well, you have to grant usage on whatever schema the type is in.
   
   > As its a production environment, we are not really happy to give away the
   > usage on public schema.
   
   This seems like a very strange requirement.  What are you keeping in
   "public" that you don't want to be generally available in that
   database, and why?  You do understand the difference between USAGE
   and CREATE privileges for schemas, right?
   
   > Is there a way we could get the end users make use of the extension without
   > granting usage on public schema
   
   You could put it in some other schema, but then users would have to
   adjust their search_path or name the schema explicitly.  The design
   expectation is that you use the public schema for stuff that should
   be available to all SQL users, and put stuff that needs more
   protection in some other schema(s).
   
   There is a reasonable debate about whether giving out CREATE privilege
   on the public schema is a good idea (probably not, if you have
   not-fully-trustworthy users).  But I'm having a hard time seeing why
   you'd not want to give out USAGE.
   
   			regards, tom lane
   
   
   
   

3. Re: Can't create a table with vector type as a non-super user

   Adrian Klaver <adrian.klaver@aklaver.com> — 2025-09-30T18:11:51Z

   
   On 9/30/25 6:35 AM, mrudula attili wrote:
   > Hello Team,
   > 
   > 
   
   > Concern:
   > As its a production environment, we are not really happy to give away 
   > the usage on public schema.
   > 
   > Is there a way we could get the end users make use of the extension 
   > without granting usage on public schema
   
   In addition to what Tom Lane suggested:
   
   https://github.com/pgvector/pgvector/blob/master/vector.control
   
   relocatable = true
   
   And per here:
   
   https://www.postgresql.org/docs/current/extend-extensions.html#EXTEND-EXTENSIONS-RELOCATION
   
   "
   A fully relocatable extension can be moved into another schema at any 
   time, even after it's been loaded into a database. This is done with the 
   ALTER EXTENSION SET SCHEMA command, which automatically renames all the 
   member objects into the new schema. Normally, this is only possible if 
   the extension contains no internal assumptions about what schema any of 
   its objects are in. Also, the extension's objects must all be in one 
   schema to begin with (ignoring objects that do not belong to any schema, 
   such as procedural languages). Mark a fully relocatable extension by 
   setting relocatable = true in its control file.
   "
   
   You could move the extension to a schema that the users do have usage on.
   
   > 
   > 
   > Thanks,
   > SA
   > 
   > 
   > 
   
   -- 
   Adrian Klaver
   adrian.klaver@aklaver.com
   
   
   
   
   

4. pg_hint_tables

   Wong, Kam Fook (TR Technology) <kamfook.wong@thomsonreuters.com> — 2025-09-30T19:51:49Z

   I am trying to use pg_hint_tables in AWS Aurora Postgres.  I changed the Postgres parameter "pg_hint_plan.enable_hint_table = 1".  Then I logout but can't login anymore with the following error message.
   
   “postgres@postgres:[2719]:ERROR:  relation "hint_plan.hints" does not exist at character 21”.
   
   I undo the changes, log back in.  Can't find a good resources that lead me to the root cause.  So I try a few other things, see below.  But no luck.  Any guidance or help is greatly appreciated.
   
   ALTER EXTENSION pg_hint_plan  UPDATE TO '1.4';
   GRANT SELECT, INSERT, UPDATE, DELETE ON hint_plan.hints TO PUBLIC;
   GRANT USAGE ON SCHEMA hint_plan TO PUBLIC;
   
   Thank you
   Kam Fook Wong
   This e-mail is for the sole use of the intended recipient and contains information that may be privileged and/or confidential. If you are not an intended recipient, please notify the sender by return e-mail and delete this e-mail and any attachments. Certain required legal entity disclosures can be accessed on our website: https://www.thomsonreuters.com/en/resources/disclosures.html
   

5. Re: pg_hint_tables

   Adrian Klaver <adrian.klaver@aklaver.com> — 2025-09-30T20:13:21Z

   On 9/30/25 12:51, Wong, Kam Fook (TR Technology) wrote:
   > I am trying to use pg_hint_tables in AWS Aurora Postgres.  I changed the Postgres parameter "pg_hint_plan.enable_hint_table = 1".  Then I logout but can't login anymore with the following error message.
   
   To be clear it is pg_hint_plan not pg_hint_tables?
   
   > 
   > “postgres@postgres:[2719]:ERROR:  relation "hint_plan.hints" does not exist at character 21”.
   > 
   > I undo the changes, log back in.  Can't find a good resources that lead me to the root cause.  So I try a few other things, see below.  But no luck.  Any guidance or help is greatly appreciated.
   
    From here:
   
   https://github.com/ossc-db/pg_hint_plan/blob/master/docs/installation.md
   
   Did you do?:
   
   "
   Loading pg_hint_plan
   
   pg_hint_plan does not require CREATE EXTENSION. Loading it with a LOAD 
   command will activate it and of course you can load it globally by 
   setting shared_preload_libraries in postgresql.conf. Or you might be 
   interested in ALTER USER SET/ALTER DATABASE SET for automatic loading in 
   specific sessions.
   
   postgres=# LOAD 'pg_hint_plan';
   LOAD
   "
   > 
   > ALTER EXTENSION pg_hint_plan  UPDATE TO '1.4';
   > GRANT SELECT, INSERT, UPDATE, DELETE ON hint_plan.hints TO PUBLIC;
   > GRANT USAGE ON SCHEMA hint_plan TO PUBLIC;
   > 
   > Thank you
   > Kam Fook Wong
   -- 
   Adrian Klaver
   adrian.klaver@aklaver.com
   
   
   
   

6. Re: pg_hint_tables

   Rob Sargent <robjsargent@gmail.com> — 2025-09-30T20:24:48Z

   I am top posting because I believe you have “hikacked” a thread. 
   
   Rather than replying to an existing thread unrelated to your topic you should start your own specific thread. 
   
   
   > On Sep 30, 2025, at 2:13 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
   > 
   > On 9/30/25 12:51, Wong, Kam Fook (TR Technology) wrote:
   >> I am trying to use pg_hint_tables in AWS Aurora Postgres.  I changed the Postgres parameter "pg_hint_plan.enable_hint_table = 1".  Then I logout but can't login anymore with the following error message.
   > 
   > To be clear it is pg_hint_plan not pg_hint_tables?
   > 
   >> “postgres@postgres:[2719]:ERROR:  relation "hint_plan.hints" does not exist at character 21”.
   >> I undo the changes, log back in.  Can't find a good resources that lead me to the root cause.  So I try a few other things, see below.  But no luck.  Any guidance or help is greatly appreciated.
   > 
   > From here:
   > 
   > https://github.com/ossc-db/pg_hint_plan/blob/master/docs/installation.md
   > 
   > Did you do?:
   > 
   > "
   > Loading pg_hint_plan
   > 
   > pg_hint_plan does not require CREATE EXTENSION. Loading it with a LOAD command will activate it and of course you can load it globally by setting shared_preload_libraries in postgresql.conf. Or you might be interested in ALTER USER SET/ALTER DATABASE SET for automatic loading in specific sessions.
   > 
   > postgres=# LOAD 'pg_hint_plan';
   > LOAD
   > "
   >> ALTER EXTENSION pg_hint_plan  UPDATE TO '1.4';
   >> GRANT SELECT, INSERT, UPDATE, DELETE ON hint_plan.hints TO PUBLIC;
   >> GRANT USAGE ON SCHEMA hint_plan TO PUBLIC;
   >> Thank you
   >> Kam Fook Wong
   > --
   > Adrian Klaver
   > adrian.klaver@aklaver.com
   > 
   >