[RCU] How To Remove Users From Database

Michael Orlitzky michael at orlitzky.com
Fri Aug 26 19:16:07 CEST 2011


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



More information about the users mailing list