Question
Managed Postgres Clusters: pg_restore errors for superuser privileges
- Posted June 12, 2019
- 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?
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.
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
This is pretty mad… Similar to others I need superuser access - can’t really see any logic for that not being possible. We’re just going to move the current project out of DO as a result.
Response to edit the data and remove superuser needs is a bit dismissive. It’s less work for me to switch DB provider for this project than it is to edit the masses of data I need to import.
I also need superuser access…
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.
Is this still not solved? I need superuser access.
Need superuser access! Why is this not supported by default? Really strange!
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 ??
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 have a similar problem. I can’t execute a Function because that has the statement ‘SET session_replication_role = 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.
Thanks for the reply, but then how can I import a database in which some Operator Class are defined?
Without superuser the managed DB feature is useless for Odoo ERP.
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.
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?
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.)