How to fix Must be superuser to create extension in PostgreSQL

Connect to your PostgreSQL database and verify your current user role:

SELECT current_user, usesuper FROM pg_user WHERE usename = current_user;

If usesuper is "f" (false), you are not a superuser. If it's "t" (true), you have superuser privileges.

The safest approach is to have a superuser create the extension, then your application user can use it:

# Login as postgres system user
sudo -u postgres psql -d your_database

Then create the extension:

CREATE EXTENSION IF NOT EXISTS extension_name;

After creation, any user in the database can use the extension's functions and types.

If you're using a managed service, you typically can't log in as the postgres user. Instead:

Supabase: Use the provided postgres role or contact support to enable the extension.

Google Cloud SQL: Extensions must be created by users with the cloudsqlsuperuser role. The default postgres user has this role and can create extensions.

AWS RDS: Connect as the master user (admin account) to create extensions.

Azure Database for PostgreSQL: Use the admin account to create extensions.

Check your provider's documentation for the exact process.

Confirm the extension exists in your database:

\dx extension_name

Or query the system catalog:

SELECT * FROM pg_extension WHERE extname = 'extension_name';

For production, create a dedicated application user that does NOT have superuser privileges, but can use the extension:

-- Create application user (if not exists)
CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_password';

-- Grant connect and usage rights
GRANT CONNECT ON DATABASE your_database TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;

-- Grant access to extension functions
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO app_user;

This allows your application to use the extension without superuser access.

Starting with PostgreSQL 13, some extensions are marked as "trusted" in their control file, allowing non-superusers to install them:

CREATE EXTENSION trusted_extension_name;

You can check if an extension is trusted by examining the control file on the server or querying:

pg_config --sharedir  # Find the extensions directory
cat /path/to/extension_name/extension_name.control | grep trusted

Trusted extensions include many common ones like uuid-ossp, pgcrypto (if marked trusted), and others. However, complex extensions like PostGIS still require superuser installation.