Multisite backup using user discourse rather than DB_USER or value from multisite.yml

Bug
1

EDIT/Tl;dr: ActiveRecord::Base.connection_pool.db_config.configuration_hash has the username in user, not username, but Discourse is looking for it in username.

Trivial PR: FIX: backup_restore.rb wants db user from user, not username by pfaffman · Pull Request #28229 · discourse/discourse · GitHub

…

I’ve got a multisite instance using Digital Ocean’s hosted postgres. It’s working fine but when I try to backup, I get an error that

[2024-08-05 16:13:31] pg_dump: error: connection to server at "private-forum-cluster-postgresql-prod-do-user-1230.j.db.ondigitalocean.com" (10.11.1.6), port 25060 failed: FATAL:  password authentication failed for user "discourse"

But the user is not discourse. It’s using the correct user other places, including ActiveRecord::Base.connection_pool.db_config.configuration_hash and BackupRestore.database_configuration (when I run those commands in RAILS_DB=sitename rails c). PG_USER is not set in the environment (that I can see).

It looks ike this is to blame:

I edited it in the container (to pull it from DISCOURSE_DB_USER, which seemed like a good idea at the time) and the backup works now. I don’t know if you’re supposed to be able to pull connection info out of multisite.yml or not, but ignoring DB_USER seems wrong.

Or maybe I should just make the user be discourse like I guess everyone else must be doing?

EDIT: Wait. No.

config['username'] should be config['user']

    config = ActiveRecord::Base.connection_pool.db_config.configuration_hash

returns this!

   
=> {:adapter=>"postgresql",
 :database=>"theDatabase",
 :pool=>25,
 :port=>25060,
 :timeout=>5000,
 :host=>"private-forum-cluster-postgresql-prod-do-user-123-0.j.db.ondigitalocean.com",
 :user=>"theCurrentUsername",
 :password=>"supersecret",
 :host_names=>["forum.example.com"],
 :db_key=>"mydb",
 :prepared_statements=>false}

So

    config["username"] || username || ENV["USER"] || "postgres",

should be

    config["user"] || username || ENV["USER"] || "postgres",
2 Likes
2

@pfaffman I’m afraid we had to revert this, because it broke restoration of backups in our production environments.

Our sample multisite config (and indeed, the production config) has it under ‘username’:

2 Likes
3

So sorry! I am still confused how it works for everything except backup.

Hmm.

Maybe that’s my issue. But how is it working for everything else?

Maybe everything else uses the global setting from an environment variable.

2 Likes
4

I’m so sorry.

It didn’t occur to me that ActiveRecord::Base.connection_pool.db_config.configuration_hash was just whatever nonsense I put into the multisite.yml file.

I renamed all of my errant user: fields as username: and now my backup works.

My explanation (which I have not tested) is that the rest of rails overrides the db_username from GlobalSettings if one is set there, but when it goes to get that information to make the external call to pg_dump it pulls it from the multisite config.

I still think it’s a bug that the backup gets a different user and that the fix would be to include pulling env['DISCOURSE_DB_USERNAME'] somewhere in that assignment statement, but I’m not willing to risk breaking production again, and now it’s working.

You should unlike my post and remove my badge! :crying_cat_face:

2 Likes
5

No worries at all, this stuff happens. Ideally, it would’ve been caught in CI, but I guess we don’t have coverage of this logic (maybe difficult, since the db setup is all different in tests anyway)

Thanks for documenting what you found - I’m sure it’ll help others in future. I agree the inconsistency here is bad, and would be good to fix at some point.

3 Likes