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

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')  
       )  
   );

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;

[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.

[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

[NguyenTanThanh2402]

@soedirgo restricting on April 21, but today, when I login my project website (my project website using Supabase), it's return error. I change, add, detele user in Auth, it's return the same error {}
When I check log auth in Supabase, there is an error:

{"level":"fatal","msg":"running db migrations: error executing migrations/20240806073726_drop_uniqueness_constraint_on_phone.up.sql, sql: alter table auth.mfa_factors drop constraint if exists mfa_factors_phone_key;\ndo $$\nbegin\n -- if both indexes exist, it means that the schema_migrations table was truncated and the migrations had to be rerun\n if (\n select count(*) = 2\n from pg_indexes \n where indexname in ('unique_verified_phone_factor', 'unique_phone_factor_per_user')\n and schemaname = 'auth'\n ) then\n execute 'drop index auth.unique_verified_phone_factor';\n end if;\n\n if exists (\n select 1\n from pg_indexes\n where indexname = 'unique_verified_phone_factor'\n and schemaname = 'auth'\n ) then\n execute 'alter index auth.unique_verified_phone_factor rename to unique_phone_factor_per_user';\n end if;\nend $$;\n: ERROR: must be owner of table mfa_factors (SQLSTATE 42501)","time":"2025-03-21T08:32:17Z"}

Are the above restrictions the reason for this issue?
(I'm not the owner of my Supabase project. I was invited only)

[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!