Non-Superuser SDE database user on PostgreSQL

507
4
03-29-2022 09:55 AM
Status: Open
BillMitchell
New Contributor III

When creating an enterprise Postgres database, the SDE user currently requires superuser privileges.  As the Postgres documentation indicates, it is a dangerous level of privileges.  Moreover, when using modern cloud-hosted or managed services (e.g. Azure Flexible Server for Postgres), database administrators may not be able to grant (or may not have themselves) such elevated permissions.

It would be great to have tools that allow for the SDE setup to be created without superuser privileges.  Not only does it better secure the systems by adhering to principle of least privilege, but it also allows the use of newer database versions and features in cloud-hosted environments.

4 Comments
TonyContreras_Frisco_TX

The way we handle this in my organization is to temporarily grant the sde user SU privileges to create the database and then enable the geodatabase capability. After that, the sde user can be demoted until you are ready to upgrade the geodatabase version. We also create a data owner user who has db owner privileges. This is the user that is used to upload/create feature classes, tables, feature datasets, etc. The data owner user has all the access needed to administer the data/schema, but not make potentially disastrous changes to the DBMS. Help page with more info below.

 

The PostgreSQL administrator creates the database; the geodatabase administrator creates the geodata... 

BillMitchell

I'm glad to hear that for routine use the superuser privileges aren't necessary.  However, the issue I have is that I can't grant superuser privileges.  We are looking to use an Azure Postgres Flexible Server, which is the Microsoft managed Postgres option (Single Server for Postgres is being phased out and is well behind current).  With that service, administrators do not have---and cannot grant---superuser privileges.

The effect is that it is impossible to use ArcGIS with the Azure Postgres Flexible Server unless Esri fixes their tooling to never require superuser access.

Using managed Postgres is very convenient from a scalability and replication standpoint.  However, not being able to use ArcGIS with it is very unfortunate and we will likely have to host our own database server and handle all the replication ourselves.  If, as I suggested above, the ArcGIS tooling didn't require superuser access, we would be able to take advantage of these new technologies.

TonyContreras_Frisco_TX

That makes sense why you would need this capability. Unfortunately, I doubt that change is coming any time soon because the process to enable to geodatabase requires high level (Super User) access. Maybe @VinceAngelo can explain to you how that works.

You may have already seen it, but this document could be helpful. Would one of the PostgreSQL/PostGIS options work as a substitute for Esri Enterprise Geodatabase?

imranrajjad_cc

Guys! I am having the same issue, I am in process of setting up a system from scratch, and the solution team has decided to go ahead with Azure Postgres Flexible Server. Has anybody found any way around this?