topic Problem with connection to PostgreSQL database in Geodatabase Questions

Problem with connection to PostgreSQL database

GIS_MJ — Tue, 10 Sep 2019 17:14:59 GMT

I am sorry if this question has been answered before. I searched geonet, and haven't found an answer that has worked for me yet.

I am having problems connecting a user to a new SDE database.

To create the new database, I ran the create enterprise geodatabase tool in ArcCatalog for a PostgreSQL database (ArcMap 10.7, postgreSQL 10.6). The database was created successfully and I can access it using my login credentials (both as my username and as the sde) in ArcCatalog and pgAdmin 4. The database is hosted on a server and our technology dept has allowed access to the database via the pg_hba.conf file for the user that I am working with. The user can use telnet to check the firewalls are working. The user can also login to the database via pgAdmin and through a Python script. However, when they attempt to add a database connection using ArcCatalog or ArcMap they get the following error: "Unable to get database list. A database connection exception has occurred. Unable to connect to database server to retrieve database list; please verify your server name, user name, password info and try again. Bad login user."

To create the user, I ran the following script in pgAdmin:

CREATE USER user WITH NOLOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

REVOKE CONNECT ON DATABASE mydb FROM user;
REVOKE ALL ON SCHEMA public FROM user;
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM user;
REVOKE ALL ON ALL FUNCTIONS IN SCHEMA public FROM user;
REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM user;

GRANT CONNECT ON DATABASE mydb TO user;
GRANT USAGE ON SCHEMA public TO user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO user;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO user;

In the script, the variable "user" was replaced with the username of the person I am working with. I also replaced the variable "mydb" with my database name. I ran the script for both the sde schema and the public schema. I ran this script logged in as both the superuser and as the sde (which is another superuser as far as I can tell). What am I doing wrong or missing?

Re: Problem with connection to PostgreSQL database

ChristianWells — Tue, 10 Sep 2019 17:29:05 GMT

These new users have needs to access both the SDE and public schemas to view the system repository tables. Often when a user cannot connect through ArcGIS but can through pgAdmin, it is related to permissions on SDE or public.

Could you try one of the methods here to see if you get a different result?:

Add login roles to PostgreSQL—Help | ArcGIS Desktop

Re: Problem with connection to PostgreSQL database

GIS_MJ — Tue, 10 Sep 2019 18:05:45 GMT

Hi Christian,

Thanks for replying. Since i granted connect and usage on both the sde and the public schema, wouldn't that be enough permissions? Also, I have not configured the log files at all. I am not sure which configuration to choose when i run the tool (configure log files in arcgis). Do you have any experience with that?

Re: Problem with connection to PostgreSQL database

ChristianWells — Tue, 10 Sep 2019 18:19:52 GMT

Usage on SDE and public should be enough, as long as the public user has the requisite table, sequence, and functions specified for st_geometry and SDE table access. By testing one of the methods I sent we can then confirm if something is missing from the explicit object permissions.

I'd also be more than happy to get you in touch with Technical Support so that someone could give you a call to troubleshoot and get this figured out.

Log files are configured by default but we've had some changes between the last few releases. Could you share which version are you using? Log file tables in geodatabases in PostgreSQL—Geodatabases in PostgreSQL | ArcGIS Desktop