PostgreSQL 9.5 Using ident in pg_hba

Question

I am working on a local network based on Windows, and I would like to use the account system of the network to allow users to connect to my DB.

As I understand the proper way is to set the pg_hba method on "ident" as such :

host    all    all    10.0.0.0/8      ident

But when I do so I get a message saying

"Error connecting to the server: FATAL: ident authentication failed for user <user>"

As far as I can think of it, my guess is that there could be two problems : - I do not properly set the pg_hba. - The role I created does not match the ident found by the server.

In the first case, could you tell if the syntax is correct ? I tried to add "sameuser" after the ident, but it corrupt my pg_hba, and also map=sameuser, but I get the same error as mentionned.

In the second case, I am using a batch run on every session to get the users names like

find /c "%username%"

or the Python3.6 function os.getlogin(), which produce the same result.

How does PostgreSQL get its "ident" ? And more importantly, how can I reproduce this process to know the idents of the sessions that PostgreSQL will recognize ?

Thanks.

Answer 1

after reading ident.conf manual and using the example from hba_file manual, I set this up:

postgres=# create user ident_user_db;
CREATE ROLE
vao@vao-X102BA:~$ sudo tail -n 1 /etc/postgresql/9.6/main/pg_hba.conf
host    all             all             192.168.8.0/24          ident   map=vao_ident
vao@vao-X102BA:~$ sudo tail -n 1 /etc/postgresql/9.6/main/pg_ident.conf
vao_ident         vao           ident_user_db

Now I'm connecting from same machine to its external IP:

vao@vao-X102BA:~$ psql -h 192.168.8.107 -U ident_user_db

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

Seems familiar. Though I followed manual. Last thing to check:

vao@vao-X102BA:~$ telnet localhost 113
Trying 127.0.0.1...
telnet: Unable to connect to remote host: Connection refused

So nothing provides my identity. As advised here

vao@vao-X102BA:~$ sudo apt-get install oidentd
...
vao@vao-X102BA:~$ telnet localhost 113
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
^]
telnet> quit
Connection closed.

so try now:

vao@vao-X102BA:~$ psql -h 192.168.8.107 -U ident_user_db -d vao
psql (9.6.3)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

Hope it helps