0

My postgresql connection works if I stick to the following default set up:

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     md5
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

Now I'm trying to allow another computer in the same household connecting to the current server, and am trying a few settings. One of the changes I made is to the IPv6 local connection line, is to use my Temporary IPv6 Address instead, because if I check what's my ip on google, that is the ip shown up there.

# IPv6 local connections:
host    all             all             1111:2222:a111:a11:b222:11a:abcd:efgs                md5

(Note the ip used here is just an example)

However, this would lead to the follow errors in postgresql log:

2021-11-21 12:26:40.508 PST [10356] LOG:  starting PostgreSQL 13.3, compiled by Visual C++ build 1914, 64-bit
2021-11-21 12:26:40.515 PST [10356] LOG:  listening on IPv6 address "::", port 5432
2021-11-21 12:26:40.517 PST [10356] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2021-11-21 12:26:40.529 PST [10356] LOG:  invalid IP mask "md5": Unknown host
2021-11-21 12:26:40.529 PST [10356] CONTEXT:  line 88 of configuration file "C:/current_dir/PostgresSQL/data/pg_hba.conf"
2021-11-21 12:26:40.531 PST [10356] FATAL:  could not load pg_hba.conf
2021-11-21 12:26:40.533 PST [10356] LOG:  database system is shut down

What are the possible reasons causing this error please? Thank you very much.

Helene
  • 953
  • 3
  • 12
  • 22
  • 2
    Per error `1111:2222:a111:a11:b222:11a:abcd:efgs` is invalid. See [IPv6 validator](http://sqa.fyicenter.com/1000334_IPv6_Address_Validator.html) 'Invalid character: g' – Adrian Klaver Nov 21 '21 at 20:51
  • 2
    You're missing `/128` at the end of that IP address to indicate it's only that particular IP, but it's not the main problem. You're trying to connect over an internal connection using an external address of your entire network. Find out what is your local IP address of the computer you want to whitelist and use that instead. – Zegarek Nov 21 '21 at 21:07
  • @AdrianKlaver thank you for the comment. That was intentional... it was just an example sorry – Helene Nov 21 '21 at 21:28
  • @Zegarek thank you for the comment. Could you please elaborate? You are saying on the server computer, I should edit that line in pg_hba.conf and use my other computer's ip (which I want to whitelist)? – Helene Nov 21 '21 at 21:31
  • Correct, but remember about the netmask `/128`. External IP is like an address of your house, internal IP is an address of your room/PC inside the house. Sites like "what is my IP" show your external IP because they see where you connect from, but can't tell your local IP. If you are trying to connect from one computer inside the house, your local network, to a database on another one in the same network, you need to whitelist the local IP of your client PC. – Zegarek Nov 22 '21 at 06:59

1 Answers1

0

You cannot specify a client IP address in pg_hba.conf, you have to specify a CIDR. Per the documentation:

An IP address range is specified using standard numeric notation for the range's starting address, then a slash (/) and a CIDR mask length. The mask length indicates the number of high-order bits of the client IP address that must match. Bits to the right of this should be zero in the given IP address. There must not be any white space between the IP address, the /, and the CIDR mask length.

So the entry would have to look like:

host  all  all  fe80::8b0a:b1bf:4ce5:9a4a/128  md5
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263