ArcSDE PostgreSQL mixed environment error connecting to schemas

Question

I have an enabled ArcSDE enterprise geodatabase with PostgreSQL 9.4 and ArcGIS 10.5.1. I am the database administrator and have only 2 other users who I work with use these SDE/DB connections. I personally use PostGIS but my coworkers use strictly ArcGIS tools. The database I set up has a sde/postgis mixed environment see here PostGIS and ArcSDE mixed environment

So I will probably get scolded for attempting to setup my DB design like I did, but I tried to do an unconventional DB/arcsde setup for easier viewing.

Because I will have hundreds of tables in this environment I didn't want a massive laundry list of every single table when I open a database connection. So instead of storing all the spatial tables in the sde schema and assigning roles and groups with different privileges, I attempted connect to specific PostgreSQL schemas instead of the standard sde schema. so every schema has name with that specific data in it, and a unique username and password.

so for example the base_layers schema contains only the base_layers data and when you sign in with the base_layers user name and password you only get the data in that schema not a hundreds of layers

so this all was working for me when it was just me connecting and using this database (which is hosted on my computer not a server).

QUESTION

now my other coworkers have started using these database connections and initially they worked fine for my other colleagues and then out of nowhere all my database connections stopped working (except when I connected with the sde user and to view all the tables). the error I would get is

what is even stranger is if I go to add query layer and sign in with one of the database schemas (that was not working when I tried to use it from add database connection) it worked and I am able to select any table I want to bring in...

no now nobody can connect specifically to those schemas, we can only use the sde account

UPDATE

from pauldzy response I ran this the query below and it returned a row in the sde_table_registry which points to a table whos name has changed. I then deleted this row and so far all the schema log in names that were not working have worked. Will update when I have tried all of them

Answer 1

It seems like such a broken model that ArcGIS would force you to put all tables in the sde schema.

One possibility is your settings are not right for the schemas and you just need to corrected them.

I would first ensure the sde user has rights access all schemas with.

GRANT USAGE ON SCHEMA base_layers TO sde;
GRANT ALL ON ALL TABLE IN SCHEMA base_layers to base_layers;
GRANT ALL ON ALL TABLES IN SCHEMA base_layers to sde;

ALTER DEFAULT PRIVILEGES IN SCHEMA base_layers
  GRANT ALL ON TABLES TO base_layers;

ALTER DEFAULT PRIVILEGES IN SCHEMA base_layers
  GRANT ALL ON TABLES TO sde;

If you need other users to access the tables, add them to a group and grant rights to the group. You could be lazy and USE public group which everyone that can log into the postgres server is a member of.

GRANT USAGE ON SCHEMA base_layers TO public;

Do the above for sde and base_layers and try. If that partially fixes it, repeat for each schema you have the issue.

If the above doesn't work, then perhaps Vince is right, your database violates ESRI quirky best practice and should be recreated to follow a standard that works with their application.

Answer 2

This posting goes to a lot of different places. I am not entirely sure if the "unconventional" description really fits as no one stores their tables in the sde schema, but I'll just hop over those issues to the question itself...

I think the problem is most likely that ziggy created a geodatabase user via ArcCatalog, then created one or more resources with that user/schema and then deleted the resources and/or schema outside ArcCatalog.

Essentially Ziggy needs to inspect his sde.sde_layers and sde.sde_table_registry for any orphaned references for resources which no longer exist in the database. Just delete these rows and the connection errors will go away. There is some kind of startup scan whereby ArcCatalog bumps each table in the sde metadata and fails if the table does not exist.

This has caught me before as I recognize that less than helpful error message. I usually work in Oracle where the geodatabase is not at all this picky about stuff vanishing, it's a quirk of the Esri pg implementation.

---

EDIT ONE

Note directly editing your SDE metadata is completely unsupported by Esri. Do so at your own risk and don't expect them to save your bacon if things go south. Secondly I recommend a full backup of your geodatabase before you go mucking around with the SDE metadata. My advice is merely the advice of a random person on the internet.

So to find the problem records, execute the following:

SELECT
a.*
FROM
sde.sde_layers a
LEFT JOIN
pg_tables b
ON
a.schema = b.schemaname
AND a.table_name = b.tablename
WHERE
b.schemaname IS NULL;

Then do this again against sde.sde_table_registry. Note I just ran this on my system and I have some hits as I must have deleted some tables outside ArcCatalog. I would clarify my response in that this seems to occur when the schema itself has been dropped outside ArcCatalog rather than the table. So examine your list of results for any records that reference in the schema column a schema that no longer exists.

Then carefully remove those records.

---

Cheers,

Paul