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