3

I'm having problems assessing a postgres database from straight ruby.

I've created a Postgres database using Rails

>rails new www --database=postgresql

using Rails 4.2.5 and Postgres is 9.4

It produces the following config/database.yml file.

default: &default
  adapter: postgresql
  encoding: unicode
  pool: 5

development:
  <<: *default
  database: www_development

test:
  <<: *default
  database: www_test

production:
  <<: *default
  database: www_production
  username: www
  password: <%= ENV['WWW_DATABASE_PASSWORD'] %>

I can run rails server, db:drop, db:create and db:migrate fine.

I can also access the database fine with psql

>psql www_development

But when I run the following app.rb from a non Rails project directory, I get a fe_sendauth: no password supplied (PG::ConnectionBad) error message.

It's clearly not a Rails issue. I've either missed something in my ruby or Postgres need a tweek to handle some difference between Rails and pure Ruby [that I'm not aware off]. I've also included Postgres' pg_hba.conf file.

At wits end trying to figure this one out. Any help would be much appreciated.

app.rb

require 'yaml'
require 'active_record'
require 'pg'

ActiveRecord::Base.establish_connection(
  adapter:  'postgresql',
  host:     'localhost',
  database: 'www_development',
)

/etc/postgresql/9.4/main/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
Promise Preston
  • 24,334
  • 12
  • 145
  • 143
gnoll110
  • 332
  • 1
  • 4
  • 11
  • Check this answer to properly create a postgresql user and add the user and password to the `database.yml` file: https://stackoverflow.com/a/23127354/2774342 – dani24 Apr 03 '22 at 16:54

5 Answers5

1

You don't specify neither username, no password in your ActiveRecord::Base.establish_connection(. I assume you want to use some SUPERUSER without password for www_development database - right?

as per documentation peer auth does

Obtain the client's operating system user name from the operating system and check if it matches the requested database user name.

That is why if you can psql without password, you should be able run app.rb with same OS user and environment without password. If you can't, then app.rb tries to connect with different username or so...

Options:

  1. put username: postgres to ActiveRecord::Base.establish_connection(

  2. change local all all peer to local all all trust

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
  • Thanks Tsun. I running app.rb, rails server and psql under that same user. I want to understand why I'm getting a different result in pure ruby. Are you saying rails server accesses the database as postgres, rather than the user that ran the command? – gnoll110 Dec 22 '15 at 07:54
  • Could be. Try option 1. If it fixes the issue - yes – Vao Tsun Dec 22 '15 at 08:08
  • Well, using postgres as the user worked, so seems likely. I'm not leaving that info smeared over any code bases. I'm going with option 2. Thanks for your help. – gnoll110 Dec 22 '15 at 08:47
  • My pleasure, @gnoll110 – Vao Tsun Dec 22 '15 at 08:54
1

With ENV variable as password it's very likely that the variable itself is not present. Confirm the presence with printenv. You need to relogin/reboot for the variable to be accessible after you've included it in /etc/environment file for example. If this works, it's probably better than changing pg_hba.conf.

Kasperi
  • 853
  • 7
  • 17
  • You can always source the file (`. /etc/environment`) and export the variable to make sure it's available to the function you start afterward. No need to log out and back in. – Alexis Wilke Oct 18 '19 at 06:52
1
development:
  <<: *default
  database: postgres
  username: postgres
  password: postgres
  # must specify the right DB, superuser and superuser Password as per postgreSQL setup

This worked for me, the only changes I needed to make. (ok fine i re-installed pgsql with 12.1)

Ra Fi
  • 39
  • 1
0

I had this same issue when setting up a Rails 6 application.

The issue was that when start the rails server using rails server, and try to view the application from a browser, I get the error below:

ActiveRecord::ConnectionNotEstablished
fe_sendauth: no password supplied

Here's how I solved it:

The issue was caused by me not specifying/supplying the database password to be used by the application, so when the application tries to connect to the database specified in the config/database.yml it runs into that error.

I solved it by specifying the database password for the application in the config/database.yml:

# The password associated with the postgres role (username).
password: my-password

Also, ensure that you've created the database for the application if you've not created it already using:

rails db:create

Afterwhich I restarted the rails server:

rails server

This time when I tried viewing the application from the browser, it worked fine.

That's all.

I hope this helps

Promise Preston
  • 24,334
  • 12
  • 145
  • 143
0

I was trying to use peer authentication which shouldn't ask for the password.

For me the issue was that I was specifying localhost in the database.yml so that was forcing the database driver to try to make a different type of connection (e.g., tcp, named pipe, etc)

Removing the "host: 'localhost'" line from my config solved the problem for me per this other answer: ActiveRecord connection to a localhost postgresql database in trust mode

Note that in my case I'm using the 'peer' method and not the 'trust' method but the solution is the same

Tareq Saif
  • 19
  • 2