1 #!/usr/bin/env python

     2 # -*- coding: UTF-8 -*-

     3 # Copyright 2008 VEB IT

     4 # See COPYING for distribution information.

     5 # $Id$

     6 

     7 from ConfigParser import ConfigParser

     8 from pyPgSQL import PgSQL

     9 

    10 cff = file('/usr/local/etc/vmm.cfg', 'r')

    11 cf = ConfigParser()

    12 cf.readfp(cff)

    13 cff.close()

    14 

    15 dbh = PgSQL.connect(database=cf.get('database', 'name'),

    16         user=cf.get('database', 'user'), host=cf.get('database', 'host'),

    17         password=cf.get('database', 'pass'), client_encoding='utf8',

    18         unicode_results=True)

    19 dbc = dbh.cursor()

    20 dbc.execute("SET NAMES 'UTF8'")

    21 

    22 for service in ['smtp', 'pop3', 'imap', 'managesieve']:

    23     dbc.execute(

    24             "ALTER TABLE users ADD %s boolean NOT NULL DEFAULT TRUE" % service)

    25     dbh.commit()

    26 

    27 dbc.execute("SELECT uid FROM users WHERE disabled")

    28 res = dbc.fetchall()

    29 if len(res):

    30     for uid in res:

    31         dbc.execute("UPDATE users SET smtp = FALSE, pop3 = FALSE, imap = FALSE, managesieve = FALSE WHERE uid = %s", uid[0])

    32     dbh.commit()

    33 dbc.execute("ALTER TABLE users DROP disabled")

    34 dbh.commit()

    35 

    36 dbc.execute("DROP VIEW dovecot_password")

    37 dbh.commit()

    38 dbc.execute("""CREATE OR REPLACE VIEW dovecot_password AS

    39     SELECT local_part || '@' || domains.domainname AS "user",

    40            passwd AS "password", smtp, pop3, imap, managesieve

    41       FROM users

    42            LEFT JOIN domains USING (gid)""")

    43 dbh.commit()

    44 dbh.close()

    45 

    46 # print importnat information

    47 print 

    48 print "* set permissions for replaced views:"

    49 print "connect to your database [psql %s] and execute:" % cf.get('database',

    50 'name')

    51 print "GRANT SELECT ON dovecot_password TO your_dovecot_dbuser;"