Do you have some “dead” users in your production database? Users which belonged to some former employees, who are long gone, but their Postgres users still maintain their ghostly presence in your environment? Just be honest :). I know that all of us should have some process in place to drop a user when somebody leaves the company. Most of the time, we are doing it promptly. But sometimes…
Sometimes, when we are trying to drop a user, we get an error: that user owns some objects. And there is no “DROP CASCADE” option for a user. Moreover, most likely, you do not want to drop cascade because that user might own some critical objects you do not want to lose!
Yes, we should not have such situations in the first place, and you might even have a policy stating that objects should never belong to the individual users, only to the group roles. But things happen. Maybe, somebody was in a hurry fixing production issues. Maybe, something was created “temporarily.” In any case, you can’t drop this user. You need to reassign the objects the user owns to the correct user/role. You need to revoke permissions. In case you can understand what in the world these permissions mean!
Several weeks ago, I was facing one of these situations. I successfully reassigned objects and revoked permissions, except for a couple. These last ones were weird, and I could not revoke then no matter what! What I mean, I was issuing a revoke command, it would complete successfully, but permissions still stayed! Apparently, I did not know what I was doing :).
I had to rely on the last resort – ask Grandpa Google. And I quickly discovered a command I didn’t use before: DROP OWNED BY. Looks like exactly what I wanted, almost DROP CASCADE, right? Wrong! Because in contrast to DROP CASCADE, it won’t tell you what exactly it dropped! When you issue DROP CASCADE in a transaction, it would tell you which objects it is going to drop, and you can make sure you are not accidentally dropping something you didn’t mean to drop. But this command executes in dead silence!
I can’t even describe how scary it was to actually press ENTER:)
… In case you think I have no fear!
The World of Data 