2

I attempting to restore a database into a PostgreSQL instance on Azure.

I am issuing these statements as postgres and attempting to change owner to another role that was a superuser.

My restore errors on this line (actual names changed to protect the innocent):

ALTER TABLE schema_m.table_a OWNER TO role_a;

SQL Error [42501]: ERROR: must be member of role "role_a"
I've tried: GRANT postgres TO role_a GRANTED BY postgres;

I believe the issue is related to the postgres user not being a superuser on Azure PostgreSQL, but maybe I'm wrong.

I've also tried granted all privs to both:

 GRANT ALL PRIVILEGES ON SCHEMA  schema_m TO role_a;
 GRANT ALL PRIVILEGES ON SCHEMA  schema_m  TO postgres;

But I still receive the error. Am I missing something obvious?

PostgreSQL 9.5.14, compiled by Visual C++ build 1800, 64-bit
3
  • You forgot two essential bits of information: your version of Postgres (always) and the current role of the session where you issue those statements. (Is it postgres?) Dec 31, 2018 at 17:38
  • Added. That's the problem--role_a can't be a superuser in the Azure env. like it was in my source database. Dec 31, 2018 at 17:44
  • postgres and role_a are definitely not superuser: stackoverflow.com/a/46674322/386619 Dec 31, 2018 at 17:49

1 Answer 1

2

That doesn't seem to be specific to Azure. It's a general rule in Postgres: Only the owner or a superuser can use ALTER TABLE.

To verify your role is a superuser (or not), run in the current session:

SELECT rolsuper FROM pg_roles WHERE rolname = current_user;

Related:

1
  • There is also the option to have user "user_a" and "user_b" be members of the role "role_a" and alter ownerhsip of the table to role "role_a" which then allows "user_a" and "user_b" to alter the table.
    – Riccardo
    Jan 3, 2019 at 11:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.