[SOLVED] Reassign owned by in Postgres fails due to PgAudit

Reassign owned by in Postgres fails due to PgAudit

I have a Postgres 14 running in Azure Flexible Server. The server has the extension pg_audit enabled. I am using a psql client version 12 installed in a Linux machine.

I've used pg_restore to restore a lot of schemas and tables from an original single server to this new flexible. The data was backed up using pg_dump. All went ok, until the step when I need to reassign all the objects to the new owner.

 /usr/bin/psql --host=$PGTARGET --port=5432 --username=$USERPG dbname=postgres
psql (12.12, server 14.11)
WARNING: psql major version 12, server major version 14.
         Some psql features might not work.
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=> REASSIGN OWNED BY posadmn001 TO wsalesadmin;
ERROR:  permission denied to change owner of event trigger "pgaudit_ddl_command_end"
HINT:  The owner of an event trigger must be a superuser.
postgres=>

The problem is that I have dozens of schemas, functions and different objects, so I really don't understand why the event trigger of pg_audit is not allowing me to run the reassign owned command. Besides, as the command fails here, all the thousands of tables and functions are left with the wrong owner.
The hint is useless in this case, as this is a PaaS Database, therefore I have no superuser. I am using the administrator user provided by Azure when you create the server.

Is there any particular reason for this? Am I doing something wrong? This is the test environment, but I will have to do this in Pro in the future and I'd like to know whether I have to disable pg_audit or install it after the data migration.

Thanks in advance

Solution

Well, it looks like posadmn001 is the owner of the event trigger pgaudit_ddl_command_end, and only superusers can change the owner of an event trigger.

Since the event trigger seems to belong to pgAudit, I would disable that extension or do whatever else is necessary to get rid of the event trigger. Then you should be able to REASSIGN OWNED, if you have the permissions to change ownership.