Removing a project with OIDC configured fails

[miketheman]

When removing a project via admin (such as malware), currently the removal fails, presumably due to not having an on delete cascade for the foreign key relationship.

(psycopg.errors.ForeignKeyViolation) update or delete on table "projects" violates foreign key constraint "oidc_publisher_project_association_project_id_fkey" on table "oidc_publisher_project_association"
DETAIL:  Key (id)=(REDACTED) is still referenced from table "oidc_publisher_project_association".
[SQL: DELETE FROM projects WHERE projects.id = %(id)s::UUID]
[parameters: {'id': UUID('REDACTED')}]

We probably want to delete a publisher on project removal, since it's a one-to-one relationship, is that correct @woodruffw ?

We probably also want Admin UI functionality to remove an OIDC publisher from a project.

[miketheman]

Manual workaround for now:

>>> from warehouse.oidc.models import GitHubPublisher
>>> pub = db.query(GitHubPublisher).filter_by(repository_owner='redacted', repository_name='redacted').one()
>>> db.delete(pub)
>>> db.commit()

[woodruffw]

We probably want to delete a publisher on project removal, since it's a one-to-one relationship, is that correct @woodruffw ?

Unfortunately not -- it's a many-many relationship, since other PyPI projects could have the same publisher registered. So I think we want to persist the publisher unless it's the last project associated with it, in which case it can be deleted 🙂

[miketheman]

Oh, I see - I must have misread the relationships. Instead of removing the publisher itself, then maybe the related OIDCPublisherProjectAssociation should be removed on project delete?

It works for publisher-side delete - when I ran the code above to remove the publisher, it removed the association as well.

[woodruffw]

Yeah, that makes sense to me!