From: Tom Lane <tgl@sss.pgh.pa.us>
To: mrudula attili <mrudula.attili@gmail.com>
cc: pgsql-general@postgresql.org
Subject: Re: Can't create a table with vector type as a non-super user
In-reply-to: <CACmLaviY60Vx64hcacS687kBA3tXRfcYzrSJb6XaHU4X5qOrmQ@mail.gmail.com>
References: <CACmLaviY60Vx64hcacS687kBA3tXRfcYzrSJb6XaHU4X5qOrmQ@mail.gmail.com>
Comments: In-reply-to mrudula attili <mrudula.attili@gmail.com>
	message dated "Tue, 30 Sep 2025 14:35:06 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <498207.1759249713.1@sss.pgh.pa.us>
Date: Tue, 30 Sep 2025 12:28:33 -0400
Message-ID: <498208.1759249713@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/498208.1759249713%40sss.pgh.pa.us>
Precedence: bulk

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