The main problem comes from obvious - our application's PostgreSQL user needs to have an Superuser role as it manages most of its (dedicated) database (creates tables, drops those, manages views, triggers ... ).
No, the things you want to manage within the database do not require Superuser. You only need superuser once to configure the system in such a way, through role and grants and possibly default permissions, that from then on most everything an application user would want to do can be done by the role(s) you have created.
So these - pg_read_server_files, pg_write_server_files and pg_execute_server_program roles are inherited from the Superuser
Those roles are entirely independent of Superuser; and in fact exists for that very purpose, since Superuser can already do those things.
and are also super sensitive for us, but our application user apparently still needs to be superuser for multiple reasons.
You will need to be specific as to exactly what capability you need that you cannot get a non-superuser role to accomplish.
1/ What we need is to create a postgresql user who would be pg_database_owner but not superuser
OWNER is an attribute of specific objects, including the database object. You can already do this.
... but apparently we still need our user to be superuser to be able to carry on with some operations (picking one of more then few) ....
CREATE OR REPLACE LANGUAGE plpython3u;
HINT: Must be superuser to create this extension.
Yeah, this will be a bit tougher since you are in conflict regarding whether you need O/S resources or not. It is hard to solve this problem without the bigger picture.
2/ Second option is to introduce some sort of limitation of allowed SQL queries
A useful tool is "SECURITY DEFINER" tagged routines.
The lack of a separate database migration process seems to be your main issue - something that can run audited code in an elevated context to put the system into a state where non-elevated users can then do the things they need to do. Even if you just have modes in your application that are "admin mode" and "user mode" so admin mode can do the, hopefully limited, subset of actions that need superuser while user mode operates under the only semi-powerful database owner role you can probably bridge the gap (if you indeed must have the database do things directly on the underlying operating system).
David J.
