ArcGIS client user connection hanging when connecting to PostgreSQL geodatabase

Question

ArcGIS Catalog and Pro are both hanging when I try to connect as a user that is a member of a role. I think it is related to the SDE schema permissions but I'm not sure.

I have an enterprise geodatabase on PostgreSQL 9.6. using ArcGIS 10.6.1.

I have an SDE schema for the geodatabase tables and a data schema, geodata, for feature classes etc. I also have users of the same names. These users and schemas work fine. I can connect to geodata and create and view/edit FC no problem.

I then created a new role called jpa and a new user called jpa_viewer. jpa_viewer is a member of jpa. I want to use the role to control users. But when I try to connect to the geodatabase using jpa_viewer ArcCatalog or Pro both hang and I have to manually kill them.

Permissions I have set

grant usage on schema geodata to jpa  ;
GRANT select ON ALL TABLES IN SCHEMA geodata TO jpa ;
grant usage on schema sde to jpa;
GRANT select ON ALL TABLES IN SCHEMA sde TO jpa ;

I can connect and query the geodata and sde schema and tables as jpa_viewer no problem using pgAdmin. The problem only occurs using an ArcGIS client.

When I am creating a new connection in ArcCatlog using jpa_viewer I can initially see the list of databases in the instance in the dropdown. It only hangs when I pick one and save.

Answer 1

Turns out there were orphan records in the sde.table_registry table.

When I checked the logs it showed that the jpa_viewer account was getting a lot of errors trying to select from tables that didn't exist in the geodata schema. But the phantom tables existed as records in the sde.table_registry. Once I cleaned out these phantom tables jpa_viewer account connected successfully.