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
Jay Cummins
  • 615
  • 3
  • 10
  • 17
  • 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: https://stackoverflow.com/a/46674322/386619 – Jay Cummins Dec 31 '18 at 17:49

1 Answers1

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:

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
  • 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 03 '19 at 11:20