Re: psql: FATAL: Ident authentication failed for user "postgres"

Hi everyone,

I am having problems logging into psql as a non-'postgres' user.

/home/djoo[5:38pm]$ %psql kermit -U postgres

psql: FATAL: Ident authentication failed for user "postgres"

Below is a portion of the pg_hba.conf file, which I believe is
configured so that a password is not required.

# TYPE DATABASE USER CIDR-ADDRESS METHOD

# "local" is for Unix domain socket connections only

local all all trust

# IPv4 local connections:

host all all 127.0.0.1/32 trust

# IPv6 local connections:

host all all ::1/128 trust

Any suggestions would be greatly appreciated!

Dan

Dan Joo wrote:
> Hi everyone,
>
>
>
> I am having problems logging into psql as a non-‘postgres’ user.
>
>
>
> */home/djoo[5:38pm]$ %psql kermit -U postgres*
>
> *psql: FATAL: Ident authentication failed for user "postgres"*

Sure there isn't a line like this:

local all postgres ident sameuser

uncommented?

$ cat /path/to/pg_hba.conf | grep -v '^#'

Normally the 'postgres' user is 'ident' only unless you remove (or
comment out) that particular line, then restart postgres.

--
Postgresql & php tutorials
http://www.designmagick.com/

Dan,

Always cc the mailing list so others can provide other suggestions where
necessary.

Dan Joo wrote:
> Thanks, Chris, but yes, I am sure that there is no such line. (see below)
>
> [root(at)RH-Dev data]# cat pg_hba.conf | grep -v '^#'
>
>
>
>
>
> local all all trust
> host all all 127.0.0.1/32 trust
> host all all ::1/128 trust
>
> Dan

Did you HUP postmaster?

The docs say you can just pg_ctl reload to do this.

> -----Original Message-----
> From: Chris [mailto:dmagick(at)gmail(dot)com]
> Sent: Monday, May 19, 2008 6:22 PM
> To: Dan Joo
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] psql: FATAL: Ident authentication failed for user "postgres"
>
> Dan Joo wrote:
>> Hi everyone,
>>
>>
>>
>> I am having problems logging into psql as a non-‘postgres’ user.
>>
>>
>>
>> */home/djoo[5:38pm]$ %psql kermit -U postgres*
>>
>> *psql: FATAL: Ident authentication failed for user "postgres"*
>
> Sure there isn't a line like this:
>
> local all postgres ident sameuser
>
> uncommented?
>
> $ cat /path/to/pg_hba.conf | grep -v '^#'
>
> Normally the 'postgres' user is 'ident' only unless you remove (or
> comment out) that particular line, then restart postgres.
>

--
Postgresql & php tutorials
http://www.designmagick.com/

Got it about cc'ing.

Actually, I restarted postgres using:

/etc/init.d/postgresql restart

Any other suggestions?

________________________________

From: Chris [mailto:dmagick(at)gmail(dot)com]
Sent: Mon 5/19/2008 8:34 PM
To: Dan Joo; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] psql: FATAL: Ident authentication failed for user "postgres"

Dan,

Always cc the mailing list so others can provide other suggestions where
necessary.

Dan Joo wrote:
> Thanks, Chris, but yes, I am sure that there is no such line. (see below)
>
> [root(at)RH-Dev data]# cat pg_hba.conf | grep -v '^#'
>
>
>
>
>
> local all all trust
> host all all 127.0.0.1/32 trust
> host all all ::1/128 trust
>
> Dan

Did you HUP postmaster?

The docs say you can just pg_ctl reload to do this.

> -----Original Message-----
> From: Chris [mailto:dmagick(at)gmail(dot)com]
> Sent: Monday, May 19, 2008 6:22 PM
> To: Dan Joo
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] psql: FATAL: Ident authentication failed for user "postgres"
>
> Dan Joo wrote:
>> Hi everyone,
>>
>>
>>
>> I am having problems logging into psql as a non-'postgres' user.
>>
>>
>>
>> */home/djoo[5:38pm]$ %psql kermit -U postgres*
>>
>> *psql: FATAL: Ident authentication failed for user "postgres"*
>
> Sure there isn't a line like this:
>
> local all postgres ident sameuser
>
> uncommented?
>
> $ cat /path/to/pg_hba.conf | grep -v '^#'
>
> Normally the 'postgres' user is 'ident' only unless you remove (or
> comment out) that particular line, then restart postgres.
>

--
Postgresql & php tutorials
http://www.designmagick.com/

Hi Luigi,

It is Linux. I am trying to login as postgres but as a user not in the
database. This is because I will be creating a web front end, and
various users, not registered in the database, will need to access the
database. Thus, as "postgres" I can log in fine:

bash-3.1$ psql kermit -U postgres

Welcome to psql 8.1.11, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms

\h for help with SQL commands

\? for help with psql commands

\g or terminate with semicolon to execute query

\q to quit

kermit=#

But as myself, I can't.

/home/djoo[8:25am]$ %psql kermit -U postgres

psql: FATAL: Ident authentication failed for user "postgres"

This is pretty much the same setup I had with another company, so I am
confused why I can't access. Is there another file that I need to alter
besides the pg_hba.conf file?

Thanks for your help,

Dan

From: Luigi Castro Cardeles [mailto:luigi(dot)cardeles(at)gmail(dot)com]
Sent: Tuesday, May 20, 2008 5:53 AM
To: Dan Joo
Subject: Re: [GENERAL] psql: FATAL: Ident authentication failed for user
"postgres"

hi,

what's your machine configuration? Linux, Mac Os?
if you are using mac, maybe you have a problem with identd.

what user are you trying to logon?

comand: psql -U <user-name> -d <database-name>
[]'s

--
Luigi Castro Cardeles

Hi Karsten,

The only non-commented lines are:

[root(at)RH-Dev data]# cat pg_hba.conf | grep -v '^#'

local all all trust
host all all 127.0.0.1/32 trust
host all all ::1/128 trust

Dan

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Karsten Hilbert
Sent: Tuesday, May 20, 2008 8:51 AM
To: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] psql: FATAL: Ident authentication failed for
user"postgres"

On Tue, May 20, 2008 at 08:24:30AM -0700, Dan Joo wrote:

> /home/djoo[8:25am]$ %psql kermit -U postgres
>
> psql: FATAL: Ident authentication failed for user "postgres"

> confused why I can't access. Is there another file that I need to
alter
> besides the pg_hba.conf file?

That depends on the content of your pg_hba.conf file.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

On Tue, May 20, 2008 at 08:24:30AM -0700, Dan Joo wrote:

> /home/djoo[8:25am]$ %psql kermit -U postgres
>
> psql: FATAL: Ident authentication failed for user "postgres"

> confused why I can't access. Is there another file that I need to alter
> besides the pg_hba.conf file?

That depends on the content of your pg_hba.conf file.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

On Tue, 2008-05-20 at 08:50 -0700, Dan Joo wrote:
> Hi Karsten,
>
> The only non-commented lines are:
>
> [root(at)RH-Dev data]# cat pg_hba.conf | grep -v '^#'
>
> local all all trust
> host all all 127.0.0.1/32 trust
> host all all ::1/128 trust
>
> Dan
>

If that is your production pg_hba.conf you have either failed to reload
the database or you aren't actually looking at the right pg_hba.conf.

Sincerely,

Joshua D. Drake

On Tue, May 20, 2008 at 08:50:16AM -0700, Dan Joo wrote:

> The only non-commented lines are:
>
> [root(at)RH-Dev data]# cat pg_hba.conf | grep -v '^#'
>
> local all all trust
> host all all 127.0.0.1/32 trust
> host all all ::1/128 trust

Well, neither of which allows IDENT based authentication.

This doesn't explain, however, why it's attempting IDENT in
the first place. It really *should* just work due to the
first line.

Is this the pg_hba.conf that's actually being used ? Perhaps
there's another one lying in another cluster's directory ?

What happens if you rename it so PG cannot find it anymore
- does PG complain about the missing file ?

> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Karsten Hilbert

Interesting. Are my mails being screened ?

> > confused why I can't access. Is there another file that I need to
> alter
> > besides the pg_hba.conf file?
>
> That depends on the content of your pg_hba.conf file.

Assuming the above really IS the file in use the answer is
No.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

On Tue, May 20, 2008 at 09:17:03AM -0700, Joshua D. Drake wrote:

> > The only non-commented lines are:
> >
> > [root(at)RH-Dev data]# cat pg_hba.conf | grep -v '^#'
> >
> > local all all trust
> > host all all 127.0.0.1/32 trust
> > host all all ::1/128 trust
> >
> > Dan
> >
>
> If that is your production pg_hba.conf you have either failed to reload
> the database

Make the database engine reload its config file, that is, I
assume.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

Great suggestion!

I renamed it to "pg_hba.conf_tmp", and I can still log into PG. There
is another linux box in the network and changed the title of the
pg_hba.conf file as well, and still allows me to log in to PG.

Is there a command that I can use to find out which file PG is looking
into for the settings in pg_hba.conf? Could it be that the config file
is not even titled "pg_hba.conf"?

Just so that you know, I restarted postgres after changing the file
names in both machines.

[root(at)RH-Dev djoo]# /etc/init.d/postgresql restart
Stopping postgresql service: [ OK ]
Starting postgresql service: [ OK ]

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Karsten Hilbert
Sent: Tuesday, May 20, 2008 9:27 AM
To: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] psql: FATAL: Ident authentication failed
foruser"postgres"

On Tue, May 20, 2008 at 08:50:16AM -0700, Dan Joo wrote:

> The only non-commented lines are:
>
> [root(at)RH-Dev data]# cat pg_hba.conf | grep -v '^#'
>
> local all all trust
> host all all 127.0.0.1/32 trust
> host all all ::1/128 trust

Well, neither of which allows IDENT based authentication.

This doesn't explain, however, why it's attempting IDENT in
the first place. It really *should* just work due to the
first line.

Is this the pg_hba.conf that's actually being used ? Perhaps
there's another one lying in another cluster's directory ?

What happens if you rename it so PG cannot find it anymore
- does PG complain about the missing file ?

> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Karsten
Hilbert

Interesting. Are my mails being screened ?

> > confused why I can't access. Is there another file that I need to
> alter
> > besides the pg_hba.conf file?
>
> That depends on the content of your pg_hba.conf file.

Assuming the above really IS the file in use the answer is
No.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

On Tue, May 20, 2008 at 09:47:55AM -0700, Dan Joo wrote:

> Is there a command that I can use to find out which file PG is looking
> into for the settings in pg_hba.conf? Could it be that the config file
> is not even titled "pg_hba.conf"?

http://www.postgresql.org/docs/8.3/static/runtime-config-file-locations.html

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

Thanks Karsten!

Yes, there was another config file in another directory!

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Karsten Hilbert
Sent: Tuesday, May 20, 2008 10:36 AM
To: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] psql: FATAL: Ident authentication
failedforuser"postgres"

On Tue, May 20, 2008 at 09:47:55AM -0700, Dan Joo wrote:

> Is there a command that I can use to find out which file PG is looking
> into for the settings in pg_hba.conf? Could it be that the config
file
> is not even titled "pg_hba.conf"?

http://www.postgresql.org/docs/8.3/static/runtime-config-file-locations.
html

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346