PostgreSQL ERROR: role cannot be dropped because some objects depend on it

Drop role output error "cannot be dropped because some objects depend on it"

postgres=# drop role apqry ;
ERROR:  role "apqry" cannot be dropped because some objects depend on it
DETAIL:  215 objects in database datap
215 objects in database datad
9 objects in database logdb
18 objects in database ssodb
242 objects in database batchdb

Connect any database and try to delete using drop owned command, it does not show dependency in logdb

postgres=# \c logdb
You are now connected to database "logdb" as user "postgres".

logdb=# drop owned by apqry cascade;
DROP OWNED

logdb=# drop role apqry ;
ERROR:  role "apqry" cannot be dropped because some objects depend on it
DETAIL:  215 objects in database datap
215 objects in database datad
18 objects in database ssodb
242 objects in database batchdb

I wrote a simple shell script to log into each database and try to drop the owned and then drop the role.

#!/bin/sh

for DB in $(psql -t -c "SELECT datname FROM pg_database WHERE datname NOT IN ('postgres', 'template0', 'template1')"); do
  psql -d $DB -t -c "drop owned by apqry"
done

psql -t -c "drop role apqry"

張貼留言

0 留言