Trouble bootstrapping with RDS --- could not open certificate file

Product Support Self-hosting

AI-generated summary

A user is experiencing issues bootstrapping a Discourse instance with RDS, where the process fails during asset upload due to SSL certificate and pg_hba.conf errors. The error indicates a failure to open the certificate file /root/.postgresql/postgresql.crt and a missing pg_hba.conf entry, despite successful database migration and precompilation. The user notes that the container can connect to PostgreSQL using the same credentials, suggesting the issue is specific to the bootstrap process. After investigation, the user discovers that adding PGSSLCERT: /tmp/postgresql.crt to the environment variables resolves the issue, which was previously configured in the staging environment but missing in production. pfaffman pfaffman

1

I’ve got a pretty standard install with databse on RDS. It was on 13.2 and I just upgraded it to 15.2. The existing container is working just fine.

When I bootstrap a new image, it migrates the database, precompiles assets

and then

                                                                                                                                                                            
I, [2023-12-20T20:16:12.865112 #1]  INFO -- : > cd /var/www/discourse && sudo -E -u discourse bundle exec rake s3:upload_assets                                              
Plugin name is 'discourse-linkedin-auth', but plugin directory is named 'discourse-plugin-linkedin-auth'                                                                     
Plugin name is 'stemaway-experience-bbcodes', but plugin directory is named 'experience-bbcodes'                                                                             
Plugin name is 'discourse-badge-search', but plugin directory is named 'stemaway-badge-search'                                                                               
Plugin name is 'points-plugin', but plugin directory is named 'tacks-plugin'                                                                                                 
Plugin name is 'discourse-group-assign-by-custom-field', but plugin directory is named 'ucf-group-assignment'                                                                
rake aborted!                                                                                                                                                                
ActiveRecord::NoDatabaseError: We could not find your database: discourse. Which can be found in the database configuration file located at config/database.yml. (ActiveRecor
d::NoDatabaseError)

and then later stuff like this:

Caused by:                                                                                                                                                                   
PG::ConnectionBad: connection to server at "172.31.x.y", port 5432 failed: could not open certificate file "/root/.postgresql/postgresql.crt": Permission denied (PG::Connec
tionBad)                                                                                                                                                                     
connection to server at "172.31.x.y", port 5432 failed: FATAL:  no pg_hba.conf entry for host "172.31.x.y", user "postgres", database "discourse", no encryption   
...
Pups::ExecError: cd /var/www/discourse && sudo -E -u discourse bundle exec rake s3:upload_assets failed with return #<Process::Status: pid 7133 exit 1>

It doesn’t make sense (to me) that it can migrate andprecompile but not upload assets.
If I get into the container as it’s building, I can connect to postgres using the credentials in the ENV.

I’ll work on getting the full log out of tmux. . .well, the buffer wasn’t very big and won’t fit here. I"ll try to make a bigger buffer, run another bootstrap, and stick the full log somewhere.

2

Turns out, you need

env:
  PGSSLCERT: /tmp/postgresql.crt

I don’t know where I figured this out, but it’s in the yml for the staging site, but not the production one. And it worked just fine until recently. It’s bizarre, because the database migrated just fine, and didn’t fail until it started trying to precompile assets.;