--- a/pgsql/set-permissions.py Mon Mar 24 19:22:04 2014 +0200
+++ /dev/null Thu Jan 01 00:00:00 1970 +0000
@@ -1,169 +0,0 @@
-#!/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()