Cannot `ALTER EXTENSION` due to owner conflicts

[henningko]

Bug report

• [x] I confirm this is a bug with Supabase, not with my own application.
• [x] I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

When trying to alter an an extension in a migration after installation in a previous migration, the operation fails due to ownership conflicts.

To Reproduce

Steps to reproduce the behavior, please provide code snippets or a repository:

1. Create a migration with

CREATE EXTENSION "vector";
ALTER EXTENSION "vector" SET SCHEMA extensions;

2. The following error is returned

Error: ERROR: must be owner of extension vector (SQLSTATE 42501)
At statement 2: ALTER EXTENSION "vector" SET SCHEMA extensions

This is due to the extension having the id of supabase_admin as its user listed in pg_extension.extowner. However, via the CLI, it is not possible to change extowner to the id of postgres, and there seems to be no programmatic way of changing the owner of an extension.

In some cases, the extension can be dropped and reinstalled. But if you already use a vector data type in a table, the operation will fail.

Expected behavior

alter extension [ext] set schema [schema] should work via the CLI as long as the extension is relocatable.

System information

• OS: macOS
• Version of supabase-cli: 1.50.4

[sweatybridge]

Transferred from CLI repo because it also affects the hosted platform.

[mercurydiogo]

any updates on this?

[03hgryan]

Any updates?

[03hgryan]

any updates on this?

If you use prisma, try this.

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["postgresqlExtensions"]
}

datasource db {
  provider   = "postgresql"
  url        = env("DATABASE_URL")
  extensions = [pgvector(map: "vector", schema: "extensions")]
}

[cwooldridge1]

any updates on this?

[olirice]

@soedirgo I was able to reproduce this no the hosted platform

any idea how easy/hard this would be to enable?

[soedirgo]

Don't foresee any issues with it - moving this to supautils for tracking