Postgres Pro
Downloads
Home   >   mailing lists

Thread: ident auth does not works as usual

ident auth does not works as usual

From
pan snowave
Date:
Well  , describe as follows:

pg_pba.conf
host    all             root             127.0.0.1/32           ident    map=test
host    all             all             127.0.0.1/32            trust


pg_indent.conf

test   root    cce


and  login pg  via  root  user , it doesnt work

error info is 

2023-08-26 16:56:29.484 CST [18349] LOG:  no match in usermap "test" for user "root" authenticated as "root"
2023-08-26 16:56:29.484 CST [18349] FATAL:  Ident authentication failed for user "root"
2023-08-26 16:56:29.484 CST [18349] DETAIL:  Connection matched pg_hba.conf line 91: "host    all             root             127.0.0.1/32           ident    map=test"


i checked config file  many times, and could not find  any reason  of this error.

pls help me.  

thanks.



Re: ident auth does not works as usual

From
Alban Hertroys
Date:
> On 26 Aug 2023, at 11:31, pan snowave <windxp@LIVE.CN> wrote:

(…)

> pg_indent.conf
>
> test   root    cce

If that is indeed the name of the file, that would explain your problem. No doubt that it should be named pg_ident.conf
instead,without the ’n'. 

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

Re: ident auth does not works as usual

From
"David G. Johnston"
Date:
On Saturday, August 26, 2023, pan snowave <windxp@live.cn> wrote:
Well  , describe as follows:

pg_pba.conf
host    all             root             127.0.0.1/32           ident    map=test
host    all             all             127.0.0.1/32            trust


pg_indent.conf

test   root    cce


and  login pg  via  root  user , it doesnt work

You are saying root os user can login as pg role cce but that line of the pg_hba.conf doesn’t permit cce to login.  In short, the mapping is useless.

David J.

回复: ident auth does not works as usual

From
pan snowave
Date:
Hi, 

Sorry,   the name of ident file  that i used  is wrong,  it is pg_ident.conf (the same as you says）

And, I found  pg_ident.conf works, because  i can found ident entry in pg views ,

 line_number | map_name | sys_name | pg_username | error
-------------+----------+----------+-------------+-------
          43 | test     | root     | cce         |

But when i connect to psserver by using os user root  
/usr/local/pgsql/bin/psql   -p5432 -d db1     -h127.0.0.1  
psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL:  Ident authentication failed for user "root"

I have check  config files many times,  everything is right, and hba config file content is:

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             root             127.0.0.1/32           ident    map=test
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust


Thanks.

发件人: Alban Hertroys <haramrae@gmail.com>
发送时间: 2023年8月26日 10:38
收件人: pan snowave <windxp@LIVE.CN>
抄送: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
主题: Re: ident auth does not works as usual
 

> On 26 Aug 2023, at 11:31, pan snowave <windxp@LIVE.CN> wrote:

(…)

> pg_indent.conf
>
> test   root    cce

If that is indeed the name of the file, that would explain your problem. No doubt that it should be named pg_ident.conf instead, without the ’n'.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

回复: ident auth does not works as usual

From
pan snowave
Date:
Hi

"Show your psql command that is failing."

[root@~ pg]# /usr/local/pgsql/bin/psql   -p5432 -d db1     -h127.0.0.1
psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL:  Ident authentication failed for user "root"

If the error is  Ident authentication failed for user "cce", i think the ident config file taked effect.

Reference information:
PG version 15.3


Thanks.
发件人: David G. Johnston <david.g.johnston@gmail.com>
发送时间: 2023年8月26日 14:43
收件人: pan snowave <windxp@live.cn>
抄送: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
主题: Re: ident auth does not works as usual
 
On Saturday, August 26, 2023, pan snowave <windxp@live.cn> wrote:
Well  , describe as follows:

pg_pba.conf
host    all             root             127.0.0.1/32           ident    map=test
host    all             all             127.0.0.1/32            trust


pg_indent.conf

test   root    cce


and  login pg  via  root  user , it doesnt work

You are saying root os user can login as pg role cce but that line of the pg_hba.conf doesn’t permit cce to login.  In short, the mapping is useless.

David J.

Re: ident auth does not works as usual

From
"David G. Johnston"
Date:
On Sunday, August 27, 2023, pan snowave <windxp@live.cn> wrote:
Hi

"Show your psql command that is failing."

[root@~ pg]# /usr/local/pgsql/bin/psql   -p5432 -d db1     -h127.0.0.1
psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL:  Ident authentication failed for user "root"

If the error is  Ident authentication failed for user "cce", i think the ident config file taked effect.

What about your psql command is supposed to inform the system that you want to login at the db role cce?  The ident file doesn’t magically alias one name to another, it’s purpose is to allow some OS user to specify some other name to use to authenticate instead of the OS user they are executing the command with.

Re: ident auth does not works as usual

From
"David G. Johnston"
Date:
On Mon, Aug 28, 2023 at 7:34 AM pan snowave <windxp@live.cn> wrote:
But when i connect to psserver by using os user root  
/usr/local/pgsql/bin/psql   -p5432 -d db1     -h127.0.0.1  

If you want to login as the cce role add " -U cce " to your command line (or one of the various other ways you can specify a db role name via the cli or libpq)

psql -U cce -h 127.0.0.1 -d db1

Keep in mind you could very well have two lines in your pg_ident.conf file:

test root cce
test root ddf

David J.

回复: ident auth does not works as usual

From
pan snowave
Date:
HI

I've got the  right meanng of pg_hba.conf and pg_ident.conf.

My previous understanding was incorrect. I thought pgserver would automatically handle the conversion between OS users and PostgreSQL roles. 

Thanks all.


发件人: David G. Johnston <david.g.johnston@gmail.com>
发送时间: 2023年8月28日 14:44
收件人: pan snowave <windxp@live.cn>
抄送: Alban Hertroys <haramrae@gmail.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
主题: Re: ident auth does not works as usual
 
On Mon, Aug 28, 2023 at 7:34 AM pan snowave <windxp@live.cn> wrote:
But when i connect to psserver by using os user root  
/usr/local/pgsql/bin/psql   -p5432 -d db1     -h127.0.0.1  

If you want to login as the cce role add " -U cce " to your command line (or one of the various other ways you can specify a db role name via the cli or libpq)

psql -U cce -h 127.0.0.1 -d db1

Keep in mind you could very well have two lines in your pg_ident.conf file:

test root cce
test root ddf

David J.