It's a common problem when new users start to deal with PostgreSQL ,
if you have login problem many possibilities exist and i tried to cover as much as possible of them .
first you should know how to start PostgreSQL ,
for must of Posix operation systems you can start PostgreSQL by :
service postgresql start
in some installation cases specialy when you use offical PostgreSQL repositories you need to specify Server version in service command for example if you use version 9.4 your command will be :
service postgresql-9.4 start
First problem : Incorrect Password For postgres user
if you are mysql user you know mysql default superuser is `root`
but in postgresql default superuser is `postgres`
it's quite easy to change postgreSQL after installation , but make sure that your Server is running
you need to change your current user to postgres user for this proccess
first login as root or do following commands with sudo .
then change user with this command :
after changing your user you can simply access to postgresql server without password autentication with following command :
to change password execute this query :
ALTER USER postgres WITH PASSWORD 'your-new-password';
and it's done , you can login to postgresql with new password with following command :
psql -U postgres -W
to check connection over TCP you can use this command :
psql -U postgres -W -h localhost
if you got any other problem you can continue reading .
Second Problem : Disallowed Authentication Methods
it is possible that PostgreSQL server reject your valid login data because of some security configuration that automaticly declered on installation proccess.
as i found out , these security configurations depend on which repository and which operation system you use .
for example i recieved these problems mostly with Centos/Redhat base repository .
but it fine for me in most of Ubuntu/Debian based servers .
if you got following errors at login attempts ,follow the proccess to solve your problem :
psql: FATAL: Ident authentication failed for user "postgres"
psql: FATAL: Peer authentication failed for user "postgres"
ok to solve this problem first you should i understand for what purpose you recieved these errors .
PostgreSQL limit login sources (ip,socket and etc) by some security policies .
these security policies defined in a file that named as pg_hba.conf
this file location is different by operation system that you use .
to find configuration file you simply can login with previously explained method ( su postgres and then psql )
then run this query :
postgres=# show config_file;
and you will get the full path to main postgres config :
ok now go to this path and first edit postgresql.conf ,
because in some cases postgresql.conf does not include hba files automaticly .
search for these lines :
data_directory = '/var/lib/postgresql/9.4/main'
hba_file = '/etc/postgresql/9.4/main/pg_hba.conf'
ident_file = '/etc/postgresql/9.4/main/pg_ident.conf'
if these lines are empty , commented or defined with default values like Configdir/pg_hba.conf you need to change lines as explained above ,
it's important to specify data_directory and hba_file .
ok after this create(if not exist) or open pg_hba.conf
you may see some other config line it is important to comment them all with # operator
after that you can add these lines at the end of file :
local all all trust
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
these lines means that all localhost methods with all authentication methods are available for all users .
so everything is ok now , so restart server .
service postgresql-9.4 restart
if you got any error on restart proccess , check the config files , you mess up something in these files :D .
ok after restart you should able to login with postgres user .
i'm looking to hear your comments ,
have a nice day .