Password authentication failing for Postgresql

Question

I'm creating a Rails app and using a Postgresql database with it. I've created a few tables and a user, core, which is the owner of each of the tables.

postgres=# create user core with password 'n7zD5FG5';
CREATE ROLE
postgres=# create database core_apps_prod with owner core;
CREATE DATABASE
postgres=# create database core_apps_dev with owner core;
CREATE DATABASE
postgres=# create database core_apps_test with owner core;
CREATE DATABASE

And my database.yml file:

development:
  adapter: postgresql
  database: core_apps_dev
  username: core
  password: n7zD5FG5
  host: localhost

However, when I run rake db:migrate, I get the error

rake aborted!
FATAL:  password authentication failed for user "core"

I also cannot connect to psql manually: psql -U core -W -d core_apps_dev - I get the same error.

How can I allow core to connect to Postgresql on localhost?

The output of SELECT * FROM pg_roles where rolname='core'; is:

 rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolconfig |  oid  
---------+----------+------------+---------------+-------------+--------------+-------------+----------------+--------------+-------------+---------------+-----------+-------
 core    | f        | t          | f             | f           | f            | t           | f              |           -1 | ********    |               |           | 16392

Add `-h localhost` to psql to connect to localhost, otherwise it's using Unix domain sockets which have different authentication rules. Also by using `-W` you won't know whether the server requires a password or not, which adds to the confusion when troubleshooting password problems. Just omit `-W` — Daniel Vérité, Nov 21 '13 at 03:09
I tried that, by running the command `psql -h localhost -U core -d core_apps_dev` and entering the password, but it still says `psql: FATAL: password authentication failed for user "core"`. I made doubly sure that the password is correct by running `ALTER USER core PASSWORD 'n7zD5FG5';` but it still failed. — josh, Nov 21 '13 at 03:15
What you did works for me. At this point I'd suspect a few possibilities: 1. having created the account on a different PG instance. 2. [pgadmin bug on valuntil](http://stackoverflow.com/questions/14564644), although you never mentioned pgadmin. 3. Weirdness in pg_hba.conf. 4. weirdness in postgresql.conf on auth options. To clear out a bit, I'd set the auth method to `trust` in pg_hba.conf on the relevant line, reload PG and retry. It should connect without asking for a password. — Daniel Vérité, Nov 21 '13 at 10:02

AJcodez · Answer 1 · 2013-11-21T01:00:39.987

0

You need to add privileges to the role when you create it. To allow logging in and using rake db:create use the following:

create role core with login createdb password 'n7zD5FG5';

To fix your problem, try altering the role to allow login:

alter role core with login;

And if that doesn't work, see if making the login valid forever works:

alter role core valid until 'infinity';

More about roles: http://www.postgresql.org/docs/8.2/static/sql-createrole.html

Edit:

I also had to do this when I installed postgres:

$ psql postgres -c 'CREATE EXTENSION "adminpack";'

edited Nov 21 '13 at 01:00

answered Nov 21 '13 at 00:53

AJcodez

31,780
20
84
118

I believe that `core` has login privilege. I ran `SELECT * FROM pg_roles;` and `core` has the role `rolcanlogin`. – josh Nov 21 '13 at 00:57
yeah just read `create user` implies the login role. see if the password is valid, and if that's not it, not sure what is – AJcodez Nov 21 '13 at 00:58

score 0 · Accepted Answer · answered Nov 21 '13 at 19:54

The issue turned out to be an issue with the port. The Postgresql Activerecord adapter defaults to port 5432, while the port in my configuration was port 5433. My database.yml now looks like this:

development:
  adapter: postgresql
  database: core_apps_dev
  username: core
  password: n7zD5FG5
  host: localhost
  port: 5433

score -1 · Answer 3 · answered Nov 21 '13 at 00:05

-1

The newly created user probably is lacking database privileges. There are many different privileges you can give to a user.

Assuming that you have admin account access to psql, go into psql and run

GRANT ALL PRIVILEGES ON DATABASE core_apps_dev to core;

answered Nov 21 '13 at 00:05

Jason Kim

18,102
13
66
105

It doesn't have all privileges to the database when it's the owner? – josh Nov 21 '13 at 00:06
Yes, you will give admin privileges this way. [More on database privileges for postgresql...](http://www.postgresql.org/docs/9.0/static/sql-grant.html) – Jason Kim Nov 21 '13 at 00:08
So, if I create the database with owner `core`, it should have all privileges on the database? – josh Nov 21 '13 at 00:10

score -1 · Answer 4 · answered Nov 21 '13 at 06:10

-1

Can you please try

alter role core with password "n7zD5FG5";

answered Nov 21 '13 at 06:10

Sameer Kumar

1

Password authentication failing for Postgresql

4 Answers4