PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    If you have control over both roles and wsalesadmin is meant to replace posadmn001 on this system, you can flip this around: demo

    begin;
reassign owned by wsalesadmin to posadmn001;
grant wsalesadmin to posadmn001 with inherit true,set true,admin true;
alter role wsalesadmin rename to wsalesadmin_replaced_by_posadmn001_202406071450;
alter role posadmn001 rename to wsalesadmin;
commit;

    You’ll have to swap the keys and passwords as well as all else that wsalesadmin had and posadmn001 did not. Ship of Theseus comes to mind.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search