Foreman 1.24 and SCRAM-SHA-256 auth?

Attempting to cut over my 1.24 instance from mysql to postgres following the migration guide.

Running the db:create returns:
"rake aborted!\nPG::ConnectionBad: SCRAM authentication requires libpq version 10 or above

OS is RHEL7 - so i “know” my file is 9.x by default. However - RedHat does release postgresql 12.x versions in thier “redhat SCL’s”

rh-postgresql12-postgresql-devel-12.5-1.el7.x86_64 : PostgreSQL development header files and libraries
Repo : rhel-7-base-rpms-paychex-versioned-202102
Matched from:
Filename : /opt/rh/rh-postgresql12/root/usr/lib64/

On a RHEL7 host - is there any way to have Foreman “use” this one instead? Or is my only option reverting to “md5” authentication within our PostgreSQL 12 database?

I think you have to rebuild rubygem-pg against the SCL to pick up that libpq but we haven’t built it like that in our repositories. You may want to consider upgrading your host to RHEL8 where libpq comes from version 12 (even if you enable the PostgreSQL 10 module since libpq doen’t come from the module).

thanks @ekohl that is what i suspected. RHEL8 is on our roadmap, but im hoping to get PostgreSQL, Puppet 6, Foreman 2.4 out of the way first if i can.

I switched to “Md5” for now and all is well. Im hitting another issue with the “prod2dev” upgrade process:

The primary “symptom” is i end up with zero “host” entries on the other end (things like hostgroups, subnets, etc. are all migrated fine)
Looking at the output below i “suspect” my issue is related to having the openscap plugin installed at one point - but having removed it several years back. Is there any way to “clean up” or cleanly remove that plugin?

TASK [debug] ***************************************************************************************************************************
ok: [] => {
    "newdbseed.stdout_lines": [
        "Rubocop not loaded.",
        "Converting ansible_roles...0 records converted in 0.0792656468693167 seconds",
        "Converting ar_internal_metadata...1 records converted in 0.0474925790913403 seconds",
        "Converting architectures...5 records converted in 0.08209603815339506 seconds",
        "Converting architectures_operatingsystems...47 records converted in 0.08895676187239587 seconds",
        "Converting audits...2 records converted in 0.07688051904551685 seconds",
        "Converting auth_sources...3 records converted in 0.08704504603520036 seconds",
        "Converting bookmarks...21 records converted in 0.0818417260888964 seconds",
        "Converting cached_user_roles...444 records converted in 0.5705248429439962 seconds",
        "Converting cached_usergroup_members...141 records converted in 0.24771491205319762 seconds",
        "Converting compute_attributes...10 records converted in 0.09945579897612333 seconds",
        "Converting compute_profiles...10 records converted in 0.09731642017140985 seconds",
        "Converting compute_resources...1 records converted in 0.10253084800206125 seconds",
        "Converting config_group_classes...0 records converted in 0.03877653693780303 seconds",
        "Converting config_groups...1 records converted in 0.05613608402200043 seconds",
        "Converting domains...5 records converted in 0.07514671492390335 seconds",
        "Converting dynflow_actions...1529 records converted in 2.0623122400138527 seconds",
        "Converting dynflow_coordinator_records...25 records converted in 0.15930955996736884 seconds",
        "Converting dynflow_delayed_plans...7 records converted in 0.13188278814777732 seconds",
        "Converting dynflow_envelopes...608 records converted in 0.7396504739299417 seconds",
        "Converting dynflow_execution_plans...1528 records converted in 2.8943605560343713 seconds",
        "Converting dynflow_schema_info...1 records converted in 0.10391441197134554 seconds",
        "Converting dynflow_steps...3022 records converted in 4.915207296842709 seconds",
        "Converting environment_classes...3433 records converted in 3.507519543869421 seconds",
        "Converting environments...3 records converted in 0.13438157504424453 seconds",
        "Converting external_usergroups...10 records converted in 0.14007010613568127 seconds",
        "Converting fact_names...7654 records converted in 10.270437904866412 seconds",
        "Converting fact_values...336959 records converted in 416.57349839201197 seconds",
        "Converting features...16 records converted in 0.09999963594600558 seconds",
        "Converting filterings...1170 records converted in 1.2839810219593346 seconds",
        "Converting filters...483 records converted in 0.6000112830661237 seconds",
        "Converting foreign_input_sets...0 records converted in 0.05433906987309456 seconds",
        "Unable to convert foreman_openscap_arf_report_raws, skipping: PG::UndefinedTable: ERROR:  relation \"foreman_openscap_arf_repor                                                                                                                     t_raws\" does not exist",
        ": ALTER TABLE foreman_openscap_arf_report_raws DISABLE TRIGGER ALL;Unable to convert foreman_openscap_arf_reports, skipping: PG                                                                                                                     ::UndefinedTable: ERROR:  relation \"foreman_openscap_arf_reports\" does not exist",
        ": ALTER TABLE foreman_openscap_arf_reports DISABLE TRIGGER ALL;Unable to convert foreman_openscap_asset_policies, skipping: PG:                                                                                                                     :UndefinedTable: ERROR:  relation \"foreman_openscap_asset_policies\" does not exist",
        ": ALTER TABLE foreman_openscap_asset_policies DISABLE TRIGGER ALL;Unable to convert foreman_openscap_assets, skipping: PG::Unde                                                                                                                     finedTable: ERROR:  relation \"foreman_openscap_assets\" does not exist",
        ": ALTER TABLE foreman_openscap_assets DISABLE TRIGGER ALL;Unable to convert foreman_openscap_policies, skipping: PG::UndefinedT                                                                                                                     able: ERROR:  relation \"foreman_openscap_policies\" does not exist",
        ": ALTER TABLE foreman_openscap_policies DISABLE TRIGGER ALL;Unable to convert foreman_openscap_policy_arf_reports, skipping: PG                                                                                                                     ::UndefinedTable: ERROR:  relation \"foreman_openscap_policy_arf_reports\" does not exist",
        ": ALTER TABLE foreman_openscap_policy_arf_reports DISABLE TRIGGER ALL;Unable to convert foreman_openscap_policy_revisions, skip                                                                                                                     ping: PG::UndefinedTable: ERROR:  relation \"foreman_openscap_policy_revisions\" does not exist",
        ": ALTER TABLE foreman_openscap_policy_revisions DISABLE TRIGGER ALL;Unable to convert foreman_openscap_scap_content_profiles, s                                                                                                                     kipping: PG::UndefinedTable: ERROR:  relation \"foreman_openscap_scap_content_profiles\" does not exist",
        ": ALTER TABLE foreman_openscap_scap_content_profiles DISABLE TRIGGER ALL;Unable to convert foreman_openscap_scap_contents, skip                                                                                                                     ping: PG::UndefinedTable: ERROR:  relation \"foreman_openscap_scap_contents\" does not exist",
        ": ALTER TABLE foreman_openscap_scap_contents DISABLE TRIGGER ALL;Unable to convert foreman_openscap_tailoring_files, skipping:                                                                                                                      PG::UndefinedTable: ERROR:  relation \"foreman_openscap_tailoring_files\" does not exist",
        ": ALTER TABLE foreman_openscap_tailoring_files DISABLE TRIGGER ALL;Converting foreman_tasks_locks...5 records converted in 0.06                                                                                                                     059942813590169 seconds",
        "Converting foreman_tasks_recurring_logics...0 records converted in 0.03618226200342178 seconds",
        "Converting foreman_tasks_remote_tasks...0 records converted in 0.030247112037613988 seconds",
        "Converting foreman_tasks_task_group_members...38 records converted in 0.08586969110183418 seconds",
        "Converting foreman_tasks_task_groups...38 records converted in 0.08398075983859599 seconds",
        "Converting foreman_tasks_tasks...1518 records converted in 2.4501946889795363 seconds",
        "Converting foreman_tasks_triggerings...38 records converted in 0.1164923629257828 seconds",
        "Converting host_ansible_roles...0 records converted in 0.0409992269705981 seconds",
        "Converting host_classes...27 records converted in 0.07612157496623695 seconds",
        "Converting host_config_groups...2 records converted in 0.0720908590592444 seconds",
        "Converting host_facets_reported_data_facets...394 records converted in 0.5824937480501831 seconds",
        "Converting host_status...897 records converted in 0.9873137059621513 seconds",
        "Converting hostgroup_ansible_roles...0 records converted in 0.04684117413125932 seconds",
        "Converting hostgroup_classes...327 records converted in 0.3545582399237901 seconds",
        "Converting hostgroups...Unable to convert hostgroups, skipping: can't write unknown attribute `openscap_proxy_id`Converting hos                                                                                                                     ts...Unable to convert hosts, skipping: can't write unknown attribute `openscap_proxy_id`Converting http_proxies...0 records converted i                                                                                                                     n 0.10333224316127598 seconds",
        "Converting images...0 records converted in 0.28142897691577673 seconds",
        "Converting job_invocations...1 records converted in 0.08217221195809543 seconds",
        "Converting job_template_effective_users...0 records converted in 0.030556200072169304 seconds",
        "Converting jwt_secrets...0 records converted in 0.03001812705770135 seconds",
        "Converting key_pairs...0 records converted in 0.04146413202397525 seconds",
        "Converting locations_organizations...13 records converted in 0.049369588028639555 seconds",
        "Converting logs...0 records converted in 0.03174217697232962 seconds",
        "Converting lookup_keys...1364 records converted in 1.8135841398034245 seconds",
        "Converting lookup_values...471 records converted in 0.5988729521632195 seconds",
        "Converting mail_notifications...7 records converted in 0.10334994597360492 seconds",
        "Converting media...19 records converted in 0.13086817995645106 seconds",
        "Converting media_operatingsystems...34 records converted in 0.0876327040605247 seconds",
        "Converting messages...0 records converted in 0.03395745693705976 seconds",
        "Converting models...10 records converted in 0.07820467813871801 seconds",
        "Converting monitoring_results...0 records converted in 0.04584553698077798 seconds",
        "Converting nics...688 records converted in 1.2811415831092745 seconds",
        "Converting notification_blueprints...10 records converted in 0.08954163594171405 seconds",
        "Converting notification_recipients...147 records converted in 0.21903191995806992 seconds",
        "Converting notifications...17 records converted in 0.10913613298907876 seconds",
        "Converting operatingsystems...40 records converted in 0.1377374769654125 seconds",
        "Converting operatingsystems_provisioning_templates...141 records converted in 0.17743001505732536 seconds",
        "Converting operatingsystems_ptables...49 records converted in 0.09342107689008117 seconds",
        "Converting operatingsystems_puppetclasses...0 records converted in 0.03378517599776387 seconds",
        "Converting os_default_templates...86 records converted in 0.15185530786402524 seconds",
        "Converting parameters...5695 records converted in 7.568163855932653 seconds",
        "Converting permissions...257 records converted in 0.3751151370815933 seconds",
        "Converting personal_access_tokens...0 records converted in 0.04499999899417162 seconds",
        "Converting puppetclasses...473 records converted in 0.5266926791518927 seconds",
        "Converting realms...0 records converted in 0.04308642912656069 seconds",
        "Converting remote_execution_features...5 records converted in 0.13982332008890808 seconds",
        "Converting reports...0 records converted in 0.04670938104391098 seconds",
        "Converting roles...44 records converted in 0.1010528861079365 seconds",
        "Converting sessions...0 records converted in 0.0382315251044929 seconds",
        "Converting settings...174 records converted in 0.29139576805755496 seconds",
        "Converting setup_provisioners...0 records converted in 0.04455906501971185 seconds",
        "Converting smart_proxies...15 records converted in 0.0772663161624223 seconds",
        "Converting smart_proxy_features...62 records converted in 0.13263114215806127 seconds",
        "Converting sources...0 records converted in 0.03657072293572128 seconds",
        "Converting ssh_keys...1 records converted in 0.062394017120823264 seconds",
        "Converting stored_values...0 records converted in 0.04135494399815798 seconds",
        "Converting subnet_domains...16 records converted in 0.06939882785081863 seconds",
        "Converting subnets...16 records converted in 0.13501397520303726 seconds",
        "Converting table_preferences...0 records converted in 0.05821221508085728 seconds",
        "Converting target_remote_execution_proxies...8 records converted in 0.06416247598826885 seconds",
        "Converting targeting_hosts...1 records converted in 0.053764992859214544 seconds",
        "Converting targetings...1 records converted in 0.068974400870502 seconds",
        "Converting taxable_taxonomies...78895 records converted in 88.5008072881028 seconds",
        "Converting taxonomies...10 records converted in 0.1195334151852876 seconds",
        "Converting template_combinations...0 records converted in 0.05440985504537821 seconds",
        "Converting template_inputs...50 records converted in 0.18965611513704062 seconds",
        "Converting template_invocation_input_values...0 records converted in 0.07126815197989345 seconds",
        "Converting template_invocations...2 records converted in 0.08145981002599001 seconds",
        "Converting template_kinds...12 records converted in 0.10706747602671385 seconds",
        "Converting templates...401 records converted in 0.7926302200648934 seconds",
        "Converting tokens...0 records converted in 0.09729877603240311 seconds",
        "Converting trend_counters...2757 records converted in 3.713928929064423 seconds",
        "Converting trends...242 records converted in 0.38998483889736235 seconds",
        "Converting upgrade_tasks...0 records converted in 0.05185642605647445 seconds",
        "Converting user_mail_notifications...2 records converted in 0.08027779497206211 seconds",
        "Converting user_roles...192 records converted in 0.24335553613491356 seconds",
        "Converting usergroup_members...141 records converted in 0.19946168595924973 seconds",
        "Converting usergroups...13 records converted in 0.09951333003118634 seconds",
        "Converting users...142 records converted in 0.3588217298965901 seconds",
        "Converting widgets...1241 records converted in 1.6536803820636123 seconds"

In case anyone stumbles across this. I “think” i figured it out. Very convoluted and complicated, but:

  1. Install tfm-rubygem-foreman_openscap For 1.24.3 that is currently version 2.0.2 - this re-adds the 44 missing Migration files needed: foreman_openscap/db/migrate at v2.0.2 · theforeman/foreman_openscap · GitHub
  2. foreman-rake db:migrate:status should now show no migrations with Missing Files (this is important)
  3. Developer Docs mention this - it WILL NOT WORK: foreman-rake db:migrate SCOPE=foreman_openscap VERSION=0
  4. Instead you must “down/un-migrate” each of the 44 versions in reverse chronological order. The command ends up being: foreman-rake -v db:migrate:down SCOPE=foreman_openscap VERSION=20190103093409
  5. Repeat the above 43 more times in reverse chronological order
  6. NOTE: 5-6 of the above will fail - skip them - a mix of non atomic operations (change) and create tables that already existed are the culprits here. At the end (due to the failures) - you end up with one remaining table and one remaining view. CLean those up manually:
  7. Drop the remaining table and view (no foreign keys or anything so this is easy) DROP TABLE IF EXISTS scaptimony_assets; DROP VIEW IF EXISTS scaptimony_arf_report_breakdowns;
  8. REMOVE THE PLUGIN: tfm-rubygem-foreman_openscap
  9. Follow the migration guide as defined
  10. I was concerned about the failed migrations “still being in there” However (i think) because the new postgresql database is created from nothing - it doesn’t know of, or apply those migrations (we removed the RPM containing them before initializing that DB). Furthermore - post-migration i can confirm that those migration ID’s are not present in a db:migrate:status command against postgreSQL

I “think” this solves the issue cleanly - but I’m not an expert. Hope I’m not shooting myself in the foot with a self-inflicted problem at a later date!

1 Like