This error occurs when trying to create a PostgreSQL extension without superuser privileges. Most extensions require superuser permissions for security reasons, though some trusted extensions can be installed by database owners.
PostgreSQL extensions contain functions, types, operators, and other objects that extend the database. Loading an extension requires the same privileges needed to create its component objects. Many extensions use C functions or create scalar types, which can only be created by superusers to prevent security vulnerabilities and server crashes. Some modern PostgreSQL versions (13+) support "trusted extensions" that can be installed by non-superusers. When you attempt to create an extension without sufficient privileges, PostgreSQL returns this permission denied error to protect your database from potentially malicious or poorly-written extension code.
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_databaseThen 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_nameOr 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 trustedTrusted extensions include many common ones like uuid-ossp, pgcrypto (if marked trusted), and others. However, complex extensions like PostGIS still require superuser installation.
Security Considerations: Extensions can execute arbitrary code at installation time. PostgreSQL requires superuser privileges for untrusted extensions to prevent malicious code execution. This is especially important for extensions with C functions.
Template Databases: To automatically include an extension in all new databases, create it in the template1 database: CREATE EXTENSION extension_name; on template1. All subsequently created databases will inherit it.
Extension Dependencies: Some extensions depend on others. If you get errors when creating an extension, check its dependencies and install those first with superuser privileges.
Cloud Provider Limitations: Managed database services often restrict direct superuser access. They may have different mechanisms to enable popular extensions (e.g., Supabase's pg_cron, PostGIS through dashboard). Check your provider's extension list before deploying.
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