Managed Postgres Clusters: pg_restore errors for superuser privileges

Posted June 12, 2019 4.2k views

I’m trying to import a database with pg_restore to a managed postgres cluster but I get the following error:

could not execute query: ERROR: must be superuser to create an operator class

I’m connecting as “doadmin” which doesn’t have superuser privileges, how can I connect as “postgres” user? or is there a way to pg_restore allowing the creation of operator class as admin user?

  • it seems like this issue is what has blocking me from using pg_restore --disable-triggers.

    it’s very disappointing after the time i spent preparing for the migration to DigitalOcean Postgres, but now it seems it may not be possible to restore without data loss.

    is there a workaround specifically for --disable-triggers? (no, i am not going to try to figure out how to manually restore one table at a time in a specific order.)

  • Like others, I am also looking for super user privileges. I’m very new to this, but it looks like the postgres user that is created by default with the managed database has this access. Why not enable login as that user?

  • Any update here? This is a pretty huge issue and makes migrating from a managed database to any other form of database very complicated. We need to be able to have superuser privileges.

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.

Submit an Answer
12 answers

Hey there,

At this time it isn’t possible to create a superuser with DO Managed Databases; this bars operator class access as a superuser is always needed to create one.

We’re learning more about Managed Databases every day and are always looking to improve the end-user experience. Should you want these to be implemented we’d love to hear your feedback on our IDEAS page. This is where our Product team looks to the community for ideas and use-cases that could be prioritized in future work cycles for us.

Ethan Fox
Developer Support Engineer II - DigitalOcean

  • I have a serious problem with that. Trying to import several csv files and got the error:
    defaultdb=> COPY XXXXXXXXX FROM ’/Users/laptop/Desktop/total.csv’ WITH (FORMAT csv);
    ERROR: must be superuser or a member of the pgreadserver_files role to COPY from a file
    HINT: Anyone can COPY to stdout or from stdin. psql’s \copy command also works for anyone.

    I tried everything and every answer from stackoverflow, and now you are telling me that it can’t be done?

I have a similar problem. I can’t execute a Function because that has the statement ‘SET sessionreplicationrole = replica’ and only run by super privileges. Why DO can’t give to us a superuser???
Let’s try to help us resolve this problem.

Seriously ? Releasing postgres managed DB without access to superuser ? What was the use case you were addressing when releasing this feature. And do you really expect everyone to manage things without a superuser ??

Need superuser access! Why is this not supported by default? Really strange!

Any news on this? We just had to switch gears and use something other than DO on a project using postgraphile because we can’t use it without being able to set up event triggers.

  • Exactly the same here! I’m testing DigitalOcean’s Managed Postgres and one of the first things I need to so is create an event trigger to fire when a table is created.

    To do this I need to be a SuperUser.

    As far as I can see, I can’t be a SuperUser with a Managed DigitalOcean Postgres.

    So the DigitalOcean Managed Postgres can’t work form me. Shame :-(

    Does anyone know if this is a fundamental thing that can never be changed for security reasons?

Thanks for the reply, but then how can I import a database in which some Operator Class are defined?

  • Hey there,

    You would need to remove the Operator Class from the data set before importing it to DO Managed Databases at this time. Could you clarify your use-case (either here or in an IDEA) so we can pass along the information to our Product team?

    Ethan Fox
    Developer Support Engineer II - DigitalOcean

Having same issue too. And I’m sure that every single one who tried to use DO managed DB would experienced the same issue. We need superuser access to DB to actually using it. This is basic of basic. When does the superuser access going to be supported on DO?

I need event triggers, replications and more. Nothing is accessible without superuser privileges. That’s some serious WTF moment.

Is this still not solved?
I need superuser access.

Without superuser the managed DB feature is useless for Odoo ERP.

Previous 1 2 Next