Password in postgres 8.3 not working

Question

i have installed postgres 8.3, i entered test as the password while the installation...my problem is that each time i try to access the local database...each time i enter the password...it say database connecting failed...i have tried to change the password via command line but it does not work...even tried to change the configuration file...it does not work...is there any way out for me to make the database work? thanks for your reply...

Answer 1

Often people cannot connect to postgres with a password because it is configured by default to use ident authentication for local connections. Try switching to the postgres system account and connecting with that username. If you get in without a password you're configured for ident. You can also check the authentication methods in pg_hba.conf.

If you want to switch to using passwords for local connections, you can modify the setting in pg_hba.conf. At the bottom of the file there's a section like this:

# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database
# super user can access the database using some other method.
# Noninteractive
# access to all databases is required during automatic maintenance
# (autovacuum, daily cronjob, replication, and similar tasks).
#
# Database administrative login by UNIX sockets
local   all         postgres                          ident sameuser

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               md5 #ident sameuser
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
host    all         all         ::1/128               md5

You can see on the second "local" line I have changed my setting to use md5 authentication instead of ident sameuser. That allows me to log in as any database user from my system account by using the correct password. If you make the same change and restart postgres that might solve your problem.

Be careful not to change the first "local" line. The postgres user is the super user and if you change that authentication mechanism you may not be able to get in.

Answer 2

In addition to the comment about editing pg_hba.conf, I also had to set a password for myself like in this thread

http://ubuntuforums.org/showthread.php?t=388632

using

ALTER USER postgres WITH ENCRYPTED PASSWORD 'yourhardtoguesspassword';