Bad Login in Postgres EGDB

[CarstenHogertz]

Hi,

I've got 4 databased in my Postgres instance:

• dev
• test
• prod
• postgres

dev/test/prod are geodatabase enabled with the geodb repositories in a sde schema in each database. postgres database is the standard db that comes with installation.

I've got three users:

• postgres (standard user comes with installation)
• sde (dba of dev/test/prod)
• editor (creates tables)

All these user can connect via APRO without any problem.

Now I created via APRO another user 'agsread' (using the connection to the dev db) which works fine. In pgAdmin4 I can use 'agsread' to connect to any of the four db.

In the pg_hba file I allow the 'agsread' user to connect to the dev db.

So when I now try to connect via APRO, the DBMS logs say that 

agsread@postgres FATAL:  no pg_hba.conf entry for host "aaa.bbb.ccc.dd", user "agsread", database "postgres", SSL off

That's true, because it has no entry for the postgres db (which is technically correct, because 'agsread' shall only connect to dev db).

So my question is: Why does this happen? Isn't possible to restrict user access that way? What would be a solution/workaround?

Thanks!

[Brian_Wilson · Accepted solution]

Maybe you are hitting this problem?

When you are setting up a new database connection in Pro, the dialog tries to to query the database server to populate the Database drop down list. If you type in the name of the database (eg "dev") then I think it will work for you. Alternatively you can give agsread read permission on the postgres database so that it can populate the list.

Either way once you have a ".sde" connection file created then it should work from then on.

This is the entry from my log file -- looks like yours --

postgis_1 | 2022-08-04 17:05:59.335 UTC [171] FATAL: no pg_hba.conf entry for host "10.10.4.68", user "gis_owner", database "postgres", SSL off

This is the dialog from Pro -- the error pops up when I click on the highlighted drop down (I had already typed in dev earlier)

[CarstenHogertz]

Typing the DB name directly into the dropdown works! Thanks!!