#!/usr/bin/env python

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

# Copyright 2008 VEB IT

# See COPYING for distribution information.

# $Id$

from ConfigParser import ConfigParser

from pyPgSQL import PgSQL

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

cf = ConfigParser()

cf.readfp(cff)

cff.close()

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

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

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

        unicode_results=True)

dbc = dbh.cursor()

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

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

    dbc.execute(

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

    dbh.commit()

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

res = dbc.fetchall()

if len(res):

    for uid in res:

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

    dbh.commit()

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

dbh.commit()

dbc.execute("DROP VIEW dovecot_password")

dbh.commit()

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

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

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

      FROM users

           LEFT JOIN domains USING (gid)""")

dbh.commit()

dbh.close()

# print importnat information

print 

print "* set permissions for replaced views:"

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

'name')

print "GRANT SELECT ON dovecot_password TO your_dovecot_dbuser;"