Thread: pgsql-jdbc and ident

pgsql-jdbc and ident

From
Matt Rose
Date:
I am having the oddest problem with postgresql-9.2-1002.jdbc3.jar doing
ident authentication against postgresql-9.2.3

I have in my pg_ident.conf the following entries

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
nusers root postgres
nusers root sqlrelay
nusers nable sqlrelay

and pg_hba.conf has the following entry.
host  all all 127.0.0.1/32 ident map=nusers

and when I test the following configuration with psql, everything works
perfectly:

[root@localhost]# psql mickey sqlrelay -h 127.0.0.1
psql (9.2.2)
Type "help" for help.

mickey=>

and I can even see the identd server getting the ident auth request from
postgresql and return the correct string

Mar  1 10:46:58 localhost oidentd[31730]: Connection from localhost
(127.0.0.1):56502
Mar  1 10:46:58 localhost oidentd[31730]: [localhost] Successful lookup:
37704 , 5432 : root (root)

However, when I try and connect with a simple jdbc tester that I made, I
get the following.

[root@localhost]# java -jar /home/admin/pgtest.jar
Running as user 'root'
postgresql JDBC Driver Registered!
SQLException: FATAL: Ident authentication failed for user "sqlrelay"
SQLState: 28000
VendorError: 0
Exception in thread "main" java.lang.NullPointerException
    at
net.folkwolf.PostgreSQLJDBCDriverTest.main(PostgreSQLJDBCDriverTest.java:35)

and even in the logs I see that, even tho the JDBC is authenticating as
root, the ident server is returning NO-USER

Mar  1 10:48:14 localhost oidentd[31745]: Connection from localhost
(127.0.0.1):42316
Mar  1 10:48:14 localhost postgres[31744]: [64-1] LOG:  00000: invalidly
formatted response from Ident server: "37705,5432:ERROR:NO-USER#015
Mar  1 10:48:14 localhost postgres[31744]: [64-2] #011"
Mar  1 10:48:14 localhost postgres[31744]: [64-3] LOCATION:  ident_inet,
auth.c:1743
Mar  1 10:48:14 localhost postgres[31744]: [65-1] FATAL:  28000: Ident
authentication failed for user "sqlrelay"
Mar  1 10:48:14 localhost postgres[31744]: [65-2] LOCATION:
auth_failed, auth.c:302

I've tried both kinds of connection calls, both the
conn =
DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/mickey",
"sqlrelay",null);
and
conn =
DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/mickey?user=sqlrelay");

with identical results.  Can anyone help me figure out what I'm missing here?

--
Matt Rose, Systems Engineer, N-able Technologies: mrose@n-able.com


Re: pgsql-jdbc and ident

From
dmp
Date:
Hello Matt,

Have you looked through the archive for ident authorizations with the pgJDBC?

If that turns up no results please post ident quthorization with the JDBC to
the server PostgreSQL forum. Else see if someone else replays that knows more.

I can duplicate your problem and have tracked the return from the initial
sendStartupPacket() in v3/ConnectionFactoryImpl to 'E' for doAuthentication().

This would imply to me that the server does not recognize the operating
system user as required in the pg_ident.conf for your user, root, to be
sqlrelay. I not sure this is even being communicated to the server from
pgJDBC for authorization with ident, in your test case.

Please also look through the manual to see if other parameters can be sent
to the server to indicate the operating system user in the connection string.

danap.

Matt Rose wrote:
> I am having the oddest problem with postgresql-9.2-1002.jdbc3.jar doing
> ident authentication against postgresql-9.2.3
>
> I have in my pg_ident.conf the following entries
>
> # MAPNAME       SYSTEM-USERNAME         PG-USERNAME
> nusers root postgres
> nusers root sqlrelay
> nusers nable sqlrelay
>
> and pg_hba.conf has the following entry.
> host  all all 127.0.0.1/32 ident map=nusers
>
> and when I test the following configuration with psql, everything works
> perfectly:
>
> [root@localhost]# psql mickey sqlrelay -h 127.0.0.1
> psql (9.2.2)
> Type "help" for help.
>
> mickey=>
>
> and I can even see the identd server getting the ident auth request from
> postgresql and return the correct string
>
> Mar  1 10:46:58 localhost oidentd[31730]: Connection from localhost
> (127.0.0.1):56502
> Mar  1 10:46:58 localhost oidentd[31730]: [localhost] Successful lookup:
> 37704 , 5432 : root (root)
>
> However, when I try and connect with a simple jdbc tester that I made, I
> get the following.
>
> [root@localhost]# java -jar /home/admin/pgtest.jar
> Running as user 'root'
> postgresql JDBC Driver Registered!
> SQLException: FATAL: Ident authentication failed for user "sqlrelay"
> SQLState: 28000
> VendorError: 0
> Exception in thread "main" java.lang.NullPointerException
>     at
> net.folkwolf.PostgreSQLJDBCDriverTest.main(PostgreSQLJDBCDriverTest.java:35)
>
> and even in the logs I see that, even tho the JDBC is authenticating as
> root, the ident server is returning NO-USER
>
> Mar  1 10:48:14 localhost oidentd[31745]: Connection from localhost
> (127.0.0.1):42316
> Mar  1 10:48:14 localhost postgres[31744]: [64-1] LOG:  00000: invalidly
> formatted response from Ident server: "37705,5432:ERROR:NO-USER#015
> Mar  1 10:48:14 localhost postgres[31744]: [64-2] #011"
> Mar  1 10:48:14 localhost postgres[31744]: [64-3] LOCATION:  ident_inet,
> auth.c:1743
> Mar  1 10:48:14 localhost postgres[31744]: [65-1] FATAL:  28000: Ident
> authentication failed for user "sqlrelay"
> Mar  1 10:48:14 localhost postgres[31744]: [65-2] LOCATION:
> auth_failed, auth.c:302
>
> I've tried both kinds of connection calls, both the
> conn =
> DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/mickey",
> "sqlrelay",null);
> and
> conn =
> DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/mickey?user=sqlrelay");
>
> with identical results.  Can anyone help me figure out what I'm missing here?
>


Re: pgsql-jdbc and ident

From
Ian Pilcher
Date:
On 03/01/2013 10:04 AM, Matt Rose wrote:
> I am having the oddest problem with postgresql-9.2-1002.jdbc3.jar doing
> ident authentication against postgresql-9.2.3

Your ident server might be having a problem recognizing IPv6
connections, including "IPv4-mapped IPv6 addresses" (see
http://en.wikipedia.org/wiki/IPv6#IPv4-mapped_IPv6_addresses).

Try running your program with -Djava.net.preferIPv4Stack=true to
determine if your problem is IPv6-related.

--
========================================================================
Ian Pilcher                                         arequipeno@gmail.com
Sometimes there's nothing left to do but crash and burn...or die trying.
========================================================================

Re: pgsql-jdbc and ident

From
Matt Rose
Date:
On Sun, Mar 03, 2013 at 10:49:49PM -0500, Ian Pilcher wrote:
> On 03/01/2013 10:04 AM, Matt Rose wrote:
> > I am having the oddest problem with postgresql-9.2-1002.jdbc3.jar doing
> > ident authentication against postgresql-9.2.3
>
> Your ident server might be having a problem recognizing IPv6
> connections, including "IPv4-mapped IPv6 addresses" (see
> http://en.wikipedia.org/wiki/IPv6#IPv4-mapped_IPv6_addresses).
>
> Try running your program with -Djava.net.preferIPv4Stack=true to
> determine if your problem is IPv6-related.

Thank you, that was the problem.

[root@localhost ~]# java -jar /home/admin/pgtest.jar
Running as user 'root'
postgresql JDBC Driver Registered!
SQLException: FATAL: Ident authentication failed for user "sqlrelay"
SQLState: 28000
VendorError: 0
Exception in thread "main" java.lang.NullPointerException
    at
net.folkwolf.PostgreSQLJDBCDriverTest.main(PostgreSQLJDBCDriverTest.java:35)
[root@localhost ~]# java -jar -Djava.net.preferIPv4Stack=true
/home/admin/pgtest.jar
Running as user 'root'
postgresql JDBC Driver Registered!

For some reason, even if you specify an ipv4 address in the connection
url, the jdbc driver only connects successfully over ipv6.

I have some ideas as to why this happens, but I can mull them over
later.  Thanks again.

NB:  Also, when I configure my ident server to listen to ipv6, and add
an ident map to pg_hba.conf to listen to ipv6 localhost, it also works.

Matt

>
> --
> ========================================================================
> Ian Pilcher                                         arequipeno@gmail.com
> Sometimes there's nothing left to do but crash and burn...or die trying.
> ========================================================================
>
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc

--
Matt Rose, Systems Engineer, N-able Technologies: mrose@n-able.com