All geek questions in one place

Postgres: authentication fails when trying to log in using another unix user

I have a hduser user and a postgres user,

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 log into the same database with another user, for example hduser or another?

thanks

+4
source share
4 answers

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

On the other hand, access to postgres using IP transport channels is configured by default to use passwords IF there is a password. Therefore, this should help you for ordinary users.

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

Bad message: DB postgres superuser does not have a password by default, so you must set it first.

+15
source

Postgres processes authentication and authorization separately.

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

Database and table access authorization is configured by issuing GRANT statements in SQL.

On most Linux systems, a Postgres user is configured to use "ident" authentication and does not have a password by default, so if you want to log in using a password, you need to configure it using an SQL statement, and then change your pg_hba.conf to allow postgres users to login with password:

ALTER ROLE postgres WITH PASSWORD password

+1
source

You can customize the file . pgpass for hduser

0
source

I added the following to pg_hba.conf and got the fix

 local all all trust
-2
source

Source: https://habr.com/ru/post/1385185/

More articles:


All Articles