How do you use psql client to connect to a postgresql ipv6 host?

# 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.

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.

StackExchange Database Administrators Q#148086, answer score: 3