openvsx
openvsx copied to clipboard
Need the ability to remove a user/publisher and all data
We need the ability to, on request, delete a user/publisher and all of their data. This can be in the form of a script or an entry on the admin panel. @amvanbaren FYI
@kineticsquid Script to manually delete user/publisher and all data:
BEGIN TRANSACTION;
-- delete file resources published by user
DELETE FROM file_resource WHERE extension_id IN (
SELECT ev.id
FROM extension_version ev
JOIN user_data u ON u.id = ev.published_with_id
WHERE u.login_name = 'doamatto'
);
CREATE TEMPORARY TABLE entities_to_delete (
id BIGINT NOT NULL,
type CHARACTER VARYING(32) NOT NULL
);
INSERT INTO entities_to_delete(id, type)
SELECT id, 'extension_version' FROM extension_version WHERE published_with_id = (
SELECT id FROM user_data WHERE login_name = 'doamatto'
);
INSERT INTO entities_to_delete(id, type)
SELECT extension_id, 'extension' FROM extension_version WHERE published_with_id = (
SELECT id FROM user_data WHERE login_name = 'doamatto'
);
-- decouple extension and extension_version to prevent foreign key violation
UPDATE extension SET latest_id = NULL WHERE latest_id IN (
SELECT id FROM entities_to_delete WHERE type = 'extension_version'
);
-- decouple extension and extension_version to prevent foreign key violation
UPDATE extension SET preview_id = NULL WHERE preview_id IN (
SELECT id FROM entities_to_delete WHERE type = 'extension_version'
);
-- delete extension versions published by user
DELETE FROM extension_version WHERE id IN (
SELECT id FROM entities_to_delete WHERE type = 'extension_version'
);
-- delete extension reviews posted by user
DELETE FROM extension_review WHERE user_id = (
SELECT id FROM user_data WHERE login_name = 'doamatto'
);
-- delete extensions published by user
DELETE FROM extension WHERE id IN (
SELECT id FROM entities_to_delete WHERE type = 'extension'
);
DROP TABLE entities_to_delete;
-- delete user's namespace memberships
-- namespaces will be deleted or re-assigned by OrphanNamespaceMigration
DELETE FROM namespace_membership WHERE user_data = (
SELECT id FROM user_data WHERE login_name = 'doamatto'
);
-- delete user's personal access tokens
DELETE FROM personal_access_token WHERE user_data = (
SELECT id FROM user_data WHERE login_name = 'doamatto'
);
-- delete user
DELETE FROM user_data WHERE login_name = 'doamatto';
END TRANSACTION;
Awesome! Thanks!
@kineticsquid Can we close this issue?
Yes.