1
# postgresql.conf
listen_addresses='::'

and

# pg_hba.conf
hostssl  webdb  webserver   ::0/0    cert

The postgresql server is running on docker with pingable ipv6 address of "GlobalIPv6Address": "fe80::242:ac12:2" - so no firewalls obstructing.

I am using the following command to connect

psql --command="select * from test;" -d webdb -h fe80::242:ac12:2 -p 5432 -U postgres
psql: could not connect to server: Invalid argument
    Is the server running on host "fe80::242:ac12:2" and accepting
    TCP/IP connections on port 5432?

Why is the host not recognized? Is it not possible to use ipv6 with psql? Also, I did not find an ssl parameter option in psql.

  • Is the Postgres service started? – a_horse_with_no_name Aug 27 '16 at 11:29
  • Yes. It's started. Was able to run selects by logging into the running docker container. – Srikanth Aug 27 '16 at 11:31
  • 1
    fe80::/10 are link-local addresses, can't be used without specifiying their interface. This is not related to psql, ping6 on that address would presumably produce the same error ("Invalid argument") – Daniel Vérité Aug 27 '16 at 12:33
  • @DanielVérité You are right. I did specify the interface while using ping6. How do I specify the interface for psql? – Srikanth Aug 27 '16 at 12:37
  • 1
    On Unix, %interface as a suffix to the ipv6 address. Windows uses numbers instead. – Daniel Vérité Aug 27 '16 at 12:51
2

As Daniel Vérité mentioned in the comment, on Unix, "%interface" should be given as a suffix to the ipv6 address. You can get the interfaces by running ifconfig command. So, the right psql command was

psql --command="select * from test;" -d webdb -h fe80::242:ac12:2%br-67d2c47f5b8e -p 5432 -U webserver

I had other problems. But now, the host was reachable. Next, I noticed that the suffix "clientcert = 1" had to be added to pg_hba.conf (don't know why)

# pg_hba.conf
hostssl  webdb  webserver   ::0/0    cert clientcert=1

listen_addresses value in postgresql.conf was fine (restricting requests & responses to only ipv6 addresses) - so is the value '::0/0' in pg_hba.conf

I then got ssl validation errors from the server. I had to set path variables on my client side for psql.

export PGSSLCERT=/my-folder/my-certificate.pem
export PGSSLKEY=/my-folder/my-private-key.pem

If your private key is password protected, psql prompts you for the password. Once the connection went through, (=> ssl works fine) , I noticed that the role "webserver" didn't have login permission. I had to alter-role to allow login and had to grant select and update access to the test table.

Now it works. Thanks to Daniel for the ipv6 suffix solution.

0

To make postgres avaliable in your network, you need to configure the listen_addresses to *.

Then you adjust the pg_hba.conf[2] to allow connection from your host. E.g:

hostssl  webdb  webserver   fe80::242:ac12:2/128    cert

After ajust the files, reload the postgres server.

Please, see the doc[1] about more authentication errors.

Hope it helps.

References:

  1. https://www.postgresql.org/docs/current/static/client-authentication-problems.html
  2. https://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html
  3. http://www.ibm.com/support/knowledgecenter/STCMML8/com.ibm.storage.ts3500.doc/opg_3584_IPv4_IPv6_prefix_subnet_mask.html
  • I just had to use <ipv6-address>%<interface> to make it connect to the database. My pg-hba.conf needed the "clientcert = 1" suffix. The rest was fine - i.e. I wanted to allow all ipv6 addresses - hence "::0/0" in pg_hba.conf – Srikanth Aug 27 '16 at 14:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.