UPGRADE: Added procedure for updating from v0.6.0.
#!/usr/bin/env python# coding: utf-8# Copyright 2012, 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."""importgetpassimportsysfromoptparseimportOptionParserhas_psycopg2=Falsetry:importpsycopg2has_psycopg2=TrueexceptImportError:try:frompyPgSQLimportPgSQLexceptImportError:sys.stderr.write('error: no suitable database module found\n')raiseSystemExit(1)ifhas_psycopg2:DBErr=psycopg2.DatabaseErrorelse:DBErr=PgSQL.libpq.DatabaseErrordefcheck_opts(opts,err_hdlr):ifnotopts.postfix:err_hdlr('missing Postfix database user name')ifnotopts.dovecot:err_hdlr('missing Dovecot database user name')ifopts.askp:opts.dbpass=getpass.getpass()defget_dbh(database,user,password,host,port):ifhas_psycopg2:returnpsycopg2.connect(database=database,user=user,password=password,host=host,port=port)returnPgSQL.connect(user=user,password=password,host=host,database=database,port=port)defget_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')returnparserdefset_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()defset_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']))fortable,columnsindb['dovecot_tbls'].iteritems():dbc.execute('GRANT SELECT (%s) ON %s TO %s'%(columns,table,db['dovecot']))fortable,columnsindb['postfix_tbls'].iteritems():dbc.execute('GRANT SELECT (%s) ON %s TO %s'%(columns,table,db['postfix']))dbc.close()defset_versions(dbh,versions):dbc=dbh.cursor()ifhasattr(dbh,'server_version'):versions['pgsql']=dbh.server_versionelse:try:dbc.execute("SELECT current_setting('server_version_num')")versions['pgsql']=int(dbc.fetchone()[0])exceptDBErr:versions['pgsql']=80199dbc.execute("SELECT relname FROM pg_stat_user_tables WHERE relname LIKE ""'userquota%'")res=dbc.fetchall()dbc.close()tbls=[tbl[0]fortblinres]if'userquota'intbls:versions['dovecot']=12elif'userquota_11'intbls:versions['dovecot']=11else:sys.stderr.write('error: no userquota table found\nis "'+dbh.dsn+'" correct? is the database up to date?\n')dbh.close()raiseSystemExit(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)ifversions['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()