Обсуждение: Quick hack: permissions generator

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

On Mon, Dec 12, 2005 at 02:38:57PM +0200, Marko Kreen wrote:
>
> 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:

Seems neat. I suggest putting it on http://pgfoundry.org.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

On Mon, Dec 12, 2005 at 09:36:27PM -0600, Jim C. Nasby wrote:
> On Mon, Dec 12, 2005 at 02:38:57PM +0200, Marko Kreen wrote:
> >
> > 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:
>
> Seems neat. I suggest putting it on http://pgfoundry.org.

Thanks.  I'll consider it.

--
marko