How to fix must be owner of extension in PostgreSQL

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

SELECT extname, extowner::regrole FROM pg_extension;

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

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:

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

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

# 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:

pg_dump --exclude-schema=information_schema --exclude-schema=pg_catalog DATABASE_NAME > dump.sql

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

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.

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

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.

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

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.