Postgresql – password confusion in postgres sql

postgresqlpostgresql-9.3

I have logged into postgresql server using

sudo -u postgres psql

Then I also created a database from within /usr/local/bin directory using the command. 

createdb -h localhost -p 5432 -U postgres testdb

When prompted for password, I entered the PostgreSQL admin user password (that I had changed from 'postgres' to 'upendra').

When I do 

\l

in the postgres prompt, I get the following output
enter image description here

When I connect to testdb from within the postgres prompt, I am not required to enter the password. the connection is instantaneous.

However, when I try to connect to this database from the terminal using

psql -h localhost -p 5432 postgres testdb

I am required to enter password. Why is it so?Moreover, no matter what password I type it always says that authentication failed. what's the catch?

enter image description here

Also, why does the prompt say password authentication failed for user "testdb". From what I have understood, testdb is the name of the database and not the name of the user. The user name is postgres.

Best Answer

First, to answer the question you asked about the behavior of this command:

 psql -h localhost -p 5432 postgres testdb

Also, why does the prompt say password authentication failed for user "testdb". From what I have understood, testdb is the name of the database and not the name of the user. The user name is postgres.

Because you have given two trailing options to this psql command: postgres and testdb. Since neither the -d nor -U option were given, psql assumes the database name should be the first argument and the username should be the second argument, per the documented Synopsis of the psql command.

Second, to answer your other question:

When I connect to testdb from within the postgres prompt, I am not required to enter the password. ... However, when I try to connect to this database from the terminal using [... psql, and getting a password prompt ... ]

psql has some logic by which it determines whether it is safe to keep and reuse the password you entered initially, see the logic about keep_password in command.c. Presumably when you are able to use the \c meta-command to reconnect from within an existing psql session, psql is preserving your initial password and reusing it -- either that, or your pg_hba.conf rules allow a connection without a password with the given user/database, e.g. a trust or ident rule.

Related Question