PostgreSQL Flexible server Admin and Owner not able to change ownership

Question

Running commands such as for any schema returns an error. The commands are being run by the admin user of the server and owner of the database and all its resources. The user also has azure_pg_admin role.
REASSIGN OWNED BY adminuser TO "fabric_user";

ALTER TABLE "TableName" OWNER TO "fabric_user";

ALTER TABLE public."TableName" OWNER TO fabric_user;

The error is as follows:
SQL Error [42501]: ERROR: permission denied for schema

Answer 1

Hi @Vinicius Barbosa

Please follow the scenario

-- Step 1: Create test roles
CREATE ROLE adminuser LOGIN PASSWORD 'pass';
CREATE ROLE fabric_user LOGIN PASSWORD 'pass';

-- Step 2: Create a separate schema owner
CREATE ROLE schema_owner LOGIN PASSWORD 'pass';

CREATE SCHEMA test_schema AUTHORIZATION schema_owner;

-- Step 3: Create a table in that schema
SET ROLE adminuser;

CREATE TABLE test_schema.test_table (id int);

-- Step 4: Attempt ownership change
ALTER TABLE test_schema.test_table OWNER TO fabric_user;

If the role performing the ownership change does not have the required permissions on the schema, PostgreSQL may return an error similar to:

ERROR: permission denied for schema test_schema

To validate this, connect as the schema owner and grant the necessary schema privileges:

SET ROLE schema_owner;

GRANT USAGE ON SCHEMA test_schema TO adminuser;
GRANT CREATE ON SCHEMA test_schema TO adminuser;

Then retry the ownership change.

Hope this helps, Please let us know if you have any questions or concerns.

Answer 2

Hi @Vinicius Barbosa

In Azure Database for PostgreSQL Flexible Server, being the server administrator and a member of azure_pg_admin provides enhanced administrative capabilities, but ownership changes must still satisfy PostgreSQL ownership and schema privilege requirements.

To help identify the cause, could you please provide:

SELECT current_user;

\du fabric_user

\dn+

and confirm which schema contains the affected table?

The permission denied for schema error commonly occurs when the target owner role does not have the required privileges on the schema containing the object. Once we review the schema ownership and privileges, we can determine the appropriate remediation.