CODEWITHSUNDEEP
postgresqlpostgresql-9.1
Safdar Iqbal
Safdar Iqbal

Reputation: 25

Why does the OS user has to be same as the database user while connecting to PostgreSQL using md5 authentication?

I have configured md5 authentication for user foo by putting the following in pg_hba.conf:

# TYPE    DATABASE    USER    ADDRESS    METHOD
local     all         foo                md5

When I try to connect (using psql) as the Linux user foo, psql asks for the password and the connection is successful. However, if I run psql as some other Linux user, I receive the following error:

psql: FATAL:  password authentication failed for user "foo"

What is the reason for this behavior? I am under the impression that this only necessary if I use the peer or ident authentication methods.

Edit: I am using the command psql -U foo -W to connect. When I prefix the command with sudo -u foo it works; when I don't it gives the error as mentioned above. Sorry for not mentioning this while posting the original question!

Edit 2:

Here are all the un-commented lines from my pg_hba.conf in order:

# TYPE    DATABASE    USER        ADDRESS        METHOD
local     all         postgres                   peer
local     all         foo                        md5
local     all         all         127.0.0.1/32   md5
local     all         all         ::1/128        md5

Upvotes: 0

Views: 176

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125254

If you don't specify the database it will try to connect to the database with the same name as the Linux user. If you are logged as bar and do:

psql -U foo -W

It will try to log to the bar database with the foo user. To connect to the foo database from the bar loggin do:

psql -U foo -W foo

Upvotes: 1

Chris Farmiloe
Chris Farmiloe

Reputation: 14175

psql defaults to whatever the current user is. Use the -U flag to pick the database "role".

Postgres roles and the system users are seperate, although authentication can be configured to work against the system's own (pam/ldap/etc).

Upvotes: 0

Related Questions