AnsweredAssumed Answered

Problem with connection to PostgreSQL database

Question asked by Martha_Jensen on Sep 10, 2019
Latest reply on Sep 10, 2019 by Christian_Wells-esristaff

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?

Outcomes