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()