I needed to re-set all permissions on a database as the database access philosophy changed. But as it had a lot of tables, I was losing overview very quick. The original permission script used m4 for SQL generation, but it didn't cut anymore. So I wrote a small Python script which read a .ini-style config file for input. And it ended up being surprisingly clear and effective. As it seems to be generally useful, I post it here. The basic assumption is that user has couple of groups of tables that have somewhat different access pattern for couple of user groups. Small example: [DEFAULT] groups = admins, users [op_tables] tables = op, op_row users = select, insert admins = select [item_tables] tables = item, price, currency, they, can, be, on, several, lines admins = select, insert, update, delete users = select The 'DEFAULT' section is magic section for ConfigParser (Python .ini parser module). It allows internal variable substitution, first searching current section and then the DEFAULT section. So there can be own variables inside config: [DEFAULT] test_tables = table1, table2 [foo] just_test = foo tables = %(test_tables)s, %(just_test)s In addition to 'tables' I also added support for 'functions', 'languages', 'schemas', 'tablespaces'. [db_funcs] functions = foofn(int, text), bazfn(int2, int2) users = execute There is also automatic handling of sequence permissions. If in DEFAULT section is variable 'auto_seq' set to 1, it will generate 'select, update' for all tables' key field. The name of the key field can be changed with variable seq_name. Character '!' after table name negates 'auto_seq' setting for that table. [DEFAULT] auto_seq = 1 seq_name = nr # what perms to give on seqs - default: select, update seq_perm = all [main] tables = main_table, # generates grant for main_table_nr_seq test_table! # no grant will be generated -- marko
#! /usr/bin/env python """Generator for PostgreSQL permissions. ConfigParser docs: http://docs.python.org/lib/module-ConfigParser.html Example: -------------------------------------------------------------------- [DEFAULT] users = user1, user2 # users to handle groups = group1, group2 # groups to handle auto_seq = 0 # dont handle seqs (default) # '!' after a table negates this setting for a table seq_name = id # the name for serial field (default: id) seq_perm = select, update # default permissions for seq # section names can be random, but if you want to see them # in same order as in config file, then order them alphabetically [1.section] tables = testtbl, testtbl_id_seq, # here we handle seq by hand table_with_seq! # handle seq automatically # (table_with_seq_id_seq) user1 = select group1 = select, insert, update # instead of 'tables', you may use 'functions', 'languages', # 'schemas', 'tablespaces' --------------------------------------------------------------------- """ import sys, os, getopt from ConfigParser import ConfigParser def usage(err): sys.stderr.write("usage: genperms [-r|-R] CONF_FILE\n") sys.stderr.write(" -r Generate also REVOKE commands\n") sys.stderr.write(" -R Generate only REVOKE commands\n") sys.exit(err) class PConf(ConfigParser): "List support for ConfigParser" def __init__(self, defaults = None): ConfigParser.__init__(self, defaults) def get_list(self, sect, key): str = self.get(sect, key).strip() res = [] if not str: return res for val in str.split(","): res.append(val.strip()) return res def gen_revoke(obj_str, seq_list, type, subj_list): "Generate revoke for one section / subject type (user or group)" if len(subj_list) == 0: return if type: subj_tmp = [] for subj in subj_list: subj_tmp.append(type + " " + subj) subj_str = ", ".join(subj_tmp) else: subj_str = ", ".join(subj_list) if len(seq_list) > 0: obj_str += ", " + ", ".join(seq_list) print "REVOKE ALL ON %s FROM %s CASCADE;" % (obj_str, subj_str) def gen_one_type(cf, obj_str, seq_list, sect, type, subj_list, seq_perm=''): "Generate GRANT for one section / type (user or group)" seq_allowed = [] for subj in subj_list: if not cf.has_option(sect, subj): continue perm = cf.get(sect, subj) print "GRANT %s ON %s TO %s %s;" % ( perm, obj_str, type, subj) # check for seq perms if len(seq_list) > 0: loperm = perm.lower() if loperm.find("insert") >= 0 or loperm.find("all") >= 0: seq_allowed.append(subj) # if there was any seq perms, generate grants if len(seq_allowed) > 0: seq_str = ", ".join(seq_list) subj_str = ", ".join(seq_allowed) print "GRANT %s ON %s TO %s %s;" % (seq_perm, seq_str, type, subj_str) def handle_other(cf, sect, listname, type, revoke, group_list, user_list): if not cf.has_option(sect, listname): return # don't parse list, as in case of functions it may be complicated obj_str = type + " " + cf.get(sect, listname).strip() if revoke: gen_revoke(obj_str, [], "group", group_list) gen_revoke(obj_str, [], "", user_list) if revoke != 2: gen_one_type(cf, obj_str, [], sect, "group", group_list) gen_one_type(cf, obj_str, [], sect, "", user_list) def handle_tables(cf, sect, revoke, group_list, user_list, auto_seq, seq_name, seq_perm): if not cf.has_option(sect, 'tables'): return table_list = cf.get_list(sect, 'tables') seq_list = [] cleaned_list = [] for table in table_list: if table[-1] == '!': table = table[:-1] if not auto_seq: seq_list.append("%s_%s_seq" % (table, seq_name)) else: if auto_seq: seq_list.append("%s_%s_seq" % (table, seq_name)) cleaned_list.append(table) obj_str = ", ".join(cleaned_list) if revoke: gen_revoke(obj_str, seq_list, "group", group_list) gen_revoke(obj_str, seq_list, "", user_list) if revoke != 2: gen_one_type(cf, obj_str, seq_list, sect, "group", group_list) gen_one_type(cf, obj_str, seq_list, sect, "", user_list) def gen_perms(cf_file, revoke): defs = {'auto_seq': '0', 'seq_name': 'id', 'seq_perm': 'select, update', 'groups': '', 'users': ''} cf = PConf(defs) cf.read(cf_file) group_list = cf.get_list('DEFAULT', 'groups') user_list = cf.get_list('DEFAULT', 'users') auto_seq = cf.getint('DEFAULT', 'auto_seq') seq_name = cf.get('DEFAULT', 'seq_name') seq_perm = cf.get('DEFAULT', 'seq_perm') sect_list = cf.sections() sect_list.sort() for sect in sect_list: handle_tables(cf, sect, revoke, group_list, user_list, auto_seq, seq_name, seq_perm) handle_other(cf, sect, 'functions', 'function', revoke, group_list, user_list) handle_other(cf, sect, 'databases', 'database', revoke, group_list, user_list) handle_other(cf, sect, 'languages', 'language', revoke, group_list, user_list) handle_other(cf, sect, 'schemas', 'schema', revoke, group_list, user_list) handle_other(cf, sect, 'tablespaces', 'tablespace', revoke, group_list, user_list) def main(): revoke = 0 opts, args = getopt.getopt(sys.argv[1:], "hrR") for o, v in opts: if o == "-h": usage(0) elif o == "-r": revoke = 1 elif o == "-R": revoke = 2 if len(args) != 1: usage(1) gen_perms(args[0], revoke) if __name__ == '__main__': main()
