On 08/26/11 12:29, Carlos Mennens wrote:

When a user logs into Roundcube, it automatically creates entries for that user into my database (PostgreSQL) however when I delete a user from my mail server, how exactly do I delete all entries or traces of that user from my 'roundcube' database? I'm not sure how many tables user data is written to but do you guys have a command or script that removes all traces of a users database files? If you could please share, I would be greatly appreciative!

-Carlos

We run a ruby script every night that checks for accounts which were deleted through PostfixAdmin. If it finds any, it removes their maildirs and drops them from the RoundCube DB.

Here's the RoundCube part. The user_id query is just "SELECT user_id FROM users WHERE username = $1;"

def delete_account(account) # Delete the given username and any records in other tables # belonging to it. user_id = self.get_user_id(account)

sql_queries = ['DELETE FROM cache WHERE user_id = $1::int;']
sql_queries << 'DELETE FROM contactgroupmembers WHERE

contactgroup_id IN (SELECT contactgroup_id FROM contactgroups WHERE user_id = $1::int);' sql_queries << 'DELETE FROM contactgroups WHERE user_id = $1::int;' sql_queries << 'DELETE FROM contacts WHERE user_id = $1::int;' sql_queries << 'DELETE FROM identities WHERE user_id = $1::int;' sql_queries << 'DELETE FROM messages WHERE user_id = $1::int;' sql_queries << 'DELETE FROM users WHERE user_id = $1::int;'

begin
  connection = PGconn.connect(@db_host,
                              @db_port,
                              @db_opts,
                              @db_tty,
                              @db_name,
                              @db_user,
                              @db_pass)

  sql_queries.each do |sql_query|
    connection.query(sql_query, [user_id])
  end

  connection.close()

rescue PGError => e
  # Pretend like we're database-agnostic in case we ever are.
  raise DatabaseError.new(e)
end

end

List info: http://lists.roundcube.net/users/ BT/9b404e9e

On 08/26/11 13:38, Carlos Mennens wrote:

On Fri, Aug 26, 2011 at 1:16 PM, Michael Orlitzky michael@orlitzky.com wrote:

...

Here's the RoundCube part. The user_id query is just "SELECT user_id FROM users WHERE username = $1;"

I tried the RoundCube part of your ruby script & when I attempted to run the command straight into my database engine, it failed:

webmail-# FROM users WHERE username = $1; ERROR: there is no parameter $1 LINE 2: FROM users WHERE username = $1; ^ Any ideas why that failed?

Did you replace,

user_id = self.get_user_id(account)

with the user ID you want to delete? A plain old integer should work.

List info: http://lists.roundcube.net/users/ BT/9b404e9e

On 08/26/11 15:13, Carlos Mennens wrote:

On Fri, Aug 26, 2011 at 2:05 PM, Michael Orlitzky michael@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.