PostgreSQL script to remove a confluence, jira or crowd user directory from the database. Very helpful for those that do get errors while trying to remove it, like https://jira.atlassian.com/browse/CWD-4606

confluence-remote-directory.sql

-- Function: remove_last_directory()

-- DROP FUNCTION remove_last_directory();

CREATE OR REPLACE FUNCTION remove_last_directory()
  RETURNS integer AS
$BODY$/*
	author:		Sorin Sbarnea
	version:	1.0
	homepage:	https://gist.github.com/2352885

	BACKUP FIRST !!!

	WARNING: running this procedure will directly remove the 
	last authentication directory entry from your:
	Confluence, Jira, ... that are using the embedded Crowd.

	All the users from the directory will be deleted, but not their pages.

	If you call it more than once you may endup with a database with no users at all.

	This script may be needed if you added a new directory and
	you want to migrate the old users to the new one.
	See: https://gist.github.com/2352960

*/
DECLARE vDir integer;

BEGIN
	SELECT directory_id 
		FROM cwd_app_dir_mapping 
		ORDER BY list_index 
		DESC LIMIT 1 
		INTO vDir;

	delete from cwd_user_attribute 
		where directory_id = vDir;

	delete from cwd_user_attribute 
		where user_id in (select id from cwd_user where directory_id = vDir);

	delete from cwd_membership 
		where child_user_id 
		in (select id from cwd_user where directory_id = vDir);

	delete from cwd_app_dir_operation 
		where app_dir_mapping_id 
		in (select id from cwd_app_dir_mapping where directory_id = vDir);


	delete from cwd_app_dir_mapping where directory_id = vDir;

	delete from cwd_app_dir_group_mapping 
		where directory_id = vDir;

	delete from cwd_user 
		where directory_id = vDir;

	delete from cwd_membership 
		where child_user_id in (select id from cwd_user where directory_id = vDir)
		or child_group_id in (select id from cwd_group where directory_id = vDir) 
		or parent_id in (select id from cwd_group where directory_id = vDir);

	delete from cwd_group_attribute 
		where directory_id = vDir;

	delete from cwd_group 
		where directory_id = vDir;


	delete from cwd_directory_operation 
		where directory_id = vDir;

	delete from cwd_directory_attribute 
		where directory_id = vDir;

	delete from cwd_directory 
		where id = vDir;

RETURN 1;

END;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION remove_last_directory() OWNER TO postgres;

And after this a final fix http://confluence.atlassian.com/plugins/viewsource/viewpagesrc.action?pageId=278694203