|
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;" |