Confluence user renaming PostgreSQL stored procedure.

confluence-rename-users.sql

DECLARE vdir integer;

BEGIN

-- BEGIN;
-- ^not needed for stored procedure

CREATE TABLE IF NOT EXISTS usermigration
(
  oldusername character varying(255) NOT NULL,
  newusername character varying(255) NOT NULL,
  CONSTRAINT pk_oldusername PRIMARY KEY (oldusername)
)
WITH (
  OIDS=FALSE
);

update attachments 
	set creator = newusername from usermigration u 
	where creator = u.oldusername;
 
update attachments
	set lastmodifier = newusername from usermigration u
	where lastmodifier = u.oldusername;
 
update content
	set creator = newusername from usermigration u
	where creator = u.oldusername;
 
update content
	set lastmodifier = newusername from usermigration u
	where lastmodifier = u.oldusername;
 
update content
	set username = newusername from usermigration u
	where username = u.oldusername;
 
update content_label
	set owner = newusername from usermigration u
	where owner = u.oldusername;
 
update content_perm
	set creator = newusername from usermigration u
	where creator = u.oldusername;
 
update content_perm
	set lastmodifier = newusername from usermigration u
	where lastmodifier = u.oldusername;
 
update content_perm
	set username = newusername from usermigration u
	where username = u.oldusername;
 
update contentlock
	set creator = newusername from usermigration u
	where creator = u.oldusername;
 
update contentlock
	set lastmodifier = newusername from usermigration u
	where lastmodifier = u.oldusername;
 
update cwd_user
	set lower_user_name = lower(newusername) from usermigration u
	where lower_user_name = lower(u.oldusername);
 
update cwd_user
	set user_name = newusername from usermigration u
	where user_name = u.oldusername;
 
update extrnlnks
	set creator = newusername from usermigration u
	where creator = u.oldusername;
 
update extrnlnks
	set lastmodifier = newusername from usermigration u
	where lastmodifier = u.oldusername;
 
update follow_connections
	set followee = newusername from usermigration u
	where followee = u.oldusername;
 
update follow_connections
	set follower = newusername from usermigration u
	where follower = u.oldusername;
 
update label
	set owner = newusername from usermigration u
	where owner = u.oldusername;
 
update links
	set creator = newusername from usermigration u
	where creator = u.oldusername;
 
update links
	set lastmodifier = newusername from usermigration u
	where lastmodifier = u.oldusername;
 
update notifications
	set creator = newusername from usermigration u
	where creator = u.oldusername;
 
update notifications
	set lastmodifier = newusername from usermigration u
	where lastmodifier = u.oldusername;
 
update notifications
	set username = newusername from usermigration u
	where username = u.oldusername;
 
update pagetemplates
	set creator = newusername from usermigration u
	where creator = u.oldusername;
 
update pagetemplates
	set lastmodifier = newusername from usermigration u
	where lastmodifier = u.oldusername;
 
update remembermetoken
	set username = newusername from usermigration u
	where username = u.oldusername;
 
update spacegroups
	set creator = newusername from usermigration u
	where creator = u.oldusername;
 
update spacegroups
	set lastmodifier = newusername from usermigration u
	where lastmodifier = u.oldusername;
 
update spacepermissions
	set creator = newusername from usermigration u
	where creator = u.oldusername;
 
update spacepermissions
	set lastmodifier = newusername from usermigration u
	where lastmodifier = u.oldusername;
 
update spacepermissions
	set permusername = newusername from usermigration u
	where permusername = u.oldusername;
 
-- just in case the newuser namaged to create a new space before we did the migration
-- we will rename this one: ~username => ~~username
update spaces
	set spacename = '~~' || spacename from usermigration u
	where spacename = '~' || u.newusername;

-- rename the old space to the new one (now it does to exist)
update spaces
	set spacename = '~' || newusername from usermigration u
	where spacename  = '~' || u.oldusername;

update spaces
	set creator = newusername from usermigration u
	where creator = u.oldusername;
 
update spaces
	set lastmodifier = newusername from usermigration u
	where lastmodifier = u.oldusername;

 
update trackbacklinks
	set creator = newusername from usermigration u
	where creator = u.oldusername;
 
update trackbacklinks
	set lastmodifier = newusername from usermigration u
	where lastmodifier = u.oldusername;

-- if new user already exists we remove these entries
delete from os_propertyentry where entity_name in (select 'CWD_' || newusername from usermigration);

update os_propertyentry
	set entity_name = 'CWD_' || newusername from usermigration u
	where entity_name = 'CWD_' || u.oldusername;

update spaces
	set spacekey = '~' || newusername from usermigration u
	where spacekey = '~' || u.oldusername;
  
update bandana
	set bandanacontext = '~' || newusername from usermigration u
	where bandanacontext = '~' || u.oldusername;

update bandana
	set bandanavalue = replace(bandanavalue, '~' || oldusername, '~' || newusername)
	from usermigration u;

-- that's risky but looks necessary, letg just hope nobody has a too short username
update bodycontent 
	set body = replace(body, '~' || oldusername, '~' || newusername)
	from usermigration u;
	
-- now we delete migrated users, we do not want to risk running the migration twice because it will remove os_propertyentry records.

delete from usermigration;

-- COMIT;
-- ^not needed for stored procedure

RETURN 1;

END;

Hi Do you have one for Oracle, if so please share.

Regards,
vh

Hi,
I tried this on confluence 4.2 and doesn't seems to be working, after renaming the user if I click on the username for profile I have a null pointer exception
Pls help in fixing this.

Sorry, I cannot help with Oracle. Also, I do not know about your null pointer exception. Still, I would restore the DB and try the update while confluence is OFFLINE. And see the problem after that. Still, you should post a full exception error and try to find what generated it. Also, I suggest disabling all plugins and see if the problem still persists. It would not be surprised to find out that this is caused by a plugin.

do you have any update for the changed encrypted username format?