pgsql/set-permissions.py
changeset 571 a4aead244f75
parent 558 2fcf12fdecc2
child 644 d879f915c1c5
child 703 58815c004a61
equal deleted inserted replaced
465:c0e1fb1b0145 571:a4aead244f75
       
     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, postfix_gid, relocated, transport, users TO %s'
       
    97                 % postfix)
       
    98     dbc.close()
       
    99 
       
   100 
       
   101 def set_permissions84(dbh, dc_vers, dovecot, postfix):
       
   102     dc_rw_tbls = ('userquota_11', 'userquota')[dc_vers == 12]
       
   103     dc_ro_tbls = 'mailboxformat, maillocation, service_set, quotalimit'
       
   104     pf_ro_tbls = 'alias, catchall, postfix_gid, relocated, transport'
       
   105     db = dict(dovecot=dovecot, postfix=postfix)
       
   106     db['dovecot_tbls'] = {
       
   107         'domain_data': 'domaindir, gid, qid, ssid',
       
   108         'domain_name': 'domainname, gid',
       
   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()