0

The story so far:

  • I have installed Postgres on my Linux based desktop (HOST).
  • I have created a test database, with test data, on HOST.
  • I have added to the end of the pg_hba.conf file on HOST: host samerole all 0.0.0.0/0 md5
  • I have added to the end of the postgresql.conf file on HOST: listen_addresses = '*'
  • I have installed PGAdmin4 on the Linux virtual machine of my Chromebook (CLIENT).
  • The IP address of HOST is 192.168.1.159, and the port is 5432.
  • If I run telnet 192.168.1.159 5432 on CLIENT, it seems to connect.
  • However, if try to connect to the same IP address and port via the PGAdmin GUI, it responds:

Unable to connect to server: FATAL: no pg_hba.conf entry for host "192.168.1.208", user "postgis_user", database "postgis_prototype", SSL on FATAL: no pg_hba.conf entry for host "192.168.1.208", user "postgis_user", database "postgis_prototype", SSL off

  • If I try to connect via the command line, I get a very similar response.

Does anyone know why this is happening, and what I can do to fix it?

Tom Hosker
  • 103
  • 1
  • 4

2 Answers2

1

Per documentation, the meaning of samerole in the database column is:

The value samerole specifies that the requested user must be a member of the role with the same name as the requested database.

The error message indicates that the rejected connection is attempted with user "postgis_user", database "postgis_prototype". Since the user name and database name differ, the rule with samerole cannot match (well, unless there is a postgis_prototype role with postgis_user as a member, but if you had set that up, surely you would have mentioned it in the question).

The simplest solution is probably to list the authorized databases in the 2nd column, the authorized users in the 3rd column, and forget about using samerole.

You may use comma-separated list in these columns, or split them into several rules.

The rules in pg_hba.conf are evaluated from top to bottom, stopping at the first matching rule.

The solution of accepting everything with host all all 0.0.0.0/0 md5 is indeed not recommended at all if your server can be reached from the Internet. There are evil bots out there that try to connect as the postgres superuser to every reachable Postgres installation.

Daniel Vérité
  • 3,045
  • 16
  • 19
  • Does the `md5` at the end not offer password protection, even in the `all all` case? As things are, when I connect to my database remotely, I still have to supply the password corresponding to the `postgis_user` username. Are you saying that a malicious bot could bypass that password protection if the `all all` setting was used? – Tom Hosker Jul 28 '22 at 18:45
  • 1
    @TomHosker: No, a bot cannot bypass it, but it might use brute force. For example: [pgminer](https://www.securityweek.com/pgminer-crypto-mining-botnet-abuses-postgresql-distribution). – Daniel Vérité Jul 28 '22 at 19:55
  • Forgive me for playing devil's advocate, but could it not also brute force the correct username and database name too? – Tom Hosker Jul 28 '22 at 21:22
  • 1
    @TomHosker: technically yes, but bots want superuser accounts. Anyway the recommendation **not to** use `host all all 0.0.0.0/0 md5` often means in practice to filter on the origin of the connection. For instance, it's very common to use `host all all 192.168.1.0/24 md5` if the local network has addresses in the 192.168.1.* range. – Daniel Vérité Jul 29 '22 at 09:11
0

A potential solution: if I change the line added to pg_hba.conf to:

host all all 0.0.0.0/0 md5

Then everything works as expected. But this amendment may cause unacceptable security issues that I haven't anticipated.

Tom Hosker
  • 103
  • 1
  • 4