How to change owner of a table within a PostgreSQL Database on Azure?

Question

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

• Erwin Brandstetter: 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?)
• JC5577: Added. That's the problem--role_a can't be a superuser in the Azure env. like it was in my source database.
• JC5577: postgres and role_a are definitely not superuser: stackoverflow.com/a/46674322/386619

Answer 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:

• Give particular privileges for a table to existing role

• Riccardo: 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.