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

From: Adrian Klaver <[email-masked]>
Date: Tue, 30 Sep 2025 11:11:51 -0700

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
[email-masked]

---

From: "Wong, Kam Fook (TR Technology)" <[email-masked]>
Date: Tue, 30 Sep 2025 19:51:49 +0000

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