Skip to content

fix: postgres database user becomes orphaned after database deletion #303

@alehostert

Description

@alehostert

Static Badge Static Badge

When deleting a PostgreSQL database using os db delete, the associated database user becomes orphaned and cannot be deleted afterwards using os db delete-user.

Steps to Reproduce

  1. Create a PostgreSQL database:

    os db create --db-type postgresql --db-name mydatabase
  2. Create a user with privileges on that database:

    os db create-user --db-type postgresql --db-name mydatabase --username myuser --password mypassword --privileges all
  3. Delete the database:

    os db delete --db-type postgresql --db-name mydatabase
  4. Verify the user still exists but has no database:

    os db get -t postgres -u myuser

    Returns empty databases array:

    {
      "status": "success",
      "body": {
        "pagination": {
          "pageNumber": 0,
          "itemsPerPage": 10,
          "pagesTotal": 0,
          "itemsTotal": 0
        },
        "databases": []
      }
    }
  5. Try to delete the orphaned user:

    os db delete-user --db-type postgresql --db-name mydatabase --db-username myuser
    {
      "status": "infraError",
      "body": "DatabaseNotFound"
    }
    os db delete-user --db-type postgresql --db-username myuser
    Error: required flag(s) "db-name" not set

Expected Behavior

The orphaned user should be deletable, by allowing os db delete-user to work without requiring --db-name when the database no longer exists.

Workaround

Before deleting the database, run:

psql -U postgres -d mydatabase -h 127.0.0.1 -c "DROP OWNED BY myuser;"
os db delete-user --db-type postgresql --db-name mydatabase --db-username myuser
os db delete --db-type postgresql --db-name mydatabase

This ensures all objects owned by the user are dropped before attempting to delete the user.

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions