PostGIS - access group-owned tables

BrianFreed1 — Tue, 09 Jul 2024 23:33:44 GMT

ArcGIS cannot read tables that are owned by a PostgreSQL role without login permissions.

When I change the owner of my tables from our admin group to any login name instead, the tables work fine in ArcMap/ArcCatalog.

My theory:

In the PostgreSQL 8.x era, there were "users" and "groups".

Now there are just "roles." "User" is just an alias for a role granted login permission, and "group" is an alias for a role without that permission.

I'm not sure why ArcGIS needs to access the list of users in order to display any table [1], but whenever trying to use a table owned by a group-type role, in ArcGIS 10.3, the table disappears from the Catalog list, while in 10.4 it appears, but double-clicking on it throws a "DBMS table not found" error.

I think if ESRI looks in their code for the "pg_users" system table and replaces it with the newer "pg_roles" table, the problem might be fixed.

Note: this is a very difficult issue to track down. If it's too expensive to fix, then the problem should at least be added to the ArcGIS documentation somewhere.

Lets say we have an admin group - "our_superusers", of which a login "bob_smith" is a member. We have a read-only group for our client, "client_readers", with a member "client_jim_bob".

We've shared the deliverables schema and all the tables in it with "client_readers", because we want all the client logins to see all the tables.

When they can't see it, it's super-not-intuitive that setting the owner of all of those objects to be "bob_smith" is the thing that will finally allow "client_jim_bob" to see it!

(owner="client_jim_bob" would also work, but then it can't be read-only access. The trick is to set ownership to ANY role with a login permission that you can trust with full access. (or I suppose you could grant log-in permission to the "our_superusers" role, but that just seems wrong))

[1] An alternate title for this idea might be "You're overthinking it! Don't query the list of database users and try to infer what you should see, just ask the database for a list of things, like a normal database client. Postgres will tell you what you can see, by showing it to you!"

idea PostGIS - access group-owned tables in ArcGIS Pro Ideas

PostGIS - access group-owned tables