Security Patch Notice

[sweatybridge]

Security Patch Notice

To better secure your Supabase server instances, we will be removing superuser access from the dashboard SQL Editor over the next 30 days. Existing projects with tables, functions, or other Postgres entities created via the dashboard SQL Editor require a one time migration to be run. This migration should take less than 10 seconds to run but since it modifies your existing schema, we will be rolling out this change over a buffer period to minimise breakages.

Opt-in Period: 5 Oct - 5 Nov

During the opt-in period, a notification will be delivered to all affected Supabase projects. The notification contains instructions to manually apply the migration. If you have separate staging and production Supabase projects, apply it on the staging project first to verify everything is working as expected.

If you only have one Supabase project, try to avoid hours of high application traffic when applying the migration to minimise potential downtime. If you notice elevated error rates or other unusual activities after migrating, follow the rollback instructions to revert the change. Both apply now and rollback actions are idempotent. If you encounter any problems during migration or rollback, please contact [email protected] for further assistance.

For paused projects, applying now will schedule the migration script to run the next time your project is restored. We suggest that you restore your project immediately to verify that everything works or rollback if necessary. If you project is in any other states, please contact [email protected] to bring it to an active healthy state before continuing with the migration.

After successfully applying the migration, all entities you have created from the dashboard's SQL Editor will be owned by a temporary role. These entities are currently owned by supabase_admin role by default. You can check the current owner of all your schemas using the query below.

select *, nspowner::regrole::name from pg_namespace;

New entities created via the SQL Editor will also be owned by this temporary role. Since the temporary role is not a superuser, there are some restrictions with using the SQL Editor after migrating. If you are unsure whether those restrictions affect your project, please contact [email protected] for assistance.

After 5 Nov

After the opt-in period, you will receive another notification to drop the temporary role and reassign all entities owned by the temporary role to postgres role. The SQL Editor will also default to using postgres role. New projects created after 5 Nov will also default to using the postgres role. Since this change is irreversible, it is crucial that you run the migration during the opt-in period to verify that your project continues to work.

For any projects not migrated after 5 Nov deadline, we will run the migration on your behalf to reassign all entities to postgres role. No temporary role can be used for rollback. If you notice any breakages then, please do not hesitate to contact [email protected].

Restricted Features

After revoking superuser access, you will not be able to perform the following actions through the dashboard SQL Editor.

Managing Event Triggers

You will no longer be able to create, alter, or drop event triggers directly through SQL statements.

Event triggers can only be created by superusers and you will not be able to manage them after the migration. One exception is Postgres extensions. When toggling extensions, they can still create or drop event triggers as needed.

If you are currently using custom event triggers, please contact [email protected] to explain your use case. We will try our best to figure out an alternative for your project. Note that regular triggers are unaffected by the migration.

Restricted use of Supabase schemas

You will no longer be able to: create, alter, or drop tables, views, functions, triggers, sequences, and other entities in Supabase managed schemas, including extensions, graphql, realtime, and supabase_functions.

Supabase managed schemas are used to support platform features for all projects. Entities in these schemas are owned by supabase_admin role to prevent users from accidentally overriding them and breaking platform features. Unless explicitly granted, non-superuser roles cannot manage entities in Supabase managed schemas after the migration.

If you think modifying these schemas is necessary for your project, please contact [email protected] to explain your use case. We will try our best to accommodate your use case using alternative suggestions.

Entities in auth and storage schemas have been explicitly granted all permissions to postgres role. Therefore, you can still manage these schemas directly through SQL statements. If you have existing triggers created on these schemas, they will continue to work as well.

All user defined schemas and the public schema will be owned by postgres role after the migration. Therefore, you should be able to manage entities in those schemas directly through SQL statements. One exception is if you have manually changed the owner of specific schemas before. In that case, you can either reassign their owner to postgres role manually or leave them untouched. Please reach out to [email protected] if you are unsure what to do.

Managing RLS Policies on Supabase schemas

You will no longer be able to create or drop RLS policies on entities in Supabase managed schemas.

RLS policies can only be created or dropped by entity owners or superusers. After the migration, you can’t manage RLS policies in Supabase managed schemas through the SQL Editor. If you need to expose certain tables in realtime schema to anon or authenticated users, one way is to create a view in the public schema using the postgres role.

RLS policies in auth, storage, public, and all user defined schemas can still be managed directly through SQL statements. Unless you have policies that check for supabase_admin role, all existing RLS policies should be unaffected by the migration.

Restricted use of Role Attributes

You will no longer be able to alter role attributes of replication, superuser, and reserved roles directly through the SQL Editor.

Only superuser roles can alter attributes of other superuser and replication roles. Reserved roles include anon, authenticated, postgres, service_role, etc. After the migration, you will not be able to change attributes of these roles directly through SQL statements. You can still alter attributes of other roles created by yourself, except to elevate those roles to superuser or replication.

Some common attributes that can’t be changed include password, login, and bypassrls. Here are some known workarounds:

1. To change your postgres role password, you can do it via dashboard settings page.
2. If you need to run one-off scripts that bypass RLS, you can use the provided service key.
3. If you are pushing schema migrations from CLI, superuser privilege is no longer required as all entities are owned by postgres role after the migration.
4. Migrating between projects no longer requires superuser privilege.

Update 26/10/22

A number of users reported the following error accessing the dashboard restoring a paused project.

Error: [500] failed to get pg.tables: password authentication failed for user "postgres_temporary_object_holder"

It is due to a bug in the restore script that we have since fixed. If you are still experiencing this issue, you may pause and restore the project again to fix it manually. If that fails, please don't hesitate to contact [email protected].

Update 03/11/22

We will be adding additional privileges to the postgres role to do the following actions, which otherwise can only be done by a superuser:

• manage the bypassrls role attribute
• set the session_replication_role runtime config

Update 28/12/22

• TimescaleDB extension fails to toggle (we can enable manually via support)
• Custom security definer functions will run as non-superuser (only affects extensions schemas owned by supabase_admin)

[TEMHITHORPHE]

First time commenting on GitHub ... so forgive any non-conformance encountered

**First Question ... **
Does this mean i can now cross query "public" schema and "auth." schema from the SDK??? ... I use Supabase on the server side with the service key.

Now I do understand why it's restricted for the "anon" key, security and all, but for the "service" key?? i don't know why i am not able to do something as simple as that ... now i have to resort to creating Functions and Triggers for even the most benign of queries, not ... friendly

And it's quite counter-productive or inconvenient to have to create a replica or near mirror copy of "auth.users" in my public schema, all because i can't reference "auth.users" from the SDK/API, and then now create Functions and Triggers to ensure that data in both schemas are in sync, and honestly the cognitive overhead is getting too much and impacting development time ... it's not pretty ... most devs just want something that works without having to jump through unnecessary... well, i guess it's now necessary, better put my hoop jumping shoes on.

Now i partially blame myself for using features of the platform marked as "Alpha Preview" or "Beta" ... but again, Supabase left me no ... US with no other option, there was just no way to query "auth.users" table from the SDK without involving some inconvenient shenanigans ... none ... well ... ... ... there was the recently found out escape of using VIEWS. (which i did not find on the documentation page btw).

Now am sure some of you reading this are super ultimate pro ninjas with SQL/PlSQL , bending it to your utmost will to do both godly and legendary things out of reach of mortals ... ... no really ... i know you guys exist, met a bunch on SO ... but thing is ... most devs are mortals ... i am alright with SQL, but that's all i want to be, "alright", that's fine with me, so while i can jump through hoops to get my shit to work, it's not a good developer experience when i have to always deep dive into the SQL editor to get things done.

And don't get me started on inconsistencies (that rarely occurs, but occurs nonetheless) between SQL Editor's UI and what is actually in the underlying instance, and i have to now decide whether i am truly stupid and dumb when it comes to writing code, Or ... Oh! wait! ... ... am using Supa now not Fire ... ... well ... ... that narrows it down to just stupid. But you get the point, another time spent debugging correctly working code, now on every error i have to perform 2 checks ... is it me just being me again, or is Supabase complicit?.

Add that to the fact that it was a pain to get Supabase RPC to work with parameters, apparently there are disparities in the ways Postgre itself, the SQL Editor and JS SDK sees/expects data. But of course ... disparities occur everywhere, not totally okay, but i can live with it ... ... what i can absolutely do without; is having to dig through GitHub issues and discussions to find answers to things that should be on the documentation. No, i definitely don't agree with whatever reason you might be thinking as to why the Docs is not updated or otherwise ... ah mean come on, these are core parts of the platform, the major selling features, in no way should anything that has to do with them be ambiguous or buried in Github issues and discussions.

Thank You.

Sorry ... this was unnecessarily long, my major issue is outlined in the third(3) paragraph, the rest is just me ranting about my other related Supabase frustrations.

Keep Up The Good Work Folks

[fmp777]

I'm having the same annoyance right now too... just for simple things like displaying a user's name to another user... duplicate data and manual setup of triggers... ugh....

[seb-lewis]

Right?? Also, does this mean no more superuser access in any way?

[sweatybridge]

Apologies for the late reply on this one.

reference "auth.users" from the SDK

I looked into the privileges of service_role and found that the following query grants it access to auth.users. It can be run without superuser using the postgres role.

GRANT ALL ON TABLE auth.users TO service_role;

After that, you should be able to use the service key to access auth.users table.

Also, it should work with or without applying this patch since it's unrelated. Not sure if we are going to make it the default but at least I will update our documentation to clear up this confusion.

no more superuser access in any way

Essentially yes, there's usually a safer alternative to superuser. This is also inline with other postgres providers RDS and Cloud SQL. If you really need superuser, our support will be able to help.

[TEMHITHORPHE]

Oh ... this solution absolutely works!, thanks!.

Apologies 🥺 for the late attribution ... ... and the earlier rants 😞.

PS: I also had to add auth schema to the search path (which can be done under settings).

Apologies for the late reply on this one.

reference "auth.users" from the SDK

I looked into the privileges of service_role and found that the following query grants it access to auth.users. It can be run without superuser using the postgres role.

GRANT ALL ON TABLE auth.users TO service_role;

After that, you should be able to use the service key to access auth.users table.

Also, it should work with or without applying this patch since it's unrelated. Not sure if we are going to make it the default but at least I will update our documentation to clear up this confusion.

no more superuser access in any way

Essentially yes, there's usually a safer alternative to superuser. This is also inline with other postgres providers RDS and Cloud SQL. If you really need superuser, our support will be able to help.

[TEMHITHORPHE]

Oh Great ... of course ... you can't create foreign relationships between VIEWS and TABLES ... ... i really am stupid ... ... i guess it's back to more Functions and more Triggers.

[GaryAustin1]

@sweatybridge
4) Migrating between projects](https://supabase.com/docs/guides/database/migrating-between-projects#migrate-the-database) still require superuser privilege. If you need this feature, please contact [email protected] for assistance.

Maybe I'm missing something, but this is fairly common when we in the past moved from Postgres version 13 to 14 on our instances, or we need to make a copy of an instance for testing. I assume soon we will want to move to Postgres 15. Is there a plan for being able to migrate a project from one instance to another in the future thru another process, or will everyone need to contact Supabase support?

[sduduzog]

The linked page seems to now lead to a 404. Just a headsup @sweatybridge

[sweatybridge]

Thanks for the heads up. I've updated the link in my comment.

[drocha87]

The linked page is again 404!

[sweatybridge]

Sorry about that, I've notified the docs team to help setup redirects in the future if possible. For now, I've manually updated the link again.

[dannykng]

Sorry about that folks! We migrated to a new docs platform last week and a few bugs have cropped up. Thanks for your patience as we address them!

[GaryAustin1]

@sweatybridge
Sorry, another follow up. One way used to test RLS is to use:

...
 set session role authenticated;
 set request.jwt.claims to '{"role":"authenticated", "sub":"5950b438-b07c-4012-8190-6ce79e4bd8e5"}';
 return QUERY
   EXPLAIN ANALYZE
    SELECT * FROM realtest ;
...

I do this from the SQL editor and it works fine. A user attempted to do something like this from the Postgres port as user Postgres and it does not work.

From the docs:
The specified role_name must be a role that the current session user is a member of. (If the session user is a superuser, any role can be selected.)

So with this new change I assume we will lose the ability to test RLS this way.
If true, any work around possible with role setups or other means?

[bummzack]

@WesleyYue I'm struggling with this, as migrations do not allow me to set the owner of a view to authenticated… are you aware of a workaround?

[WesleyYue]

@bummzack Run grant anon, authenticated to postgres; :)

[bummzack]

I've tried that (in migration file, but also in the UI), but migrations still fail. I have now resorted to removing the offending statement from the migration… the offending statement being:

alter view public.my_view owner to authenticated;

When I run it inside the Supabase UI, it seems to work.
Maybe that's something that has to be fixed within Supabase, so that grant anon, authenticated to postgres; is applied before migrations run?

[WesleyYue]

I've tried that (in migration file, but also in the UI), but migrations still fail.

That's surprising, what does your migration error say?

removing the offending statement from the migration

If you do that, you will remove RLS from your views and they will be completely public and all rows will be queryable by anyone with a anon key even if the underlying data in the tables are RLS protected.

Maybe that's something that has to be fixed within Supabase, so that grant anon, authenticated to postgres; is applied before migrations run?

Not sure what you mean here. You should paste the actual error you see here because it's probably something else.

[jimschmit]

I also have the issue of having a view that has postgres as owner and I can't change the owner to authenticated.
Now the RLS no longer applies to that view and everyone can query the view :/
Result from the supabase UI:
Failed to run sql query: permission denied for schema public

Result when running the query in DBeaver:
SQL Error [42501]: ERROR: must be owner of view businesses_with_user

The view:

Both tables referenced have the postgres as owner.

[pocin]

Can we see the contents of the actual migration to see what exactly it does before trying it out?
Or maybe be able to see the diff of what changed when executed

After successfully applying the migration, all entities you have created in your project will be owned by a temporary role. You can check the current owner of all your schemas using the query below.

Emphasis mine. Does this include views? This would be devastating.

I am using a very custom flow (I do not use the editor or the supabase UI, i execute all my schema manipulations using custom migration scripts (dbmate) executed by postgres role (with superuser).
Just trying to assess how it will impact me.

[pocin]

If there's something you need superuser to do, please mention it here so we can find an alternative for you.

@sweatybridge one usecase when you need a superuser is for creating an user/role with bypassrls. Eg a background analytics/reporting service or in my case a discord bot having full rw access to a subset of the database tables.

[soedirgo]

@pocin We're making a change so that the postgres role can do that (though this hasn't been rolled out yet). I've updated the notice to reflect this.

[miguderp]

@soedirgo could you confirm please whether the change you’re referring to has been published or not yet? Still getting must be superuser to change bypassrls attribute on my end despite being logged as postgres through psql 🤔

Also it was my understanding that the SQL editor within Studio was also becoming postgres but when I run select current_user; in my production server I receive postgres_temporary_object_holder. Am I missing something here?

Edit: I ended up re-creating my project and now all settings are correct. I was lucky enough to be at an early stage of my project 😟

[sweatybridge]

The temp role change and bypassrls are separate changes. If your dashboard is showing postgres_temporary_object_holder and want to use postgres now, you can use the "Finalize" notification shown below. We are rolling out the finalise script in batches, so it might not have reached your old project yet.

For bypassrls attribute, the current workaround is to use service_role key for background workers. Creating additional roles with bypassrls requires a separate update that's being rolled out soon.

[soedirgo]

Just an update re: bypassrls: the fix for this has been rolled out, so you should now be able to create bypassrls roles without being superuser.

[GaryAustin1]

Not sure when this final migration occurs but a user is having trouble running this migration script from Supabase with the user postgres_temporary_object_holder.

https://supabase.com/docs/guides/integrations/prisma#troubleshooting
It has many lines similar to this: grant all privileges on all tables in schema public to postgres, anon, authenticated, service_role, supabase_admin;

[sweatybridge]

Thanks for pointing this out. I will update the docs. Granting to supabase_admin is no longer needed after the migration.

The final migration is happening in batches. All projects should receive a notification to apply by end of this week.

[GaryAustin1]

So for the user in the middle right now what should they do? This is a long thread with a log of other debugging first and the user is done for the day https://discord.com/channels/839993398554656828/1006358244786196510/threads/1039119523254644736
I've recommended adding postgres_temporary_object_holder to the script for now.

[sweatybridge]

So for the user in the middle right now what should they do?

For the issue mentioned in discord, the following script should suffice.

grant all privileges on all tables in schema public to postgres, anon, authenticated, service_role;
grant all privileges on all functions in schema public to postgres, anon, authenticated, service_role;
grant all privileges on all sequences in schema public to postgres, anon, authenticated, service_role;

alter default privileges in schema public grant all on tables to postgres, anon, authenticated, service_role;
alter default privileges in schema public grant all on functions to postgres, anon, authenticated, service_role;
alter default privileges in schema public grant all on sequences to postgres, anon, authenticated, service_role;

The postgres_temporary_object_holder is a member of postgres role so it will automatically have access when permissions are granted to postgres.

[jayalmaraz]

Would this mean that the altering the timeout docs are out of date? They tell you to run an alter role ... query but that just returns permission errors when attempted:

alter role anon set statement_timeout = '15s';

-- Running...
-- Failed to run sql query: "anon" is a reserved role, only superusers can modify it

Might be worth mentioning that this ^ is on a project being managed by Prisma, with migrations run (but maybe not relevant info 🤷 )

[sweatybridge]

Yes, thanks for highlighting this issue. We will work on a fix to support altering certain attributes like statement_timeout. I will update the docs once a workaround is available.

[sweatybridge]

Meanwhile if anyone needs to change statement_timeout for your project, please reach out to [email protected] so we can apply the change manually.

[soedirgo]

Update: this has been fixed - you should now be able to run the above query without hitting permission errors.

[GaryAustin1]

@steve-chavez suggests this method to use the new explain feature

alter role authenticator set pgrst.db_plan_enabled to true;
NOTIFY pgrst, 'reload config';

error is Failed to run sql query: "authenticator" is a reserved role, only superusers can modify it

This worked previously, but now with an instance with postgres as the sql editor user it does not.

Any work around?

[GaryAustin1]

Right now this is gumming up using PostgREST features (to test RLS) like above
AND not being able to set role in the SQL editor to PostgREST roles is eliminating testing RLS there too.

[relferreira]

Hello @sweatybridge, any news about this?

[GaryAustin1]

This is also now stopping us from getting more log details (like the sql being executed) from PostgREST requests. I can turn on the audit feature for postgres user, but not for authenticator....

alter role authenticator set pgaudit.log to 'function, write, ddl'; No longer works.

[steve-chavez]

Working on this on supabase/supautils#40

[soedirgo]

Update: this has been fixed - you should now be able to set pgrst.db_plan_enabled.

[saendu]

Will postgres functioned created with the SQL Editor, that where used to use elevated privileges to bypass RLS still work (on the public schema)? Or will they stop working because they where created with the supabase superuser?
Also will triggers still work created via SQL Editor on public and managed schema (e.g. auth)?

[saendu]

Yes, this os exactly the case. I use some functions to elevate privilages in order to bypass RLS as an anonymous user. And for this I am using ''security definer''.

How can I make sure that this still works after the patch? Will the functions run as the postgres user and therefore still be able to do select and insert on tables in the public schema?

Can I somehow force the migration and check if it still works?

[sweatybridge]

Can I somehow force the migration and check if it still works?

Yup, I've sent an in-app notification to your project after looking it up in your support ticket. You should be able to see a "Apply now" button in the notifications page (near top right of dashboard). This will apply the patch in a reversible way.

You can click "Rollback" to revert if anything breaks.

[saendu]

@sweatybridge I've run the migration and when I run SELECT current_user; in the SQL Editor I can now see that I am authenticated as postgres_temporary_object_holder. So I guess the migration worked.

I can confirm that my functions still work despite the fact that they use security definer set search_path = public. I guess this is because the postgres user still has read (select) and write permissions (insert) on the tables of the public schema, correct? Or am I missing something?

[sweatybridge]

I guess this is because the postgres user still has read (select) and write permissions (insert) on the tables of the public schema, correct?

Yup, that sounds correct. Postgres user has read/write access to public schema and all its entities granted by default. It can also access everything that it is an owner of. So barring a few tables created by extensions, most RLS policies should work.

Good to know that it is working for you.

[saendu]

Great thanks for your support! 🙏

[GaryAustin1]

I used to be able to create a database role and assign it to authenticator so it could be passed in with a custom jwt with the role claim.
This of course now fails... GRANT testme TO authenticator;

This allowed having a custom schema that could only be accessed thru the API by applying grants on that schema to the new user role.
Although this probably could be handled thru a custom claim with RLS, this means added an extra RLS statement on all table policies and an extra auth.jwt() call which is somewhat costly performance wise.

Between this and RLS testing there needs to be a way for Postgres to assign authenticator roles. Neither are superuser so it does not violate the "no superuser" methodology Supabase has gone to. Is there anyway this can be done by a function or setting? Need both Grant and set session role authenticated;.

[steve-chavez]

Just FYI, supabase/supautils#40 will reallow this

[soedirgo]

This has been rolled out, so granting custom roles to authenticator should succeed.

[spider195]

I am trying to add a lot of rows to a supabase table in an operation that takes about 10 minutes (long story, but I have thousands of .DAT files that I parse and modify, then want to upload certain clean values into the supabase table). I now get timeout errors doing this. I had success previously modifying the timeout using:
alter role authenticated set statement_timeout = '600s'; (https://supabase.com/docs/guides/database/timeouts).

However post the changes to superusers now when I do this I get this error (noting I am the sole owner of this database):
Failed to run sql query: "authenticated" is a reserved role, only superusers can modify it.
It will return success if I change the session timeout (eg set statement_timeout to 600000), but I still get timed out when I perform the upload.

Are there any good options here to extend the timeout? I know there are some workarounds like doing a local instance of the database and then migrating it when the data is up, or else upload all the data to a giant CSV first, but I'd rather avoid that if possible. The low timeouts will definitely cause me issues in the future so wanted to solve it on the hosted version ideally.

[sweatybridge]

Hi @spider195, we are still working on exposing the timeout config via the dashboard. If this is blocking, could you email [email protected] with your project ref so we can increase the timeout manually for you?

[spider195]

Thanks for the quick response. I have just sent an email to support

[steve-chavez]

Bringing back support for this is being worked at supabase/supautils#40

[eidnanrohod1]

Any solution available on this issue?

[soedirgo]

The fix for this has been rolled out, so the alter role ... set statement_timeout ... statement should work now.

[danielstaleiny]

This migration has caused me some confusion and resulting in this thread discord thread.

Basically, I had change role into supabase_admin to perform anything on public tables. After all that one of the user pointed me to this migration and this cleared things and lack of need to do what I had to do.

However, I have observed that if I create new project I still have supabase_admin public table ownership, therefore migration is needed on new project as well.

[grschafer]

I've followed this discussion for a while, but ran into a limitation today that surprised me. For context, I have background worker processes that read data from the database, download files from storage and process them, then update data in the database. They log into the database with a separate user, called background_worker which has limited privileges for just the tables that it needs to read and modify.

I need to query auth.users to get the user's email address to email them a notification when the work completes, but it looks like I cannot grant usage on auth (or storage) to the custom background_worker role

postgres@db/postgres=# grant usage on schema auth to background_worker;
WARNING:  01007: no privileges were granted for "auth"
LOCATION:  restrict_and_check_grant, aclchk.c:310
GRANT

I was able to grant this privilege from the SQL Editor, but I would expect to be able to grant this from postgres, so that I can include this code and track it in database migrations when using the supabase CLI.

Alternatives I've considered:

• I could use the service_role user instead of background_worker, but then this background worker has way more database privileges than it needs.
• I could grant authenticated to background_worker because authenticated has usage on auth/storage, but I use authenticated for end-users, so there's a mismatch of privileges in this case.

Let me know if I'm missing something or if this is the wrong place to bring this up!

[soedirgo]

Thanks for bringing this up! I think the long term fix for this is for us to grant usage on schema auth to postgres with grant option, so postgres can in turn grant the privileges to other roles.

For now an alternative is to create a security definer function that only background_worker can execute (make sure to revoke execute on function ... from public, anon, authenticated, service_role) and read auth.users from there.

[bombillazo]

I hope the long term solution has been discussed, we're in need of allowing postgres to grant to a custom role we're required to have so it can access the auth and storage schemas.

[MedLeon]

RLS Insert Problem
I can't allow inserts to everyone at the moment:

CREATE POLICY "allow inserts" ON "public"."emailList" AS PERMISSIVE FOR INSERT TO public WITH CHECK (true);
returns:
Failed to run sql query: must be owner of table emailList

I suspect this new rule to be the cause. Is it possible to work around this?

[soedirgo]

Hmm there might be some issues when the patch was applied. Can you file a ticket on https://app.supabase.com/support/new and link to this comment?

[MedLeon]

Just did. Thanks for the super quick response!

[mosnicholas]

I'm still running into issues with superbase_admin. I've followed the migration docs, but ran into the problem defined here. My DB was created a week or two ago, it seems that this is still an issue? Should I be manually updating the migration files to change all

ALTER TYPE "public"."call_status" OWNER TO "supabase_admin";
to
ALTER TYPE "public"."call_status" OWNER TO "postgres";?

Or is there a more elegant solution?

[kulturpessimist]

Have the same problems now... :(

[DeniCarvalho]

I am facing this problem now. One of my tables simply changed owner to ''subase_admin'' and I can't get it back to 'postgres'

[fbriou]

@DeniCarvalho, did you have an answer from the support team? I need it to for running supabase db dump --db-url "$DB_URL" -f data.sql --data-only;

[DeniCarvalho]

unfortunately not. As my project is recent, it was easy to recreate the table. But that put me off, I lost control under database.

[Shawns2759]

I am having this exact same issue :/

[loekj]

I'm having issues with creating a down migration on my shadow database (that I rarely use, only for creating down migrations).
If I run the following command locally in my macOS terminal npx prisma migrate diff --from-schema-datamodel prisma/schema.prisma --to-migrations prisma/migrations --shadow-database-url < url > --script > down_migration.sql

I get the error db error: ERROR: must be member of role "supabase_admin". This used to go just fine.

I created the shadow database from the Supabase UI, and then used to interface it locally by doing alter user postgres with superuser;. Right now I can't even use it.
Any ideas anyone? I'm stuck how to grant access doing a simple migration command from my local machine.

[Ashish-Ban]

I have faced such breaking changes from supabase for a long time and during such changes they provide only poor support and docs does not seem to help much. I am planning to move to another platform. When implementing a breaking change they should include proper docs, with actual working solutions or workarounds and a reliable support.

[loekj]

Yes I'm facing similar headwinds now and considering the same. Don't really have any solution right now that automates (for safety purposes) my down migration SQL files creation. I just returned to doing the down migrations by hand and hoping for the best, flying blind in production upon rollbacks. No bueno.

[loekj]

@sweatybridge I'm sorry to bug you again on this security patch discussion, but I really cannot find a work-around and am currently manually doing migrations.
Can you please provide an answer on how I can use the supabase database to aid in creating migrations locally? Only thing I want to do is just use Prisma + supabase as the shadow-database.

[loekj]

BTW @sweatybridge I use paid supabase plans for our production databases

[devjones]

I cannot create a foreign data wrapper server:

According to:
https://www.postgresql.org/docs/current/sql-createserver.html

Creating a server requires USAGE privilege on the foreign-data wrapper being used.

It appears the postgres user, which is the only one I have access to, does not have this privilege and therefore cannot setup a new foreign data wrapper.

[soedirgo]

Are you using postgres_fdw? We're working on this, but as a workaround you can do:

create foreign data wrapper my_postgres_fdw handler postgres_fdw_handler validator postgres_fdw_validator;
create server my_server foreign data wrapper my_postgres_fdw ...;

[devjones]

Thanks! This works for now.

[soedirgo]

Just an update: this should work now OOtB

[jaredramirez]

Hey there, I'm hitting an issue in a new Supabase project where the postgres role doesn't have select access to tables created by extensions. Specifically, with tables related to address standardization in the extensions address_standardizer_data, address_standardizer_data_us, and postgis_tiger_geocoder.

I explained it in more detail here: #12588

In the mean time, is there any way I can grant the postgres role with select permissions on tables created by extensions? Possibly by connecting to the DB briefly as supabase_admin?

[soedirgo]

Just an update: this should be working now for all projects 👍 Please comment here if you run into other issues.

[ShawnToubeau]

Hi! I'm running into an issue where I cannot create a operator class and I get the error must be superuser to create an operator class.

For reference, I'm running a migration that looks like this:

CREATE
    OR REPLACE FUNCTION public.hashpoint(point) RETURNS integer
    LANGUAGE sql
    IMMUTABLE
AS
'SELECT hashfloat8($1[0]) # hashfloat8($1[1])';

-- we must drop the operator class because it doesn't support 'CREATE OR REPLACE'
DROP OPERATOR CLASS IF EXISTS public.point_hash_ops USING hash;

CREATE
OPERATOR CLASS public.point_hash_ops DEFAULT FOR TYPE point USING hash AS
OPERATOR 1 ~=(point,point),
FUNCTION 1 public.hashpoint(point);

Now that we can't use superuser, does anyone know a workaround for this? I'm happy to share more details if needed!

[calvinbrewer]

@ShawnToubeau Did you ever find a solution to this issue?

[ShawnToubeau]

Hey @calvinbrewer, no, I didn't find a solution.

[mimranzulkfal]

Is that why suddenly after Dec I am not getting a response from Supabase?. I am using redwood graphql, and when I update user, it does update the fields in Supabase but I dont get the response back.
Kindly guide me what should I do after this update? If I click on finalize

[soedirgo]

That sounds like a different issue. If you've recently updated your supabase-js version, these release notes might be helpful.

[icopp]

How do I make LEAKPROOF functions now? We've been relying on these to work around the performance limitations of RLS and now this is suddenly broken even on old, local migrations.

[icopp]

The pre-request thing is irrelevant in this case, as we're not doing anything that references JSON in the RLS policies other than using auth.uid() (which is easy to make sure it's re-using cached results by just doing (SELECT auth.uid()) everywhere it's used).

I'm not really seeing how the materialized CTE would help, since as far as I know it's still going put those into the query plan after joins/indexes between tables, and that's where the slowdown comes from. For example: Without policies for this situation built around LEAKPROOF functions, SELECT * FROM widgets JOIN doodads ON doodads.widget_id = widgets.id ORDER BY widgets.created_at LIMIT 100 will execute the RLS policies for all rows in widgets, doodads and users_profiles, controllers, and controllers_users, and then apply the LIMIT.

[steve-chavez]

I'm referring to precomputing any value in the pre-request(the JSON casting was just an example) and in this case avoid the need for the security definer function, which seems to be the root cause of the issue here.

Take the example on https://supabase.com/docs/guides/auth/row-level-security#policies-with-security-definer-functions. We can avoid the SDF by doing:

create or replace function pre_request()
returns void as $$
  with team_ids as (
    select team_id
    from members
    where user_id = auth.uid()
  )
  select
    set_config(
      'request.team_ids'
    , array_agg(team_id)::text
    , true
    )
  from team_ids;
$$ language sql;

And then defining the policy like:

create policy "Team members can update team members if they belong to the team."
on members
for all using (
  (
    with cached as materialized(
       select (current_setting('request.team_ids', true))::bigint[] as val
    )
    select team_id = any(val) from cached
  )
);

[steve-chavez]

@icopp Could you share an example of your policy using the LEAKPROOF function? So it seems that the materialized CTE would help in that case. If your SDF is named like is_admin(), the RLS policy would be defined like:

alter policy "rls_test_select" on realtest to authenticated
using (
    (
    with cached as materialized(
       select is_admin() as val
    )
    select val from cached
    )
); 

We confirmed this improved performance on https://github.com/orgs/supabase/discussions/9311#discussioncomment-5725297.

[steve-chavez]

Another possibility for avoiding the security definer function is using https://github.com/supabase-community/supabase-custom-claims to cache data and then query it. Related blog post: https://blog.mansueli.com/using-triggers-to-map-database-relationships-in-custom-claims

---

Also just for reference this HN comment seems related to the same issue.

[GaryAustin1]

Not sure I totally understand the type of SD function going on here. But so far in my testing ANY SD function wrapped in a select is only run once in a query. Every time I wrap an SD function (if it has no parameters from the rows, not clear if that is your case) it is evaluated once for all rows, not for each row.
So (select complicatedSD()) in the policy is run once.
col = (select complicatedSD()) the SD is run once and result is used in the query. Maybe leakproof evaluates "better"?
col = ANY(ARRAY(select arrayreturningSD())) is evaluated once.
Indexing if comparing to a col, when the operation resulting from the single run of the SD is a large array is likely also needed.

https://github.com/GaryAustin1/RLS-Perfomance/blob/main/README.md
Very bottom is the best example...but there are others thru out.

[srowe0091]

Hello, I was told to post this here as it could pertain to security permissions. In the Dashboard I was testing out a trigger, and accidentally created it in the net._http_response table (because that is the default chosen table upon opening the side drawer.) It didn't show up in the trigger list page and when I realized where the trigger was, I tried to delete it in the SQL editor but was unable to do so because I get this error

ERROR: must be owner of relation _http_response

Basically saying I do not having the right privileges. So basically since superuser privileges were removed, I'm stuck with a trigger in a table I have no idea what's happening.

Why was I able to create it in the first place? How would I go about getting this trigger removed?

I also submitted a support ticket, I can send my ticket number for reference.

[TheOtherBrian1]

Someone in the pg_net repo is having the same problem, so if you were able to solve the issue, I would appreciate if you could tell me how you did it

[vickkhera]

It is even more interesting: you can drop the function pointed to by the trigger with cascade and it will dutifully delete the trigger for you along the way. That really seems like a hole in security to me.

https://discord.com/channels/839993398554656828/1136586483298467980

[marinhojess]

How can I copy from one project to other?

I ran the command below to make the database dump:

pg_dump -h $HOST -p 5432 -U postgres -F c -b -v postgres -f db.backup

But when I tried to restore in other project, I got a lot of errors related to permissions:

pg_restore: creating EVENT TRIGGER "pgrst_drop_watch" pg_restore: from TOC entry 4830; 3466 16617 EVENT TRIGGER pgrst_drop_watch supabase_admin pg_restore: error: could not execute query: ERROR: permission denied to create event trigger "pgrst_drop_watch" HINT: Must be superuser to create an event trigger. Command was: CREATE EVENT TRIGGER pgrst_drop_watch ON sql_drop EXECUTE FUNCTION extensions.pgrst_drop_watch();

How can I make this copy if I can't set the postgres user as a superuser anymore?

[shellscape]

There really needs to be an easy escape hatch for customers that are doing more than the basics with their DB. We're now required to reach out to support for every new DB we create in the hopes that we can get proper access back to things like pg_attributes. Really, really poor customer experience here folks - something that is not the norm for Supabase.

[asharejaz1]

When I create some new tables in supabase, it sometimes automatically make 'supabase_admin' the owner of those tables instead of 'postgres' and then I am unable to create/modify a RLS policy, or any field of the table. One workaround I have found is that I need to create a copy of the current table, then delete the original table and then try my changes again on the 'copy' table. But after some time the owner automatically changes to 'supabase_admin' for those 'copy' tables as well putting me in the same situation again.

Please fix this.

[maxdeichmann]

Hi Supabase team,
i tried to enable the pgaudit extension and wanted to execute a query like ALTER DATABASE SET.. Unfortunately, only supabase has superuser rights, which are required according to these docs: https://access.crunchydata.com/documentation/pgaudit/1.2.0/

Can you explain to me how to enable audit logs?

[maxdeichmann]

I am using pgbouncer and it seems it has its own role but i cannot edit it via alter role pgbouncer set pgaudit.log to 'function, write, ddl'; This results in Failed to run sql query: "pgbouncer" is a reserved role, only superusers can modify it

[sergiofra98]

Hey guys, i just found this thread and i hope you can give me a hand.

Im trying to create a replication system in supabase, one project is master and the second is slave, replicating using the included publication and subscription systems in postgres, but in order to continue i need to create a subscription which is not possible because i need super user permitions to do so.

how can i create a subscription without superadmin? can this be changed or is there a way i can access the superuser account of supabase.

thank you

[Zyles]

How do you create event triggers?

Error running SQL: Failed to run sql query: permission denied to create event trigger "on_create_table"

[monicakh]

It is not possible to set event triggers at this time.

But you can use this workaround with pg_cron:
https://blog.mansueli.com/sudo-with-postgres-supabase#heading-taking-it-to-the-next-level

[Zyles]

That doesn't work.

[besteman]

@sweatybridge is there an update on this? if is not convenient

[bombillazo]

Could we consider adding GRANT privileges to the postgres role so we can create our custom db roles that can access other non-public PG schemas, including the SB ones?

We don't want to give postgres role access to any connection that requires accessing the internal schemas.

[draew6]

this is exactly what i need

[ardabeyazoglu]

If the postgres will now bu superuser, we would need a way to run supabase migrations with a superuser when required.