Drop connections from an enterprise geodatabase in Amazon RDS for PostgreSQL using the SDE user

Summary

In an Amazon RDS for PostgreSQL geodatabase, the SDE user cannot drop connections using the geodatabase administration dialog box. When attempting that, an "Insufficient Permissions" error is returned.

Administering connections to a PostgreSQL geodatabase using ArcGIS tools requires PostgreSQL superuser privileges. In an Amazon RDS for PostgreSQL instance, the only superuser is rdsadmin, which is managed directly by Amazon. The SDE user is in the rds_superuser role, which is a pre-defined Amazon RDS role similar to the PostgreSQL superuser, but with some restrictions.

Procedure

As a workaround, the SDE user, or anyone in the rds_superuser role, can manage connections to the PostgreSQL geodatabase through pgAmin using the pg_terminate_backend command.

1. Connect to the geodatabase as SDE in pgAmin.
2. In a new Query Tool window, issue the following command to list all open connections in the database:

SELECT * FROM pg_stat_activity WHERE datname = '<database_name>';

3. From the result returned, find the process ID (pid) of the connection to be dropped. Run the following command to terminate the connection:

SELECT pg_terminate_backend(<pid>);

4. Verify in geodatabase administration dialog box that the connection has been dropped.