(How To Solve ) PostgreSQL Login Problem Solution On CentOS RedHat Ubuntu Debian :: Jul 03 2015

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 :

su postgres

after changing your user you can simply access to postgresql server without password autentication with following command :

psql

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 :

               config_file                
------------------------------------------
 /etc/postgresql/9.4/main/postgresql.conf
(1 row

ok now go to this path and first edit postgresql.conf ,

why ?

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 .