Postgresql database cluster enable extension

Posted on April 23, 2019

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?

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!

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.

efox

May 14, 2019

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.