direct connect to ArcSDE using postgresql doesn't work

[SebastianBrocks]

Hi,

I'm having a problem with the direct connection using the "sde:postgresql:servername" entry as the service in ArcCatalog.
As far as I can tell, the entered info is all correct, and connection via the sde service works with the same usernames and passwords, but I get the "Bad login user" error message when trying the direct connection. pg_hba.conf is also set up correctly, I can for example directly connect to the database across the network using pgAdmin.
Does anyone have an idea what might cause this?

[DerekLaw]

Hi Sebastian,

Q) What OS are you using for your ArcSDE geodatabase on PostgreSQL?

If it's Red Hat Linux (RHEL) 5 or SUSE 10, please review this KnowledgeBase Article:

KB #38606 - HowTo: Make a Direct Connection from ArcGIS Server or ArcGIS Engine Runtime on SUSE to A...

Hope this helps,

[SebastianBrocks]

Hi Sebastian,

Q) What OS are you using for your ArcSDE geodatabase on PostgreSQL?

If it's Red Hat Linux (RHEL) 5 or SUSE 10, please review this KnowledgeBase Article:
Hope this helps,

It's Windows Server 2008, so that doesn't help, I'm afraid.

[DerekLaw]

Hi Sebastian,

It's Windows Server 2008, so that doesn't help, I'm afraid.

Please review this KnowledgeBase Article:

KB #29585 - Error: Bad Login User

Hope this helps,

[SebastianBrocks]

Hi Sebastian,



Please review this KnowledgeBase Article:

KB #29585 - Error: Bad Login User

Hope this helps,

I have reviewed it, and it doesn't help.
Does direct connect only work with operation system authentification?

[tKasiaTuszynska · Accepted solution]

Sebastian,
The "bad login user" error is an hba issue.
If you think that the entries in your hba are correct than it might be a refresh issue, was the pg service restarted after the hba change? Was the client app restarted after the change?

- check what the pg error log says, often it gives very good indication of the problem
- possible problems:
      - your network has both ipv4 and ipv6 addresses and you need a entry for both
      - your authentication method is not appropriate  
      - some silly spelling mistake

Sincerely,
Kasia

[SebastianBrocks]

      - your network has both ipv4 and ipv6 addresses and you need a entry for both

Thank you, this was the problem.

[mattwilkie]

I fought with this for hours. In the end the only thing that worked for me was to comment out both ipv4 and v6 and just open the gate completely:

     host all         all         all         md5

I note there is a discrepancy in syntax between Postgres and Esri docs for ipv6 that may come in to play. Compare the suffixes, "%10/n" vs "/n":

[Esri] To allow any machines with an address beginning with fe60 to connect, you would add this line to the pg_hba.conf file:

     host all all fe60::0000:0000:0000:0000%10/8 md5

[Postgres] An IPv6 address range might look like ::1/128 for a single host (in this case the IPv6 loopback address) or fe80::7a31:c1ff:0000:0000/96 for a small network.

[tKasiaTuszynska]

Matt,

not sure what you mean by comment out the entries for ipv4 and ipv6 completely?Do the settings below work?

- first row for ipv4

- second for ipv6

this is opening ipv4 and ipv6 wide open....

host	all	all	0.0.0.0/0	md5
host	all	all	::/0	md5

Kasia