Description of problem: When trying to configure an external database from the appliance_console, the "create region" step fails. Version-Release number of selected component (if applicable): 5.6 How reproducible: always Steps to Reproduce: 1. create an external postgres database, make sure to not use standard names. e.g. I create a user "external-cfme" and a database "external-cfme" 2. start appliance_console and setup external database 3. provide DB credentials Actual results: create region fails Expected results: it should complete with the DB and username specified Additional info: I tested with a 5.5 appliance and it works fine there
Created attachment 1187652 [details] appliance_console.log I guess the clue are those lines: FATAL: no pg_hba.conf entry for host "192.168.101.227", user "external-cfme", database "postgres", SSL off Couldn't drop database 'external-cfme' It tries to login to the DB "postgres" on the remote server - although it should actually try to login to the DB "external-cfme"
Christian, Can you give some more detail around how you installed PostgreSQL on the external server? What repo did you use? What version of PostgreSQL did you install? Did you make any changes to postgresql.conf or pg_hba.conf? How exactly did you create the database and user? (please provide the commands you used) Thanks.
I was able to reproduce this issue by not allowing the user I created on the database server access to the "postgres" database. This error is being caused when we drop the database in evm:db:reset here https://github.com/ManageIQ/manageiq/blob/master/lib/tasks/evm_dba.rake#L178 which is a part of creating a region. When dropping a postgres database rails needs to maintain a connection to the server in order to continue functioning so it has hardcoded the postgres database for that purpose here https://github.com/rails/rails/blob/5-0-stable/activerecord/lib/active_record/tasks/postgresql_database_tasks.rb#L86 If we have a specific need to *not* allow the cfme user to access the postgres database we could attempt to implement something other than dropping the database and recreating it when creating a region (like dropping the public schema and recreating it), but there are some complications that come along with that as can be seen in this conversation http://stackoverflow.com/questions/3327312/drop-all-tables-in-postgresql As far as I can tell this also is not a regression (rails has behaved this way in this scenario since at least 2012) so I think this would have also failed in previous versions. Because of that, I'm going to reduce the priority and severity to medium and treat this more like an RFE.
So TLDR; Put https://github.com/ManageIQ/manageiq-appliance/blob/master/TEMPLATE/var/opt/rh/rh-postgresql95/lib/pgsql/data/pg_hba.conf.erb#L3 in the pg_hba.conf when creating a database server.
HiHo, I provided the details you asked for. I understand now why you're doing it this way, but I guess it would be nice, if we would not need access to the postgres DB. Specially in scenarios where a customer wants to use an external DB, they might have security concerns about it, in particular if it's a shared database server. I can also confirm that after granting the external user access to all databases, configuring the external DB works on the 5.6 appliance. But I also had to give this user the CREATEDB privileges, so it could create the database during setup. As an additional note, I configured the very same database server with the same DB name and user credentials on a 5.5 appliance and it works there. I don't know why, but it works fine. ;-) Regards, Christian
That's good to know! I'll look into what has changed between 5.5 and 5.6 that could be causing this. I'll have to find it, but I think we document somewhere that we require CREATEDB and also that it is not recommended to share a database server because of some of the specific setting we have for replication and soon HA. As far as how to create the user and database, the only way that I know is fully tested is how we do it when we create an "internal" (on a cfme server) database. This can be seen in the code snippet here https://github.com/ManageIQ/manageiq/blob/master/gems/pending/appliance_console/internal_database_configuration.rb#L180-L189
It looks like a bug was actually fixed within rake that was previously allowing this process to succeed when it should have failed. With the way we are doing things now, I would expect us to require access to the "postgres" database.
Moving to 'NEW' while assigned to the default assignee.
I have made a note of the required change in the bug where creation of remote databases has been requested and is being addressed. Closing this as a duplicate. *** This bug has been marked as a duplicate of bug 1348787 ***