Episodes
Steve Lim PRO said 8 months ago on What's New in Rails 6 :
Hi David,

Do you have an idea what could have caused to raise this error PG::InsufficientPrivilege on production env after I updated the db settings?
I have a Heroku app that is connected to Amazon RDS and when I make the following changes below I am getting  PG::InsufficientPrivilege error on production env.
Let me know if you have any suggestions?

Prior to this change, I used Makara gem for splitting DB-r and DB-w.

production:
  <<: *default
  url: postgresql-makara:///
  makara:
    sticky: true
    connections:
      - role: master
        name: primary
        url: <%= ENV["DATABASE_URL"] %>
      - name: replica
        url: <%= ENV["ANALYTICS_URL"] %>

and I changed to as follows:

default: &default
  adapter: postgresql
  encoding: unicode
development:
  primary:
    <<: *default
    database: hot_seller_development
  follower:
    <<: *default
    database: hot_seller_development
    replica: true

production:
  primary:
    <<: *default
    url: <%= ENV['DATABASE_URL'] %>
  follower:
    <<: *default
    url: <%= ENV['ANALYTICS_URL'] %>
    replica: true

whenever I push this change to production environment, the postgres log in Amazon RDS says,
"2021-09-28 11:13:06 UTC:ec2-52-XXXX.compute-1.amazonaws.com(57186):[email protected]:[8428]:LOG: could not receive data from client: Connection reset by peer" <-- this is db with write access (DATABASE_URL)
2021-09-28 11:14:06 UTC:ec2-XXXXX.compute-1.amazonaws.com(47640):[email protected]:[445]:ERROR: permission denied for relation users <-- this is db with read access (ANALYTICS_URL)
It seems my app is relegated to using db with read access because it could not connect with db with write access..

If I simply run, 
ActiveRecord::Base.establish_connection(Rails.application.credentials.fetch :DATABASE_URL)
on rails console, it works.. So I feel like this could be heroku related...

I appreciate it if you could offer any suggestions!

Thanks!


David Kimura PRO said 8 months ago on What's New in Rails 6 :
  Steve Lim I haven't had this use case yet, but it makes sense. Have a look at https://devcenter.heroku.com/articles/amazon-rds. It worries me though that they're saying to open the database up to the entire world. It is a security risk in my opinion.

Steve Lim PRO said 8 months ago on What's New in Rails 6 :
Sorry if I was not clear the first time. So I already have a Heroku app running that is connected to Amazon RDS. my question is how could this change could have created this side effect? I feel like there is some subtle thing I may not be aware in rails ecosystem.

David Kimura PRO said 8 months ago on What's New in Rails 6 :
Just to make sure, in your ApplicationRecord class, did you set the connects_to?

connects_to database: { writing: :primary, reading: :follower }

Steve Lim PRO said 8 months ago on What's New in Rails 6 :
yes I did. also to shed some more light.. the DB-r (ANALYTICS_URL) should not be used because I don't even have this line in place..it should be using my primary db..(DATABASE_URL)
but my postgres log is showing that this DB-r is being used. 2021-09-28 11:14:06 UTC:ec2-XXXXX.compute-1.amazonaws.com(47640):[email protected]:[445]:ERROR: permission denied for relation users <-- this is db with read access (ANALYTICS_URL) which is raising this error...


# config.active_record.database_selector = { delay: 2.seconds } I am not using this line so all the db connections should all go to db-w...



self.abstract_class = true
connects_to database: { writing: :primary, reading: :follower }

Login to Comment