PG::ConnectionBad: fe_sendauth: no password supplied

Question

When I attempt to run "rake test" on a local postgres database, it throws the above exception.

Here is my pg_hba.conf file: # Database administrative login by Unix domain socket local all postgres peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             username                                  peer
local   myapp_dev   myapp                               md5
local   myapp_test  myapp                               md5
local   myapp_prod  myapp                               md5
#local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5

and here is the relevant section from my database.yml

test:

adapter: postgresql
database: myapp_test
pool: 5
timeout: 5000
host: localhost
username: username
password:

In the real database.yml, 'username' is replaced with my actual user name that I am logged in as. Since the authentication method is defined as 'peer', no password should be required.

I have also taken care to restart Postgres

sudo -u postgres pg_ctlcluster 9.3 main restart

What else am I missing here?

Daniel Vérité · Accepted Answer · 2017-11-21T15:12:23.520

32

localhost as a host refers to a TCP connection, which means the auth method is md5 (password required) per your pg_hba.conf:

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

For the peer method to be taken, you'd need to connect through Unix domain sockets, and since you seem to be using a debian-like OS, that means putting /var/run/postgresql in the host field, or nothing at all (it's the default unless environment variables say otherwise).

EDIT: if using database URIs (supported since Rails-4.1, as announced in http://weblog.rubyonrails.org/2014/4/8/Rails-4-1/), the syntax could be:

for localhost:
test: "postgresql://localhost/myapp_test"
for the default Unix socket domain (host field left empty):
test: "postgresql:///myapp_test"

edited Nov 21 '17 at 15:12

answered Apr 30 '14 at 00:27

Daniel Vérité

58,074
15
129
156

4

Please note that **host field** refers to ```host: localhost``` in database.yml, I used ```host: /var/run/postgresql``` and it works as well. Thanks for answering! (under Debian 7.8) – pimgeek Feb 11 '15 at 06:40
When you say "nothing at all", do you mean `host: ""` or removing the `host` key completely? – Dennis Feb 14 '15 at 02:29
@Dennis: good question, I was answering from a postgresql perspective, but in terms of syntax in database.yml and if/how empty fields are to be submitted, I don't have the answer. If you or someone else are inclined in testing it, feel free to edit the answer with the results. – Daniel Vérité Feb 17 '15 at 17:39
@DanielVérité I removed `host` and didn't notice issues, and @Isiden had success with `host: ""` so my guess is both work. I didn't it look into it much so I feel safer leaving this comment instead of editing the answer. – Dennis Feb 25 '15 at 22:36
13

`pg_hba.conf` file is located on the `/etc/postgresql/9.3/main/pg_hba.conf` on Ubuntu – Bruno Peres Mar 07 '16 at 13:26
When using `postgres://` style URIs, replacing `postgres://localhost/database` by `postgres:///database` does the trick. – mkataja Nov 21 '17 at 14:04

Ravistm · Answer 2 · 2018-04-02T13:11:47.753

32

Change the code as below and it will work

pg_hba.conf:
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

Below its explanation:

trust

Allow the connection unconditionally. This method allows anyone that can connect to the PostgreSQL database server to login as any PostgreSQL user they wish, without the need for a password or any other authentication.

md5

Require the client to supply a double-MD5-hashed password for authentication.

refer for more here

edited Apr 02 '18 at 13:11

answered Apr 25 '16 at 04:05

Ravistm

2,163
25
25

6

It actually worked for me. But could someone elaborate on if it rather secure or not? Does it mean, that all connections omit the password from now on? – mohnstrudel Nov 01 '16 at 10:13
That means all connections from any local user are [allowed](https://www.postgresql.org/docs/11/auth-trust.html) without password or any other sort of authentication. "When trust authentication is specified, PostgreSQL assumes that anyone who can connect to the server is authorized to access the database with whatever database user name they specify (even superuser names)." I'd rather choose the [`peer`](https://www.postgresql.org/docs/11/auth-peer.html) method: "...works by obtaining the client's operating system user name from the kernel and using it as the allowed database user name..." – x-yuri Apr 23 '19 at 10:39

score 11 · Answer 3 · answered May 20 '16 at 20:11

If your hb_conf has already been modified to force passwords, then make sure your rails app's database configuration includes a password in both development and test environments.

default: &default
  adapter: postgresql
  encoding: unicode
  pool: 5
  host: localhost
  username: your_user
  password: your_password

development:
  <<: *default
  database: your_db_development

test:
  <<: *default
  database: your_db_test

production:
  url: <%= ENV['DATABASE_URL'] %>

I was getting this error when I failed to supply a password for the test database.

score 0 · Answer 4 · edited Dec 10 '19 at 16:32

0

I met this question, too. I checked my database config, /var/www/myproject/shared/config/database.yml:

production: 
adapter: postgresql 
pool: 5 
timeout: 5000 
encoding: utf8 
host: localhost 
database: myproject
username: myname 
password: <%= ENV['name_DATABASE_PASSWORD'] %>

I found the last paragraph is wrong, the right code is

password: <%= ENV['myproject_DATABASE_PASSWORD'] %>

edited Dec 10 '19 at 16:32

wscourge

10,657
14
59
80

answered Jul 02 '17 at 10:28

hofffman

41
4

score 0 · Answer 5 · edited Apr 09 '19 at 20:40

I use Postgres App. Which is really great because you don't have to supply username and password in the default part of the database.yml

database.yml

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

development:
  <<: *default
  database: my-app_development

test:
  <<: *default
  database: my-app_test

production:
  <<: *default
  database: my-app_production
  username: my-app
  password: <%= ENV['MY-APP_DATABASE_PASSWORD'] %>

My problem: I changed the path and wasn't able to get postgres to work.

Solution: use the documentation to:

1) Uninstall the app

2) Stop the postgres process using this answer, which says to use sudo pkill -u postgres

3) Reinstall and start the app

Everything should work fine now, have a great day!

Note: Doing this also solves the problem Postgres.app port 5432 in use

score 0 · Answer 6 · answered May 04 '20 at 16:16

I had this problem, solved by a coworker: You need to set a local user and password in postgres

createuser --interactive --pwprompt

It will ask you for the name of the role (the user) and the password you want to have.

Then you have to add this username and password to your database.yml file

score 0 · Answer 7 · answered Mar 08 '21 at 14:21

rename method from peer to trust in /etc/postgresql/<postgres-version>/main/pg_hba.conf file.

# Database administrative login by Unix domain socket
local   all             postgres                                trust

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

score 0 · Answer 8 · answered Jun 14 '22 at 14:05

initialize dot environment in your project

   # dot gem
 gem 'dotenv-rails', groups: [:development, :test]

than bundle install

and than make a .env file in your project and add the following line

POSTGRES_HOST=localhost
POSTGRES_USER= user_name
POSTGRES_PASSWORD= your_password
RAILS_MAX_THREADS=5

PG::ConnectionBad: fe_sendauth: no password supplied

8 Answers8

Linked