Postgresql database cluster enable extension

April 23, 2019 558 views
PostgreSQL Clustering Ubuntu 18.04

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?

1 comment
1 Answer

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

  • I executed the command with the user doadmin, that’s why I posted this question.

    • Hey there,

      I’m sorry to hear you are having this issue still with the doadmin user. I wasn’t able to recreate the issue on my own cluster.

      Does the user you are running this as (doadmin) own the database you are taking action on? That would be the problem I assume, but it’s hard to tell without looking at the cluster itself.

      If you continue to face this issue could you submit a ticket to our support team: https://cloudsupport.digitalocean.com/s/my-tickets including all the details of the change? This would include things like the user, the database you are taking action on, the extension, and which cluster you are running things on.

      Our team can then further investigate and try to recreate the issue, potentially escalating further to our Product/Engineering team if more investigation needs to be done.

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

Have another answer? Share your knowledge.