Can't log into PostgreSQL database

Question

I created a user like this:

create user blog with password 'blog';

Then I made it the owner of a database:

alter database blog_development owner to blog;

Then I tried to log in and it didn't work:

$ psql -d blog_development -U blog -W
Password for user blog:
psql: FATAL:  Ident authentication failed for user "blog"

Any idea why?

One thing I've tried is editing pg_hba.conf

 76 # Database administrative login by UNIX sockets
 77 local   all                postgres                          ident
 78
 79 # TYPE  DATABASE           USER        CIDR-ADDRESS          METHOD
 80
 81 # "local" is for Unix domain socket connections only
 82 local   all                all                               ident
 83 # IPv4 local connections:
 84 host    all                all         127.0.0.1/32          md5
 85 host    blog_development   blog        127.0.0.1/32          md5
 86 # IPv6 local connections:
 87 host    all                all         ::1/128               md5

Line 85 is the one I added. I restarted PostgreSQL after that but it didn't seem to change anything.

Interestingly, I can log in remotely via pgAdmin III (after adding another line to pg_hba.conf) but I still can't log in from the command line. — Jason Swett
– Jason Swett, Commented Dec 30, 2010 at 20:24
Also, I can connect via a PHP script. I'm starting to think I'm trying to log in the wrong way. — Jason Swett
– Jason Swett, Commented Dec 30, 2010 at 20:25

Jason Swett · Accepted Answer · 2010-12-30 20:31:14Z

44

I was just trying to connect in the wrong way. Here's the right way:

psql -h localhost -U blog -d blog_development

answered Dec 30, 2010 at 20:31

Jason Swett

45.4k70 gold badges231 silver badges360 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Scott Marlowe Over a year ago

yeah, without a -h host pgsql tries to log in on local unix sockets i.e. the line that starts with local and ends with ident is what's getting in the way.

nate c · Accepted Answer · 2010-12-30 20:39:17Z

5

psql uses a local socket by default (check out -h flag in man) - that should match line 82 when you log in.

82 local all all ident

ident needs a real user in the system to check the password against. So you can either connect by host or add an user so it matches you new config line.

answered Dec 30, 2010 at 20:39

nate c

9,0532 gold badges29 silver badges28 bronze badges

1 Comment

Scott Marlowe Over a year ago

Or change the local all all ident to something else as well.

Peter Eisentraut · Accepted Answer · 2010-12-30 21:54:59Z

4

Before line 82, add

local   blog_development   blog   md5

and reload, or connect using -h localhost.

answered Dec 30, 2010 at 21:54

Peter Eisentraut

37.1k12 gold badges91 silver badges91 bronze badges

Comments

John P · Accepted Answer · 2010-12-30 19:32:25Z

1

Have you added the user to pg_hba.conf? Here is the documentation.

http://www.postgresql.org/docs/8.4/static/auth-pg-hba-conf.html

I don't really remember the exact syntax but you should add something like this:

host    blog_development     blog  127.0.0.1/32  md5

You might need to reload or restart the server so that pg_hba.conf is reread.

answered Dec 30, 2010 at 19:32

John P

15.2k4 gold badges51 silver badges58 bronze badges

1 Comment

Jason Swett Over a year ago

Hmm. I tried that but it didn't seem to work. See my edit to see what I changed.

Collectives™ on Stack Overflow

Can't log into PostgreSQL database

4 Answers 4

1 Comment

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related