Question

Managed Postgres Clusters: pg_restore errors for superuser privileges

Posted June 12, 2019 723 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
Add a comment

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.

7 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
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
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
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
Submit an Answer

Related

Looking for something else?

Ask a new question Search for more help