Postgres can't detach or manually delete user and db

Questions / Help
1

I’m trying to detach an app from a Postgres cluster that was added using fly pg attach.

> flyctrl pg detach -a <my-attached-app> <my-pg-app> --verbose

Yields this error:

Error error running user-delete: 500: ERROR: role "<my-attached-app>" cannot be dropped because some objects depend on it (SQLSTATE 2BP01)

Not sure how to work around as the flyctrl pg [db|users] sub commands seem limited to just list. I can see <my-attached-app>'s user and db, but can’t seem to manually delete them through flyctl.

2 Likes
2

I was able to clean up this (I think) by doing the following:

flyctl pg connect -a <my-pg-app>
# DROP DATABASE <my-attached-app-db> WITH (FORCE);
# DROP USER <my-attached-app-role> WITH (FORCE);

Would appreciate to hear if this is the correct way of doing this. Or if there are plans to add more management commands to flyctl pg.

4 Likes
3

Looks like a bug we need to address. Sorry about that.

Anyways, it depends on your intentions. If you just want to drop the user and retain everything it owns, then it’s typically best to re-assign ownership.

You could connect to your database as the postgres user and run something like:

REASSIGN OWNED BY <user-to-remove> TO postgres;

If you’re user owns things across multiple databases you’d need to run that query in each db.

Once your’e done with that, you should be able to drop the user.

2 Likes
4

DROP DATABASE WITH (FORCE);

is enough for detach,

DROP USER WITH (FORCE);

I don’t know what is my-attached-app-role