How to Remove all External Users from the Ranger / Ranger usersync database

[#1]

In the course of testing the usersync tool, we had some settings wrong and the users are mis-synced. We'd like to clear them all out and restart the sync. Is there an easy way/tool to remove all the external users from the Ranger / Ranger usersync database so we can resync with our new settings?

[#2 · Accepted solution]

scripts.zip

Check attached scripts and see if it helps..

[#3]

Will this work on Postgress also?

[#4]

@bganesan Now that https://issues.apache.org/jira/browse/RANGER-205 is fixed, can we use the rest API instead of DB script?

[#5]

Really useful scripts. Helped me with my MySQL environment.

To prepare the input.txt, I had to run this and pass this as input to the script:

$ mysql -u userName -p dbName -e “select user_name from x_user” > /tmp/input.txt

[#6]

CC @abajwa@hortonworks.com @sneethiraj@hortonworks.com @vperiasamy@hortonworks.com

[#7]

Rest apis used for delete are:

• DELETE http://<ip>:6080/service/xusers/users/userName/<userName>;
• DELETE http://<ip>:6080/service/xusers/groups/groupName/<groupName>;
• DELETE http://<ip>:6080/service/xusers/groups/<id>;
• DELETE http://<ip>:6080/service/xusers/users/<id>;

[#8]

Awesome! These API urls were really helpful!

You have to make sure the user or group is not assigned to any policy before you delete it. If the user/group is assigned to a policy the API returns 404 not found as result.

[#9]

Until the API gets fixed to clean things up correctly, here's a PostgreSQL anonymous code block which cleans up the stuff it leaves behind which we used:

DO $
DECLARE 
	u record;
	r record;
	p record;
BEGIN
	FOR u IN select id, login_id from x_portal_user where login_id not in (select user_name from x_user)
	LOOP
		RAISE NOTICE 'User roles in x_portal_user_role:';
		FOR r IN select id, user_id, user_role from x_portal_user_role where user_id = u.id
		LOOP
			RAISE NOTICE USING MESSAGE = '  ' || r.user_role;
			RAISE NOTICE USING MESSAGE = 'DELETE from x_portal_user_role WHERE id = ' || r.id;
			-- Uncomment next line to perform action
			--EXECUTE 'DELETE from x_portal_user_role WHERE id = ' || r.id;
		END LOOP;
		RAISE NOTICE 'User permissions in x_user_module_perm:';
		FOR p IN select id, user_id, module_id from x_user_module_perm where user_id = u.id
		LOOP
			RAISE NOTICE USING MESSAGE = '  ' || (select module from x_modules_master where id = p.module_id);
			RAISE NOTICE USING MESSAGE = 'DELETE from x_user_module_perm where id = ' || p.id;
			-- Uncomment next line to perform action
			--EXECUTE 'DELETE from x_user_module_perm where id = ' || p.id;
		END LOOP;
		RAISE NOTICE USING MESSAGE = 'DELETE FROM x_portal_user WHERE id = ' || u.id;
		-- Uncomment next line to perform action
		--EXECUTE 'DELETE FROM x_portal_user WHERE id = ' || u.id;
		RAISE NOTICE '  ';
	END LOOP;
END$;

[#10]

For complete cleaning of users from DB, please use the scripts provided by @bganesan@hortonworks.com above.

REST API DELETE calls perform only soft-delete.

Hard DELETE feature is still in the works.https://issues.apache.org/jira/browse/RANGER-205