Identifying & Removing Disabled User projects within CDSW.

[#1]

Customers at times have users that utilized CDSW in the past but are no longer in the organization.  Unfortunately there are limitations within CDSW to 1) Delete those users, and 2) Delete projects associated with those users. 

As of CDSW 1.6 administrators can only "disable" users.  Deleting projects associated with the users is a bit convoluted but possible.  This requires access to the underlying PSQL database within CDSW & Kubernetes.

To accomplish the deletion of projects associated with a user (in this example let's say we are removing projects from /var/lib/cdsw/current/projects/projects/0/) you will have to create two queries to execute this.  

1) Create query to find which users are banned/disabled within CDSW.

# kubectl exec -it $(kubectl get pods -l role=db -o jsonpath='{.items[*].metadata.name}') -- psql -P pager=off -A -t -U sense -c "SELECT username  FROM users where banned='t'" >> /tmp/list_usersdisabled.tx

2) Create a query for mapping users to projects to their respective locations on disk. 

# kubectl exec -it $(kubectl get pods -l role=db -o jsonpath='{.items[*].metadata.name}') -- psql -P pager=off -A -t -U sense -c "select u.username, u.id as user_id, u.email, u.last_logout_at_tz as user_last_logout_at_tz, u.last_seen_at as user_last_seen_at, '/var/lib/cdsw/current/projects/projects/0/' || p.id as project_id, p.name as project_name from users u , projects p where u.id = p.user_id;"  >> /tmp/mapping_projectstodisk.txt

Once you have these two files, you can perform the mapping in Excel or alternatively run "grep -Ff file1 file2" to show all the projects associated with the banned users.  From there you can delete them within the CDSW UI.

[#2]

Thanks bantone for your article