Getting "Unable to check config: Unable to check superuser conditions: PG::ConnectionBad: FATAL: password authentication failed for user" in config_check

[yrgoldteeth]

Hi! I am attempting to use this tool on a replication setup. It has worked fine for one database, but another has been nothing but trouble. I have created new superusers on each side, I have confirmed that I can login with psql to each of the URL setups on the same system as the pg_easy_replicate install. But this particular configuration always returns: Unable to check config: Unable to check superuser conditions: PG::ConnectionBad: FATAL: password authentication failed for user $USERNAME

Is there something I'm doing wrong in the setup? Thanks for your time.

[shayonj]

I am sorry to hear that! Thanks for opening the issue, we are gonna try and get this sorted. Can you share a few more details about the setup that will help me debug this better

• Whats the version of PG where its working well, and where it isn't?
• What is the pg_easy_replicate CLI command you are using?
• Where is the database hosted (for the one where it is failing)
• Wild guess: But does passing --special-user-role help? - https://github.com/shayonj/pg_easy_replicate?tab=readme-ov-file#bootstrap-and-config-check-with-special-user-role-in-aws-or-gcp

[yrgoldteeth]

I tried both users in the --special-user-role (they are differently named superusers on each side) and still got the same results.

Using version 0.1.9

The replications are all v11 to v11 (for reasons) and the one which worked was with the standard postgres user on each side, but again there are reasons the other databases having different user accounts. Both source and target users have the same password, and the password is random hex characters. The error always occurs on the password authentication on the target database. It happens when I switch to the default postgres account as well in the TARGET_DB_URL.

[yrgoldteeth]

all are self-hosted, forgot to mention!

[yrgoldteeth]

$ pg_easy_replicate config_check
/usr/bin/pg_dump
Unable to check config: Unable to check superuser conditions: PG::ConnectionBad: FATAL:  password authentication failed for user "postgres"
FATAL:  password authentication failed for user "postgres"

[shayonj]

oh! very interesting indeed. Thank you for all the details, appreciate it. Two more Q's

• Is it safe to say the issue isn't happening with the source DB url?
• And are you able to psql to the target db url ? If so, does it work?

Thanks

[yrgoldteeth]

psql works to both source and target urls.

Well, now that I'm poking around it seems to be failing at a check with the source db. I dropped a debugger into the gem code where I'm running it and found it's failing in is_super_user? and the url passed to it is the source database. It gets to the Query.run() and fails the connection there. The url looks to be correct though. Very odd!

[yrgoldteeth]

aha!! I think I have found the discrepancy. the url passed into is_super_user? is for the source, but the user: param is pulling the user out of the target url. I am going to try making the user names match on each side and see if that fixes things.

[yrgoldteeth]

that's it! if the superuser names are different between target and source, it can cause a discrepancy checking for the superuser credential. I think you can close this other than maybe if you want to document that, thanks for the help!

[shayonj]

ahh! glad it sorted out. Nice debugging!

I will keep it open and see if we can have it support different / contextual usernames, specific to the URL being passed in for each of the checks. Sounds like something that can be confused with.

[yrgoldteeth]

well now I'm getting Unable to bootstrap: Unable to create user: PG::DependentObjectsStillExist: ERROR: role "pger_su_h1a4fb" cannot be dropped because some objects depend on it on the schema load. should I open another ticket? I'm going to put this aside and come back to it tomorrow, I don't think it's going to happen for me today.

[shayonj]

oh interesting! Does either of the database has pger_su_h1a4fb user from previous runs?

I wonder if this issue is related to the previous one, so will take a look tomorrow and get back

[shayonj]

I have pushed 0.1.10, if you wanna try that. Also two questions:

• Are you using pgbouncer or anything like that when connecting to the DB?
• Does either of the database has pger_su_h1a4fb user from previous runs?
  • You can try cleaning them via - pg_easy_replicate cleanup --group-name=GROUP_NAME --everything if so

[shayonj]

Update: v0.1.11 is now out, which I think should resolve the user error you were seeing, if you'd like to try that again.

[yrgoldteeth]

Much closer! I think there's still one lingering bug on an edge case: the source database has a dash in the name.

Unable to bootstrap: Unable to create user: Unable to drop user: PG::SyntaxError: ERROR: syntax error at or near "-"

[shayonj]

Thanks! I caught an issue with your report. Just pushed out v0.1.12. Lmk how does it look, when you have a chance

[shayonj]

After some local testing I am not able to replicate the issue with the latest fixed release. Thank you so much for your reports. I am closing the issue but feel free to reopen if it’s still persisting.