Unable to connect to PostgreSQL database

[PhilipOrlando]

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!

[AyanPalit · Accepted solution]

@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]

@AyanPalit thanks for the troubleshooting help. 

I was trying to use a PostgreSQL client application (psql) to create this user. I followed the previously linked documentation religiously, but there must be some additional privileges that are not documented.

I followed your advice and tried creating this user from ArcGIS Pro via the "Create Database User tool" instead. This required me to first establish a connection from a superuser account, and then pass this connection object to the "Input Database Connection" parameter of the "Create Database User" tool. All of this required a Standard or Advanced license, which is not ideal. Despite these drawbacks, I was able to successfully create a new database user and establish a non-superuser connection.

I'm hoping that the minimum privileges documentation will be updated to include all necessary configuration to achieve the same result from a PostgreSQL client application in the future. 

Thanks for the help!

[PhilipOrlando · Accepted solution]

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};