How to handle roles to dump and restore a PostgreSQL database on another database in the same cluster

I am struggling with restoring a dump for several reasons and I am not even sure I am taking the right direction. Basically my issue seems to depends on my understanding of role and priviledges in postgre so feel free to teach me.

My goal is to create a copy my database on another database in the same cluster via my App. This is a legacy feature that previously used a superuser on the database cluster.

Now I am not sure how to proceed, I need to connect to original database, drop the target database and create it, then I execute this query :

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname='{target_database}';

and after that i generate a .bat file that I then execute. This file contains this command which is the main one :

pg_dump.exe -T dos_histomemo "postgresql://{username}:{encoded_pwd}@{ip}:{port}/{original_database}" | "{path_to_psql}psql.exe" -v ON_ERROR_STOP=1 "postgresql://{username}:{encoded_pwd}@{ip}:{port}/{target_database}"

what my tests have led to is that :

• the user creating the target database owns it so it is easier if the one restoring it and creating are the same user.
• the owner of the schema public is postgres so I will always have troubles because if I dump with --no-owner then i need to grant to my user_app permissions to create in schema on target_database and if I dumb with ownerships I need to resotre with postgres
• When doing all of this with postgres I cannot restore roles because the ALTER DEFAULT PRIVILEDGES was used with others users

My question is: I am going the right direction and should I use postgres user to administrate every priviledges in the cluster ? Or more generaly, how to handle roles in this situation ? At the end I have to respect ISO 27001 norms for database users and I am not sure what it implies in this case.

I would use the --clean option with pg_dump, so that the restore drops all conflicting objects.

Using a superuser to restore a dump is a good idea, and you don't need to worry that ALTER DEFAULT PRIVILEGES cannot be restored.