Horton
Menu

Thread

  1. ident auth does not works as usual

    pan snowave <windxp@live.cn> — 2023-08-26T09:31:42Z

    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.

  2. Re: ident auth does not works as usual

    Alban Hertroys <haramrae@gmail.com> — 2023-08-26T10:38:30Z

    > 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.

  3. Re: ident auth does not works as usual

    David G. Johnston <david.g.johnston@gmail.com> — 2023-08-26T14:43:39Z

    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.

  4. 回复: ident auth does not works as usual

    pan snowave <windxp@live.cn> — 2023-08-27T01:54:25Z

    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.

  5. 回复: ident auth does not works as usual

    pan snowave <windxp@live.cn> — 2023-08-28T03:30:17Z

    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<mailto:windxp@live.cn>> wrote:
Well  , describe as follows:

pg_pba.conf
host    all             root             127.0.0.1/32<http://127.0.0.1/32>           ident    map=test
host    all             all             127.0.0.1/32<http://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.

  6. Re: ident auth does not works as usual

    David G. Johnston <david.g.johnston@gmail.com> — 2023-08-28T03:47:01Z

    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.

  7. Re: ident auth does not works as usual

    David G. Johnston <david.g.johnston@gmail.com> — 2023-08-28T14:44:09Z

    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.

  8. 回复: ident auth does not works as usual

    pan snowave <windxp@live.cn> — 2023-09-19T03:46:07Z

    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<mailto: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.