idea Support PostgreSQL connection without "public" schema in Data Management Ideas

Support PostgreSQL connection without "public" schema

The_Right_Honorable_MatthewSyp — Fri, 09 Sep 2022 18:45:41 GMT

Support PostgreSQL/PostGIS connections without a default "public" schema. As of 10.4.1, ArcGIS still is not able to connect to a PostgreSQL database if PostGIS is installed in a different schema. In the modern database landscape, this would appear not only as a limitation but as a defect. It is certainly not a valid reason for requiring refactoring or creating one's database to accommodate such a narrow implementation.

ArcGIS can and should easily be able to connect to PostgreSQL/PostGIS installations and data in any schema, even if no "public" schema exists at all. It is a default schema on the server but not required; it can be and often is renamed or deleted like any other schema. Some DB admins automatically remove it and the "public" user/role as a basic matter of security because it is a default. Starting with version 2.3.0 (released Sept 26, 2016), PostGIS installation is non-relocatable into the "postgis" schema.

I see two immediate, straight forward implementations (I'm sure there are others):

Rely on the search_path variable, as most probably do in their other PostGIS interactions, and do not specify a schema with the PostGIS calls, or
Ask the user to specify the schema into which PostGIS is installed.

In any case, all explicit references to or dependencies on the "public" schema should be removed.

REFERENCES

Example of problem:

Errors when postgis extension (PostgreSQL) is installed in a schema other than "public".

Error message:

Failed to connect to the specified server. Do you want to continue?
Underlying DBMS error[ERROR: schema "public" does not exist
LINE 1: SELECT public.postgis_lib_version()
^SQL state: 3F000]

PostGIS non-relocatable:
PostGIS — PostGIS 2.3.0 Released

#3496 (Make postgis non-relocateable and schema qualify at least core functions (extension installs)) – PostGIS

Re: Support PostgreSQL connection without "public" schema

The_Right_Honorable_MatthewSyp — Tue, 24 Apr 2018 17:03:45 GMT

Still no change in ArcGIS Pro 2.1.2. Same error message. A support case with Esri resulted in a declaration that the "public" schema is required: "ArcGIS requires the public schema to be in place for a user other than that data owner to connect."

The part about a user other than the data owner does not appear to be correct, because I was connecting as the owner and superuser. Simply adding a schema called public does not solve the problem; it only changes the connection error to:

A database connection exception has occurred.

Function or procedure does not exist.

The database log shows that it was trying to run the same query:

SELECT public.postgis_lib_version()

(There is also a query for "select count(*) from sde.sde_layers" with the same result which ArcGIS may be ignoring.)

So ArcGIS currently requires that PostGIS be installed in a schema called "public".

PostgreSQL documentation explicitly says:

There is nothing special about the public schema except that it exists by default. It can be dropped, too.

And later in the same document:

...there is no concept of a public schema in the SQL standard. For maximum conformance to the standard, you should not use the public schema.

Re: Support PostgreSQL connection without "public" schema - Status changed to: Closed

SSWoodward — Wed, 05 Oct 2022 22:12:36 GMT

Thanks for the idea!

Esri has supported installing PostGIS in schemas other than PUBLIC since 2.3/10.7. This limitation was addressed thanks to the contributions of users like yourselves!

Esri does store sde_spatial_references in the Public schema. This is to support select access to all users. ESRI does not support renaming or dropping the Public schema.

https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-postgresql/geodatabase-system...