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
– Laurenz Albe, Commented Oct 30, 2020 at 11:25
Does this answer your question? How to drop a PostgreSQL database if there are active connections to it? — iLuvLogix
– iLuvLogix, Commented Oct 30, 2020 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
– copser, Commented Oct 30, 2020 at 11:29
I am wondering about the other connections that are blocking the DROP DATABASE - are they by superusers? — Laurenz Albe
– Laurenz Albe, Commented Oct 30, 2020 at 11:30

Laurenz Albe · Accepted Answer · 2020-10-30 12:38:29Z

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, 2020 at 12:38

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

copser Over a year ago

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.

Collectives™ on Stack Overflow

Unable to drop database postgres RDS instance

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related