topic Unable to connect to PostgreSQL database in ArcGIS Pro Questions

Unable to connect to PostgreSQL database

PhilipOrlando — Tue, 22 Nov 2022 22:07:33 GMT

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!

Re: Unable to connect to PostgreSQL database

AyanPalit — Wed, 23 Nov 2022 15:22:49 GMT

@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

Re: Unable to connect to PostgreSQL database

PhilipOrlando — Wed, 23 Nov 2022 17:15:38 GMT

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

Re: Unable to connect to PostgreSQL database

PhilipOrlando — Mon, 28 Nov 2022 21:39:40 GMT

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