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

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