[RCU] How To Remove Users From Database

Michael Orlitzky michael at orlitzky.com
Fri Aug 26 21:29:21 CEST 2011

On 08/26/11 15:13, Carlos Mennens wrote:
> On Fri, Aug 26, 2011 at 2:05 PM, Michael Orlitzky <michael at orlitzky.com> wrote:
>> Did you replace,
>>  user_id = self.get_user_id(account)
>> with the user ID you want to delete? A plain old integer should work.
> I did not. I'm not sure I understand your SQL statement you showed me
> and how to run this on my database system. Lets say I am trying to
> delete "all" instances of Mike Smith in RoundCube:
> webmail=# SELECT user_id FROM users WHERE username = 'msmith';
>  user_id
> ---------
>      114
> (1 row)
> So now I've identified that I want to remove user_id '114' from every
> instance of the 'users' table however I'm still not clear on how I
> present this command based on your example script. Sorry I'm slow or
> missing something but I'm trying to understand.
> Also it appears that this just manages data from the user in the
> 'users' table but then you still have the 'identities' table which I'm
> guessing still has orphaned data from Mike Smith too, no?

Now you run all of these queries (in order):

1. DELETE FROM cache WHERE user_id = $1::int;

2. DELETE FROM contactgroupmembers WHERE
contactgroup_id IN (SELECT contactgroup_id FROM contactgroups WHERE
user_id = $1::int);

3. DELETE FROM contactgroups WHERE user_id = $1::int;

4. DELETE FROM contacts WHERE user_id = $1::int;

5. DELETE FROM identities WHERE user_id = $1::int;

6. DELETE FROM messages WHERE user_id = $1::int;

7. DELETE FROM users WHERE user_id = $1::int;

Except in each case, the "$1::int" needs to be replaced by 114. The ruby
script does that for you, but otherwise, there's no need to run those
commands from within ruby.

You could probably get away with just using bash and psql, but be
careful with your live database.
List info: http://lists.roundcube.net/users/

More information about the users mailing list