How to fix RDS: Cannot create extension (RDS_SUPERUSER required) in PostgreSQL

Connect to your RDS instance and verify the PostgreSQL version:

SELECT version();

Version 13 and higher support trusted extensions with reduced permissions. Version 12 and earlier require rds_superuser for most extensions.

# From your terminal
psql -h your-rds-endpoint.rds.amazonaws.com -U postgres -d postgres -c "SELECT version()"

Note: For RDS, the default admin user is typically postgres not root.

If you are running RDS PostgreSQL 12 or earlier, you need to grant rds_superuser to a role that will install extensions. Connect as the postgres user and run:

-- Connect as postgres superuser
GRANT rds_superuser TO your_application_user;

Then the user can create extensions:

-- Connect as your_application_user
CREATE EXTENSION uuid_ossp;

Note: The postgres user in RDS has rds_superuser (not full superuser). Roles inheriting from rds_superuser can install extensions.

For RDS PostgreSQL 13 and later, many common extensions are marked as "trusted" and can be installed by any user with CREATE privilege on the database:

-- As postgres user, grant CREATE privilege
GRANT CREATE ON DATABASE your_database TO your_application_user;

-- Now your_application_user can install trusted extensions
CREATE EXTENSION uuid_ossp;
CREATE EXTENSION pgcrypto;
CREATE EXTENSION hstore;

Common trusted extensions:
- uuid_ossp
- pgcrypto
- hstore
- json_enhancements
- btree_gin
- btree_gist

To see all available extensions:

SELECT * FROM pg_available_extensions;

For RDS PostgreSQL 14 and later, use the delegated extension support feature to grant extension management without full rds_superuser:

-- As postgres user, create the rds_extension role if it does not exist
CREATE ROLE rds_extension WITH NOLOGIN;

-- Grant rds_extension to your application user
GRANT rds_extension TO your_application_user;

Now your_application_user can install delegated extensions:

-- Connect as your_application_user
CREATE EXTENSION uuid_ossp;

Note: Some extensions cannot be managed this way (log_fdw, pg_cron, pg_tle, pglogical, postgis_raster, postgis_tiger_geocoder, postgis_topology).

If your RDS instance has the rds.allowed_extensions parameter set, it restricts which extensions can be installed. Check the parameter group:

SHOW rds.allowed_extensions;

If it returns a restricted list, contact AWS Support to modify the parameter group or request specific extensions to be added. You can update custom parameter groups:

# Via AWS CLI
aws rds modify-db-parameter-group \
  --db-parameter-group-name your-param-group \
  --parameters "ParameterName=rds.allowed_extensions,ParameterValue=uuid-ossp,pgcrypto,hstore,ParameterApplyMethod=immediate"

Note: A reboot may be required for some parameter changes.

After granting permissions, verify the extension installs correctly:

CREATE EXTENSION IF NOT EXISTS uuid_ossp;
SELECT * FROM pg_extension WHERE extname = 'uuid_ossp';

List all installed extensions:

\dx
-- or
SELECT * FROM pg_extension;

If you still get permission errors, check the current user and role memberships:

SELECT current_user;
SELECT rolname FROM pg_roles WHERE pg_has_role(current_user, oid, 'member');

Ensure the user has the necessary role inheritance. For IAM database authentication, verify IAM policy grants access and the user was created with GRANT rds_iam TO iam_user;.