Обсуждение: Can't create a table with vector type as a non-super user
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:
- Granted usage of type vector to user
- No luck
- 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
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
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
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 anyattachments. Certain required legal entity disclosures can be accessed on our website: https://www.thomsonreuters.com/en/resources/disclosures.html
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
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 loadit globally by setting shared_preload_libraries in postgresql.conf. Or you might be interested in ALTER USER SET/ALTERDATABASE 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 > >