Permissions for pg_terminate_backend on Amazon RDS
Amazon chose a confusing name for their database administrator role. It is not, in fact, a superuser as far as PostgreSQL is concerned, which you can verify with:
SHOW is_superuser;The actual PostgreSQL superuser access level is not available in RDS, because it'd let you "break out" of the database system by loading your own code, modifying files directly, etc.
So you can't use superuser-only functions in RDS unless Amazon provides a SECURITY DEFINER wrapper function for it, or exposes an AWS API call to let you invoke that functionality indirectly.
However, in this case, you only read half the error message:
ERROR: must be superuser or have the same role to terminate other server processes
Your SQL tries to terminate every connection to the DB, because it has no WHERE clause. And some of those connections must be with different user roles, but you're not a superuser, so you can't do that.
You can still terminate connections from your own username, though.