Question

Postgresql database cluster enable extension

After creating the cluster I want to enable a supported extension

Using psql I check the available extensions: SELECT * FROM pg_available_extensions order by name; and see all the available extensions and that the installed version is null. If I want to enable the extension using
create extension insert_username

I get the following error:

ERROR:  permission denied to create extension "insert_username"
HINT:  Must be superuser to create this extension.
SQL state: 42501

Creating a new user with superuser privileges doesn’t work, because doadmin isn’t a superuser, what to do?

Show comments

Submit an answer

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Sign In or Sign Up to Answer

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

Want to learn more? Join the DigitalOcean Community!

Join our DigitalOcean community of over a million developers for free! Get help and share knowledge in Q&A, subscribe to topics of interest, and get courses and tools that will help you grow as a developer and scale your project or business.

Hey there,

My name is Ethan from the DigitalOcean Platform Support team, I specialize in storage-related inquiries and am happy to help here.

Superusers can create extensions on any database, so the error points to being a superuser. However, you can also create extensions on databases that your user owns. DOAdmin by default will own Databases you create. You can check the current owner of your created databases with this SQL:

SELECT d.datname as "Database Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner" FROM pg_catalog.pg_database d ORDER BY 1;

Using the “doadmin” user should resolve this error allowing you to create the extension. Alternatively, you can force ownership changes with ALTER DATABASE[1] then use your custom user to CREATE EXTENSION.

  1. https://www.postgresql.org/docs/current/sql-alterdatabase.html

Regards, Ethan | Captain of Storage Pillar, DigitalOcean Platform Support