How to fix must be owner of extension in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 26, 2025· 244 views

This error occurs when a non-owner or non-superuser attempts to modify, drop, or comment on a PostgreSQL extension. Most commonly encountered during database dumps/restores or Rails migrations, it requires either ownership privileges or superuser permissions to resolve.

Quick fix

Fixes 42501

SELECT extname, extowner::regrole FROM pg_extension;
  1. Check extension ownership with psql
  2. Remove extension comments from dump files
  3. Exclude extension definitions from dumps (Rails)
  4. Grant superuser privileges temporarily (if possible)
postgresql — error
SELECT extname, extowner::regrole FROM pg_extension;
must be owner of extension

What this error means

PostgreSQL extensions are database objects that have owners, just like tables or schemas. Only the owner (the user who created the extension) or a superuser can modify, drop, or manage an extension. When you attempt an extension operation without proper ownership rights, PostgreSQL denies the action with this error. This commonly occurs in two scenarios: (1) When restoring a database dump that includes extension comments or metadata that were created by a different user than the one running the restore, and (2) When running database migrations in applications like Rails where the migration user lacks extension ownership. In cloud-hosted PostgreSQL environments like Heroku, Railway, or Google Cloud SQL, this error is particularly common because the database user typically lacks superuser privileges for security reasons.

Typical symptoms

  • Error appears during `rake db:structure:load` or `rake db:setup` in Rails applications
  • Database restore from pg_dump fails with extension-related errors
  • Heroku deploy fails at database migration step
  • Error message includes specific extension names like "plpgsql", "uuid-ossp", or "pgcrypto"
  • Operations succeed when run as postgres superuser but fail with application user

Common causes

  1. Database dump includes COMMENT ON EXTENSION statements created by a superuser, but restore is run by a non-superuser account
  2. Extension was originally created by a different database user than the current migration runner
  3. Database dump includes extension installation commands, but the extension already exists with a different owner
  4. Using a limited non-superuser account in managed PostgreSQL services (Heroku, Cloud SQL, Railway, Supabase) that cannot own extensions
  5. Attempting to drop or alter an extension you do not own without superuser privileges

How to fix "must be owner of extension"

1Check extension ownership with psql

Connect to your PostgreSQL database and list all extensions with their owners:

sql
SELECT extname, extowner::regrole FROM pg_extension;

This shows which user owns each extension. Compare this to the user running your migrations.

2Remove extension comments from dump files

If you are restoring from a pg_dump file, open the dump file (typically structure.sql or db/structure.sql in Rails) and remove any lines that start with COMMENT ON EXTENSION:

sql
-- REMOVE THIS LINE:
-- COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

Extensions are already installed in PostgreSQL by default, so these comments are not necessary and only cause permission errors. After removing these lines, retry the restore.

3Exclude extension definitions from dumps (Rails)

If you are using Rails, modify your config/application.rb or db/structure.sql to skip extension-related statements:

ruby
# config/application.rb
config.active_record.schema_format = :ruby  # Use schema.rb instead of structure.sql

Alternatively, if you must use structure.sql, ensure your dump process excludes extension definitions:

bash
pg_dump --exclude-schema=information_schema --exclude-schema=pg_catalog DATABASE_NAME > dump.sql
4Grant superuser privileges temporarily (if possible)

If you control the database and can temporarily grant superuser rights, do so during migration:

sql
ALTER USER migration_user WITH SUPERUSER;
-- Run migrations
ALTER USER migration_user WITH NOSUPERUSER;

Note: This approach is not available in managed databases like Heroku or Google Cloud SQL where superuser access is restricted.

5Create extensions as the superuser in template1

For managed databases, install commonly-needed extensions to the template1 database before creating application databases:

sql
psql -U postgres -d template1 -c "CREATE EXTENSION IF NOT EXISTS plpgsql;"
psql -U postgres -d template1 -c "CREATE EXTENSION IF NOT EXISTS uuid-ossp;"

All new databases inherit extensions from template1, so your application user will not need to create them.

6Use IF NOT EXISTS and skip recreation in migrations

When writing migrations, use IF NOT EXISTS to safely handle extensions that may already be installed:

sql
CREATE EXTENSION IF NOT EXISTS plpgsql;
CREATE EXTENSION IF NOT EXISTS uuid-ossp;

Better yet, remove extension creation from migrations entirely if your hosting provider (Heroku, Railway, Supabase) pre-installs them. Check your provider's documentation for available extensions.

Advanced notes

For PostgreSQL 13+, extensions can be marked as "trusted" by editing the extension's control file and adding trusted = true. This allows non-superuser database owners to create the extension. However, this requires access to the PostgreSQL server files and is not available on managed databases.

In Supabase and other managed PostgreSQL services, extensions are pre-installed with a special admin user. If you attempt to alter or drop them in migrations, you may encounter ownership conflicts. The safest approach is to check whether the extension already exists before creating it, or contact your hosting provider's support to pre-install specific extensions.

Cloud SQL for PostgreSQL restricts changing extension ownership via ALTER EXTENSION OWNER TO because superuser privileges are not exposed to users. If you need custom extensions, request them through the Cloud SQL console or use the Cloud SQL proxy with appropriate permissions.

Frequently asked questions

What does "42501" mean?
PostgreSQL extensions are database objects that have owners, just like tables or schemas. Only the owner (the user who created the extension) or a superuser can modify, drop, or manage an extension. When you attempt an extension operation without proper ownership rights, PostgreSQL denies the action with this error.
How do I fix "42501"?
This error occurs when a non-owner or non-superuser attempts to modify, drop, or comment on a PostgreSQL extension. Most commonly encountered during database dumps/restores or Rails migrations, it requires either ownership privileges or superuser permissions to resolve.
What causes "42501"?
Database dump includes COMMENT ON EXTENSION statements created by a superuser, but restore is run by a non-superuser account Extension was originally created by a different database user than the current migration runner Database dump includes extension installation commands, but the extension already exists with a different owner
Is the "42501" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading