Remove superuser access from Studio - Supabase

Overview

In late 2022, Supabase introduced a security change in hosted projects that removed superuser access from the dashboard SQL editor and shifted ownership of user-created database objects away from supabase_admin toward the postgres role. You can read more about it in the official announcement.

However, this migration was never automatically applied to self-hosted Supabase instances.

As a result:

Objects created via the dashboard may still be owned by supabase_admin
Behavior differs from the Supabase platform
Some migrations may fail when run as postgres

This guide explains how to align your self-hosted Supabase instance with the security enhancements and ownership model used on the Supabase platform.

Changing the configuration

Step 1: Update database object ownership

Use the provided script to reassign ownership of database objects in the public schema from supabase_admin to postgres. From the project directory containing docker-compose.yml, run:

sh utils/reassign-owner.sh

This script only updates ownership for database objects in the public schema. Supabase-managed and custom schemas are not affected.

</Admonition>

Step 2: Update environment variables in docker-compose.yml

In your docker-compose.yml configuration, uncomment the following line for the studio service to use the postgres role for read/write operations:
yml
```
studio:
  environment:
    POSTGRES_USER_READ_WRITE: postgres
```
Locate the meta service environment variables and change the PG_META_DB_USER environment variable from supabase_admin to postgres:
yml
```
meta:
  environment:
    PG_META_DB_USER: postgres
```

Studio uses its own credentials to access Postgres via postgres-meta, so this change is only needed for backward compatibility and consistency.

</Admonition>

Step 3: Restart Supabase

docker compose down && docker compose up -d

Verify roles

After restarting your services, verify that Supabase Studio is now using the postgres role. Run the following query in the Supabase Studio SQL Editor:

sql

select current_user;
-- expected result: postgres