Unable to connect to PostgreSQL database

Question

I have a PostgreSQL database with the PostGIS extension and ArcSDE enabled. I am able to connect to this database from ArcGIS Pro when using a superuser account, however, I'm unable to establish a connection from a non-superuser account. When I try to connect from a non-superuser account, the connection hangs indefinitely and no error is returned.

I have followed this documentation and have ensured that the user in question meets the minimum privilege requirements.

I'm currently poring over all of the tables within the information_schema to see if there are any missing privileges that need to be granted to the non-superuser account.

I'm hoping an Esri rep can point me in the right direction. Thanks!

Ayan Palit · Accepted Answer

@PhilipOrlando Suggest the following:

* use the Create Database User tool and create a user with DATABASE_USER role
* test using the newly created user

You may be using a PostgreSQL client application to create the user and missing some of the required privileges.

Reference: Add login roles to PostgreSQL

PhilipOrlando · Accepted Answer

The solution provided by @AyanPalit is still valid, but here is another approach to creating database users from a PostgreSQL client application.

I had to consolidate the requirements from the Privileges for geodatabases in PostgreSQL and Add login roles to PostgreSQL docs in order to figure this out. I also had to compare privileges between a user created from ArcGIS Pro against the PostgreSQL client. It was during this step that I noticed that I needed to grant temporary on my database to the new user. I also needed to create a new schema with the same name as the target user. This schema must also be owned by this user.

The full list of PostreSQL commands are below:

-- Create new user
CREATE USER {user} WITH PASSWORD 'secret_password';

-- Create matching schema with ownership, create, usage
CREATE SCHEMA AUTHORIZATION {user};

-- Allow new user usage on SDE
GRANT USAGE ON SCHEMA sde TO {user};

-- Additional PostGIS privileges
GRANT TEMPORARY ON DATABASE {database} TO {user}; 
GRANT USAGE ON SCHEMA public TO {user};
GRANT SELECT ON spatial_ref_sys TO {user};
GRANT SELECT ON geography_columns TO {user};
GRANT SELECT ON geometry_columns TO {user};