1

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
  • 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?) – Erwin Brandstetter Dec 31 '18 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. – Jay Cummins Dec 31 '18 at 17:44
  • postgres and role_a are definitely not superuser: stackoverflow.com/a/46674322/386619 – Jay Cummins Dec 31 '18 at 17:49
1

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:

| improve this answer | |
  • 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 '19 at 11:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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