Postgres privileges needed by ManageIQ

[ebilbil]

Hello team,
I would like to know what are the exact privileges needed by ManageIQ for an external Postgres database/instance.
Our database is actually hosted on an external Postgres 13 and our DBA team would like to apply the principle of least privilege.

We created a specific user with all privileges on the vmdb_production database and tried to apply new credentials in ManageIQ with the following command :

# appliance_console_cli --hostname="1.2.3.4" --username="miq_user_db" --dbname="vmdb_production" --password="mypassword"

 configuring external database

All seems to be OK (no error message), but ManageIQ continues to use old credentials (old credentials are still present in database.yml file)

It works when we add the 'SUPERUSER' role to my user.

Any help would be appreciated.

ManageIQ release : Petrosian

Best regards,

[Fryguy]

Hi @ebilbil, so there are 2 main reasons we use superuser (@jrafanie Keep me honest here)

1. We create the tables/indexes etc during migrations on upgrades
2. Enabling replication requires we have access to the WAL and the replication subscriptions

If you try limiting to those features things should still work. Please let us know what you find, as we can update our Hardening Guide with this information.

[ebilbil]

Hello @Fryguy ,
I found that during the external database configuration, the region_number_from_sequence method is called.

def region_number_from_sequence
      sequence_name = connection.select_value("SELECT relname FROM pg_class WHERE relkind = 'S' LIMIT 1")
      return if sequence_name.nil?
      id_to_region(connection.select_value("SELECT last_value FROM #{sequence_name}"))
 end

I found in Postgres log that it's the second query failed

2025-05-12 11:36:37.797 CEST [551790] ERROR:  permission denied for relation accounts_id_seq
2025-05-12 11:36:37.797 CEST [551790] STATEMENT:  SELECT last_value FROM accounts_id_seq

I'm not a database specialist but granting SELECT and UPDATE on the accounts_id_seq to my user resolve the issue.

GRANT SELECT, UPDATE ON accounts_id_seq TO miq_user_db;

Notes:

• accounts_id_seq is not really a table but a relation
• in my example accounts_id_seq is the first row of the result of the first query (LIMIT 1). Maybe in some case result could be different?