Support PostgreSQL connection without "public" schema

Idea created by msyphus on Oct 11, 2016
    New
    Score50
    • Guus.Klaas_stedin
    • andyfarmerboy
    • rchueh
    • msyphus
    • michaelhaertel

    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):

    1. 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
    2. 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