|
1 #!/usr/bin/env python |
|
2 # coding: utf-8 |
|
3 # Copyright 2012, Pascal Volk |
|
4 # See COPYING for distribution information. |
|
5 |
|
6 """ |
|
7 Use this script in order to set database permissions for your Dovecot |
|
8 and Postfix database users. |
|
9 |
|
10 Run `python set-permissions.py -h` for details. |
|
11 """ |
|
12 |
|
13 import getpass |
|
14 import sys |
|
15 |
|
16 from optparse import OptionParser |
|
17 |
|
18 has_psycopg2 = False |
|
19 try: |
|
20 import psycopg2 |
|
21 has_psycopg2 = True |
|
22 except ImportError: |
|
23 try: |
|
24 from pyPgSQL import PgSQL |
|
25 except ImportError: |
|
26 sys.stderr.write('error: no suitable database module found\n') |
|
27 raise SystemExit(1) |
|
28 |
|
29 if has_psycopg2: |
|
30 DBErr = psycopg2.DatabaseError |
|
31 else: |
|
32 DBErr = PgSQL.libpq.DatabaseError |
|
33 |
|
34 |
|
35 def check_opts(opts, err_hdlr): |
|
36 if not opts.postfix: |
|
37 err_hdlr('missing Postfix database user name') |
|
38 if not opts.dovecot: |
|
39 err_hdlr('missing Dovecot database user name') |
|
40 if opts.askp: |
|
41 opts.dbpass = getpass.getpass() |
|
42 |
|
43 |
|
44 def get_dbh(database, user, password, host, port): |
|
45 if has_psycopg2: |
|
46 return psycopg2.connect(database=database, user=user, |
|
47 password=password, host=host, port=port) |
|
48 return PgSQL.connect(user=user, password=password, host=host, |
|
49 database=database, port=port) |
|
50 |
|
51 |
|
52 def get_optparser(): |
|
53 descr = 'Set permissions for Dovecot and Postfix in the vmm database.' |
|
54 usage = 'usage: %prog OPTIONS' |
|
55 parser = OptionParser(description=descr, usage=usage) |
|
56 parser.add_option('-a', '--askpass', dest='askp', default=False, |
|
57 action='store_true', help='Prompt for the database password.') |
|
58 parser.add_option('-H', '--host', dest='host', metavar='HOST', |
|
59 default=None, |
|
60 help='Hostname or IP address of the database server. Leave ' + |
|
61 'blank in order to use the default Unix-domain socket.') |
|
62 parser.add_option('-n', '--name', dest='name', metavar='NAME', |
|
63 default='mailsys', |
|
64 help='Specifies the name of the database to connect to. ' + |
|
65 'Default: %default') |
|
66 parser.add_option('-p', '--pass', dest="dbpass", metavar='PASS', |
|
67 default=None, help='Password for the database connection.') |
|
68 parser.add_option('-P', '--port', dest='port', metavar='PORT', type='int', |
|
69 default=5432, |
|
70 help='Specifies the TCP port or the local Unix-domain socket ' + |
|
71 'file extension on which the server is listening for ' + |
|
72 'connections. Default: %default') |
|
73 parser.add_option('-U', '--user', dest='user', metavar='USER', |
|
74 default=getpass.getuser(), |
|
75 help='Connect to the database as the user USER instead of the ' + |
|
76 'default: %default') |
|
77 parser.add_option('-D', '--dovecot', dest='dovecot', metavar='USER', |
|
78 default='dovecot', |
|
79 help='Database user name of the Dovecot database user. Default: ' + |
|
80 '%default') |
|
81 parser.add_option('-M', '--postfix', dest='postfix', metavar='USER', |
|
82 default='postfix', |
|
83 help='Database user name of the Postfix (MTA) database user. ' + |
|
84 'Default: %default') |
|
85 return parser |
|
86 |
|
87 |
|
88 def set_permissions(dbh, dc_vers, dovecot, postfix): |
|
89 dc_rw = ('userquota_11', 'userquota')[dc_vers == 12] |
|
90 dbc = dbh.cursor() |
|
91 dbc.execute('GRANT SELECT ON domain_data, domain_name, mailboxformat, ' |
|
92 'maillocation, quotalimit, service_set, users TO %s' % dovecot) |
|
93 dbc.execute('GRANT SELECT, INSERT, UPDATE, DELETE ON %s TO %s' % |
|
94 (dc_rw, dovecot)) |
|
95 dbc.execute('GRANT SELECT ON alias, catchall, domain_data, domain_name, ' |
|
96 'maillocation, relocated, transport, users TO %s' % postfix) |
|
97 dbc.close() |
|
98 |
|
99 |
|
100 def set_permissions84(dbh, dc_vers, dovecot, postfix): |
|
101 dc_rw_tbls = ('userquota_11', 'userquota')[dc_vers == 12] |
|
102 dc_ro_tbls = 'maillocation, service_set, quotalimit' |
|
103 pf_ro_tbls = 'alias, catchall, relocated, transport' |
|
104 db = dict(dovecot=dovecot, postfix=postfix) |
|
105 db['dovecot_tbls'] = { |
|
106 'domain_data': 'domaindir, gid, qid, ssid', |
|
107 'domain_name': 'domainname, gid', |
|
108 'mailboxformat': 'format', |
|
109 'users': 'gid, local_part, mid, passwd, qid, ssid, uid', |
|
110 } |
|
111 db['postfix_tbls'] = { |
|
112 'domain_data': 'domaindir, gid, tid', |
|
113 'domain_name': 'domainname, gid', |
|
114 'maillocation': 'directory, mid', |
|
115 'users': 'gid, local_part, mid, tid, uid', |
|
116 } |
|
117 dbc = dbh.cursor() |
|
118 dbc.execute('GRANT SELECT, INSERT, UPDATE, DELETE ON %s TO %s' % |
|
119 (dc_rw_tbls, db['dovecot'])) |
|
120 dbc.execute('GRANT SELECT ON %s TO %s' % (dc_ro_tbls, db['dovecot'])) |
|
121 dbc.execute('GRANT SELECT ON %s TO %s' % (pf_ro_tbls, db['postfix'])) |
|
122 for table, columns in db['dovecot_tbls'].iteritems(): |
|
123 dbc.execute('GRANT SELECT (%s) ON %s TO %s' % (columns, table, |
|
124 db['dovecot'])) |
|
125 for table, columns in db['postfix_tbls'].iteritems(): |
|
126 dbc.execute('GRANT SELECT (%s) ON %s TO %s' % (columns, table, |
|
127 db['postfix'])) |
|
128 dbc.close() |
|
129 |
|
130 |
|
131 def set_versions(dbh, versions): |
|
132 dbc = dbh.cursor() |
|
133 if hasattr(dbh, 'server_version'): |
|
134 versions['pgsql'] = dbh.server_version |
|
135 else: |
|
136 try: |
|
137 dbc.execute("SELECT current_setting('server_version_num')") |
|
138 versions['pgsql'] = int(dbc.fetchone()[0]) |
|
139 except DBErr: |
|
140 versions['pgsql'] = 80199 |
|
141 dbc.execute("SELECT relname FROM pg_stat_user_tables WHERE relname LIKE " |
|
142 "'userquota%'") |
|
143 res = dbc.fetchall() |
|
144 dbc.close() |
|
145 tbls = [tbl[0] for tbl in res] |
|
146 if 'userquota' in tbls: |
|
147 versions['dovecot'] = 12 |
|
148 elif 'userquota_11' in tbls: |
|
149 versions['dovecot'] = 11 |
|
150 else: |
|
151 sys.stderr.write('error: no userquota table found\nis "' + dbh.dsn + |
|
152 '" correct? is the database up to date?\n') |
|
153 dbh.close() |
|
154 raise SystemExit(1) |
|
155 |
|
156 |
|
157 if __name__ == '__main__': |
|
158 optparser = get_optparser() |
|
159 opts, args = optparser.parse_args() |
|
160 check_opts(opts, optparser.error) |
|
161 dbh = get_dbh(opts.name, opts.user, opts.dbpass, opts.host, opts.port) |
|
162 versions = {} |
|
163 set_versions(dbh, versions) |
|
164 if versions['pgsql'] < 80400: |
|
165 set_permissions(dbh, versions['dovecot'], opts.dovecot, opts.postfix) |
|
166 else: |
|
167 set_permissions84(dbh, versions['dovecot'], opts.dovecot, opts.postfix) |
|
168 dbh.commit() |
|
169 dbh.close() |