Automatically deleting Ranger Users from MySQL and Postgres After Using REST API - HDP 2.4

[#1]

Authors -

Gerdan dos Santos - https://community.hortonworks.com/users/12342/gerdan.html

Pedro Drummond - https://community.hortonworks.com/users/10987/pedrodrummond.html

After deleting users using REST API, one might get error "404 not found". This articles demonstrates how to automatically delete the "ghost" users from MySQL and Posgres.

Reproducing the Error

Creating testUser

1. Create testUser at Ranger UI - http://{ranger_host}:6080

2. Username: testUser; Group: public .

3. Check if the default Resource Based Policies is assigned to testUser .

4. If testUser is not assigned to the policy, add it and click the save button .

Deleting testUser using REST API

Reference: https://community.hortonworks.com/questions/1021/how-to-remove-all-external-users-from-the-ranger-r....

Through the terminal, delete the testUser using REST API.

For instance:

curl -X DELETE -v -u admin:password http://{RangerHost}:6080/service/xusers/users/userName/testUser

You should get something like this: .

Verifying the Error

1. Edit the Resource Based Policies .

2. Just click the save button .

3. There you go! Error 404 Not Found

This happens because once you delete the testUser using REST API, you are only deleting the user from the UI and not from the permissioning that it is assigned to.

Solving this issue

All you have to do is connecting in the Ranger's Metadata DB and run the following scripts thought the database client logged with a db user that has full permissions to the database:

For MySQL:

-------------------------
----------MYSQL----------
-------------------------
CREATE TEMPORARY TABLE IF NOT EXISTS ranger.tmp_users_clean as 
 select id from ranger.x_portal_user
 where 
  login_id not in (
  select user_name from ranger.x_user
 );
delete from ranger.x_auth_sess where user_id in(
 select id from ranger.tmp_users_clean
);
delete from ranger.x_portal_user_role where user_id in(
 select id from ranger.tmp_users_clean
);
delete from ranger.x_user_module_perm where user_id in(
 select id from ranger.tmp_users_clean
);
delete from ranger.x_portal_user where id in(
 select id from ranger.tmp_users_clean
);

For Postgres:

-------------------------
--------POSTGRESQL-------
-------------------------
delete from x_auth_sess where user_id in(
 select id from x_portal_user
 where 
  login_id not in (
  select user_name from x_user
 )
);
delete from x_portal_user_role where user_id in(
 select id from x_portal_user
 where 
  login_id not in (
  select user_name from x_user
 )
);
delete from x_user_module_perm where user_id in(
 select id from x_portal_user
 where 
  login_id not in (
  select user_name from x_user
 )
);
delete from x_portal_user where id in(
 select id from x_portal_user
 where 
  login_id not in (
  select user_name from x_user
 )
);

Just a little tip: if you don't know where your Ranger's metadata DB is located, you may find it at Ambari UI -> Ranger -> Configs -> Ranger DB host.

For more info: gerdan@gmail.com and pedro.dru@hotmail.com.

Thank you!

[#2]

I remember this was a big discussion point at my previous job. The outcome was that often you do not want to remove a user completely from the Ranger database, because you need to keep an audit trail of who had access in the past. Therefore the GUI never actually deletes a user from the database but leaves a tombstone.

Also note that it is possible (although not advisable) to have two user name entries with the same name but different ID.