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