Azure Postgres Flexible - pg_signal_backend

Question

We are attempting to use Azure Postgres Flexible

Our application needs to terminate existing existing connection and then drop the database.

This requires 'pg_signal_backend'

https://www.postgresql.org/docs/current/predefined-roles.html

However, this granting this operation is not supported

GRANT pg_signal_backend TO <user name>;

GRANT statement fails with:

'Detail: Only roles with the ADMIN option on role "pg_signal_backend" may grant this role.'

Other posts discuss adding this grant:

https://learn.microsoft.com/en-us/answers/questions/740611/deleting-active-connections-in-azure-postgresql

The migration guide also talks about granting this permission:

https://learn.microsoft.com/en-us/azure/postgresql/migrate/migration-service/best-practices-migration-service-postgresql

Answer 1

@Aaron Phethean I'm glad that we were able to resolve your issue and thank you for posting the provided solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost the solution in case you'd like to "Accept " the answer.

Issue: how can we GRANT pg_signal_backend? is pg_signal_backend possible on Azure Postgres Flexible?

Solution: connecting and terminating the backend connections as the user who owns the database is a workable solution. (As opposed to our previous implementation that terminated the backend pids as the admin user

SELECT

pg_terminate_backend(pid)

FROM pg_stat_activity

WHERE

-- don't kill my own connection!

pid <> pg_backend_pid()

AND usename = 'testuser';

If you have any other questions or are still running into more issues, please let me know. Thank you again for your time and patience throughout this issue.

Please remember to "Accept Answer" if any answer/reply helped, so that others in the community facing similar issues can easily find the solution.

Regards

Geetha

Answer 2

For our case, connecting and terminating the backend connections as the user who owns the database is a workable solution. (As opposed to our previous implementation that terminated the backend pids as the admin user

SELECT
pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE
-- don't kill my own connection!
pid <> pg_backend_pid()
AND usename = 'testuser';