Related

Join the DigitalOcean Community Community

Join 1M+ other developers and:

  • Get help and share knowledge in Q&A
  • Subscribe to topics of interest
  • Get courses & tools that help you grow as a developer or small business owner
 Join Now
Migration of data from PostgreSQL 9.1.13 to PostgreSQL 9.3 Question Question
How to give root files permision to postgres Question Question

Question

Managed Postgres Clusters: pg_restore errors for superuser privileges

Posted June 12, 2019 4.2k views
PostgreSQL

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?

Add a comment
maxovolab
By:
maxovolab

Related

Join the DigitalOcean Community Community

Join 1M+ other developers and:

  • Get help and share knowledge in Q&A
  • Subscribe to topics of interest
  • Get courses & tools that help you grow as a developer or small business owner
 Join Now
Migration of data from PostgreSQL 9.1.13 to PostgreSQL 9.3 Question Question
How to give root files permision to postgres Question Question
Add a comment
3 comments
  • panderssen March 13, 2021
    Reply Report

    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.)

  • brianjstroh May 2, 2021
    Reply Report

    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?

  • tonymg August 28, 2021
    Reply Report

    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
efox June 12, 2019

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.

Regards,
Ethan Fox
Developer Support Engineer II - DigitalOcean

Reply Report
  • jb01285c446e541194c1afe7bf December 12, 2019

    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?

    Reply Report
dungngo2912 July 9, 2019

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.

Reply Report
prakashdb70f02e0d036dbea4c November 21, 2019

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 ??

Reply Report
tcsm December 1, 2019

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

Reply Report
ajpoland August 2, 2020

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.

Reply Report
  • nigelrmtaylor April 24, 2021

    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?

    Reply Report
maxovolab June 12, 2019

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

Reply Report
  • efox June 12, 2019

    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?

    Regards,
    Ethan Fox
    Developer Support Engineer II - DigitalOcean

    Reply Report
yechan November 13, 2019

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?

Reply Report
lyxsus December 30, 2019

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

Reply Report
taagerco June 13, 2020

Is this still not solved?
I need superuser access.

Reply Report
bojanprogramer August 26, 2020

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

Reply Report
Previous 1 2 Next
Load More Answers

Related

Looking for something else?

Ask a new question Search for more help