Required pg-user permissions for external postgres db?

Hello,
does somebody of you know what exactly permissions the manageiq database user on the database server needs when using a external postgres db?

Im struggling with insufficient priviliges on my managed database server. I found a thread where “fbladilo” said super user role ist required on the postgres server. In my environment super user priviliges aren’t an option and I think this it can’t be the solution to give this privilige. (manageiq deployment using external DB not working · Issue #168 · ManageIQ/manageiq-pods · GitHub)

I very appreciate your help!

I figured out that the following two privileges are required:
`GRANT ALL PRIVILEGES ON TABLE pg_subscription TO ;
GRANT ALL PRIVILEGES ON TABLE pg_replication_origin_status TO ;

But this is still not suitable for a productive environment because the table pg_subscription is unique for a hole database cluster and not for each database.

Is it possible to operate manageiq under normal security conditions? ( without privilege escalation on the cluster for the miq user)

I think it’s generally recommended that MIQ uses a dedicated PostgreSQL database server/appliance/cluster, as it has several specific database tuning parameters that are recommended.

pemcg

Hi,
thank you for your answer. I thought external database also includes hosting on a database cluster.

Update to the both tables, they seems to be a part of the database but you need additional privileges to edit them. I’ll try a hosting on a db cluster. Otherwise I’ll switch to a dedicated database.

Roman

Checkout the file /etc/manageiq/postgresql.conf.d/01_miq_overrides.conf which contains the default MIQ-specific PostgreSQL settings.

(see also ManageIQ)

pemcg

1 Like

@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.

FYI, this PR is now much closer to being complete. I have successfully run the new developer logical replication rake task as both a superuser and non-superuser roles. It gracefully prints a warning and continues to setup the databases as should be done since logical replication is not supported for non-superuser roles.

I have also verified bin/setup and bin/update along with bin/rails s and clicking around in the UI all work for a non-superuser without a fatal error.

If you’re currently trying to get manageiq running without a postgresql superuser role, please try it out or wait for it to be merged and let me know how it goes.

2 Likes