Unable to drop database postgres RDS instance

Question

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.

What are the permissions on the database? Are the connected users superusers? — Laurenz Albe, Oct 30 '20 at 11:25
Does this answer your question? [How to drop a PostgreSQL database if there are active connections to it?](https://stackoverflow.com/questions/5408156/how-to-drop-a-postgresql-database-if-there-are-active-connections-to-it) — iLuvLogix, Oct 30 '20 at 11:25
@iLuvLogix well I'm trying to do it with that, but found my self in a loop of now been able to drop it, there are always idle connections which I need to terminate — copser, Oct 30 '20 at 11:29
I am wondering about the other connections that are blocking the `DROP DATABASE` - are they by superusers? — Laurenz Albe, Oct 30 '20 at 11:30
@LaurenzAlbe I've heard some people saying they would even pay large amounts of money for a `dropdb --force` ;) — iLuvLogix, Oct 30 '20 at 11:33
@LaurenzAlbe they have added that in PG13, but I'm on 12, and idle connection which are idle and always appearing are by my_user, who is also a superuser — copser, Oct 30 '20 at 11:36
Does this answer your question? [How can I drop all the tables in a PostgreSQL database?](https://stackoverflow.com/questions/3327312/how-can-i-drop-all-the-tables-in-a-postgresql-database) — Tiago Martins Peres, Mar 03 '22 at 12:22
@iLuvLogix that's possible - https://www.postgresql.org/docs/current/sql-dropdatabase.html — Tiago Martins Peres, Mar 03 '22 at 12:24

score 3 · Accepted Answer · answered Oct 30 '20 at 12:38

3

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.

answered Oct 30 '20 at 12:38

Laurenz Albe

209,280
17
206
263

Thanks for clarifying that, I eventually figured out that was the case, I solved this differently, the app which is connecting to the db is elixir(phoenix) and ecto have `mix ecto.rollback --step 1` which I didn't realize at the time. – copser Oct 30 '20 at 17:27

Unable to drop database postgres RDS instance

1 Answers1