ZeosDBO 7.2.1 and PostgreSQLL pg_hba.conf error

[cipto_kh]

Hi,

I make a connection to PostgreSQL 9.6 with Delphi 7 and ZeosDBO 7.2.1-rc on my local computer. The database is on my local computer. If the HostName property of TZConnection set to "localhost" then it works perfectly.

If I set the HostName property to my computer name (my local computer name) "OFFICE-201" then it will error:
SQL Error: FATAL: no pg_hba.conf entry for host "fe60::53ba28f3:84fa%11", user "admin", database "myDB", SSL off.


I already set the file pg_hba.conf on my C:\Program Files (x86)\PostgreSQL\9.6\share folder. My pg_hba.conf setting is:
@authcomment@
# TYPE DATABASE USER ADDRESS METHOD
@remove-line-for-nolocal@# "local" is for Unix domain socket connections only
@remove-line-for-nolocal@local all all @authmethodlocal@
# IPv4 local connections:
#host all all 127.0.0.1/32 @authmethodhost@
host all all 0.0.0.0/0 password

# IPv6 local connections:
host all all ::1/128 @authmethodhost@

# Allow replication connections from localhost, by a user with the
# replication privilege.
@remove-line-for-nolocal@#local replication @default_username@ @authmethodlocal@
#host replication @default_username@ 127.0.0.1/32 @authmethodhost@
#host replication @default_username@ ::1/128 @authmethodhost@


Is there any wrong setting of my pg_hba.conf? I want to set it, so my program can access the database from any computer on LAN/WAN.

Thank you

[marsupilami]

Hello cipto_kh,

the postgresql message says ist all: fe60::53ba28f3:84fa is an IPv6 address. You didn't specify anything for IPv6 addresses in your pg_hba.conf. You probably should at least add a specification for your link local IPv6 addresses if you want others on your subnet to be able to use PostgreSQL via IPv6.

With best regards,

Jan

[cipto_kh]

So what entry for ADDRESS IPv6? In IPv4 I use 0.0.0.0/0 to allow any IP connect to my OFFICE-201 computer (which is PostgreSQL database exists)

So In PostgeSQL itu must be defined for both IPv4 and IPv6?
Btw I use Windows 7 Prof Edition.

Thank you.

[marsupilami]

Hello : )

I am no expert at IPv6. But I would assume something like this if you want all the internet to be able to access your server:

Code: Select all

host all all ::0/0 @authmethodhost@

This *should* be similar to your other line for IPv4:

Code: Select all

host all all 0.0.0.0/0 @authmethodhost@

After reading Wikipedia, the following line should allow you to use link local addresses:

Code: Select all

host all all fe80::/64 @authmethodhost@

I am not sure why you have an address starting with fe60. Maybe you have to consult a good IPv6 guide.

With best regards,

Jan

[cipto_kh]

Still error here. Same error.

Marsupilami, maybe you can copy your pg_hba.conf here so I can test it here.

And is there any property setting of TZConnection that differ when HostName=localhost changed to HostName=OFFICE-201 (OFFICE-201 is the same computer as localhost)?

Thank you

[marsupilami]

Hello cipto_kh,

Still error here. Same error.

Marsupilami, maybe you can copy your pg_hba.conf here so I can test it here.

This is the relevant part of my pg_hba.conf:

Code: Select all

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     password
# IPv4 local connections:
host    all             all             127.0.0.1/32            password
host    all             all             samehost                password
hostssl all             all             0.0.0.0/0               pam clientcert=1

# IPv6 local connections:
host    all             all             ::1/128                 password
host    all             all             samehost                password
hostssl all             all             ::0/0                   pam clientcert=1

# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5

But beware: this host is connected to the internet and because of this uses hostssl instead of host directives. Also the authentication method is set to pam because this way I can include a two factor authentication solution. You still might want to use "md5" instead of "pam clientcert=1".

And is there any property setting of TZConnection that differ when HostName=localhost changed to HostName=OFFICE-201 (OFFICE-201 is the same computer as localhost)?

This has nothing to do with Zeos. Zeos just forwards the host name to libpq. libpq in turn will ask windows what IP address to use for localhost or OFFICE-201. It seems that your Windows resolves localhost to 127.0.0.1 or ::1 and that it resolves OFFICE-201 to fe60::53ba28f3:84fa.

All in all this is a PostgreSQL configuration problem. You might get better help at the PostgreSQL mailing lists.

With best regards,

Jan