Postgresql error, "no pg_hba.conf entry"

Question

I am a newbie to programming. I am building a rails app and now want to push it to Heroku. I followed Heroku's guidelines for converting my database from sqlite 3 to postresql. After doing that I get this error when I try to migrate or even check out my app on local host

"PG::ConnectionBad: FATAL: no pg_hba.conf entry for host "[local]", user "keemtaker", database "tuma_database_development", SSL off"

I checked out previous answers to this same problem but it is so confusing and overwhelming for me. It would be nice if I can get a step by step guide to solve this error. I have used PostgreSQL before with no issues. This is how my gemfile looks like

    source 'https://rubygems.org'
ruby '2.3.4'

gem 'figaro'
gem 'jbuilder', '~> 2.0'
gem 'puma'
gem 'rails', '5.1.4'
gem 'redis'
gem 'cloudinary'
gem 'carrierwave', '~> 1.2'
gem 'rails_admin', '~> 1.2'
gem 'pg'

gem 'autoprefixer-rails'
gem 'bootstrap-sass'
gem 'jquery-rails'
gem 'font-awesome-sass'
gem 'sass-rails'
gem 'simple_form'
gem 'turbolinks', '~> 5.0.0'
gem 'coffee-rails'

gem 'uglifier'
gem 'webpacker'
gem 'devise'
gem 'ransack'
gem 'trix'


gem "letter_opener", group: :development


group :development do
  gem 'web-console', '>= 3.3.0'
end

group :development, :test do
  # gem 'binding_of_caller'
  # gem 'better_errors'

  gem 'pry-byebug'
  gem 'pry-rails'
  gem 'listen', '~> 3.0.5'
  gem 'spring'
  gem 'spring-watcher-listen', '~> 2.0.0'
end

In my database.yml, i have

    default: &default
  adapter: postgresql
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  timeout: 5000

development:
  <<: *default
  database: tuma_database_development

test:
  <<: *default
  database: tuma_database_test

production:
  <<: *default
  database: tuma_database_production

Answer 1

According to Heroku Support this is not an error coming from your Rails application but from someone malicious trying to authenticate to your PostgreSQL database:

Issue

I'm seeing failed connection attempts in my Heroku Postgres logs, but I'm not creating these connections and they come from an IP address that I don't recognize. It looks like someone might be trying to hack my database:

sql_error_code = 28000 FATAL: no pg_hba.conf entry for host "122.180.247.11", user "postgres", database "postgres", SSL off

Variations of the above error might include the user being set to root, admin, test, or other default usernames.

Resolution

These errors indicate a failed login attempt was made to your database, which means that the connection wasn't established.

It is common to see failed connection attempts that use default usernames (such as user "postgres", database "postgres"). Being on the public internet means some level of unauthorized access attempts are possible. These are very unsophisticated attempts that usually involve trying combinations like root, psql, test and postgres. However, Heroku Postgres usernames, passwords, and database name combinations are randomly generated, making these generic access attempts futile.

Another common cause of the sql_error_code=28000, particularly when it involves the "unsupported frontend protocol" message is the use of tools like nmap. Read more about the "unsupported frontend protocol" message.

Heroku takes security seriously and works diligently to ensure the safety of your data. Furthermore, trust is a core principle of salesforce.com and Heroku. It’s this commitment to customer privacy and inspiring trust that directs the decisions we make on a daily basis. Trust is the responsibility of each and every employee and one we take seriously.

Answer 2

By this configuration, your app will look to a local database, which, on heroku, you don't have

But see here the config they're advising :

1. Add the postgres add-on
2. replace your production config lines with the ones showed in the link above

When you install the add-on, heroku will add a DATABASE_URL environment variable on your project. Rails will automatically look for and pick this variable (see here)

Answer 3

You need to specify the Database URL for Heroku, since you don't have access to a local database on their infrastructure. Replace your production section of database.yml with this:

production:
  url: <%= ENV["DATABASE_URL"] %>
  pool: 15

---

So your complete database.yml would look like this:

default: &default
  adapter: postgresql
  encoding: unicode
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  timeout: 5000

development:
  <<: *default
  database: tuma_database_development

test:
  <<: *default
  database: tuma_database_test

production:
  <<: *default
  url: <%= ENV["DATABASE_URL"] %>

---

^{Source: Getting started with Rails on Heroku}