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