# ArcSDE PostgreSQL mixed environment error connecting to schemas

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

• So your colleagues can still access those tables, but you can no longer access them? Is it only when your colleagues are access the tables? Is that error in ArcGIS or something else? – Midavalo Sep 15 '17 at 18:49
• no now nobody can connect specifically to those schemas (but we all can use the sde role...), il update that in my Q – ziggy Sep 15 '17 at 18:50
• Yes, this is so wrong, you've created a new antipattern, and destroyed the integrity of your geodatabase in the process. It's time to start over from scratch in a new database. You can choose to follow best practice within the requirements of enterprise geodatabase operation, or you can choose your "unconventional" paradigm, without a geodatabase (and in violation of all rules about database security). If you choose EGDB, be certain that you NEVER load data as the sde login, and NEVER change the schema of any table registered with the geodatabase. – Vince Sep 15 '17 at 23:40
• yes Vince I intentionally created this "antipattern" because to me (even though I am a newby in the DBMS world)it does not make sense to store all 200+ tables of mine in one massive database schema. They should be broken up by classification. so I am trying to experiment with this setup that would be more advantageous for me and my colleagues searching for tables and using them. – ziggy Sep 16 '17 at 15:38
• database security for this specific setup is not a main concern of mine, I am aiming for a neat and ordered working database where I can do my SQL in the backend and have my coworker use ESRI tools – ziggy Sep 16 '17 at 15:40

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

• "no one stores their tables in the sde schema" the standard ArcSDE practice is to store all the tables in the sde schema (It seems backwards to me) – ziggy Sep 25 '17 at 12:28
• "orphaned references for resources which no longer exist in the database" what do you mean by this? user names and schemas I deleted along the way? I actually dont recall deleting any. I have definitely deleted and renamed many tables in the DB though – ziggy Sep 25 '17 at 12:30
• So I added the actual SQL to the answer. Again I am skirting your topic of db design as I believe the problem you are seeking immediate resolution to is unrelated to these db design issues. However I would just say that you ignore Vince Angelo at your peril. Vince is the last of the SDE old-timers at Esri and with any response from him you are essentially getting level 5 support for free. He has seen it all and his advice is always worth strong consideration. And note I've never stored data in the sde schema nor ever plan to. :) – pauldzy Sep 25 '17 at 16:19
• Well if you checked against both sde.sde_layers and sde.sde_table_registry then I am just wrong about what your problem is. The next step is to do the same debugging that I did. When the database is quiet, have your user with the problem attempt to connect with ArcCatalog and thus trigger the error. Then inspect the postgresql log file for details on what happened. The error log should give you some indication of what table ArcCatalog cannot find. If it's not an issue with utterly missing resources then its probably permissions issues such as LR1234567 was driving towards. – pauldzy Sep 25 '17 at 21:55
• Glad it worked. I am also glad there are not a new scenario I didn't know of that triggers this problem. SDE in all its manifestations is a passive metadata system. Its updated when Esri clients make a change. There is no active monitoring of the database to account for changes made outside the Esri stack. So when you do any changes via pgadmin to SDE registered resources you risk breaking the system. That being said I do make changes outside Esri all the time and I roll with the consequences. This particular one is more onerous than usual. – pauldzy Sep 27 '17 at 11:25

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.

• I will get back to this on Monday at work but Im 99% sure SDE does have access to all schemas(I think its a superuser). yeah if this does not work I'll sadly give up on my experiment, thanks for the input I will let you know how this goes. – ziggy Sep 16 '17 at 15:42
• this command does not work- ALTER DEFAULT PRIVILEGES IN SCHEMA base_layers GRANT ALL ON ALL TABLES TO base_layers; – ziggy Sep 18 '17 at 17:05
• ERROR: syntax error at or near "ALL" LINE 2: GRANT ALL ON ALL TABLES TO base_layers; ^ ********** Error ********** ERROR: syntax error at or near "ALL" SQL state: 42601 Character: 64 – ziggy Sep 18 '17 at 17:05
• Sorry typo. Get rid of the word ALL before TABLES. I've updated answer to reflect – LR1234567 Sep 18 '17 at 20:38
• nope sadly did not work... I find it so strange that I can connect to these schemas with the schema log in from the add query layer method. Maybe one day Arc will change there SDE format to make it more intuitive and usable – ziggy Sep 19 '17 at 15:05