Restricting Access on Auth, Storage, and Realtime Schemas on April 21, 2025

[soedirgo]

On April 21, we are restricting certain SQL actions you can perform in your database’s auth, storage, and realtime schemas.

Why are we making these restrictions?

Supabase Auth, Storage, and Realtime services each rely on their respective schemas in order to function properly.

These restrictions prevent unintended side effects like third-party tooling and user defined changes altering schemas or their objects, such as migration tables and database functions, that could disrupt or break functionality.

What this means for your project

On April 21, you will no longer be able to perform the following actions on the auth, storage, and realtime schemas:

• Create tables and database functions
• Drop existing tables or database functions
• Create indexes on existing tables
• Perform destructive actions (i.e. INSERT, UPDATE, DELETE, TRUNCATE) on the following migration tables:
  • auth.schema_migrations
  • storage.migrations
  • realtime.schema_migrations
• Revoking privileges on tables in these schemas from API roles (e.g. anon)

However, you will still have permissions to perform the following actions:

• Create foreign keys referencing tables in the auth, storage, and realtime schemas
• Create RLS policies and database triggers on the following tables:
  • auth.audit_log_entries
  • auth.identities
  • auth.refresh_tokens
  • auth.sessions
  • auth.users
  • storage.buckets
  • storage.migrations
  • storage.objects
  • storage.s3_multipart_uploads
  • storage.s3_multipart_uploads_parts
  • realtime.messages

How to determine if you’re affected?

• Run the following query to check if you created any tables in the auth, storage, and realtime schemas:

SET search_path = '';
SELECT oid::regclass AS table_name
FROM pg_class  
WHERE  
    (relnamespace = 'auth'::regnamespace AND relowner != 'supabase_auth_admin'::regrole)  
    OR (relnamespace = 'storage'::regnamespace AND relowner != 'supabase_storage_admin'::regrole)  
    OR (  
        relnamespace = 'realtime'::regnamespace
        AND relowner NOT IN (  
            SELECT oid  
            FROM pg_roles  
            WHERE rolname IN ('supabase_admin', 'supabase_realtime_admin')  
        )  
    );

• Run the following query to check if you created any database functions in the auth, storage, and realtime schemas:

SET search_path = '';
SELECT pg_catalog.format('%s(%s)', oid::regproc, pg_get_function_identity_arguments(oid::regproc)) AS function_name
FROM pg_proc  
WHERE  
   (pronamespace = 'auth'::regnamespace AND proowner != 'supabase_auth_admin'::regrole)  
   OR (pronamespace = 'storage'::regnamespace AND proowner != 'supabase_storage_admin'::regrole)  
   OR (  
       pronamespace = 'realtime'::regnamespace  
       AND proowner NOT IN (  
           SELECT oid  
           FROM pg_roles  
           WHERE rolname IN ('supabase_admin', 'supabase_realtime_admin')  
       )  
   );

What you need to do

If any of the above queries return a result, you must move them to either the public schema or a schema that you’ve created. Otherwise, they will be deleted.

• Here’s how you can move a table to another schema:

CREATE SCHEMA IF NOT EXISTS my_custom_schema;
ALTER TABLE storage.my_custom_table SET SCHEMA my_custom_schema;

• Here’s how you can move a database function to another schema:

CREATE SCHEMA IF NOT EXISTS my_custom_schema;
ALTER FUNCTION storage.custom_function() SET SCHEMA my_custom_schema;

Additionally, if you're using Migrations or Branching, you'll need to patch your migrations to move these objects to your own schemas. E.g. if you have a migration 20250101000000_add_custom_table.sql like so:

-- ...
CREATE TABLE auth.my_custom_table (
  -- id int8 ...
);
-- ...

Then you need to edit it locally into:

-- ...
CREATE SCHEMA IF NOT EXISTS my_custom_schema;
CREATE TABLE my_custom_schema.my_custom_table (
  -- id int8 ...
);
-- ...

Then you'll need to repair the migration history on the linked project:

supabase migration repair --status reverted 20250101000000
supabase migration repair --status applied 20250101000000

[khera]

If I have a migration file that does one of these now-prohibited things, and a subsequent migration that remediates this by moving the prohibited object to another schema, how will this interact with supabase db reset? I sense that the migrations will fail to apply and I can no longer recreate the exact schema I have on my hosted production instance.

It seems like I'd have to go back and edit the old migrations, and then run a on-off manual script to apply the above ALTER commands instead of making a remediation migration. This concerns me as I consider the history of changes to be immutable and trackable.

[soedirgo]

It seems like I'd have to go back and edit the old migrations

Yes, I think this is inevitable. What I would do is edit the old migrations to use the private schema from the start, but I don't know if this will interact poorly with supabase db push or other flows. I'll ask someone else from the team to see if there's a better alternative for this.

[soedirgo]

You can use the supabase migration repair command from the Supabase CLI to sync up the migration history. After editing old migrations, you can run supabase migration repair <your migration timestamp> and it'll sync up the migration history on your hosted production database.

[khera]

Would it be possible to get a docker version posted to test this ahead of time on the CLI? That is, can I change the version number of .temp/postgres-version to some new version and have the new restrictions enabled?

I am really concerned what the migration repair might do to my staging and production instances such that db push will stop working. What exactly does "sync up the history" mean? It can't be re-applying anything.

[soedirgo]

Would it be possible to get a docker version posted to test this ahead of time on the CLI?

Sure, I'll follow up here once we have it.

What exactly does "sync up the history" mean? It can't be re-applying anything.

It's not reapplying anything, it just modifies the migration history in supabase_migrations.schema_migrations.

[soedirgo]

Sorry for the late response - supabase/postgres:15.8.1.084 has the restrictions applied. You can use this version in .temp/postgres-version to have it applied only on local dev.

[haydn]

Thanks @haydn! This was a problem with the query. We've updated it now, can you check if you're still getting these results?

@soedirgo Perfect, I get no results now. (By the way, if you hide a comment, I can't reply to it.)

[soedirgo]

Great!

if you hide a comment, I can't reply to it

👍 sorry about that

[soedirgo]

That looks unrelated. Can you file a ticket via https://supabase.com/dashboard/support/new ?

[NguyenTanThanh2402]

That looks unrelated. Can you file a ticket via https://supabase.com/dashboard/support/new ?

Yes, I did. I'm waiting for Supabase Support's response. Thanks for your suggestion!

[vickkhera]

I'm trying the remediations on a local dev server (to prepare a script to run on production) with the Supabase CLI v2.20.12. It is not letting me. I really just want to delete these debugging functions from my production server. My cli is linked to my cloud instance at Postgres image 15.8.1.054. I get the same errors when running on a cloud hosted instance.

postgres=> begin;
BEGIN
Time: 3.672 ms
postgres=*> \df auth.login_as_user
                           List of functions
 Schema |     Name      | Result data type | Argument data types | Type
--------+---------------+------------------+---------------------+------
 auth   | login_as_user |                  | IN user_email text  | proc
(1 row)

postgres=*> drop function auth.login_as_user;
ERROR:  could not find a function named "auth.login_as_user"
Time: 15.147 ms
postgres=*> drop function auth.login_as_user(text);
ERROR:  auth.login_as_user(text) is not a function
Time: 13.387 ms
postgres=*> ALTER FUNCTION auth.login_as_user SET SCHEMA public;
ERROR:  could not find a function named "auth.login_as_user"
Time: 3.120 ms
postgres=*> rollback;
ROLLBACK
Time: 1.086 ms
postgres=> select version();
                                      version
------------------------------------------------------------------------------------
 PostgreSQL 15.8 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 13.2.0, 64-bit
(1 row)

Time: 2.230 ms

The functions are already invisible to me for DROP, and I cannot even alter their schema.

EDIT: I also tried locally using the supabase_admin role, and it also returns the same errors.

How do I drop or move these functions? I'm at a loss as to what is preventing it.

[vickkhera]

I also want to add that running the migration repair is very confusing.

npx supabase --debug migration repair --status applied 20230428170012

Fails with an error:

failed to update migration table: ERROR: duplicate key value violates unique constraint "schema_migrations_pkey" (SQLSTATE 23505)

So I must run it with --status reverted.

I end up with a migration list showing this migration is not applied on remote, which I suppose is ok as it is now an empty file.

[soedirgo]

Hey Vick, seems like it's failing because it's a procedure, not a function, so you'd need to run:

drop procedure auth.login_as_user;

Thanks for reporting, this wasn't accounted for in our remediation query above 🙏

[vickkhera]

Thanks. Second set of eyes always finds the issue quickly!

[wysohn]

Is there a way to apply these restrictions earlier? We would want to test it on our test database before it is applied to the production database

@soedirgo Thank you!

[soedirgo]

You'll be able to do so soon; I'll loop back here once I have updates 👍

[soedirgo]

You can now apply the restrictions by upgrading your Postgres version via Project Settings > Infrastructure

[AverageCakeSlice]

@soedirgo, do you have any suggestions for how to properly handle #16067 then? This change effectively nullifies the suggested DB trigger approach on auth.mfa_factors to clear out old unverified factors there.

[soedirgo]

Hi @AverageCakeSlice. Is this still a problem after this change?

[eqoram]

Hi @soedirgo! I also want to create a trigger on the table "auth.mfa_factors" to prevent users from unenrolling MFA. Will this interfere with these restrictions?

[soedirgo]

It will, but we can include auth.mfa_factors in the list of tables you can create RLS policies and triggers on. It'll take some time to be supported though.

[jdgamble555]

This just broke my entire database! I have custom functions in the schemas like auth.roles(), storage.folder_is_owner() etc, which are called randomly in different migration files.

Now all of my schemas are not going to match up in my migrations files etc. Can we get some better migration tooling at least since I can't even drop the supabase_migrations table anymore to fix this! Maybe at least a button to drop all migrations! The CLI is extremely confusing and doesn't have extensive tooling.

This creates so many problems!

J

[jdgamble555]

I don't understand how this works. We can't edit tables in supabase_migrations, but we can edit tables in auth schema (identities, users)? Yet, we can't add custom functions to the schema?

Are there rules to what we are allowed to and not allowed to do? It seems we shouldn't seed our users this way to be careful in case it does get removed, yet supabase provides no alternatives to seeding users...

What are we still allowed to do, and shouldn't do?

J

[soedirgo]

You should be able to edit the supabase_migrations schema. Can you share what command you tried ti run and what error you received? Same with seeding users.

The (incomplete) list of what you can/cannot do is in the post.

[jdgamble555]

I have not tested any of this, but it seems like I need to stay away from all supabase schemas generally speaking as I could lose access one day.

So supabase_migrations is fine, but not auth.schema_migrations, storage.migrations, or realtime.schema_migrations? What is the reasoning for this?

[soedirgo]

So supabase_migrations is fine, but not auth.schema_migrations, storage.migrations, or realtime.schema_migrations?

Yep that's right. Reasoning is in the post as well, but for these internal migration tables specifically we get a lot of issues reported that was caused by these tables being accidentally DELETEd (whether directly or through 3rd party tools). We've found no legitimate use for directly modifying them and it just isn't worth the support burden.

[jdgamble555]

Got it, so they are not really migrations, but just internal tables Supabase uses. The only two internal tables that I need to touch without workarounds are supabase_migrations and auth... so that should show you there are needs for these to be handled directly in sql.

[sbushell92]

I’ve followed the instructions above: moved all my custom functions to a new schema and updated all references accordingly.

Since the auth schema is now locked down, I’ve ensured none of my custom functions remain there. However, my migrations are still failing during CI (via GitHub Actions) with the following error:

ERROR: permission denied for schema auth

Given that the migration file has already been applied and the schema permissions have since changed, what’s the recommended way to resolve this while keeping my migration history intact?

[jdgamble555]

This is the biggest problem and seemed to not be considered when these irreversible changes were made. The migration tools are small and non-customizable.

I think it is just a local issue, so try updating your remote manually, and changing, the migration file directly that has already been applied. Only work around I know outside of removing all migration data, which is what I did to start over. Either way, dangerous.

[khera]

The way I had to repair my migration history in the cloud copy of my database was the following. I don't know if it is necessary, but I suspect if you did an audit of migrations and found the files differ it could cause issues at some later point.

First, remove the old migration from the remote database. You select the ID from the output of npx supabase migration list

npx supabase migration repair --status reverted 20230428170012

Then edit the migration file (making whatever hacks you have to your git commit rules that may prevent it). Luckily for me, they ended up being empty, so I just left a single comment line referring to my git issue number for details. This is horribly bad practice to edit some file that should be considered immutable once applied, but here we are being forced to do so.

Finally, you have to tell the migration to be reapplied out of sequence. This is ok, since the migration file should now be a no-op.

npx supabase migration up --include-all

Now the migration history matches exactly with the migration files, and applying new migrations will work as expected.

[EpicWerf]

@soedirgo can you help me figure out what I'm not understanding?

I just updated my CLI last night to use supabase 2.23.4, and started getting ERROR: must be owner of table objects (SQLSTATE 42501) when I do a supabase db reset on my local dev

The only places in my migration files that I'm seeing an error is where I've added policies to my buckets, which, according to what you said in the original post, shouldn't be an issue, right?

However, you will still have permissions to perform the following actions:

Create foreign keys referencing tables in the auth, storage, and realtime schemas
Create RLS policies and database triggers on the following tables:
storage.buckets
storage.migrations
storage.objects

Here's an example of a policy that is giving me an issue

-- Customer Bucket Policies
DROP POLICY IF EXISTS "Allow managers write access to company folder" ON storage.objects;
CREATE POLICY "Allow managers write access to company folder" ON storage.objects
FOR INSERT TO authenticated
WITH CHECK (
    bucket_id = 'logo'
    AND EXISTS (
        SELECT 1 FROM public.user u
        WHERE u.id = auth.uid()
        AND u.company_id::text = (storage.foldername(name))[1]
        AND u.role = 'Manager'
    )
);

[EpicWerf]

@nietsmmar @KamiTzayig I contacted supabase support and after doing removing my .temp folder, it worked fine!

Here is the original message I got from support:

Can you double check the postgres version you are running locally? You can get it from the command: supabase services

In addition, please also check that you have set major_version = 15 or above in your local config.toml. Sometimes these permissions are due to outdated postgres version.

Lastly, you can try removing supabase/.temp directory to start local dev from a clean state. For eg.

rm -rf supabase/.temp
supabase stop --no-backup
supabase start

Let me know if it works.

[nietsmmar]

@EpicWerf thank you for sharing this information! Sadly this did not help in my case.

[actraiser]

I also require to create policies programmatically via migration files which is now permitted. Please grant CREATE POLICY access back to user postgres again on storage.objects - its crucial.

[soedirgo]

This looks to be the same issue as https://github.com/orgs/supabase/discussions/34270#discussioncomment-13505899 - @actraiser can you try the suggestion there?

[actraiser]

thanks @soedirgo , deleting/re-creating the volume did indeed fix the problem for me.

[jhdhammond]

Hey do we have any info on the timeline for deletion of functions on the restricted schemas?

The email/this post indicates 21 April for restricting creation, but no specific date for the statement "Otherwise, they will be deleted"

Currently working to relocate & fix my migration files, but could be described as bricking it that my prod instance is just going have functions deleted on it outside of my control.

[jhdhammond]

Actually just resolved my concerns, needed to do some digging to find I'm not relying on the custom auth functions anymore!
Just need to sync the migration files and should be all good 👍

[eggcaker]

did this restricting access on auth.users will complete block the trigger on auth.users to return a custom errror to api reponse ? like this issue supabase/auth#1602

[soedirgo]

This should be tangential to that issue, you can still create triggers on auth.users.

[ebellani]

Folks, this is, for me, a breaking change. I have alterations in old migrations to auth . What is the advise here?

[khera]

1. un-do the alterations
2. update your code to adapt to the changes
3. edit your old migrations and optionally fix the history on the cloud version as I did above
4. profit.

[ebellani]

I can understand your line of thought, but I find it underwhelming that supabase would settle the user with such (potentially catastrophic) work as editing the past structural modifications of a DBMS.

[jdgamble555]

100,000%

[skusez]

I feel like a simple message after getting

ERROR: permission denied for schema auth (SQLSTATE 42501)                

pointing to this discussion would have gone a long way.

This is a breaking change and I had to dig up this thread by searching through help messages. I understand the motive but communication should've been better here.

[khera]

I can understand your line of thought, but I find it underwhelming that supabase would settle the user with such (potentially catastrophic) work as editing the past structural modifications of a DBMS.

The developer experience has been on the decline for a year now, especially if you're not using the web UI to manage the database. This particular change is probably the worst so far in terms of what you need to do to keep your production system and development systems in sync and working going forward. It feels like they rushed it through due to support concerns and did not take much consideration on the DX side of things.

The time line on this change was ridiculously short. There were no preview docker images provided to let us test our approach before the new rules were enforced.

Unfortunately, this is what we're stuck with and must do our best to work around it. There is no way to automate this; you really do have to analyze what your system needs and hand craft the adjustments.

[Aietes]

We have a concrete struggle in our development setup: We are seeding dev/test data via seed.sql. This seed is only applied in dev, not in production. In the seed we need to create users, therefore we perform INSERT INTO auth.users. Unfortunately, this now fails our database resets in dev.

ERROR: must be owner of table users (SQLSTATE 42501)

From reading the above, I was assuming I could still write to auth.users? In the op there is a blacklist, with things explicitly forbidden, and a whitelist, with things explicitly allowed. What about things not on either of these lists, like our use case?

The limitations are not an issue in production, but if it is intentional that we cannot insert to any auth table in dev, it would make things difficult for development and testing.

[nietsmmar]

@Aietes I think it could be the same problem that I have here https://github.com/orgs/supabase/discussions/34270#discussioncomment-13335078

[Aietes]

It's probably related to a trigger we have on auth.users. But it's still confusing, as the trigger is using a function in public, so it should be fine:

CREATE TRIGGER create_professional_after_user
AFTER INSERT ON auth.users
FOR EACH ROW
EXECUTE FUNCTION public.handle_new_user();

The function to handle new users is running with elevated privileges, but is only writing into public.:

CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = ''
AS $$

In the seed we are disabling this trigger while inserting users, that seems to produce the error:

ALTER TABLE auth.users DISABLE TRIGGER create_professional_after_user;

ERROR: must be owner of table users (SQLSTATE 42501)

I think this should work and not throw an error, or am I missing something?

[Aietes]

I think we have tracked down the issue. We can drop the trigger, but have no permission to disable/enable it. I think if we can create and drop triggers on auth.users, we should be able to also disable/enable triggers. Where do I best address this to be fixed?

[soedirgo]

I think if we can create and drop triggers on auth.users, we should be able to also disable/enable triggers.

Agreed, we may add this in the future but this is not supported at the moment.

If you only need to disable the trigger temporarily, you can try using session_replication_role instead:

set session_replication_role to replica;
INSERT INTO auth.users ...;
reset session_replication_role;

[Xananax]

Probably related: On provisioning a new local Supabase docker for testing, my start was failing with

must be owner of relation users

This was my SQL:

DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
CREATE OR REPLACE TRIGGER on_auth_user_created
AFTER INSERT ON auth.users
FOR EACH ROW
EXECUTE FUNCTION profile_utils.create_profile_for_user();

COMMENT ON TRIGGER on_auth_user_created ON auth.users IS 'Adds a profile. Set to trigger when a user is added';

The faulty line was COMMENT ON TRIGGER .... I comment everything in my DB, each function, column, table, and trigger.

I tried surrounding the COMMENT with set session_replication_role to replica but to not effect.

This isn't a deal breaker, and in the end I commented out those lines, but it is bothersome that I can document everything else in my schema, except those triggers; makes it feel inconsistent.

[nietsmmar]

I thought creating RLS policies on storage.objects is still allowed or did i misunderstand the post?
For me running supabase db reset fails with

ERROR: must be owner of table objects (SQLSTATE 42501)
At statement 22:                                      
--create policy "Anyone can upload an avatar."        
--on "storage"."objects"                              
--as permissive                                       
--for insert                                          
--to public                                           
--with check ((bucket_id = 'avatars'::text));

The referenced migration is exactly:

create policy "Anyone can upload an avatar."
on "storage"."objects"
as permissive
for insert
to public
with check ((bucket_id = 'avatars'::text));

What should I do now? How can I create RLS for storage then?

[jdgamble555]

@soedirgo

[j7i]

I am experiencing a similar issue locally: v2.24.3

Error adding policy: ERROR: 42501: must be owner of table objects

0. I am not seeing any results when running these queries: https://github.com/orgs/supabase/discussions/34270
1. I compared the generated config.toml with the one freshly generated by yarn supabase init – couldn't spot any relevant differences to the one in my project.
2. When I schema diff the storage schema with supabase db diff --schema storage, I get loads of "changes suggested" to apply – but already the first attempt to run any of these will result in the same error. Have not started working/using storage features directly till now.
3. So I deleted all my migration files etc. and give the UI a try after restarting and resetting the db: Even though I can create a bucket – I am not able to create any policy through the dashboard as well (same permission error).

Q: What if I work around the issue locally by just marking the bucket as public and have RLS active on deployed versions only 😕 to unblock further development?
A: Well even then I get the same permission errors, though I can see the changes (public) reflected when the bucket either gets created with the dashboard, or with a config like this:

[api]
enabled = true
port = 54321
# not sure how the api would play a part in this ["public", "storage", "graphql_public"] | but both configs don't help
schemas = ["public", "graphql_public"]
extra_search_path = ["public", "extensions"]
max_rows = 1000

[db]
port = 54322
shadow_port = 54320
major_version = 15

[storage]
enabled = true
file_size_limit = "50MiB"

[storage.image_transformation]
enabled = true

[storage.buckets.test-bucket]
public = true
file_size_limit = "10MiB"
allowed_mime_types = ["image/*", "application/pdf"]

# I assume that these should not be relevant regarding permission handling
[experimental]
orioledb_version = ""
s3_host = "env(S3_HOST)"
s3_region = "env(S3_REGION)"
s3_access_key = "env(S3_ACCESS_KEY)"
s3_secret_key = "env(S3_SECRET_KEY)"

The error within the browser console when trying to upload into to public bucket / no RLS applied, using the (super nice) shadcn-dropzone example : same-same

{
  "statusCode": "403",
  "error": "Unauthorized",
  "message": "new row violates row-level security policy"
}

---

Any suggestion what I could try next; or what info I should provide in addition?

[nietsmmar]

@j7i Thanks for reporting all the details. I have tried the same. The only workaround at the moment is to edit the ./temp/postgres-version file to this version => 15.8.1.069. As this is the last postgres version that works locally for me.

[j7i]

@nietsmmar May try to delete all supabase related containers, images and especially volumes locally and start (e.g. 15.8.1.099) with fresh pulls.

At least this solved my issue which is probably the same as yours.

And thanks a lot for bringing up the ./temp folder – this helped me ending up with the proposed cleanup/solution.

[soedirgo]

For others having a similar issue, we couldn't reproduce this internally but what worked was to delete the docker volume (this resets your local database):

To do this first stop all running containers

supabase stop

Next, list the volumes for your project (this command should be output for you when you run supabase stop)

docker volume ls --filter label=com.supabase.cli.project=YOUR-PROJECT-NAME

Delete the database volume:

docker volume rm supabase_db_supa-YOUR-PROJECT-NAME

Now when you run supabase start it will need to create your database from scratch

[icopp]

With access to creating indexes gone on storage.objects, could y'all at least add some basic ones to make custom functionality less bad, like jsonb_ops or jsonb_path_ops indexes on the metadata and user_metadata columns?

[jdgamble555]

Out of curiosity, what situation would you need to access these directly?

[icopp]

Out of curiosity, what situation would you need to access these directly?

We have user_metadata values set on S3 API uploads that are used for later query filtering, as well as filtering on metadata values like size and mimetype. The whole storage experience is fairly cumbersome and un-integrated in the first place, so we specifically wanted to avoid having another table needing to be kept in sync with the files.

[soedirgo]

I've passed this to the Storage team and opened a feature request here for tracking

[elyobo]

@soedirgo, does this change also prevent us from changing permissions on storage? We previously revoked access to anon along the lines of what's discussed in https://github.com/orgs/supabase/discussions/4547.

On public this still works but storage now generates a lot of WARNING (01006): no privileges could be revoked for "objects" style warnings when doing supabase db reset in local dev and tests, which assert that access has been revoked, fail because no permission denied errors are thrown.

We are on /supabase/postgres:15.6.1.127 in prod but were looking at 15.8.1.085 and this has changed between the two and these change sound like a likely culprit even though it's not described in the original description of the discussion.

[soedirgo]

If you're still on PG15 in prod you can do this:

revoke anon from authenticator;

On PG17 projects this isn't currently possible but we're working on making it possible.

[elyobo]

Thanks, if this is an impact can it be documented in the description above? Potentially a security problem for people that relied on revoking that access if the upgrade reinstates it (depends on their RLS policies).

[soedirgo]

Updated, thanks for the suggestion! We'll update our Hardening the Data API page with an alternative

[brcamor]

I think this change might have led to the following situation I'm in:

• created a webhook that triggers on realtime.subscription INSERT (by mistake) and calls an edge function
• can't delete or update this trigger in realtime.subscription either through the dashboard UI or sql editor - in both I get the error "Failed to delete database trigger: must be owner of relation subscription"

It seems that I have no way to remove the trigger and consequently I am currently triggering 1000s of edge function invocations

[GaryAustin1]

I can confirm this on old and new instances.

[soedirgo]

As noted by @GaryAustin1 this is unrelated to this change. There'll be follow up changes to prevent creating the trigger by mistake.

For now you can file a support ticket from the dashboard to get the trigger dropped.

[istarkov]

For unknown reason we have all system auth tables returned by above query. I have feeling that if I move them our production DB will die.

"auth.mfa_amr_claims"
"auth.refresh_tokens"
"auth.saml_providers"
"auth.users"
"auth.schema_migrations"
"auth.sso_providers"
"auth.instances"
"auth.mfa_challenges"
"auth.sso_domains"
"auth.audit_log_entries"
"auth.sessions"
"storage.migrations"
"auth.mfa_amr_claims_session_id_authentication_method_pkey"
"auth.saml_providers_entity_id_key"
"auth.instances_pkey"
"auth.mfa_challenges_pkey"
"auth.amr_id_pk"
"auth.audit_log_entries_pkey"
"auth.identities_pkey"
"auth.mfa_factors_pkey"
"auth.refresh_tokens_pkey"
"auth.refresh_tokens_token_unique"
"auth.saml_providers_pkey"
"auth.sessions_pkey"
"auth.users_email_key"
"auth.users_phone_key"
"auth.users_pkey"
"auth.schema_migrations_pkey"
"auth.sso_providers_pkey"
"auth.sso_domains_pkey"
"auth.sessions_user_id_idx"
"auth.user_id_created_at_idx"
"auth.refresh_token_session_id"
"auth.refresh_tokens_instance_id_idx"
"auth.refresh_tokens_instance_id_user_id_idx"
"auth.refresh_tokens_parent_idx"
"auth.identities_user_id_idx"
"auth.factor_id_created_at_idx"
"auth.mfa_factors_user_friendly_name_unique"
"auth.saml_relay_states_for_email_idx"
"auth.sso_sessions_pkey"
"storage.buckets_pkey"
"storage.objects_pkey"
"auth.refresh_tokens_session_id_revoked_idx"
"auth.refresh_tokens_token_idx"
"auth.saml_providers_sso_provider_id_idx"
"auth.confirmation_token_idx"
"auth.email_change_token_current_idx"
"auth.email_change_token_new_idx"
"auth.reauthentication_token_idx"
"auth.recovery_token_idx"
"auth.users_instance_id_email_idx"
"auth.users_instance_id_idx"
"auth.sso_providers_resource_id_idx"
"auth.audit_logs_instance_id_idx"
"auth.sso_domains_domain_idx"
"auth.sso_domains_sso_provider_id_idx"
"storage.migrations_name_key"
"storage.migrations_pkey"
"auth.refresh_tokens_id_seq"
"auth.identities"
"auth.mfa_factors"
"auth.saml_relay_states"
"auth.saml_relay_states_sso_provider_id_idx"
"auth.sso_sessions"
"auth.sso_sessions_session_id_idx"
"storage.buckets"
"storage.bname"
"storage.objects"
"storage.bucketid_objname"
"storage.name_prefix_search"
"auth.saml_relay_states_pkey"
"auth.sso_sessions_sso_provider_id_idx"

[soedirgo]

What most likely happened was these objects were dropped and recreated through some third party migration tool, which changes the object ownership. If you file a support ticket we can help restore the ownerships.

[DvirH]

@soedirgo are you serious? auth is part of supabase ecosystem, why should I move it?

[istarkov]

reference ID: kmdpixzoqiirbmpdippy

[soedirgo]

@istarkov can you file a support ticket?

@DvirH the objects were dropped and recreated, so it can no longer be managed by Supabase. You need to file a support ticket to get it restored.

[istarkov]

I have filled support ticket it's reference ID is kmdpixzoqiirbmpdippy

[DvirH]

Running this query results with these items. I never created them, When I created DB in supabase it was part of it. What does it mean now? What should I do? will the login and authorization stop working now?

  {
    "function_name": "auth.click_house_fdw_meta()"
  },
  {
    "function_name": "auth.auth0_fdw_meta()"
  },
  {
    "function_name": "auth.auth0_fdw_validator(options text[], catalog oid)"
  },
  {
    "function_name": "auth.big_query_fdw_handler()"
  },
  {
    "function_name": "auth.big_query_fdw_meta()"
  },
  {
    "function_name": "auth.click_house_fdw_validator(options text[], catalog oid)"
  },
  {
    "function_name": "auth.big_query_fdw_validator(options text[], catalog oid)"
  },
  {
    "function_name": "auth.click_house_fdw_handler()"
  },
  {
    "function_name": "auth.cognito_fdw_handler()"
  },
  {
    "function_name": "auth.cognito_fdw_meta()"
  },
  {
    "function_name": "auth.cognito_fdw_validator(options text[], catalog oid)"
  },
  {
    "function_name": "auth.firebase_fdw_handler()"
  },
  {
    "function_name": "auth.firebase_fdw_meta()"
  },
  {
    "function_name": "auth.logflare_fdw_validator(options text[], catalog oid)"
  },
  {
    "function_name": "auth.mssql_fdw_handler()"
  },
  {
    "function_name": "auth.firebase_fdw_validator(options text[], catalog oid)"
  },
  {
    "function_name": "auth.hello_world_fdw_handler()"
  },
  {
    "function_name": "auth.mssql_fdw_meta()"
  },
  {
    "function_name": "auth.mssql_fdw_validator(options text[], catalog oid)"
  },
  {
    "function_name": "auth.hello_world_fdw_meta()"
  },
  {
    "function_name": "auth.hello_world_fdw_validator(options text[], catalog oid)"
  },
  {
    "function_name": "auth.logflare_fdw_handler()"
  },
  {
    "function_name": "auth.logflare_fdw_meta()"
  },
  {
    "function_name": "auth.redis_fdw_handler()"
  },
  {
    "function_name": "auth.redis_fdw_meta()"
  },
  {
    "function_name": "auth.redis_fdw_validator(options text[], catalog oid)"
  },
  {
    "function_name": "auth.s3_fdw_handler()"
  },
  {
    "function_name": "auth.s3_fdw_meta()"
  },
  {
    "function_name": "auth.s3_fdw_validator(options text[], catalog oid)"
  },
  {
    "function_name": "auth.stripe_fdw_handler()"
  },
  {
    "function_name": "auth.stripe_fdw_meta()"
  },
  {
    "function_name": "auth.stripe_fdw_validator(options text[], catalog oid)"
  },
  {
    "function_name": "auth.wasm_fdw_handler()"
  },
  {
    "function_name": "auth.wasm_fdw_meta()"
  },
  {
    "function_name": "auth.wasm_fdw_validator(options text[], catalog oid)"
  },
  {
    "function_name": "auth.airtable_fdw_handler()"
  },
  {
    "function_name": "auth.airtable_fdw_meta()"
  },
  {
    "function_name": "auth.airtable_fdw_validator(options text[], catalog oid)"
  },
  {
    "function_name": "auth.auth0_fdw_handler()"
  }
]```

[GaryAustin1]

Looks like the wrapper extension which is supposed to be installed in extensions schema was set to auth schema at some point and installed.
The default in the UI is extensions.

[GaryAustin1]

They are not installed by default.

[soedirgo]

In this case the only fix is to do:

drop extension wrappers;
create extension wrappers schema extension;

If the above gives errors please file a support ticket so we can manually fix it for you.

[nanbingxyz]

@soedirgo I retrieved the function auth.user_id() using the query below.

SET search_path = '';
SELECT pg_catalog.format('%s(%s)', oid::regproc, pg_get_function_identity_arguments(oid::regproc)) AS function_name
FROM pg_proc  
WHERE  
   (pronamespace = 'auth'::regnamespace AND proowner != 'supabase_auth_admin'::regrole)  
   OR (pronamespace = 'storage'::regnamespace AND proowner != 'supabase_storage_admin'::regrole)  
   OR (  
       pronamespace = 'realtime'::regnamespace  
       AND proowner NOT IN (  
           SELECT oid  
           FROM pg_roles  
           WHERE rolname IN ('supabase_admin', 'supabase_realtime_admin')  
       )  
   );

Following the documentation, I attempted to update its schema, but after running the operation:

ALTER FUNCTION auth.user_id() SET SCHEMA public;

the function auth.user_id() still exists. It seems the schema change did not take effect.

[soedirgo]

Can you file a support ticket? There’s an unrelated issue that causes the schema change to not take effect.

[Xananax]

I received an email warning me of the changes a few days ago, and telling me the restrictions will be applied on the 28th of July. We're a small operation of a few people with much more on our hands than we can chew already; I'm the only one in our team with enough domain knowledge to do the transition properly (we have multiple processes depending on functions in auth, triggers, and so on).

I had planned to take a 3 day vacation in the next few weeks (we do not have days off as we're trying to get our operation to a stable place). We're also planning an update which has been locked for months, which we will now need to rush to make space for this. This throws a significant wrench in our daily operations, as we're going to have to form a migration strategy, create tests, do a number of functional changes, and then perform the migration in production.

For breaking changes like these, the grace period should be one to three years, not one month. I'm usually very satisfied with Supabase and tend to recommend it wherever I go, but this is the kind of thing that triggers immediate alarms; I suddenly don't feel like my operation is safe anymore.

Is there any possibility of getting at least 3 additional months, so we have time to prepare properly?

[ edit: I am aware through this thread that these changes were announced in April, but I wasn't told -- I don't read the changelog or follow the issues unless I have time -- and it still wouldn't have been enough time ]

[Xananax]

I created a migration script, which works locally. But when applied as a migration, it has no effect. When running the script from the SQL editor, it also has no effect.

I've tried just running one statement, like so:

ALTER FUNCTION auth.create_user SET SCHEMA profile_utils

While I do not get any error, the auth schema is untouched, and the profile_utils schema (which I just created) is empty. The same migration script creates the profile_utils schema, so clearly, the code runs.

My script is also sprinkled of RAISE LOG statements to verify everything is ok, and, according to the logs, everything is; but nothing happens.

The behavior is similar to running a script in a transaction that has ROLLBACK at the end; I'm stumped.

[kazuooooo]

@kazuooooo
I encountered the same problem. alter schema is not available on production now.
You should recreate the same function.

[soedirgo]

Hi all, thanks for the responses so far.

The lack of comms is definitely on us. I agree in hindsight that a one month grace period is too short; for future breaking changes of this sort we'll give a minimum of 90 days grace period unless there's a strong reason to do otherwise.

As for why some didn't receive the email in March, it was only sent to a subset of users; again this is a failing of our comms and we'll improve our process so this doesn't happen again.

@Xananax please file a support ticket with a link to this thread, we'll exclude your projects from the restrictions rollout on July 28th. Really sorry for how we're handling this. That applies for other folks who need a longer lead time as well - we're trying to make the migration as smooth as possible, so please reach out to us if you need more time or need some help with performing the migration.

[soedirgo]

@Xananax @kazuooooo we've received a lot of reports of ALTER ... SET SCHEMA being ineffective; this is a known internal bug unrelated to the schema restrictions. Please file a support ticket; we'll apply the fix ASAP.

[soedirgo]

@wysohn you can apply the restrictions earlier by upgrading your project's Postgres version via Project Settings > Infrastructure. Note however that this is irreversible; you won't be able to revert to an older Postgres version.

[epurban]

If we can't create custom functions inside of the auth schema anymore, this is breaking tutorials like this auth0 one: https://supabase.com/partners/integrations/auth0

I'm not sure how to proceed

[silentworks]

You can create your own schema and add that function to it. You will just reference it from your own schema going forward. Yes this documentation will need updating along with a few others.

[muezz]

If we are not supposed to include the storage schema in our migration files, how will diffing include the policies on the buckets?

[soedirgo]

Can you share the exact errors here? The diff shouldn't be dropping iceberg_tables etc. since they are internal Storage tables.

[muezz]

@soedirgo I use taskfile (a nicer variant of makefile) so my commands have some variables:

vars:
  SP_CLI: "npx supabase@latest"
  SMPL_SCHEMAS: "public,smpl,smpl_views,smpl_billing,smpl_repairs,smpl_site"
  INTERNAL_SCHEMAS: "extensions,sys,storage,auth"
  ALL_SCHEMAS: "{{.INTERNAL_SCHEMAS}},{{.SMPL_SCHEMAS}}"
sp-main-pull-db:
    silent: true
    dotenv: [.env.main]
    cmd: "{{.SP_CLI}} db pull --schema={{.ALL_SCHEMAS}} --db-url $SP_DB_URL {{.CLI_ARGS}}"
sp-local-reset:
    silent: true
    prompt: 'This command resets your local Supabase instance. You may lose any uncomitted changes. Are you sure?'
    cmd: "{{.SP_CLI}} db reset {{.CLI_ARGS}}"

I have to run task sp-main-pull-db twice as schemas auth and storage are not diffed in the first attempt. Once both are done, I run task sp-local-reset to reset the local instance.

Here are my terminal logs:

abdulmuezzejaz@Abduls-MacBook-Pro smpl_dashboard % task sp-main-pull-db          
Connecting to remote database...
Schema written to supabase/migrations/20250811074224_remote_schema.sql
Update remote migration history table? [Y/n] y
NOTICE (42P06): schema "supabase_migrations" already exists, skipping
NOTICE (42P07): relation "schema_migrations" already exists, skipping
NOTICE (42701): column "statements" of relation "schema_migrations" already exists, skipping
NOTICE (42701): column "name" of relation "schema_migrations" already exists, skipping
Repaired migration history: [20250811074224] => applied
Finished supabase db pull.
The auth and storage schemas are excluded. Run supabase db pull --schema auth,storage again to diff them.
abdulmuezzejaz@Abduls-MacBook-Pro smpl_dashboard % task sp-main-pull-db
Connecting to remote database...
Initialising schema...base...
Seeding globals from roles.sql...
Applying migration 20250811074224_remote_schema.sql...
Schema written to supabase/migrations/20250811074524_remote_schema.sql
Update remote migration history table? [Y/n] y
NOTICE (42P06): schema "supabase_migrations" already exists, skipping
NOTICE (42P07): relation "schema_migrations" already exists, skipping
NOTICE (42701): column "statements" of relation "schema_migrations" already exists, skipping
NOTICE (42701): column "name" of relation "schema_migrations" already exists, skipping
Repaired migration history: [20250811074524] => applied
Finished supabase db pull.
abdulmuezzejaz@Abduls-MacBook-Pro smpl_dashboard % task sp-local-reset 
This command resets your local Supabase instance. You may lose any uncomitted changes. Are you sure? [y/N]: y
Resetting local database...
Recreating database...
Initialising schema...
Seeding globals from roles.sql...
Applying migration 20250811074224_remote_schema.sql...
Applying migration 20250811074524_remote_schema.sql...
ERROR: must be owner of relation prefixes (SQLSTATE 42501)                
At statement: 4                                                           
drop trigger if exists "prefixes_create_hierarchy" on "storage"."prefixes"
Try rerunning the command with --debug to troubleshoot the error.
task: Failed to run task "sp-local-reset": exit status 1

Here is the second migration file

[soedirgo]

You shouldn't have anything that touches the auth or storage schema in the output of supabase db pull except create policy. Can you delete the migration contents except for create policy and run supabase migration repair as per the instructions?

[muezz]

@soedirgo That is what I am doing at the moment. I manually remove everything and then repair the schema as applied. I dont really have a blocker at the moment but I think things could be improved in two ways:

1. When pulling the storage schema, only the policies are pulled.
2. Or at least, the schema that is pulled is valid. I am not sure where prefixes and the rest are coming through.

[soedirgo]

I've filed the issue here

[mihierchopra-Dakota]

I am getting permission denied error on app_auth (I think this was created automatically by copying the functions in the auth schema) schema. Even after giving all the privilages, I still get the permission denied error on app_auth schema. Why that's happening?