@roman First, @pemcg is right, manageiq should be on a dedicated database cluster. There are settings specific for manageiq that may have downsides OR conflict with another application. Also, some of the logical replication settings assume a dedicate cluster or you may need to manage those yourself based on what else is on the cluster.
With that said though… if you’re not using logical replication…read on…
Are you using central administration which uses postgresql’s logical replication with different manageiq databases split into regions? We only need
superuser permissions, as you found, to create logical replication
subscriptions as mentioned here in the postgresql documentation. I do wish there was a way we can create subscriptions without
superuser privileges and without having to grant all privileges on the relevant tables.
If you’re not using multiple database regions with logical replication providing central administration, you probably want to follow this pull request. It is attempting to guard the replication setup code from failing fatally and instead not enable that feature that uses replication.
Note, I don’t yet know if there are other queries we do that require superuser but with the above pull request, I’m able to setup my databases locally with just “create databases” and “login” privileges. I don’t know if we use “create roles” but I haven’t found a need for it yet.
I hope this helps.