This error occurs in AWS RDS for PostgreSQL when a user without rds_superuser privileges attempts to install an extension. In RDS versions 13+, most extensions can be installed by users with CREATE database privileges, but some require elevated permissions. The fix depends on your RDS version and whether you can use delegated extension management.
The error indicates that you are trying to create a PostgreSQL extension but lack the necessary permissions in Amazon RDS. Unlike self-managed PostgreSQL where the postgres superuser can install any extension, RDS restricts extension creation through the rds_superuser role. This is a security measure because unrestricted extension installation could compromise the managed database service. The error message specifically comes from RDS and prevents potentially dangerous system-level operations.
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;.
AWS RDS restricts extension installation to prevent untrusted code execution at the system level, which is necessary for a managed service. The rds_superuser role in RDS is NOT a true PostgreSQL superuser and cannot perform all superuser operations. For version-specific behavior: RDS 12 and earlier require rds_superuser for most extensions; RDS 13 introduces trusted extensions allowing broader access; RDS 14+ supports delegated extension management (rds_extension role) for finer-grained control. Some extensions like pg_cron and pglogical have security implications and always require elevated privileges. If you need to install unsupported extensions, consider using an EC2-hosted PostgreSQL instance instead of RDS. For multi-schema deployments, ensure the user has CREATE privilege on the target schema. When using Terraform or other IaC tools, install extensions in a separate apply step after the user is created and granted permissions.
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL