sde account with rds_superuser role on AWS RDS Postgres has insufficient privileges to disconnect users

626
6
03-02-2020 12:13 PM
HenokAlemu
New Contributor II

We have enterprise geodatabases on AWS RDS for Postgres/PostGIS. And the sde account is given the rds_superuser role. While accessing the RDS geodatabase from ArcGIS client using the SDE account, the SDE account is unable to disconnect logged-in users from the geodatabase connection. And the error message is "Insufficient Permissions" or "Connection information provided was for a non-administrative user".

In PostgreSQL, the sde account is given the superuser role, that is what is needed to be able to have the permissions to disconnect users. Not sure why rds_superuser role is not working as expected or whether I am missing something in the workflow.

Appreciate any insights or solutions to this.

Thank you,

Henok

6 Replies
SteveTaylor5
New Contributor II

Hi 
Did you manage to get a resolution for this - we are experiencing the same issue

Regards
Steve

0 Kudos
JeffreyOwen1
New Contributor III

Same configuration and issue here. I am about to open a ticket but wanted to see if anyone has figured this out beforehand. Any updated info would be helpful.

 

Thanks!

0 Kudos
JeffreyOwen1
New Contributor III

Ok, since no one else is posting solutions or any follow up information I figured I would jump in and let you know what I found.

The "rds_superuser" role is not actually given superuser privileges so the "sde" user doesn't have it and there is no available account to grant superuser to "sde". In the AWS RDS environment no user has that role except for the "rdsadmin", which is administered by Amazon and not available to us, even as admins. The "rds_superuser" role is made to be similar to a superuser, but does not have enough privileges to disconnect users from the database. Esri suggested that I do this through the SQL instead, so I will be trying that probably this weekend or some time in the near future. I will update when I have more info.

It is a little disappointing that we can't use the same built-in database maintenance tools that we would be able to in other environments and I hope that Esri at least puts this caveat in the documentation so people know sooner.

Anyone else is free to contribute their solutions if they figured it out already.

SteveTaylor5
New Contributor II

Hi Jefferey
I managed to disconnect users with the SDE user by using ArcGIS Pro and not ArcMap
It seems ArcGIS Pro behaves differently and allows the SDE user to disconnect other connections and ArcMap just reports and Insufficient Privileges error.

0 Kudos
JeffreyOwen1
New Contributor III

Interesting. I am using ArcGIS Pro as well and have the same issue as with using the script. Is your setup the same? Amazon RDS PostgreSQL database?

After talking to Esri they suggest that I use PGAdmin and run a SQL statement to kill the processes by PID. That seemed to work but I am having other issues with my database unrelated to this as well, so who knows.

0 Kudos
Bernd_Loigge
New Contributor

We are facing the same issue in azure (managed PG cloud DB - no superuser rights). Can you post the SQL statement for deleting the lock? Would be nice if ESRI would create a TroubleShooting for this kind of issue. 

0 Kudos