Non-Superuser SDE database user on PostgreSQL

[BillMitchell]

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.

[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?

[danbecker]

We just created an Az db for Postgres -Single server instance for Enterprise gdb storage.

Using PGAdmin, I logged in as postgres_admin, then created an sde user that does not have super user permission. I then created a db, with sde user as owner.

In Pro 3.1, I connect to the db as sde user, then run the Enable Enterprise Geodatabase tool. I then created dbo user using the Create database user tool, connect as dbo in Pro and start creating FCs.

Im seeing 2x performance/speed with the managed instance compared to our 4-cpu 32gb memory Linux Ubuntu Azure VM. 

[ArianaToth]

I also don't understand why Esri documentation says that Microsoft Azure Database for PostgreSQL (Flexible Server)  is supported since it doesn't allow superuser permissions. My understanding is that superuser status is required to drop end user connections to the db for maintenance purposes. Are there extensions that can be enabled that would grant these permissions without requiring superuser status? 

[ArianaToth]

I just got off a call with Esri support. I'm using pgAdmin, so that's the solution I can offer. If you highlight the database, then click Tools > Query Tool, you can run the following statement, assuming your sde user has already been created:

GRANT azure_pg_admin TO sde;

This solution comes from Microsoft documentation here. Sde still won't have superuser permissions but should be able to disconnect users from the database, which is what we really need.

[laurencecuche]

@TonyContreras_Frisco_TX 

I can testify that we could install a enterprise Geodatabase (SDE/Postgresql) on Azure Flex (Postgresql, no HA) with the non-SU SDE.

The user SDE was made 'manually':
CREATE ROLE sde WITH
  LOGIN
  NOSUPERUSER
  NOINHERIT
  CREATEDB
  CREATEROLE
  NOREPLICATION
  PASSWORD 'xxxxxxxxxxxxxxxxxxx';
 

We created the database manually as well (as user SDE).

Then, as user SDE, we used the ArcGIS Pro Geoprocessing Tool "Enable Enterprise Geodatabase" to initiate the geodb.

This worked without any problem. So i guess that the SDE user doesn't need to be SU anymore to be allowed to create a Geodatabase on Postgresql. (As far as i know it is/was different on Oracle)

We haven't tried to upgrade our gdb yet, maybe will may encounter some troubles there because of missing SU rights.. 

@ArianaToth:  I have tried to kill some User Sessions in Pro (using a connection as SDE user), it failed because of missing permissions. Granting the role 'azure_pg_admin' to user SDE doesn't help further.

In anycase this  'azure_pg_admin' role is a black box to me. The name sounds great but i could find nowhere what effectively gets granted...

On the other hand, killing a session manually (SELECT pg_terminate_backend(xxx);) works using the non SU SDE user but having the  'azure_pg_admin' role...

If anybody could report about his experience of upgrading a SDE/Postgresql on Azure Flex or could tell of what the role  'azure_pg_admin' exactely consists, i would be very glade!