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