CODEWITHSUNDEEP
postgresql
copser
copser

Reputation: 2641

Unable to drop database postgres RDS instance

I'm a bit confused about why I can't drop my database, so after connecting to my rds postgres instance with

psql --host=mu_user.amazonaws.com --port=5432 --username=my_user --password --dbname=postgres

I've REVOKED new connection to the db I want to drop with

REVOKE CONNECT ON DATABASE mydb FROM public;

then I have terminated all connection with

SELECT pid, pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = 'mydb' AND pid <> pg_backend_pid();

after that when I want to drop the db I still can't because It will say

ERROR:  database "mydb" is being accessed by other users
DETAIL:  There are 10 other sessions using the database.

If I inspect live connections with

SELECT 
    pid
    ,datname
    ,usename
    ,application_name
    ,client_hostname
    ,client_port
    ,backend_start
    ,query_start
    ,query
    ,state
FROM pg_stat_activity
WHERE state = 'active';

there will be none, but if I change active to idle I can see a bunch, and after trying to kill them with pg_terminate_backend(pid) I again can't drop the db and I again have the same ERROR, so can someone please help me understand what I'm I doing wrong here?

Those connections which are idle and always appearing are by my_user, who is also a superuser.

Upvotes: 3

Views: 1395

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246798

The problem is that those other connections are by a superuser, and superusers are exempt from permission checks, so revoking the CONNECT privilege on the database won't keep these guys out.

You could either block the connections via pg_hba.conf, or you can run both statements immediately after each other:

SELECT pg_terminate_backend(pid) FROM pg_stat_activity; DROP DATABASE mydb;

in the hope that the users won't have time to reconnect before the DROP DATABASE hits.

Upvotes: 3

Related Questions