Postgres: authentication fails when try to login with different unix user

Question

I have a user hduser and user postgres,

with hduser:

hduser@master:/$ psql -Upostgres -W analytics
Password for user postgres: 
psql: FATAL:  Ident authentication failed for user "postgres"
hduser@master:/$ 

with postgres:

postgres@master:/opt/pentaho/biserver-ce$ psql -Upostgres -W analytics
Password for user postgres: 
psql (8.4.9)
Type "help" for help.

analytics=# 

How can I login to same database with a different user like hduser or some other?

Thank you

Answer 1

The default configuration of PostgreSQL allows "local" access to a database only to database users which have the same name as the OS user. "local" means without any IP traffic using only the Unix-Domain sockets. See the documentation for the configuration file pg_hba.conf especially the lines starting with "local" and the authentication method "peer" or "ident".

On the other hand accessing postgres using an IP transport channels is by default configured to use passwords IF there IS a password. Therefore this should help youfor normal users.

foouser@host$ psql -U baruser -h 127.0.0.1 database

The bad message is, that the DB superuser postgres does NOT have a password by default, so you must set one first.

Answer 2

Postgres handles authentication and authorization separately.

Authentication options are configured in the file: pg_hba.conf -- this file describes what authentication methods users are allowed to use, and which hosts they can connect from.

Authorization to access databases and tables is configured by issuing GRANT statements in SQL.

On most Linux systems, the Postgres user is setup to use 'ident' authentication, and does not have a password by default, so if you wish to login using a password, you'll need to configure that with a SQL statement, and then alter your pg_hba.conf to allow the user postgres to login using a password:

ALTER ROLE postgres WITH PASSWORD password